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);

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

SessionA SessionB SessionC
T1 begin;
select * from t where d=5 for update;
result:(5,5,5)
T2 update t set d=5 where id=0;
T3 select * from t where d=5 for update;
result:(0,0,5)(5,5,5)
T4 insert into t values(1,1,5);
T5 select * from t where d=5 for update;
result:(0,0,5)(1,1,5)(5,5,5)
T6 commit;

可以看到,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 个间隙锁。这样就确保了无法再插入新的记录。

行锁和间隙锁比较

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

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

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

SessionA SessionB
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 的引入,帮我们解决了幻读的问题,但同时也带来了一些“困扰”。

看下面的实例

SessionA SessionB
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 退化为间隙锁。
一个特殊场景:唯一索引上的范围查询会访问到不满足条件的第一个值为止。

等值查询间隙锁

SessionA SessionB SessionC
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 这行是可以的。

非唯一索引等值锁

SessionA SessionB SessionC
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。

主键索引范围锁

SessionA SessionB SessionC
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]。

非唯一索引范围锁

SessionA SessionB SessionC
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。

唯一索引范围锁特殊场景

SessionA SessionB SessionC
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也会被锁上。

-------------本文结束感谢您的阅读-------------