MySQL知识点回顾：InnoDB的锁机制（三）Next-Key Lock

一、行锁的3种算法

InnoDB存储引擎有3种行锁的算法，其分别是：
Record Lock:单个行记录上的锁
Gap Lock:间隙锁，锁定一个范围，但不包含记录本身
Next-Key Lock：Gap Lock+Record Lock,锁定一个范围，并且锁定记录本身

  Record Lock总是会去锁住索引记录，如果nnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。
  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,+∞)

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

下面我们来实践验证一下：

首先我们创建表z，执行以下SQL：

DROP TABLE IF EXISTS `z`;
CREATE TABLE `z`  (
  `a` int(11) NOT NULL,
  `b` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`a`) USING BTREE,
  INDEX `b`(`b`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of z
-- ----------------------------
INSERT INTO `z` VALUES (1, 1);
INSERT INTO `z` VALUES (3, 1);
INSERT INTO `z` VALUES (5, 3);
INSERT INTO `z` VALUES (7, 6);
INSERT INTO `z` VALUES (10, 8);

二、Next-Key Lock降级为Record Lock

这是默认在RR隔离级别环境下

时间点	会话A	会话B
时间点1	BEGIN;
时间点2	SELECT * FROM z WHERE a=5 FOR UPDATE;
时间点3		BEGIN;
时间点4		INSERT INTO z SELECT 4,4;
时间点5	COMMIT;
时间点6		COMMIT;

  这里首先开启会话A，在时间点2时对a=5这一行进行了显式的一致性锁定读FOR UPDATE，而这里由于a是主键且唯一的，因此锁定的仅仅是5这个值，而不是（3，7）这个范围。
  这里就能够看到在会话B中insert into z select 4，4是不会被阻塞的。
  证明在查询的列是唯一索引的情况下，Next-Key Lock会降级为Record Lock。若是辅助索引，则情况会完全不同。

三、辅助索引的加锁机制

我们看一下以下的例子
这是默认在RR隔离级别环境下

时间点	会话A	会话B
时间点1	BEGIN;
时间点2	SELECT * FROM z WHERE b=3 FOR UPDATE;
时间点3		BEGIN;
时间点4		INSERT INTO z SELECT 4,4;
时间点5	COMMIT;
时间点6		COMMIT;

  这里首先开启会话A，在时间点2时对b=3这一行进行了显式的一致性锁定读FOR UPDATE，而这里由于b是辅助索引，其加上的是Next-Key Lock，锁定的范围是（1，3），特别需要注意的是，InnoDB存储引擎还会对辅助索引下一个键值加上gap lock，即还有一个辅助索引范围为(3,6)，因此会话B会执行的SQL语句会被阻塞。

总结：从上面的例子中可以看到，Gap Lock的作用是为了组织多个事务将记录插入到同一范围内，而这回导致Phantom Problem问题你的产生。例如在上面的例子中，会话A中用户已经锁定了b=3的记录。若此时没有Gap Lock锁定（3，6），那么用户可以插入索引b列为3的记录，这回导致会话A中的用户再次执行同样查询时会返回不同的记录，即导致Phantom Problem问题的产生。