14.2.10.1. InnoDB Lock Modes
InnoDB implements standard row-level locking
where there are two types of locks:
If transaction T1 holds a shared
(S) lock on tuple
t, then
A request from some distinct transaction
T2 for an S
lock on t can be granted immediately. As
a result, both T1 and
T2 hold an S
lock on t.
A request from some distinct transaction
T2 for an X
lock on t cannot be granted immediately.
If a transaction T1 holds an exclusive
(X) lock on tuple
t, then a request from some distinct
transaction T2 for a lock of either type on
t cannot be granted immediately. Instead,
transaction T2 has to wait for transaction
T1 to release its lock on tuple
t.
Additionally, InnoDB supports
multiple granularity locking which allows
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 R):
The intention locking protocol is as follows:
Before a given transaction can acquire an
S lock on a given row, it must
first acquire an IS or stronger
lock on the table containing that row.
Before a given transaction can acquire an
X lock on a given row, it must
first acquire an IX lock on the
table containing that row.
These rules can be conveniently summarized by means of a
lock type compatibility matrix:
A lock is granted to a requesting transaction if it is
compatible with existing locks. A lock is not granted to a
requesting transaction 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 is 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.
