14.2.2 The InnoDB Transaction Model and Locking

To implement a large-scale, busy, or highly reliable database
application, to port substantial code from a different database
system, or to tune MySQL performance, you must understand the
notions of transactions
and locking as they relate to
the InnoDB storage engine.

In the InnoDB transaction model, the goal is to
combine the best properties of a multi-versioning database with
traditional two-phase locking. InnoDB does
locking on the row level and runs queries as nonlocking consistent
reads by default, in the style of Oracle. The lock information in
InnoDB is stored so space-efficiently that lock
escalation is not needed: Typically, several users are permitted
to lock every row in InnoDB tables, or any
random subset of the rows, without causing
InnoDB memory exhaustion.

In InnoDB, all user activity occurs inside a
transaction. If autocommit mode is enabled, each SQL statement
forms a single transaction on its own. By default, MySQL starts
the session for each new connection with autocommit enabled, so
MySQL does a commit after each SQL statement if that statement did
not return an error. If a statement returns an error, the commit
or rollback behavior depends on the error. See
Section 14.19.4, “InnoDB Error Handling”.

If autocommit mode is disabled within a session with SET
autocommit = 0, the session always has a transaction
open. A COMMIT or
ROLLBACK
statement ends the current transaction and a new one starts.

A COMMIT means that the changes
made in the current transaction are made permanent and become
visible to other sessions. A
ROLLBACK
statement, on the other hand, cancels all modifications made by
the current transaction. Both
COMMIT and
ROLLBACK release
all InnoDB locks that were set during the
current transaction.

A user can change the isolation level for a single session or for
all subsequent connections with the SET
TRANSACTION statement. To set the server's default
isolation level for all connections, use the
--transaction-isolation option on
the command line or in an option file. For detailed information
about isolation levels and level-setting syntax, see
Section 13.3.6, “SET TRANSACTION Syntax”.

In row-level
locking, InnoDB normally uses next-key
locking. That means that besides index records,
InnoDB can also lock the
gap preceding an index record to
block insertions by other sessions where the indexed values would
be inserted in that gap within the tree data structure. A next-key
lock refers to a lock that locks an index record and the gap
before it. A gap lock refers to a lock that locks only the gap
before some index record.