Oracle 5.0, Reference Manual

Page: 1285 / 3232

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.