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.