Databases Reference
In-Depth Information
Certain commands, when issued during an open transaction, cause MySQL to commit
the transaction before they execute. These are typically Data Definition Language
(DDL) commands that make significant changes, such as
ALTER TABLE
, but
LOCK
TABLES
and some other statements also have this effect. Check your version's docu-
mentation for the full list of commands that automatically commit a transaction.
MySQL lets you set the isolation level using the
SET TRANSACTION ISOLATION LEVEL
command, which takes effect when the next transaction starts. You can set the isolation
level for the whole server in the configuration file, or just for your session:
mysql>
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
MySQL recognizes all four ANSI standard isolation levels, and InnoDB supports all of
them.
Mixing storage engines in transactions
MySQL doesn't manage transactions at the server level. Instead, the underlying storage
engines implement transactions themselves. This means you can't reliably mix different
engines in a single transaction.
If you mix transactional and nontransactional tables (for instance, InnoDB and
MyISAM tables) in a transaction, the transaction will work properly if all goes well.
However, if a rollback is required, the changes to the nontransactional table can't be
undone. This leaves the database in an inconsistent state from which it might be difficult
to recover and renders the entire point of transactions moot. This is why it is really
important to pick the right storage engine for each table.
MySQL will not usually warn you or raise errors if you do transactional operations on
a nontransactional table. Sometimes rolling back a transaction will generate the warn-
ing “Some nontransactional changed tables couldn't be rolled back,” but most of the
time, you'll have no indication you're working with nontransactional tables.
Implicit and explicit locking
InnoDB uses a two-phase locking protocol. It can acquire locks at any time during a
transaction, but it does not release them until a
COMMIT
or
ROLLBACK
. It releases all the
locks at the same time. The locking mechanisms described earlier are all implicit.
InnoDB handles locks automatically, according to your isolation level.
However, InnoDB also supports explicit locking, which the SQL standard does not
mention at all:
3
•
SELECT ... LOCK IN SHARE MODE
•
SELECT ... FOR UPDATE
3. These locking hints are frequently abused and should usually be avoided; see
Chapter 6
for more details.
