transaction.pl

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

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

{
	my $dbh = 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,
	});
	$dbh->do(q{
		CREATE TABLE sample (
			k INTEGER UNSIGNED NOT NULL,
			v INTEGER UNSIGNED NOT NULL,
			PRIMARY KEY (k)
		);
	});
	$dbh->do(q{INSERT INTO sample (k, v) VALUES (1, 0) });
}

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

# atomic
			$dbh->do(q{UPDATE sample SET v = v + 1 WHERE k = 1});

# for update
#			my $row = $dbh->selectrow_hashref(q{SELECT * FROM sample WHERE k = 1 FOR UPDATE});
#			$dbh->do(q{UPDATE sample 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;

$dbh = 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,
});

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

__DATA__
$ perl /tmp/atomic.pl
2012-12-30T15:52:02 [INFO] starting benchmark: concurrency: 10, time: 3
2012-12-30T15:52:07 [INFO] done benchmark: score 300, elapsed 3.010 sec = 99.676 / sec
$VAR1 = {
          'stashes' => {
                         '6' => {},
                         '3' => {},
                         '7' => {},
                         '9' => {},
                         '2' => {},
                         '8' => {},
                         '4' => {},
                         '1' => {},
                         '10' => {},
                         '5' => {}
                       },
          'score' => 300,
          'elapsed' => '3.0097447'
        };
$VAR1 = {
          'k' => '1',
          'v' => '300'
        };

$ perl  /tmp/for-update.pl
2012-12-30T15:53:20 [INFO] starting benchmark: concurrency: 10, time: 3
2012-12-30T15:53:25 [INFO] done benchmark: score 300, elapsed 3.016 sec = 99.475 / sec
$VAR1 = {
          'stashes' => {
                         '6' => {},
                         '3' => {},
                         '7' => {},
                         '9' => {},
                         '2' => {},
                         '8' => {},
                         '1' => {},
                         '4' => {},
                         '10' => {},
                         '5' => {}
                       },
          'score' => 300,
          'elapsed' => '3.0158181'
        };
$VAR1 = {
          'k' => '1',
          'v' => '232'
        };