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 wandering the Monastery: (7)
As of 2014-08-30 00:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (289 votes), past polls