Transactions in MySQL - MySQL

MySQL provides two transaction-safe storage engines: Berkeley DB (BDB) and InnoDB. Their specific properties are discussed in next section. Each one offers the basic BEGIN/COMMIT/ROLLBACK functionality. They differ in their supported isolation levels, locking characteristics, deadlock detection, and other features.

By default MySQL operates in AUTOCOMMIT mode. This means that unless you’ve explicitly begun a transaction, it automatically executes each query in a separate transaction. You can enable AUTOCOMMIT for the current connection by running:

SET AUTOCOMMIT = 1;

Disable it by executing:

SET AUTOCOMMIT = 0;

Changing the value of AUTOCOMMIT has no effect on non-transaction-safe tables such as MyISAM or HEAP.

Certain commands, when issued during an open transaction, cause MySQL to commit the transaction before they execute. Typically these are commands that make significant changes, such as removing or renaming a table.

Here is the list of commands for which MySQL implicitly commits a transaction:

• ALTER TABLE

• BEGIN

• CREATE INDEX

• DROP DATABASE

• DROP TABLE

• RENAME TABLE

• TRUNCATE

• LOCK TABLES

• UNLOCK TABLES

As additional features are added to MySQL, it is possible that other commands will be added to the list, so be sure to check the latest available documentation.

MySQL allows you to set the isolation level using the SET TRANSACTION ISOLATION LEVEL command. Unless otherwise specified, the isolation level is changed beginning with the next transaction.

To set the level for the whole session (connection), use:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

Here’s how to set the global level:

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE

MySQL recognizes all four ANSI standard isolation levels, and as of Version 4.0.5 of MySQL, InnoDB supports all of them:

• READ UNCOMMITTED

• READ COMMITTED

• REPEATABLE READ

• SERIALIZABLE

The default isolation level can also be set using the --transaction-isolation command-line option when starting the server or set via my.cnf.

Transaction management in MySQL is currently handled by the underlying storage engines, not at a higher level. Thus, you can’t reliably mix tables stored in transactional engines (such as InnoDB and BDB) in a single transaction. A higher-level transaction management service may someday be added to MySQL, making it safe to mix and match transaction-safe tables in a transaction. Until then, don’t expect it to work.

If you mix transaction-safe and non-transaction-safe tables (such as InnoDB and MyISAM) in a transaction, the transaction will work properly if all goes well. However, if a rollback is required, the changes to the non-transaction-safe table won’t be undone. This leaves the database in an inconsistent state that may be difficult to recover from (and renders the entire point of transactions moot).

At times you may need the behavior of transactions when you aren’t using a transac-tion-safe table. You can achieve something like transactions using MySQL’s LOCK TABLES and UNLOCK TABLES commands. If you lock the tables that will be involved in the transaction and keep track of any changes that you make (in case you need to simulate a rollback), you’ll have something equivalent to running at the serializable isolation level. But the process is kludgy and error prone, so if you really need transactions, we recommend using a transactional storage engine.

If you've enjoyed what you've seen here, or to get more information, click on the "Buy the book!" graphic. Pick up a copy today! Visit the O'Reilly Network http://www.oreillynet.com for more online content.