use strict;
use warnings;
use DBI;

# DB configuration
my $dsn = "DBI:mysql:database=test;host=127.0.0.1;port=10672"; 
my $username = "test_user";
my $password = "pass";

my $dbh_w = DBI->connect($dsn, $username, $password, {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1
}) or die "Could not connect to database: " . DBI->errstr;

my $dbh_r = DBI->connect($dsn, $username, $password, {
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1
}) or die "Could not connect to database: " . DBI->errstr;

my $data_table = "_test_index_only_race_condition";

my $sql = qq{
    CREATE TABLE IF NOT EXISTS `$data_table` (
      `_comma_flag` int(10) unsigned NOT NULL,
      `_sq` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `doc_id` int(10) unsigned NOT NULL,
      `field1` varchar(255),
      `created` int(10) unsigned,
      PRIMARY KEY (`doc_id`),
      UNIQUE KEY `_sq` (`_sq`)
      ) ENGINE=Aria CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
};

$dbh_w->do($sql) or die "Could not execute statement: $DBI::errstr";

my $sth_select_max = $dbh_w->prepare( qq[SELECT MAX( _sq ) AS _sq FROM $data_table] );

my $iterations = 5_000;
foreach my $i ( 1..$iterations ) {

  my $doc_id = 0;
  # get lock

  $sth_select_max->execute();
  printf "max _sq: %d\n", $sth_select_max->fetchrow_arrayref()->[0];
  $sth_select_max->finish();

  my $sth_insert = $dbh_w->prepare_cached(
    qq[
        INSERT INTO $data_table
            ( _comma_flag, doc_id, field1, created )
          VALUES
            ( ?, ?, ?, ?  )
    ]);
  my $sth_select = $dbh_r->prepare_cached(
    qq[
        SELECT _sq FROM $data_table
        WHERE doc_id = ?
    ]);
 
  my $sth_update = $dbh_w->prepare_cached(
    qq[
        UPDATE $data_table SET doc_id = ?
        WHERE _sq = ?
    ]);

  $sth_insert->bind_param( 1, 0, DBI::SQL_INTEGER );
  $sth_insert->bind_param( 2, $doc_id, DBI::SQL_INTEGER );
  $sth_insert->bind_param( 3, 'field1' );
  $sth_insert->bind_param( 4, time() );
  $sth_insert->execute();
  $sth_insert->finish();

  $sth_select->bind_param( 1, $doc_id, DBI::SQL_INTEGER );
  $sth_select->execute();
  my $row = $sth_select->fetchrow_arrayref();

  if ( ! defined( $row ) ) {
warn sprintf( "%.5f %s\n", Time::HiRes::time(), 'failed to find row the first time' );
    $sth_select_max->execute();
    printf "max _sq: %d\n", $sth_select_max->fetchrow_arrayref()->[0];
    $sth_select_max->finish();

    sleep 1;
    $sth_select->finish();

    $sth_select->bind_param( 1, $doc_id, DBI::SQL_INTEGER );
    $sth_select->execute();
    $row = $sth_select->fetchrow_arrayref();
    if ( defined( $row ) ) {
warn sprintf( "%.5f %s\n", Time::HiRes::time(), 'found row the second time' );
      die $row->[0];
    } 

warn sprintf( "%.5f %s\n", Time::HiRes::time(), 'failed to find row the second time' );
    $sth_select_max->execute();
    printf "max _sq: %d\n", $sth_select_max->fetchrow_arrayref()->[0];
    $sth_select_max->finish();
    die;
  }

  $doc_id = $row->[0];
  $sth_select->finish();

  $sth_update->bind_param( 1, $doc_id, DBI::SQL_INTEGER );
  $sth_update->bind_param( 2, $doc_id, DBI::SQL_INTEGER );
  my $ret = $sth_update->execute();
  $sth_update->finish();

  if ( ! $ret ) {
warn sprintf( "%.5f %s\n", Time::HiRes::time(), 'failed to update row the first time' );

    $sth_select->bind_param( 1, $doc_id, DBI::SQL_INTEGER );
    $ret = $sth_select->execute();
    my $row = $sth_select->fetchrow_arrayref();
    die join( ', ', $row, @{$row || []} );
  }
  # release lock
}

$sth_select_max->execute();
printf "max _sq: %d\n", $sth_select_max->fetchrow_arrayref()->[0];
$sth_select_max->finish();