Beefy Boxes and Bandwidth Generously Provided by pair Networks BBQ
Just another Perl shrine
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??

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.


In reply to Re: Testing transactions by kyle
in thread Testing transactions by matija

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others wandering the Monastery: (8)
    As of 2014-04-17 01:20 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      April first is:







      Results (437 votes), past polls