use strict;
use warnings;
use DBI;
use Time::HiRes;

$|++;

# 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` timestamp(6),
      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_r->prepare_cached( qq[
  SELECT
    MAX( _sq ) AS _sq,
    CURRENT_TIMESTAMP(6) AS `now`
  FROM
    $data_table
] );

my $sth_insert = $dbh_w->prepare_cached( qq[
  INSERT INTO $data_table
    ( _comma_flag, doc_id, field1, created )
  VALUES
    ( ?, ?, ?, CURRENT_TIMESTAMP(6) )
] );

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 = ?
] );


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

  my $doc_id = 0;
  # get lock

  $sth_select_max->execute();
  printf "max _sq: %d, now: %s\n", @{$sth_select_max->fetchrow_arrayref()};
  $sth_select_max->finish();

  # trace this
  $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->execute();
  $sth_insert->finish();


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


  my $max_tries = 10_000;
  my $j = 1;
  while  ( ! defined( $row ) ) {
warn sprintf( "%.5f failed to find row on attempt number %d\n", Time::HiRes::time(), $j );
    $sth_select_max->execute();
    printf "max _sq: %d, now: %s\n", @{$sth_select_max->fetchrow_arrayref()};
    $sth_select_max->finish();


    $sth_select->finish();

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

    $j++;
    last if $j >= $max_tries;
  }

  $sth_select_max->execute();
  printf "max _sq: %d, now: %s\n", @{$sth_select_max->fetchrow_arrayref()};
  $sth_select_max->finish();

  if ( ! defined( $row ) ) {
warn sprintf( "%.5f failed to find row after attempt number %d\n", Time::HiRes::time(), $j );
    die;
  }

warn sprintf( "%.5f found row after attempt number %d\n", Time::HiRes::time(), $j );
  die $row->[0] if $j > 1;

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

  # trace this
  $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' );

    # trace this
    $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, now: %s\n", @{$sth_select_max->fetchrow_arrayref()};
$sth_select_max->finish();