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 anS
lock can be granted immediately. As a result, bothT1
andT2
hold anS
lock onr
.A request by
T2
for anX
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
): TransactionT
intends to setS
locks on individual rows in tablet
.Intention exclusive (
IX
): TransactionT
intends to setX
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 tablet
, it must first acquire anIS
or stronger lock ont
.Before a transaction can acquire an
X
lock on a row, it must first acquire anIX
lock ont
.
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.
Thus, intention locks do not block anything except full table
requests (for example, LOCK TABLES ...
WRITE
). The main purpose of
IX
and IS
locks is to show that someone is locking a row, or going to lock
a row in the table.
The following example illustrates how an error can occur when a lock request would cause a deadlock. The example involves two clients, A and B.
First, client A creates a table containing one row, and then
begins a transaction. Within the transaction, A obtains an
S
lock on the row by selecting it in
share mode:
mysql>CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec) mysql>INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec) mysql>START TRANSACTION;
Query OK, 0 rows affected (0.00 sec) mysql>SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+ | i | +------+ | 1 | +------+ 1 row in set (0.10 sec)
Next, client B begins a transaction and attempts to delete the row from the table:
mysql>START TRANSACTION;
Query OK, 0 rows affected (0.00 sec) mysql>DELETE FROM t WHERE i = 1;
The delete operation requires an X
lock. The lock cannot be granted because it is incompatible with
the S
lock that client A holds, so
the request goes on the queue of lock requests for the row and
client B blocks.
Finally, client A also attempts to delete the row from the table:
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
Deadlock occurs here because client A needs an
X
lock to delete the row. However,
that lock request cannot be granted because client B already has
a request for an X
lock and is
waiting for client A to release its S
lock. Nor can the S
lock held by A be
upgraded to an X
lock because of the
prior request by B for an X
lock. As
a result, InnoDB
generates an error for
client A and releases its locks. At that point, the lock request
for client B can be granted and B deletes the row from the
table.