Using Transactions in Perl Programs

15.5.1 Problem

You want to perform a transaction in a DBI script.

15.5.2 Solution

Use the standard DBI transaction support mechanism.

15.5.3 Discussion

The DBI mechanism for performing transactions is based on explicit manipulation of auto-commit mode. The procedure is as follows:

1. 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; leaving PrintError enabled can interfere with failure detection in some cases.
2. Disable the AutoCommit attribute so that a commit will be done only when you say so.
3. 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.
4. 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 implement this procedure to perform our example transaction. It does so in such a way that the current values of the error-handling and auto-commit attributes are saved before and restored after executing the transaction. That may be overkill for your own applications. For example, if you know that RaiseError and PrintError are set properly already, you need not save or restore them.

# save error-handling and auto-commit attributes,
# then make sure they're set correctly.
$save_re = $dbh->{RaiseError};
$save_pe = $dbh->{PrintError};
$save_ac = $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

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:
$@
";
 # roll back within eval to prevent rollback
 # failure from terminating the script
 eval { $dbh->rollback ( ); };
}

# restore attributes to original state
$dbh->{AutoCommit} = $save_ac;
$dbh->{PrintError} = $save_pe;
$dbh->{RaiseError} = $save_re;

You can see that the example goes to a lot of work just to issue a couple of statements. To make transaction processing easier, you might want to create a couple of convenience functions to handle the processing that occurs before and after the eval:

sub transact_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 transact_finish
{
my ($dbh, $attr_ref, $error) = @_;

 if ($error) # an error occurred
 {
 print "Transaction failed, rolling back. Error was:
$error
";
 # 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 example transaction can be simplified considerably:

$ref = transact_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 ( );
};
transact_finish ($dbh, $ref, $@);

As of DBI 1.20, 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.

Transactions and Older Versions of DBD mysql

The DBI transaction mechanism requires DBD::mysql 1.2216 or newer. For earlier versions, setting the AutoCommit attribute has no effect, so you'll need to issue the transaction-related SQL statements yourself (BEGIN, COMMIT, ROLLBACK).