[+/-]
InnoDB
Lock ModesSELECT ... FOR UPDATE
and SELECT ... LOCK IN
SHARE MODE
Locking ReadsInnoDB
Record, Gap, and Next-Key LocksInnoDB
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 table in
InnoDB
is stored so space-efficiently that lock
escalation is not needed: Typically several users are allowed 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 13.2.13, “InnoDB
Error Handling”.
A session that has autocommit enabled can perform a
multiple-statement transaction by starting it with an explicit
START
TRANSACTION
or
BEGIN
statement
and ending it with a COMMIT
or
ROLLBACK
statement.
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.
In terms of the SQL:1992 transaction isolation levels, the default
InnoDB
level is
REPEATABLE READ
. As of MySQL
4.0.5, InnoDB
offers all four transaction
isolation levels described by the SQL standard:
READ UNCOMMITTED
,
READ COMMITTED
,
REPEATABLE READ
, and
SERIALIZABLE
. Before 4.0.5,
only REPEATABLE READ
and
SERIALIZABLE
were available.
Before MySQL 3.23.50, SET
TRANSACTION
had no effect on InnoDB
tables.
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 12.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 in
the gap immediately before the index record. 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 13.2.9.4, “InnoDB
Record, Gap, and Next-Key Locks”.
User Comments
Add your own comment.