MySQL加锁实践

间隙锁(Gap Lock)

首先谈谈间隙锁是什么。

间隙锁是专门用于解决幻读这种问题的锁,它锁的了行与行之间的间隙,能够阻塞新插入的操作,同时间隙锁的引入也带来了一些新的问题,比如:降低并发度,可能导致死锁。

这里特别记录下读读不互斥,读写/写读/写写是互斥的,但是间隙锁之间是不冲突的,间隙锁会阻塞插入操作,另外,间隙锁在可重复读级别下才是有效的。

加锁的规则

MySQL加锁总结下来包括以下方面包括两个原则、两个优化、一个bug:

原则1: 加锁的基本单位是next-key lock。next-key lock 是前开后闭区间,next-key lock是行锁和间隙锁的组合

原则2: 查找过程中访问到的对象才会加锁–对于未命中索引的查询要走全表扫描,这种在扫描前就给全表加上了next-key lock

优化1: 索引上的等值查询,给唯一索引上加锁的时候,next-key lock会退化为行锁,是匹配上的时候,如果没有匹配上,自然也就不会退化为行锁了,此时会用到优化2,从而退化成间隙锁

优化2: 索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next-key lock会退化为间隙锁

一个bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止(唯一索引范围锁的bug在最新的8.0.18已经修复)

在后面的自己实践过程中牢记MySQL加锁的规则才能更好的进行理解。

动手实践

准备

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql> 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;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> insert into t values(0,0,0),(5,5,5),
    -> (10,10,10),(15,15,15),(20,20,20),(25,25,25);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> select * from t;
+----+------+------+
| id | c | d |
+----+------+------+
| 0 | 0 | 0 |
| 5 | 5 | 5 |
| 10 | 10 | 10 |
| 15 | 15 | 15 |
| 20 | 20 | 20 |
| 25 | 25 | 25 |
+----+------+------+
6 rows in set (0.00 sec)

等值查询间隙锁

session1(第一个会话)

1
2
3
4
5
6
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set d=d+1 where id=7;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

session2(第二个会话)

1
2
3
mysql> insert into t values(8,8,8);
(blocked)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session3(第三个会话)

1
2
3
mysql> update t set d=d+1 where id=10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

分析:

表t中无id=7的记录

1、根据next-key lock原则,左开右闭session1加锁范围(5, 10]
2、索引上的等值查询,向右遍历时且最后一个值id=10时不满足等值条件的时候,next-key lock会退化为间隙锁,因此加锁范围为(5, 10)

结论:session 2 要往这个间隙里面插入id=8的记录会被锁住,但是 session 3 修改id=10这行是可以的。

非唯一索引等值锁

session1(第一个会话)

1
2
3
4
5
6
7
8
9
10
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from t where c=5 lock in share mode;
+----+
| id |
+----+
| 5 |
+----+
1 row in set (0.00 sec)

session2(第二个会话)

1
2
3
mysql> update t set d=d+1 where id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

session3(第三个会话)

1
2
3
mysql> insert into t values(7,7,7);
(blocked)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

分析:session 1 要给索引 c上c=5的这一行加上读锁

1、首先根据next-key lock 原则,左开右闭 给c索引为(0, 5]加锁。

2、因为c为普通索引(非唯一索引),索引上的等值查询,向右遍历时且最后一个值不满足c=10的等值条件的时候,next-key lock会退化为间隙锁,此时访问到的对象都加锁,即给c为(5, 10]都要加next-key lock 锁

3、根据优化2,向右遍历最后一个不满足的值时,退化为间隙锁,(5, 10);注意前面分析的(0, 5]间隙锁还是存在的,此时加锁的范围为c索引上(0, 5](5, 10)的间隙锁。

4、根据原则2,访问到的对象(这里指c=5索引这一列)才会加锁。这个查询使用的是覆盖索引,因此并不需要访问主键索引,所以session2可以执行成功,这样也验证了访问到的对象才会加锁(访问到的对象才会加锁,这个“对象”指的是c列,不是 记录行。补充一下:加锁,是加在索引上的。列上,有索引,就加在索引上,列上,没有索引,就加在主键上)

这里要特殊说明下 lock in share mode 只锁覆盖索引,但是如果是 for update 就不一样了。执行 for update 时,系统会认为你接下来要更新数据,因此会顺便给主键索引上满足条件的行加上行锁。

数据行加读锁,如果查询字段使用了覆盖索引,访问到的对象只有普通索引,并没有访问到主键索引,则不会锁主键索引。如果没有使用覆盖索引,则会回表查询,访问到主键索引,这样主键索引也会加锁。

主键索引范围锁

session1(第一个会话)

1
2
3
4
5
6
7
8
9
10
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id>=10 and id<11 for update;
+----+------+------+
| id | c | d |
+----+------+------+
| 10 | 10 | 11 |
+----+------+------+
1 row in set (0.00 sec)

session2(第二个会话)

1
2
3
mysql> insert into t values(13,13,13);
(blocked)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session3(第三个会话)

1
2
3
mysql> update t set d=d+1 where id=15;
(blocked)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

分析:

1、开始执行 访问id=10加锁范围id索引上(5, 10] next-key lock 由于id唯一索引退化为行锁id=10

2、范围查询向后查找 第一个不满足条件的值id=15加锁范围(10, 15]

结论:session2、session3 阻塞

非唯一索引范围锁

session1(第一个会话)

1
2
3
4
5
6
7
8
9
10
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where c>=10 and c<11 for update;
+----+------+------+
| id | c | d |
+----+------+------+
| 10 | 10 | 11 |
+----+------+------+
1 row in set (0.00 sec)

session2(第二个会话)

1
2
3
mysql> insert into t values(8,8,8);
(blocked)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session3(第三个会话)

1
2
3
mysql> update t set d=d+1 where c=15;
(blocked)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

分析:对c字段加锁(非唯一索引)

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

唯一索引范围锁 bug

session1(第一个会话)

1
2
3
4
5
6
7
8
9
10
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id>10 and id<=15 for update;
+----+------+------+
| id | c | d |
+----+------+------+
| 15 | 15 | 15 |
+----+------+------+
1 row in set (0.00 sec)

session2(第二个会话)

1
2
3
mysql> update t set d=d+1 where id=20;
(blocked)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session3(第三个会话)

1
2
3
mysql> insert into t values(16,16,16);
(blocked)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

分析:

1、session A 是一个范围查询,按照原则 1 的话,应该是索引 id 上只加(10, 15]这个 next-key lock,并且因为 id 是唯一键,所以循环判断到id=15这一行就应该停止了。

2、但是实现上,InnoDB 会往前扫描到第一个不满足条件的行为止,也就是id=20。而且由于这是个范围扫描,因此索引 id 上的(15, 20]这个 next-key lock 也会被锁上。

所以你看到了,session B 要更新id=20这一行,是会被锁住的。同样地,session C 要插入id=16的一行,也会被锁住。

照理说,这里锁住id=20这一行的行为,其实是没有必要的。因为扫描到id=15,就可以确定不用往后再找了。但实现上还是这么做了。

非唯一索引上存在”等值”的例子

插入一行数据

1
mysql> insert into t values(30,10,30);

MySQL加锁实践-索引C

session1(第一个会话)

1
2
3
4
5
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t where c=10;
Query OK, 2 rows affected (0.00 sec)

session2(第二个会话)

1
2
3
mysql> insert into t values(12,12,12);
(blocked)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session3(第三个会话)

1
2
3
mysql> update t set d=d+1 where id=15;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

分析:

1、session1 在遍历的时候首先访问第一个c=10的记录 ,对(c=5,id=5)(c=10,id=10)左开右闭 区域记录加 next-key lock。

2、session1 向右查询第二个c=10的记录,找到(c=10,id=30)记录之后继续向右查找直到找到(c=15,id=15)这一行,根据等值查询优化,找到第一个不等值的记录时退化为(c=10,id=30)(c=15,id=15)左开右开间隙锁,(c=15,id=15)记录不包括区域记录加 next-key lock。

加锁区域

MySQL加锁实践-索引C加锁区域

limit 语句加锁

同样插入一行数据

1
2
mysql> insert into t values(30,10,30);
Query OK, 1 row affected (0.00 sec)

session1(第一个会话不加limit)

1
2
3
4
5
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t where c=10;
Query OK, 2 row affected (0.00 sec)

session2(第二个会话不加limit)

1
2
3
mysql> insert into t values(12,12,12);
(blocked)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

session1(第一个会话加limit)

1
2
3
4
5
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t where c=10 limit 2;
Query OK, 2 rows affected (0.00 sec)

session2(第二个会话加limit)

1
2
mysql> insert into t values(12,12,12);
Query OK, 1 row affected (0.01 sec)

MySQL加锁实践-加limit后索引C加锁区域

分析:

1、session 1 的 delete 语句加了limit 2。你知道表 t 里c=10的记录其实只有两条,因此加不加limit 2,删除的效果都是一样的,但是加锁的效果却不同。可以看到,session 2 的 insert 语句执行通过了。

2、delete 语句明确加了limit 2的限制,因此在遍历到(c=10,id=30)这一行之后,满足条件的语句已经有两条,循环就结束了。因此,索引 c 上的加锁范围就变成了从(c=5,id=5)(c=10,id=30)这个前开后闭区间

在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数,让操作更安全,还可以减小加锁的范围

一个死锁的例子

session1(第一个会话)

1
2
3
4
5
6
7
8
9
10
11
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select id from t where c=10 lock in share mode;
+----+
| id |
+----+
| 10 |
| 30 |
+----+
2 rows in set (0.00 sec)

session2(第二个会话)

1
2
mysql> update t set d=d+1 where c=10;
(blocked)

session1(第一个会话)

1
2
mysql> insert into t values(8,8,8);
Query OK, 1 row affected (0.00 sec)

session2(第二个会话)

1
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

分析:

1、session A 启动事务后执行查询语句加 lock in share mode,在索引 c 上加了 next-key lock (5, 10]和间隙锁(10, 15)

2、session B 的 update 语句也要在索引 c 上加 next-key lock (5, 10],进入锁等待

3、然后 session A 要再插入(8,8,8)这一行,被 session B 的间隙锁锁住。由于出现了死锁,InnoDB 让 session B 回滚。

你可能会问,session B 的 next-key lock 不是还没申请成功吗?其实是这样的,session B 的“加 next-key lock (5, 10] ”操作,实际上分成了两步,先是加(5, 10)的间隙锁,加锁成功;然后加c=10的行锁,这时候才被锁住的。也就是说,我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道,具体执行的时候,是要分成间隙锁和行锁两段来执行的。

总结下来,可重复读隔离级别遵守两阶段协议,所有的锁都在事务提交或者回滚才释放;read-commited没有gap lock ,read-commited语句执行完就释放“不满足条件的行”的行锁,而不是在事务结束的时候才释放。总结来说,读提交隔离级别下,锁的范围更小,锁的时间更短。