MySQL Cookbook, 2nd Edition

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:

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, and 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 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 ...