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.
InnoDB transaction model, the goal is to
combine the best properties of a multi-versioning database with
traditional two-phase locking.
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.
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.18.4, “InnoDB Error Handling”.
A session that has autocommit enabled can perform a
multiple-statement transaction by starting it with an explicit
and ending it with a
statement. See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.
COMMIT means that the changes
made in the current transaction are made permanent and become
visible to other sessions. A
statement, on the other hand, cancels all modifications made by
the current transaction. Both
InnoDB locks that were set during the
In terms of the SQL:1992 transaction
isolation levels, the
InnoDB level is
InnoDB offers all four transaction isolation
levels described by the SQL standard:
REPEATABLE READ, and
A user can change the isolation level for a single session or for
all subsequent connections with the
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”.
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.
For more information about row-level locking, and the circumstances under which gap locking is disabled, see Section 18.104.22.168, “InnoDB Record, Gap, and Next-Key Locks”.
To handle locking for operations involving
SPATIAL indexes, next-key locking does not work
well to support
isolation levels. In this case,
predicate locking. See Section 22.214.171.124, “Predicate Locking for Spatial Indexes”.