This chapter covers the MySQL architecture, locking and concurrency, and transactions. It also discusses how to select the right engine and looks at each of MySQL's storage engines in detail. (From the book High Performance MYSQL: Optimization, Backups, Replication and Load Balancing, by Jeremy Zawodny and Derek Balling, ISBN: 0596-003064, O'Reilly Media, 2004.)
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:
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:
The default isolation level can also be set using the --transaction-isolation command-line option when starting the server or set via my.cnf.
Mixing storage engines in transactions
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!