Recipe 15.4. Using Transactions in Perl Programs

Problem

You want to perform a transaction in a Perl DBI script.

Solution

Use the standard DBI transaction support mechanism.

Discussion

The Perl DBI transaction mechanism is based on explicit manipulation of auto-commit mode:

Turn on the RaiseError attribute if it's not enabled and disable PrintError if it's on. You want errors to raise exceptions without printing anything, and leaving PrintError enabled can interfere with failure detection in some cases.
Disable the AutoCommit attribute so that a commit will be done only when you say so.
Execute the statements that make up the transaction within an eval block so that errors raise an exception and terminate the block. The last thing in the block should be a call to commit⁠(⁠ ⁠ ⁠), which commits the transaction if all its statements completed successfully.
After the eval executes, check the $@ variable. If $@ contains the empty string, the transaction succeeded. Otherwise, the eval will have failed due to the occurrence of some error and $@ will contain an error message. Invoke rollback⁠(⁠ ⁠ ⁠) to cancel the transaction. If you want to display an error message, print $@ before calling rollback⁠(⁠ ⁠ ⁠).

The following code shows how to follow those steps to perform our sample transaction:

# set error-handling and auto-commit attributes correctly $dbh->{RaiseError} = 1; # raise exception if an error occurs $dbh->{PrintError} = 0; # don't print an error message $dbh->{AutoCommit} = 0; # disable auto-commit eval {   # move some money from one person to the other   $dbh->do ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");   $dbh->do ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");   # all statements succeeded; commit transaction   $dbh->commit (); }; if ($@) # an error occurred {   print "Transaction failed, rolling back. Error was:\n$@\n";   # roll back within eval to prevent rollback   # failure from terminating the script   eval { $dbh->rollback (); }; }

The code shown does not save the current values of the error-handling and auto-commit attributes before executing the transaction or restore them afterward. If you save and restore them, your transaction-handling code becomes more general because it does not affect other parts of your program that might use different attribute values, but more lines of code are required. To make transaction processing easier (while avoiding repetition of the extra code if you execute multiple transactions), create a couple of convenience functions to handle the processing that occurs before and after the eval:

sub transaction_init { my $dbh = shift; my $attr_ref = {};  # create hash in which to save attributes   $attr_ref->{RaiseError} = $dbh->{RaiseError};   $attr_ref->{PrintError} = $dbh->{PrintError};   $attr_ref->{AutoCommit} = $dbh->{AutoCommit};   $dbh->{RaiseError} = 1; # raise exception if an error occurs   $dbh->{PrintError} = 0; # don't print an error message   $dbh->{AutoCommit} = 0; # disable auto-commit   return ($attr_ref);   # return attributes to caller } sub transaction_finish { my ($dbh, $attr_ref, $error) = @_;   if ($error) # an error occurred   {     print "Transaction failed, rolling back. Error was:\n$error\n";     # roll back within eval to prevent rollback     # failure from terminating the script     eval { $dbh->rollback (); };   }   # restore error-handling and auto-commit attributes   $dbh->{AutoCommit} = $attr_ref->{AutoCommit};   $dbh->{PrintError} = $attr_ref->{PrintError};   $dbh->{RaiseError} = $attr_ref->{RaiseError}; }

By using those two functions, our sample transaction can be simplified considerably:

$ref = transaction_init ($dbh); eval {   # move some money from one person to the other   $dbh->do ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");   $dbh->do ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");   # all statements succeeded; commit transaction   $dbh->commit (); }; transaction_finish ($dbh, $ref, $@);

In Perl DBI, an alternative to manipulating the AutoCommit attribute manually is to begin a transaction by invoking begin_work⁠(⁠ ⁠ ⁠). This method disables AutoCommit and causes it to be enabled again automatically when you invoke commit⁠(⁠ ⁠ ⁠) or rollback⁠(⁠ ⁠ ⁠) later.