Performing Transactions - MySQL - page 572

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. To display an error message, print $@ before calling rollback() .
5. If desired, restore the original values of the RaiseError and PrintError attributes.
Because it can be messy to change and restore the error-handling and auto-commit
attributes if an application performs multiple transactions, let's put the code to begin
and end a transaction into convenience functions that 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" ;