MySQLInnoDB lock mechanism (1)

MySQLInnoDB lock mechanism (1)

MySQL InnoDB has four types of locks: Shared locks (read locks, S locks), exclusive locks (write locks, X locks), intention shared locks (IS locks), and intention exclusive locks (IX locks ). The shared locks and exclusive locks belong to row-level locks, and the other two intention locks belong to table-level locks.

Shared lock (read lock, S lock): If transaction T adds S lock to Data Object A, transaction T can read A but cannot modify A. Other transactions can only apply S lock to transaction, instead, the X lock cannot be applied until T releases the S lock. Exclusive lock (write lock, X lock): If transaction T adds X lock to Data Object A, only T can read and modify, other transactions cannot apply any type of lock to auntil T releases the X lock on. Intention share lock (IS lock): Before applying the S lock to the Data Objects in the table, the transaction T must first add the IS (or stronger IX) lock to the table. Intention exclusive lock (IX lock): Before applying the X lock to the Data Objects in the table, the transaction T must first apply the IX lock to the table.

For example, IN the SELECT... FROM T1 lock in share mode statement, the is lock will be applied to table T1 first, and the s lock will be applied to the data only after the is lock is successfully added.

Similarly, the SELECT... FROM T1 for update statement first adds the IX lock to table T1. After the IX lock is successfully added, the X lock is applied to the data.

MySQL InnoDB Lock compatibility Array

	X	IX	S	IS
X	?	?	?	?
IX	?	?	?	?
S	?	?	?	?
IS	?	?	?	?

The MySQL website has a deadlock example, but the analysis is too general. Here we will analyze it in detail.

First, session S1 IS queried using SELECT * FROM t WHERE I = 1 lock in share mode. This statement first adds the is lock to table t and then the data (I = 1) apply the S lock.

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, session S2 executes delete from t WHERE I = 1. This statement tries to apply the IX lock to table t because the IX lock IS compatible with the IS lock, therefore, the IX lock is successfully applied to the t table. Then, the X lock is applied to the data (I = 1), but the data has been locked by session S1 transactions, so session S2 waits.

mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)mysql> DELETE FROM t WHERE i = 1;

Then, session S1 also executes delete from t WHERE I = 1. This statement first adds the IX lock to table t, although session S2 has already applied the IX lock to table t, however, the IX lock is compatible with the IX lock, so the IX lock is successfully applied to the t table. Then session S1 will apply the X lock to the data (I = 1). At this time, it is found that the IX lock occupied by session S2 is incompatible with the X lock, so session S1 also waits. In this way, session S1 and other S2 release the IX lock, while session S2 and other S1 release the S lock, resulting in a deadlock.

mysql> DELETE FROM t WHERE i = 1;Query OK, 1 row affected (0.00 sec)mysql>

Session S2.

mysql> DELETE FROM t WHERE i = 1;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionmysql>