Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

comment on

( [id://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":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others taking refuge in the Monastery: (4)
As of 2024-03-29 10:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found