Mysql Innodb引擎中的锁

本文参考Mysql官方文档

共享锁和排它锁

• 共享锁（shared locks：s lock）
• 排它锁（exclusive locks：x lock）

意向锁（intention locks）- 表级别

• 共享意向锁（intention shared lock -> IS）
• 排他意向锁（intention exclusive lock -> IX）

意向锁与行锁间的关系

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Conflict
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

意向锁注意事项

• 事务申请一个表中某行记录的共享锁前，必须申请该表的IS lock或更强的锁
• 事务申请一个表中某行记录的排他锁前，必须申请该表的IX lock
• 意向锁只阻塞表级别的加锁请求（for example, LOCK TABLES ... WRITE)。意图锁主要是为了表明某些客户端正在或将要锁住表中的某一行。

---

记录锁(record lock)

记录锁也叫做行锁，是用来在一条索引记录上加锁来实现。例如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 会阻止其他事务插入、更新或删除t.c1值为10的数据行。

记录锁通常通过锁住索引记录来实现，即便一个mysql的table没有定义索引，在这种情况下InnoDB会创建一个隐藏的索引来用该索引来加记录锁。See Section 15.6.2.1, “Clustered and Secondary Indexes”.

---

间隙锁

间隙锁是一个锁定索引记录间隙的锁，或者是一个可锁住第一条记录之前或者最后一条记录之后的锁。它只锁间隙，并不所具体的数据行。

例如, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 阻止其他事务插入一个 t.c1的值为15的记录，无论是否已经存在列为15的记录, 因为15到20间的间隙已经被锁。

间隙锁不作用于unique索引的记录，对于unique索引，间隙锁只是锁住了这行数据，并不锁对应的间隙。如sql: SELECT * FROM child WHERE id = 100;，只锁定id=100的行记录。如果id所在列没有索引记录或者是一个非唯一索引，那这条语句可以锁住语句的间隙。

需要注意的是，InnoDB中的间隙锁的目的只是为了阻止其他事务在间隙中插入数据，事务间的间隙锁是可以共存的，对于一个事务A在间隙(m, n)上获取间隙锁，并不影响事务B在同样的间隙(m, n)上获取间隙锁。对于共享和排他的间隙锁都是如此，没有差别。

探索索引为unique key时的间隙锁

1. 首先我们来建一张表，并给表中插入一部分数据：

CREATE TABLE `demo` (
  `id` int(11) NOT NULL,
  `number` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `number` (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from demo; 
+----+--------+
| id | number |
+----+--------+
| 20 |      0 |
| 30 |      0 |
|  1 |      1 |
| 10 |      1 |
|  2 |      3 |
|  3 |      3 |
|  8 |      3 |
| 18 |      3 |
| 21 |      4 |
+----+--------+
9 rows in set (0.01 sec)

2. 事务1 : select不存在的数据；事务2：插入数据。我们可以看到事务2 被阻塞

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from demo where id = 4 for update;
Empty set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into demo values (5,2);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3. 事务1 : select存在的数据；事务2：插入数据。我们可以看到事务2并没有被阻塞，同时也插入了对应的数据。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from demo where id = 18 for update;
+----+--------+
| id | number |
+----+--------+
| 18 |      3 |
+----+--------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into demo values (19,2);
Query OK, 1 row affected (0.00 sec)

非unique key索引情况下的 gap lock

1. 还是同样的表，采用number列来做展示

mysql> select * from demo;
+----+--------+
| id | number |
+----+--------+
| 20 |      0 |
|  1 |      1 |
| 19 |      2 |
|  8 |      3 |
|  2 |      5 |
|  3 |      8 |
| 10 |      9 |
| 30 |     12 |
| 18 |     15 |
| 21 |     20 |
+----+--------+
10 rows in set (0.00 sec)

2. 事务1：查询已有数据；事务2：插入数据

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from demo where number = 15 for update; // 锁住(12, 20)范围内的间隙
+----+--------+
| id | number |
+----+--------+
| 18 |     15 |
+----+--------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

// 插入number = 13的记录到间隙内，导致事务2阻塞
// 因为事务2如果插入number=15的记录，会导致事务1产生幻读，因此为了避免该操作，只能锁住(12, 20)的间隙，这样插入number=13也会阻塞
mysql> insert into demo values (24, 13);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3. 事务1：查询已有数据；事务2：插入数据

transaction 1
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

//number = 17记录不存在，同样锁住(15, 20)区间
mysql> select * from demo where number = 17 for update; 
Empty set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

// 插入number = 18的记录到间隙内，导致事务2阻塞
// 因为事务2如果插入number=17的记录，会导致事务1产生幻读（第二次查突然有了数据），因此为了避免该操作，只能锁住(12, 20)的间隙，这便导致number=18的数据也无法插入
mysql> insert into demo values (29, 18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

// number = 12 和 number = 20 的记录可以插入，证明间隙锁是一锁一个开区间的间隙
mysql> insert into demo values (29, 20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into demo values (39, 12);
Query OK, 1 row affected (0.00 sec)

间隙锁总结思考

• 唯一索引下的情况
  • 记录存在：等同于行锁，因为记录为唯一索引，锁着该行，其他事务无法进行插入和修改
  • 记录不存在：加锁范围为该记录上下界开区间，因为mysql不允许其他事务在该间隙内部插入新的数据或修改数据以便将值更新到该间隙区间，导致此事务内部产生幻读
• 非唯一索引下的情况：记录无论是否存在都会对间隙加锁，防止其他事务在修改数据更新到间隙内导致幻读

---

临键锁

简单来讲临建锁 = 行锁 + 记录前的间隙锁，间隙锁是一个左开右闭的区间，假设一张表包含了10、11、13和20这几个数据，那么可以分割的间隙有

(negative infinity, 10]
(10, 11]
(11，13]
(13，20]
(20，正无穷)

假如有SQL语句SELECT * FROM table WHERE value = 13 FOR UPDATE;，根据临建锁的定义，锁住了(11，13]的区间，但实际上(13, 20)的间隙也会被锁住，因此实际要锁住的区间为(10, 20)