When I was first learning about transactions, I wrote a test script to understand how they behave. I'm posting here a stripped down version of that script.
#!/usr/bin/perl
use warnings;
use Test::More skip_all => 'tests to teach me about transactions--done
+';
use Class::Std::Utils;
my $dbh = DBI->connect( die 'connect info goes here' );
die 'I had code here to ensure I was connected to the test database, n
+ot production.';
# CREATE TABLE ( itemnum serial, item varchar(100) );
# CREATE UNIQUE INDEX items_itemnum ON items (itemnum);
# CREATE UNIQUE INDEX items_item ON items (item);
# TESTING BEGINS.
ok( $dbh->{AutoCommit}, 'AutoCommit is on' );
my $item_there;
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.1'" );
is( $item_there, 0, 'no item in table' );
$dbh->do( "INSERT INTO items (item) VALUES ('test.1')" );
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.1'" );
is( $item_there, 1, 'item in table' );
ok( $dbh->begin_work, 'begin_work succeeds' );
ok( !$dbh->{AutoCommit}, 'AutoCommit is off' );
eval {
$dbh->do( "INSERT INTO items (item) VALUES ('test.1')" );
};
ok( $@, 'duplicate insert fails' );
ok( $@ =~ /duplicate key violates unique constraint/,
'duplicate insert fails for the expected reason' );
ok( $dbh->rollback, 'rollback succeeds' );
ok( $dbh->{AutoCommit}, 'AutoCommit is on' );
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.1'" );
is( $item_there, 1, 'item 1 in table' );
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.2'" );
is( $item_there, 0, 'item 2 not in table' );
ok( $dbh->begin_work, 'begin_work succeeds' );
eval {
$dbh->do( "INSERT INTO items (item) VALUES ('test.2')" );
$dbh->do( "INSERT INTO items (item) VALUES ('test.1')" );
};
ok( $@, 'duplicate insert after nondupe fails' );
ok( $@ =~ /duplicate key violates unique constraint/,
'duplicate insert after nondupe fails for the expected reason' );
# CAN'T SELECT during an aborted transaction.
# ($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items W
+HERE item = 'test.1'" );
# is( $item_there, 1, 'item 1 in table before rollback' );
# ($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items W
+HERE item = 'test.2'" );
# is( $item_there, 0, 'item 2 not in table before rollback' );
ok( $dbh->rollback, 'rollback succeeds' );
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.1'" );
is( $item_there, 1, 'item 1 in table after rollback' );
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.2'" );
is( $item_there, 0, 'item 2 not in table after rollback' );
ok( $dbh->begin_work, 'begin_work succeeds' );
eval {
$dbh->do( "INSERT INTO items (item) VALUES ('test.2')" );
$dbh->do( "INSERT INTO items (item) VALUES ('test.1')" );
};
ok( $@, 'duplicate insert after nondupe fails' );
ok( $@ =~ /duplicate key violates unique constraint/,
'duplicate insert after nondupe fails for the expected reason' );
ok( $dbh->commit, 'commit succeeds after aborted transaction' );
# After a transaction aborts, it doesn't matter whether I commit or ro
+llback
# They both succeed, and the transaction does not happen.
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.1'" );
is( $item_there, 1, 'item 1 in table after commit' );
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.2'" );
is( $item_there, 0, 'item 2 not in table after commit' );
ok( $dbh->begin_work, 'begin_work succeeds' );
eval {
$dbh->do( "INSERT INTO items (item) VALUES ('test.2')" );
};
ok( !$@, 'no eval error after item 2 insert' );
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.1'" );
is( $item_there, 1, 'item 1 in table during item 2 transaction' );
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.2'" );
is( $item_there, 1, 'item 2 in table during item 2 transaction' );
ok( $dbh->rollback, 'rollback succeeds' );
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.1'" );
is( $item_there, 1, 'item 1 in table after item 2 insert rollback' );
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.2'" );
is( $item_there, 0, 'item 2 not in table after item 2 insert rollback'
+ );
ok( $dbh->begin_work, 'begin_work succeeds' );
eval {
$dbh->do( "INSERT INTO items (item) VALUES ('test.2')" );
};
ok( !$@, 'no eval error after item 2 full insert' );
ok( $dbh->commit, 'commit succeeds' );
($item_there) = $dbh->selectrow_array( "SELECT count(*) FROM items WHE
+RE item = 'test.2'" );
is( $item_there, 1, 'item 2 in table after item 2 insert commit' );
####
#### ouchy tests.
####
my $dbh2 = DBI->connect( die 'connect info goes here' );
ok( $dbh2, 'dbh2' );
ok( ident $dbh2 != ident $dbh, 'dbh2 is different from dbh' );
ok( $dbh->begin_work, 'dbh1 begin_work succeeds' );
ok( $dbh2->begin_work, 'dbh2 begin_work succeeds' );
eval {
$dbh->do( "INSERT INTO items (item) VALUES ('test.3')" );
};
ok( !$@, 'dbh insert succeeds' );
warn $@ if $@;
# The second transaction BLOCKS until the first one commits.
# I guess Pg has to wait and see if it will commit or rollback before
+it knows
# whether the second one will succeed.
if ( !fork() ) {
# I am child
eval {
$dbh2->do( "INSERT INTO items (item) VALUES ('test.3')" );
};
# ok( sleep 1, 'child sleep 1' );
ok( $@, "child duplicate insert transaction causes error" );
ok( $dbh2->commit, 'child dbh2 commit succeeds' );
exit;
}
ok( sleep 2, 'parent slept' );
ok( $dbh->commit, 'parent dbh commit succeeds' );
ok( wait, 'parent waited' );
ok( $dbh2->disconnect(), 'dbh2 disconnect succeeded' );
What you might find most useful to what you're doing is one of the last tests in there. In it, I start a transaction on one database connection and then fork to try something on another database connection. The parent sleeps to ensure that the child has a chance to try what it's trying. This is not exactly a "race condition", but it does show what happens when two transactions want the same resource at the same time—one of them waits. In the particular case that I tested, they were both trying to insert the same value in a unique field. The child transaction had to wait to see if the parent committed before it knew if it could succeed.