The
InnoDB
Transaction Model and Locking
1265
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 14.2.7.4, “
InnoDB
Record, Gap, and Next-Key Locks”
.
14.2.7.1.
InnoDB
Lock Modes
InnoDB
implements standard row-level locking where there are two types of locks:
• A shared (
S
) lock permits a transaction to read a row.
• An exclusive (
X
) lock permits a transaction to update or delete a row.
If transaction
T1
holds a shared (
S
) lock on row
r
, then requests from some distinct transaction
T2
for
a lock on row
r
are handled as follows:
• A request by
T2
for an
S
lock can be granted immediately. As a result, both
T1
and
T2
hold an
S
lock
on
r
.
• A request by
T2
for an
X
lock cannot be granted immediately.
If a transaction
T1
holds an exclusive (
X
) lock on row
r
, a request from some distinct transaction
T2
for a lock of either type on
r
cannot be granted immediately. Instead, transaction
T2
has to wait for
transaction
T1
to release its lock on row
r
.
Additionally,
InnoDB
supports multiple granularity locking which permits coexistence of record locks
and locks on entire tables. To make locking at multiple granularity levels practical, additional types
of locks called intention locks are used. Intention locks are table locks in
InnoDB
. The idea behind
intention locks is for a transaction to indicate which type of lock (shared or exclusive) it will require later
for a row in that table. There are two types of intention locks used in
InnoDB
(assume that transaction
T
has requested a lock of the indicated type on table
t
):
• Intention shared (
IS
): Transaction
T
intends to set
S
locks on individual rows in table
t
.
• Intention exclusive (
IX
): Transaction
T
intends to set
X
locks on those rows.
For example,
SELECT ... LOCK IN SHARE MODE
sets an
IS
lock and
SELECT ... FOR UPDATE
sets an
IX
lock.
The intention locking protocol is as follows:
• Before a transaction can acquire an
S
lock on a row in table
t
, it must first acquire an
IS
or stronger
lock on
t
.
• Before a transaction can acquire an
X
lock on a row, it must first acquire an
IX
lock on
t
.
These rules can be conveniently summarized by means of the following lock type compatibility matrix.
X
IX
S
IS
X
Conflict
Conflict
Conflict
Conflict
IX
Conflict
Compatible
Conflict
Compatible
S
Conflict
Conflict
Compatible
Compatible
IS
Conflict
Compatible
Compatible
Compatible
A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts
with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request
conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...