Mysql锁系列(4)：算法深入-EW帮帮网

在前面介绍过Mysql的锁，及其算法，这里详细分析一下它的算法

锁的算法

行锁有3种算法

Record Lock会锁住索引记录，如果建表时没有设置添加索引，Innodb会去锁定隐式的主键。

Next-Key Lock算法的锁定称为Next-Key Locking。采用它可以解决幻读问题。要理解它，就要先理解下事物的隔离级别

SQL标准的隔离级别有4种

不同的隔离级别，会有不同的问题及并发效率

Phantom Problem，幻读问题。Mysql通过Next-Key Locking锁定技术解决了这个问题。

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

看一个例子，先建一张表

CREATE TABLE t
(
a INT PRIMARY KEY
);

INSERT INTO t VALUES(1), (2), (5);

然后执行以下步骤

Innodb存储引擎会对Next-Key Locking进行优化，如果查询的索引含有唯一属性时（唯一索引），上面的t表的a字段为主键，所以会降级为Record Lock，仅锁住索引本身，而不是范围。

但如果是辅助索引，则情况会不同，即不会发生锁降级的情况。修改一下上面的表，增加一个列b，并添加对应的索引：

ALTER TABLE t ADD COLUMN b INT;
-- 建立索引
CREATE INDEX `idx_b` ON t(b);

TRUNCATE TABLE t;
-- 插入数据
INSERT INTO t VALUES(1,1), (3,1),(5,3),(7,6),(10,8);

这时如果执行下面的sql，对b=3这一列加X锁

-- 对b=3这一列加X锁
SELECT * FROM t WHERE b = 3 FOR UPDATE;

因为myslq是行级锁，并且由于b=3这一行上有2个索引，a列的唯一索引，b列的辅助索引，所以同时也会对a=5进行Record Lock锁定。这时如果执行下面的sql

BEGIN;
-- 对a=5加共享锁
SELECT * FROM t WHERE a = 5 LOCK in SHARE MODE;

会发现在超时，是因为a=5已经被锁定了。再看下面的插入sql

还是超时。这是由于，对b=3获取独占锁时，根据Next-Key Locking，锁定的不仅是一条记录，而是一个范围，(1,3),(3,6)，所以对于b=2或b=5，都在锁定范围中，不能执行成功。

Mysql默认的隔离级别是Repeatable read。通过Next-Key Locking解决了幻读问题。

解释一下幻读：同一个事物中，连续执行2次相同的sql，可能会导致不同的结果；第2次可能会返回之前不存在的行。

如果前面的例子，改为Read commited，则不会采用Next-Key Locking，产生幻读问题，可以验证。

执行顺序	会话A	会话B
	-- 设置隔离级别 set session transaction isolation level read committed;
	BEGIN; SELECT * FROM t WHERE b > 3 FOR UPDATE;
	a b 7 6 10 8
		BEGIN; INSERT INTO t VALUES(4,2),(6,5);
		COMMIT;
	a b 6 5 7 6 10 8