Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
PerlMonks  

Re: Testing transactions

by kyle (Abbot)
on Jan 24, 2008 at 16:37 UTC ( #664069=note: print w/ replies, xml ) Need Help??


in reply to Testing transactions

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.

I did this testing with PostgreSQL (DBD::Pg). When I run the original script, every test passes, but again, what I'm posting here is modified in various ways, some more obvious than others.


Comment on Re: Testing transactions
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://664069]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (12)
As of 2015-07-03 06:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (48 votes), past polls