Transactions and Perl

Transactions in the context of the Perl DBI are slightly restricted, in that it is not possible for a transaction to persist for longer than the duration of a single connection to the database. In general, this does not present a problem. When it does present a problem, the problem can be solved by reformulating the flow of control or by using persistent database handles.

In fact, this can be advantageous. Since it is possible to have multiple connections to the same database, UPDATEs and INSERTs can be taking place in a transactional environment while SELECTs are treated through a different database handle. This is useful when the program is looping through the results of a SELECT statement and making changes to the database based on those results. Many databases terminate active SELECTs when a transaction is committed on the same database handle. Using a separate handle, although doing so may increase program startup time, solves this problem.

When a connection is established by using the DBI->connect method, the AutoCommit parameter defaults to 1 (that is, each database command exists in its own transaction and takes effect immediately). This is not guaranteed to be the case in future versions of the DBI module, however. It is a good habit to specify AutoCommit explicitly. This parameter is specified as a hash reference in the fourth position of the connect function:

my $dbh = DBI->connect   ("DBI:mysql:BibleBook","bookuser","testpass",                        {AutoCommit => 1})           or die("Cannot connect: $DBI::errstr");

AutoCommit can also be changed after the connection has been made:

$dbh->{AutoCommit}=1;

To enable transactions, simply set AutoCommit to 0 either in the DBI->connect call or in a later explicit call.

The actual transaction controls are as follows.

BEGIN

The BEGIN statement is rarely, if ever, explicitly required in a transactional database. However, since the DBI module makes transaction use optional, it is required that all clients initiate transactional statements with the begin_work method:

$dbh->begin_work       or die("Database does not support transactions"); 

If AutoCommit is on, it will be turned off until the next commit or rollback call (see the following). If the database does not support transactions, begin-work returns a fatal error.

Some databases may, separately, require a BEGIN statement; check the documentation. For PostgreSQL, send:

$dbh->do(‘BEGIN');

COMMIT

A COMMIT is explicitly required when operating in transactional mode. COMMIT defines the successful end of the transaction (and, if AutoCommit is 0, a return to single-statement mode). It is implemented with the commit method of DBI:

$dbh->commit;

It is well worth ensuring that all possible checks have been performed before issuing a COMMIT; otherwise, many of the advantages of the transactional system are lost.

If for some reason the COMMIT fails, such as the database has suddenly crashed, the standard DBI->errstr method will show the error. (This will also happen if a COMMIT is issued while the database connection is in single-statement mode.)

ROLLBACK

Often, client software issues a ROLLBACK when part of a transaction has failed. It is implemented with DBI's ROLLBACK method:

$dbh->rollback;

As with the COMMIT method, issuing a rollback returns the database connection to single-statement mode if AutoCommit is 0. If the ROLLBACK fails, which should only happen if the database is already in single-statement mode (or, in MySQL, if some nontransaction-safe tables are involved in the transaction), an error will be raised.

If connectivity is lost during a transaction, the database server assumes a ROLLBACK. There is one exception to this: if the program disconnects explicitly (using the DBI->disconnect method) with a transaction outstanding, some databases (notably Oracle and Ingres) will automatically commit outstanding statements; others, such as Informix, will roll back instead. Always specify explicitly what should be done with an outstanding transaction before disconnecting.

Note 

If the program disconnects due to an error condition, the database driver will always send a ROLLBACK.