for-update-3.pl

use Parallel::Benchmark;
use Test::mysqld;

my $mysqld = Test::mysqld->new(
  my_cnf => {
		'skip-networking' => '',
	},
);

{
	my $dbh = dbh();
	$dbh->do(q{
		CREATE TABLE t1 (
			k INTEGER UNSIGNED NOT NULL,
			v INTEGER UNSIGNED NOT NULL,
			PRIMARY KEY (k)
		) ENGINE=INNODB;
	});
	$dbh->do(q{INSERT INTO t1 (k, v) VALUES (1, 0) });
}

my $bm = Parallel::Benchmark->new(
	setup => sub {
		my $self = shift;
		$self->stash->{dbh} = dbh();
	},
	teardown => sub {
		my $self = shift;
		delete $self->stash->{dbh};
	},
	benchmark => sub {
		my $self = shift;
		my $dbh = $self->stash->{dbh};
		eval {
			$dbh->begin_work;

			my $row = $dbh->selectrow_hashref(q{SELECT * FROM t1 WHERE k = 1 LOCK IN SHARE MODE});
			$dbh->do(q{UPDATE t1 SET v = ? WHERE k = 1}, {}, $row->{v} + 1);
		};
		if ($@) {
			warn $!;
			$dbh->rollback;
		} else {
			$dbh->commit;
		}
		select undef, undef, undef, 0.1;
		1;
	},
	concurrency => 10,
#	debug => 1,
);

my $result = $bm->run();

use Data::Dumper; warn Dumper $result;

use Data::Dumper; warn Dumper dbh()->selectrow_hashref(q{SELECT * FROM t1 WHERE k = 1});

sub dbh {
	return DBI->connect($mysqld->dsn(dbname => 'test'), 'root', '', {
		AutoCommit           => 1,
		PrintError           => 0,
		RaiseError           => 1,
		ShowErrorStatement   => 1,
		AutoInactiveDestroy  => 1,
	#	mysql_enable_utf8    => 1,
	#	mysql_auto_reconnect => 0,
	});
}

=comment
$ perl /tmp/for-update-3.pl 
2012-12-30T17:13:20 [INFO] starting benchmark: concurrency: 10, time: 3
DBD::mysql::db do failed: Deadlock found when trying to get lock; try restarting transaction [for Statement "UPDATE t1 SET v = ? WHERE k = 1"] at /tmp/for-update-3.pl line 38.
	...caught at /tmp/for-update-3.pl line 41.
DBD::mysql::db do failed: Deadlock found when trying to get lock; try restarting transaction [for Statement "UPDATE t1 SET v = ? WHERE k = 1"] at /tmp/for-update-3.pl line 38.
	...caught at /tmp/for-update-3.pl line 41.
DBD::mysql::db do failed: Deadlock found when trying to get lock; try restarting transaction [for Statement "UPDATE t1 SET v = ? WHERE k = 1"] at /tmp/for-update-3.pl line 38.
	...caught at /tmp/for-update-3.pl line 41.
.
.
.
2012-12-30T17:13:25 [INFO] done benchmark: score 300, elapsed 3.013 sec = 99.582 / sec
$VAR1 = {
          'stashes' => {
                         '6' => {},
                         '3' => {},
                         '7' => {},
                         '9' => {},
                         '2' => {},
                         '8' => {},
                         '1' => {},
                         '4' => {},
                         '10' => {},
                         '5' => {}
                       },
          'score' => 300,
          'elapsed' => '3.0125781'
        };
$VAR1 = {
          'k' => '1',
          'v' => '245'
        };
=cut