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加锁的规则才能更好的进行理解。

动手实践

准备

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（第一个会话）

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（第二个会话）

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

session3（第三个会话）

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（第一个会话）

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（第二个会话）

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（第三个会话）

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（第一个会话）

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（第二个会话）

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

session3（第三个会话）

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（第一个会话）

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（第二个会话）

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

session3（第三个会话）

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（第一个会话）

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（第二个会话）

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

session3（第三个会话）

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，就可以确定不用往后再找了。但实现上还是这么做了。

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

插入一行数据

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

session1（第一个会话）

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（第二个会话）

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

session3（第三个会话）

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。

加锁区域

limit 语句加锁

同样插入一行数据

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

session1（第一个会话不加limit）

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）

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

session1（第一个会话加limit）

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）

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

分析：

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（第一个会话）

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（第二个会话）

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

session1（第一个会话）

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

session2（第二个会话）

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语句执行完就释放“不满足条件的行”的行锁，而不是在事务结束的时候才释放。总结来说，读提交隔离级别下，锁的范围更小，锁的时间更短。