MySQL Cookbook

Using Transactions in Perl Programs

Problem

You want to perform a transaction in a DBI script.

Solution

Use the standard DBI transaction support mechanism.

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