Transactions in Practice

The most convenient way to use transactions with the Perl DBI is to enclose all of the Perl constructs associated with the transaction in a single eval{} block and to call die if any failures occur. In this way, both database errors and other failures of the program can be treated together without further effort from the programmer.

Note that not all undesired conditions constitute database or Perl errors. Selecting rows from a table will not fail even if no rows match the selection criteria, for example. In cases such as this, it is most convenient to check for problems explicitly with Perl code and to raise an error deliberately with die if they are detected. This then invokes the error handling procedure, which (as you will see) can take appropriate action.

The objective of this example program is to build a list of publishers, and the number of books in the library by each publisher, in the table pubslist (which should contain "publisher" and "count" fields).

01: #! /usr/bin/perl -w 02: # 03: # transaction.pl 04: # Chapter 11 05: # Listing 1 06: #   07: use strict; 08: use DBI;

Lines 1-8 are standard for a database program.

09: my $dbh = DBI->connect ("DBI:mysql:BibleBook","bookuser","testpass", 10:          {AutoCommit => 0, RaiseError => 1}) 11:  or die("Cannot connect: $DBI::errstr");

Lines 9-11 extend the connection command to allow the setting of AutoCommit (as discussed previously) and RaiseError, which causes the DBI to return a fatal error when a database command fails.

12: $dbh->begin_work; 

Line 12 informs the database that a transaction is about to take place.

13:  eval {

Line 13 is the most important innovation. Within this block, fatal errors do not take effect as normal (causing the program to terminate immediately); instead, the block is terminated, and program flow continues.

14:   $dbh->do("DELETE FROM pubslist");

Line 14 drops the old list of publishers.

15:    my $sth=$dbh->prepare("SELECT COUNT(*),publisher FROM library 16:                          GROUP BY publisher"); 17:   $sth->execute;

Lines 15-17 should be familiar by now. Note that there is no need to trap specific errors. Any problem, be it a database error or resource exhaustion on the client, raises a fatal error, which is dealt with automatically.

18:   while (my ($count,$publisher)=$sth->fetchrow_array) { 19:     my $publisherq=$dbh->quote($publisher); 20:     $dbh->do("INSERT INTO pubslist (publisher,count) 21:               VALUES ($publisherq,$count)"); 22:   }

Lines 18-22 insert each data point in the other table. Again, this should be familiar material.

23:   $sth->finish;

Line 23 cleans up after the statement.

24: };

Line 24 is the end of the eval block. Errors that occur after this point are treated normally.

25: if ($@) {

Line 25 determines whether any errors have occurred. Whenever you have an eval block, if there were any errors, the $@ variable gets set to a true value.

26:   $dbh->rollback; 

Line 26, if errors have occurred, abandons the entire operation. This is also the point at which to provide any other error-recovery code the program requires, based on the type of error that has occurred. Something like an email notification or writing to an error log.

27: } 28: else { 29:   $dbh->commit;

Line 27 ends the if portion of the if..else block.

Line 28 begins the else portion of the if..else block. This block gets entered if there were no errors in the eval block.

Line 29 otherwise commits all changes to the database.

30: } 31: $dbh->disconnect;

Line 30 ends the if..else block.

Line 31 shuts down the database connection.

An alternative method of handling transactions is not to set the RaiseError attribute but to test for errors after each call:

$dbh->prepare($sql) or die $dbh->errstr;

This is useful only when some database calls are expected to return errors and when these errors are not fatal to the processing of the transaction.

It is also not necessary to use the eval{} block construct. Test each call with:

my $errorstatus=0; $dbh->prepare($sql) or $errorstatus=1;

Then check $errorstatus when the transaction has been completed. However, this means that fatal errors elsewhere in the code have not been handled normally; they cause an implicit rollback, as the program has disconnected automatically on termination, but do not allow for any other clean up.

As a general rule, eval{} block and RaiseError are the cleanest methods for using transactions and should be used unless there is an overpowering reason to do otherwise.