Recipe 14.15 Using Transactions

14.15.1 Problem

A single change to the database requires several INSERT, UPDATE, or DELETE commands in SQL. For example, you might have to add a person to the People table, add an address to the Address table, and add a link between them to the LivesAt table. The database is in a logically inconsistent state from the first insert until the last completes. If another client queries the database, it will get inconsistent data (e.g., there'll be a Person with no address).

You want to perform the update in such a way that another client never sees an inconsistent database—either all of the changes or none of them should be visible in the database during and after the changes, regardless of any client or server failures during processing.

14.15.2 Solution

Use transactions. The DBI supports these via the commit and rollback methods on a database handle. Use them thus:

$dbh->{AutoCommit} = 0; # enable transactions
$dbh->{RaiseError} = 1; # die( ) if a query has problems

eval {
  # do inserts, updates, deletes, queries here
  $dbh->commit( );
};

if ($@) {
  warn "Transaction aborted: $@";
  eval { $dbh->rollback( ) };   # in case rollback( ) fails
  # do your application cleanup here
}

14.15.3 Discussion

The AutoCommit option controls whether the database commits each change as soon as you issue the command. When AutoCommit is disabled, the database won't update until you call the commit method. If midway through the series of updates you change your mind or an error occurs, the rollback method undoes all pending changes.

You don't have to explicitly set the AutoCommit and RaiseError attributes before each transaction. For convenience, set those attributes in the connect call:

$dbh = DBI->connect($dsn, $username, $password,
                    { AutoCommit => 0, RaiseError => 1 });

Because RaiseError causes DBI to call die whenever a database operation fails, you break out of the eval if any database operation fails while the eval is in effect (even if from within the eval you call a function that accesses the database).

Always explicitly call commit or rollback to end a transaction. Different databases react differently if you disconnect with an unfinished transaction. Some (for example, Oracle and Ingres) commit the transaction, while some (MySQL, Informix) rollback.

When a database handle commits or rolls back a transaction, many database drivers invalidate any active statement handles from that database handle. For example:

$sth = $dbh->prepare(...);
$sth->execute( );
eval {
  $dbh->do(...);
  $dbh->commit;
};
if ($@) { eval { $dbh->rollback } }
while (@row = $sth->fetchrow_array) { ... }  # may not work

The last line is not guaranteed to work across database handles, as the acts of committing and rolling back may invalidate the statement handle in $sth. The standard solution to this is to create two database handles for the database (by calling connect twice) and use one handle for all SELECT statements.

14.15.4 See Also

The documentation with the DBI module from CPAN; http://dbi.perl.org; Programming the Perl DBI