彭诗亮的博客

0%

Mysql锁

发表于

实验环境

事物隔离场景

repeatable-read

表语句准备

1
2
3
4
5
6
7
8
9
10
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);

先假设一个没有堵塞的场景：

SessionASessionBSessionC
T1begin;
select * from t where d=5 for update;
result:(5,5,5)
T2update t set d=5 where id=0;
T3select * from t where d=5 for update;
result:(0,0,5)(5,5,5)
T4insert into t values(1,1,5);
T5select * from t where d=5 for update;
result:(0,0,5)(1,1,5)(5,5,5)
T6commit;

可以看到，session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同，都是 select * from t where d=5 for update。查所有 d=5 的行，而且使用的是当前读，并且加上写锁。现在，我们来看一下这三条 SQL 语句，分别会返回什么结果。

  1. Q1 只返回 id=5 这一行；
  2. 在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行；
  3. 在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行。

其中，Q3 读到 id=1 这一行的现象，被称为“幻读”。也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

Mysql如何解决幻读

本片需要用到的两种锁

LOCK IN SHARE MODE是读锁(只是不让别人写)，FOR UPDATE是写锁(还不让别人加读锁)

InnoDB行锁

InnoDB行锁是通过索引上的索引项来实现的，InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁！
如select * from t where d=5 for update;会锁住d=5索引覆盖范围的相关行，如果没有索引则会锁住所有行

间隙锁 (Gap Lock)

行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。
间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。
这样，当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

行锁和间隙锁比较

行锁，分成读锁和写锁。下图就是这两种类型行锁的冲突关系。

读锁写锁
读锁兼容冲突
写锁冲突冲突

也就是说，跟行锁有冲突关系的是“另外一个行锁”。
但是间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

SessionASessionB
begin;
select * from t where c=7 lock in share mode;
begin;
select * from t where c=7 for update;

这里 session B 并不会被堵住。因为表 t 里并没有 c=7 这个记录，因此 session A 加的是间隙锁 (5,10)。而 session B 也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”。

看下面的实例

SessionASessionB
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);
result:(blocked)
insert into t values(9,9,9);
result:(Deadlock)

上面的场景形成死锁了。我们按语句执行顺序来分析一下：

  1. session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);
  2. session B 执行 select … 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 语句报错返回了。

mysql锁类型

锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

MySQL 后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个特殊场景：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

等值查询间隙锁

SessionASessionBSessionC
begin;
update t set d=d+1 where id=7;
insert into t values(8,8,8);
result:(blocked)
update t set d=d+1 where id=10;
result:(ok)

由于表 t 中没有 id=7 的记录，所以用我们上面提到的加锁规则判断一下的话：

  1. 根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
  2. 同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

所以，session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的。

非唯一索引等值锁

SessionASessionBSessionC
begin;
select id from t where c=5 lock in share mode(读锁);
update t set d=d+1 where id=5;
result:(ok)
insert into t values(7,7,7);
result:(blocked)

看到这个例子，你是不是有一种“该锁的不锁，不该锁的乱锁”的感觉？我们来分析一下吧。

  1. 根据原则 1，加锁单位是 next-key lock，因此会给 (0,5] 加上 next-key lock。
  2. 要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。根据原则 2，访问到的都要加锁，因此要给 (5,10] 加 next-key lock。
  3. 但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
  4. 根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。

但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。

需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将 session A 的查询语句改成 select d from t where c=5 lock in share mode。

主键索引范围锁

SessionASessionBSessionC
begin;
select * from t where id >=10 and id<11 for update;
insert into t values(7,7,7);
result:(ok)
insert into t values(13,13,13);
result:(blocked)
update t set d=d+1 where id=15;
result:(blocked)
  1. 开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
  2. 范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。

所以，session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15]。

非唯一索引范围锁

SessionASessionBSessionC
begin;
select * from t where c>10 and c<11 for update;
insert into t values(8,8,8);
result:(blocked)
update t set d=d+1 where id=15;
result:(blocked)

这次 session A 用字段 c 来判断，加锁规则跟主键索引范围锁唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

唯一索引范围锁特殊场景

SessionASessionBSessionC
begin;
select * from t where id >10 and id<=15 for update;
update t set d=d+1 where id=20;
result:(blocked)
insert into t values(16,16,16);
result:(blocked)

session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15] 这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20] 这个 next-key lock也会被锁上。