Transactions in MySQL
- MySQL and DBI Transaction Syntax
- Requirements for Transaction Support
- Conclusion
- About This Article
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
MySQL has recently acquired a long-awaited feature—transactions. When you have multiple tables involved in your database activity, you want them to be updated together. However, it's possible that the updates will succeed for one table and fail for the other. This would leave the tables in an inconsistent state. The way to handle this is to use a transaction—a set of statements that succeed or fail as a unit. This capability is now available in MySQL.
MySQL and DBI Transaction Syntax
To execute a group of statements as a transaction, you must manipulate MySQL's statement committal mode. By default, MySQL runs in auto-commit mode: Changes produced by individual statements are committed to the database immediately, as soon as they execute. (In other words, it's as if a COMMIT statement implicitly follows each statement.) DBI also runs in auto-commit mode by default.
MySQL's auto-commit mode is affected by the following two SQL statements:
SET AUTOCOMMIT = 0 # disable auto-commit SET AUTOCOMMIT = 1 # enable auto-commit
While auto-commit is disabled, you begin a transaction with BEGIN and end it with COMMIT, like this:
BEGIN INSERT INTO table1 ... INSERT INTO table2 ... COMMIT
If an error occurs during the transaction, you can roll back the affect of all its constituent statements by issuing a ROLLBACK statement.
Unfortunately, the preceding syntax isn't necessarily portable to other transaction-capable databases, which may use somewhat different SQL statements from what MySQL uses. To deal with this problem, DBI provides a transaction abstraction for better portability. To turn auto-commit mode on or off from within a DBI script, set the AutoCommit attribute of your database handle:
$dbh->{AutoCommit} = 0; # disable auto-commit
$dbh->{AutoCommit} = 1; # enable auto-commit
To execute a multiple-statement transaction with auto-commit disabled, issue the statements, and then invoke commit() to commit the statements as a group:
$dbh->do ("INSERT INTO table1 ...");
$dbh->do ("INSERT INTO table2 ...");
$dbh->commit ();
(Another way to end a successful transaction is to set the AutoCommit attribute true again; this causes commit() to be called automatically for any pending transaction.) If an error occurs during the transaction, invoke the rollback() method to abort it.
The effect of closing a database handle while a transaction is pending is undefined in DBI, so be sure to commit transactions yourself before disconnecting from the database server.
The following example shows a general method for executing statements as a transaction in a DBI script. It works by executing the statements inside an eval block. If the statements succeed, the transaction is committed. If any of the statements fail, the eval block aborts with an error and the transaction is rolled back. (The $@ variable is non-empty if the eval block fails.)
$old_pe = $dbh->{PrintError}; # save and reset
$old_re = $dbh->{RaiseError}; # error-handling
$dbh->{PrintError} = 0; # attributes
$dbh->{RaiseError} = 1;
$dbh->{AutoCommit} = 0; # disable auto-commit mode eval
{
# ... perform queries here ...
# if this is reached, queries succeeded; commit them
$dbh->commit ();
};
$dbh->rollback () if $@; # rollback if transaction failed
$dbh->{AutoCommit} = 1; # restore auto-commit mode
$dbh->{PrintError} = $old_pe; # restore error attributes
$dbh->{RaiseError} = $old_re;
The example saves the PrintError and RaiseError attributes before executing the transaction, and then restores them afterward. You don't need to do this if you already have these attributes set to raise exceptions for DBI errors, of course.