لطفا منتظر باشید ...
 |  |
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
|  | |
| 14.14. Finding the Number of Rows Returned by a Query |  | 14.16. Viewing Data One Page at a Time |
Copyright © 2003 O'Reilly & Associates. All rights reserved.
