Using Transactions (Perl Cookbook, 2nd Edition)

## 14.15. Using Transactions

### 14.15.1. Problem

A single change to the database requires several INSERT, UPDATE, or DELETE commands in SQL. For example, you might have to add a person to the People table, add an address to the Address table, and add a link between them to the LivesAt table. The database is in a logically inconsistent state from the first insert until the last completes. If another client queries the database, it will get inconsistent data (e.g., there'll be a Person with no address).

You want to perform the update in such a way that another client never sees an inconsistent database—either all of the changes or none of them should be visible in the database during and after the changes, regardless of any client or server failures during processing.

### 14.15.2. Solution

Use transactions. The DBI supports these via the `commit` and `rollback` methods on a database handle. Use them thus:

>     $dbh->{AutoCommit} = 0; # enable transactions
>     $dbh->{RaiseError} = 1; # die( ) if a query has problems

>     eval {
>       # do inserts, updates, deletes, queries here
>       $dbh->commit( );
>     };

>     if ($@) {
>       warn "Transaction aborted: $@";
>       eval { $dbh->rollback( ) };   # in case rollback( ) fails
>       # do your application cleanup here
>     }

### 14.15.3. Discussion

The AutoCommit option controls whether the database commits each change as soon as you issue the command. When AutoCommit is disabled, the database won't update until you call the `commit` method. If midway through the series of updates you change your mind or an error occurs, the `rollback` method undoes all pending changes.

You don't have to explicitly set the AutoCommit and RaiseError attributes before each transaction. For convenience, set those attributes in the `connect` call:

>     $dbh = DBI->connect($dsn, $username, $password,
>                         { AutoCommit => 0, RaiseError => 1 });

Because RaiseError causes DBI to call `die` whenever a database operation fails, you break out of the `eval` if any database operation fails while the `eval` is in effect (even if from within the `eval` you call a function that accesses the database).

Always explicitly call `commit` or `rollback` to end a transaction. Different databases react differently if you `disconnect` with an unfinished transaction. Some (for example, Oracle and Ingres) `commit` the transaction, while some (MySQL, Informix) `rollback`.

When a database handle commits or rolls back a transaction, many database drivers invalidate any active statement handles from that database handle. For example:

>     $sth = $dbh->prepare(...);
>     $sth->execute( );
>     eval {
>       $dbh->do(...);
>       $dbh->commit;
>     };
>     if ($@) { eval { $dbh->rollback } }
>     while (@row = $sth->fetchrow_array) { ... }  # may not work

The last line is not guaranteed to work across database handles, as the acts of committing and rolling back may invalidate the statement handle in `$sth`. The standard solution to this is to create two database handles for the database (by calling `connect` twice) and use one handle for all SELECT statements.

### 14.15.4. See Also

The documentation with the DBI module from CPAN; http://dbi.perl.org; Programming the Perl DBI

Copyright © 2003 O'Reilly & Associates. All rights reserved.