MySQL Cookbook

15.5 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:

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:\n$@\n";
    # 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:\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 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).