InnoDB——锁的算法

行锁的3种算法

InnoDB存储引擎有三种行锁的算法，分别是：

• Record Lock：单个行记录上的锁。
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
• Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎在表建立时没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。
例如一个索引有10，11，13，20这四个值，那么该索引可能被Next-Key Locking的区间为：

(-∞, 10]
(10, 11]
(11, 13]
(13, 20]
(20, +♾️)

采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计目的时为了解决Phantom Problem（幻读）。
这种锁定技术锁定的不是单个值，而是一个范围，是谓词锁（predict lock）的一种改进。

previous-key locking

除了next-key locking还有previous-key locking技。同样上述的索引10、11、13、20，若采用previous-key locking技术，那么可锁定的范围区间为：

(-∞, 10)
[10, 11)
[11, 13)
[13, 20)
[20, +♾️)

Next-Key Lock降级为Record Lock

若事务T1已经通过next-key locking锁定了如下范围：
(10, 11]、(11, 13]
当插入新的记录12时，则锁定的范围会变成：
(10, 11]、(11, 12]、(12, 13]

然而，当对唯一索引的 等值 查询时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，仅锁住索引本身，而不是范围。

有下面这样一个表：

CREATE TABLE t (a INT PRIMARY KEY);
INSERT INTO t SELECT 1;
INSERT INTO t SELECt 2;
INSERT INTO t SELECt 5;

然后有这样的执行语句：

时间	会话A	会话B
1	BEGIN;
2	SELECT * FROM t
WHERE a = 5 FOR UPDATE;
3		BEGIN;
4		INSERT INTO t SELECT 4;
5		COMMIT; #成功，不需要等待锁
6	COMMIT;

在上面语句中，会话A中首先对a=5进行X锁定，且由于a是主键且唯一，因此锁定的仅是5这个值，而不会再锁定(2, 5)这个间隙（锁），否则会导致插入记录4会阻塞。因此会话B插入记录4不会阻塞，成功后立即返回。
这就是Next-Key Lock算法降级成Record Lock的优化，提高了应用的并发性。

辅助索引下Next-Key Lock 不降级

首先创建下面这个表z：

CREATE TABLE z (a INT, b INT, PRIMARY KEY(a), KEY(b));
INSERT INTO z SELECT 1, 1;
INSERT INTO z SELECT 3, 1;
INSERT INTO z SELECT 5, 3;
INSERT INTO z SELECT 7, 6;
INSERT INTO z SELECT 10, 8;

表z的列b是辅助索引，若在会话A中执行下面的SQL语句：

SELECT * FROM Z WHERE b=3 FOR UPDATE;

这时SQL语句通过索引列b进行查询，因此会使用传统的Next-Key Locking技术加锁。
并且由于有两个索引列，需要分别对其进行锁定。

• 对于**聚集索引，仅对列a=5（主键）**的索引加上Record Lock。

• 而对于辅助索引，会对其加上Next-Key Lock，锁定的范围是**（1, 3]。特别注意，InnoDB还会对辅助索引下一个键值加上gap lock，即还有一个辅助索引范围为(3, 6)的锁**。

因此，下面的新会话B中的SQL语句在阻塞：

SELECT * FROM z WHERE a=5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4, 2;
INSERT INTO z SELECT 6, 5;

• 第一个语句无法执行，它select了主键为5的记录，但a=5（主键）的列已经在上面会话A中被锁定了（Record Lock）
• 第二句SQL，插入主键4，这没问题。但插入的辅助索引的值为2，在锁定的范围（1，3）中，因此也会被苏泽
• 第三句同理，因为5在（3，6）的范围也会被阻塞。

从上面的例子可以看出，Gap Lock的作用是为了阻止多个事务将记录插入到同一范围内，而这回导致Phantom Problem（幻读）问题产生。

假如上面例子中，会话A锁定了b=3的记录，若没有Next-Key Lock锁住(1, 3]，那么用户同时可以插入索引b列为3的记录，这会导致会话A中再次执行同样的查询会返回不同的记录，即导致Phantorm Problem（幻读）的产生。

最后需要提醒的是，对于唯一键值的锁定，Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成，而查询仅是查找多个唯一索引列的其中一个，那么查询其实是range类型查询，而不是point类型查询，则InnoDB依然使用Next-Key Lock进行锁定。

解决Phantom Problem（幻读）

在默认的事务隔离级别（REPEATABLE READ）下，InnoDB采用Next-Key Locking机制来避免幻读。

这点可能不同于Oracle，它可能需要在SERIALIZABLE的事务隔离级别下才能解决幻读。

Phantom Problem（幻读）是指在同一事务下，连续执行两次同样的SQL语句可能导致不同的结果，第二次SQL语句可能会返回之前不存在的行。

InnoDB采用Next-Key Locking的算法避免幻读。
对于SQL语句 SELECT * FROM t WHERE a > 2 FOR UPDATE （a列上有索引），其锁住的不是5单个值，而是对（2，+♾️)这个范围加了X锁。因此任何对于这个范围的插入都是不允许的，从而避免幻读。

InnoDB默认的事务隔离级别是REPEATABLE READ，在该隔离级别下，其采用Next-Key Locking的方式来加锁。而在事务隔离界别READ COMMIT下，仅采用Record Lock。