表初始化

1
2
3
4
5
6
7
8
9
CREATE TABLE `t` (
    `id` INT(11) NOT NULL,
    `c` INT(11) DEFAULT NULL,
    `d` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `c` (`c`)
) ENGINE=InnoDB;

INSERT INTO t VALUES (0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

定义与问题

定义

  1. 幻读：在同一个事务内，前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行
    • 幻读专指_新插入的行_
  2. 在RR隔离级别下，普通查询是快照读，是看不到其他事务插入的数据的
    • 幻读仅在当前读时才会出现

解决思路

只有行锁

假设SELECT * FROM t WHERE d=5 FOR UPDATE;只会在id=5这一行上加X Lock，执行时序如下：

时刻 session A session B session C
T1 BEGIN;
SELECT * FROM t WHERE d=5 FOR UPDATE;
result:(5,5,5)
T2 UPDATE t SET d=5 WHERE id=0;
UPDATE t SET c=5 WHERE id=0;
T3 SELECT * FROM t WHERE d=5 FOR UPDATE;
result:(0,5,5),(5,5,5)
T4 INSERT INTO t VALUES (1,1,5);
UPDATE t SET c=5 WHERE id=1;
T5 SELECT * FROM t WHERE d=5 FOR UPDATE;
result:(0,5,5),(1,1,5),(5,5,5)
T6 COMMIT;
  1. T1返回id=5这1行
  2. T3返回id=0和id=5这2行
    • id=0不是幻读，因为不是新插入的行
  3. T5返回id=0、id=1和id=5的这三行
    • id=1是幻读，因为这是新插入的行
    • 显然只有行锁（RC）是无法解决幻读问题的

幻读的问题

破坏语义
  1. session A在T1时刻声明：锁住所有d=5的行，不允许其他事务进行读写操作
  2. session B在T2时刻修改了id=0,d=5这一行
  3. session C在T4时刻修改了id=1,d=5这一行
破坏数据一致性
数据
时刻 session A session B session C
T1 BEGIN;
SELECT * FROM t WHERE d=5 FOR UPDATE;
UPDATE t SET d=100 WHERE d=5;
T2 UPDATE t SET d=5 WHERE id=0;
UPDATE t SET c=5 WHERE id=0;
T3 SELECT * FROM t WHERE d=5 FOR UPDATE;
T4 INSERT INTO t VALUES (1,1,5);
UPDATE t SET c=5 WHERE id=1;
T5 SELECT * FROM t WHERE d=5 FOR UPDATE;
T6 COMMIT;
  1. UPDATE与SELECT...FOR UPDATE的加锁语义一致（X Lock）
  2. T1时刻，id=5这一行变成了(5,5,100)，在T6时刻才正式提交
  3. T2时刻，id=0这一行变成了(0,5,5)
  4. T4时刻，新插入了一行(1,5,5)
binlog
  1. T2时刻，session B事务提交，写入两条语句
  2. T4时刻，session C事务提交，写入两条语句
  3. T6时刻，session A事务提交，写入UPDATE t SET d=100 WHERE d=5;
1
2
3
4
5
6
7
UPDATE t SET d=5 WHERE id=0; -- (0,0,5)
UPDATE t SET c=5 WHERE id=0; -- (0,5,5)

INSERT INTO t VALUES (1,1,5); -- (1,1,5)
UPDATE t SET c=5 WHERE id=1; -- (1,5,5)

UPDATE t SET d=100 WHERE d=5; -- 所有d=5的行，d改成100
  1. 该binlog如果在备库上执行，最终结果为(0,5,100)，(1,5,100)，(5,5,100)，id=0和id=1这两行数据会与主库不一致
  2. 原因：SELECT * FROM t WHERE d=5 FOR UPDATE;只给id=5这一行X Lock

加强行锁

增强为：扫描过程中所有碰到的行，都加上X Lock，执行序列如下

时刻 session A session B session C
T1 BEGIN;
SELECT * FROM t WHERE d=5 FOR UPDATE;
UPDATE t SET d=100 WHERE d=5;
T2 UPDATE t SET d=5 WHERE id=0;(blocked)
UPDATE t SET c=5 WHERE id=0;
T3 SELECT * FROM t WHERE d=5 FOR UPDATE;
T4 INSERT INTO t VALUES (1,1,5);
UPDATE t SET c=5 WHERE id=1;
T5 SELECT * FROM t WHERE d=5 FOR UPDATE;
T6 COMMIT;
  1. session A把所有的行都加了X Lock，因此session B在执行第一个update语句时被锁住了
    • 需要等到T6时刻，session A提交之后，session B才能继续执行
  2. 对于id=0这一行，在数据库中的最终结果还是(0,5,5)

binlog

1
2
3
4
5
6
7
INSERT INTO t VALUES (1,1,5); -- (1,1,5)
UPDATE t SET c=5 WHERE id=1; -- (1,5,5)

UPDATE t SET d=100 WHERE d=5; -- 所有d=5的行，d改成100

UPDATE t SET d=5 WHERE id=0; -- (0,0,5)
UPDATE t SET c=5 WHERE id=0; -- (0,5,5)
  1. id=0这一行的最终结果也是(0,5,5)，因此id=0这一行的数据是一致的
  2. 对于id=1这一行数据而言，在数据库端的结果为(1,5,5)，而根据binlog的执行结果是(1,5,100)，数据不一致
    • 并且依然存在幻读
  3. 原因：只能给加锁时存在的行加X Lock
    • 在T3时刻，在给所有的行加X Lock时，此时id=1这一行还不存在，因此也就加不上X Lock了
    • 即使在所有的记录都加上了X Lock，依旧阻止不了插入新纪录

解决方案

Gap Lock

  1. 产生幻读的原因：行锁只能锁住行，新插入记录这个动作，要更新的是记录之间的间隙
  2. 为了解决幻读，InnoDB引入了新的锁：间隙锁（Gap Lock）

表初始化，插入了6个记录，产生了7个间隙

  1. SELECT * FROM t WHERE d=5 FOR UPDATE;
    • 给已有的6个记录加上X Lock，同时还会加上7个Gap Lock，这样就确保无法再插入新纪录
  2. 上锁实体
    • 数据行
    • 数据行之间的间隙

冲突关系

行锁

行锁的冲突关系（跟行锁有冲突关系的是另一个行锁）

S Lock X Lock
S Lock 兼容 冲突
X Lock 冲突 冲突

间隙锁

跟间隙锁存在冲突关系的是_往这个间隙插入一个记录的操作，间隙锁之间不会相互冲突_

session A session B
BEGIN;
SELECT * FROM t WHERE c=7 LOCK IN SHARE MODE;
BEGIN;
SELECT * FROM t WHERE c=7 FOR UPDATE;
  1. session B并不会被阻塞，因为表t里面并没有c=7的记录
    • 因此session A加的是间隙锁(5,10)，而session B也是在这个间隙加间隙锁
    • 两个session有共同的目标： 保护这个间隙，不允许插入值，但两者之间不冲突

Next-Key Lock

  1. 间隙锁和行锁合称Next-Key Lock，每个Next-Key Lock都是左开右闭区间
  2. SELECT * FROM t WHERE d=5 FOR UPDATE;形成了7个Next-Key Lock，分别是
    • (-∞,0],(0,5],(5,10],(10,15],(15,20],(20,25],(25,+supremum]
    • +supremum：InnoDB给每一个索引加的一个不存在的最大值supremum
  3. 约定：Gap Lock为左开右开区间，Next-Key Lock为左开右闭区间

可能死锁

1
2
3
4
5
6
7
8
9
10
11
-- 并发执行
-- 死锁并不是大问题，回滚重试即可
BEGIN;
SELECT * FROM t WHERE id=N FOR UPDATE;

-- 如果行不存在
INSERT INTO t VALUES (N,N,N);
-- 如果行存在
UPDATE t SET d=N SET id=N;

COMMIT;
session A session B
BEGIN;
SELECT * FROM t WHERE id=9 FOR UPDATE;
BEGIN;
SELECT * FROM t WHERE id=9 FOR UPDATE;
INSERT INTO t VALUES (9,9,9);(blocked)
INSERT INTO t VALUES (9,9,9);(Deadlock fund)
  1. session A执行SELECT * FROM t WHERE id=9 FOR UPDATE;，id=9这一行不存在，会加上间隙锁(5,10)
  2. session B执行SELECT * FROM t WHERE id=9 FOR UPDATE;，间隙锁之间不冲突，同样会加上间隙锁(5,10)
  3. session B试图插入一行(9,9,9)，被session A的间隙锁阻塞
  4. session A试图插入一行(9,9,9)，被session B的间隙锁阻塞，两个session相互等待，形成死锁
    • InnoDB的死锁检测很快就会发现死锁，并让session A的insert语句报错返回
  5. 解决方案：假如只有一个唯一索引，可以用INSERT ... ON DUPLICATE KEY UPDATE来替代

小结

  1. 引入Gap Lock，会导致同样的语句锁住更大的范围，_影响并发度_
  2. Gap Lock是在RR隔离级别下才生效的（在RC隔离级别是没有Gap Lock的）
  3. 解决数据与日志不一致的另一个方案：RC + binlog_format=row
    • 如果RC（没有Gap Lock，锁范围更小）隔离级别够用，业务并不需要可重复读的保证，可以选择RC

参考资料

《MySQL实战45讲》