MySQL INNODB锁分析心得

场景重现

有一个多门店点餐系统，点餐就是个下订单的过程，数据库设计上是一个订单表存储了所有店铺的订单信息，则有一个order表，并且每条记录都会关联business_id

DDL:

CREATE TABLE `o` (  `id` int(11) NOT NULL,  `business_id` int(11) DEFAULT NULL,  `status` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

插入数据：

INSERT INTO test_innodb.`order` (id,business_id,status) VALUES (0,0,0),(5,5,5),(10,10,10),(11,10,15),(20,20,20),(25,25,25);

• 更新店铺id为10的订单状态为3

UPDATE o SET status = 3 WHERE business_id = 10;

查询当前INNODB_LOCKS的情况

MySQL [test_innodb]> SELECT * FROM information_schema.INNODB_LOCKS;+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+------------------------+| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table            | lock_index | lock_space | lock_page | lock_rec | lock_data              |+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+------------------------+| 41043937:3410:3:1 | 41043937    | X         | RECORD    | `test_innodb`.`order` | PRIMARY    |       3410 |         3 |        1 | supremum pseudo-record || 41043889:3410:3:1 | 41043889    | X         | RECORD    | `test_innodb`.`order` | PRIMARY    |       3410 |         3 |        1 | supremum pseudo-record |+-------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+------------------------+

可以看到当前lockdata中显示了supremum，实际上由于business_id不是索引，Mysql需要从根结点开始遍历所有主键索引，过滤找到匹配的值，所以所有记录包括 [30, supremum+] 的主键索引都被锁住了。

此时给business_id加上一个普通索引，重放上面的session情况，再次查询INNODB_LOCKS的情况

MySQL [test_innodb]> SELECT * FROM information_schema.INNODB_LOCKS;+-------------------+-------------+-----------+-----------+-----------------------+-----------------------+------------+-----------+----------+-----------+| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table            | lock_index            | lock_space | lock_page | lock_rec | lock_data |+-------------------+-------------+-----------+-----------+-----------------------+-----------------------+------------+-----------+----------+-----------+| 41042271:3410:4:6 | 41042271    | X,GAP     | RECORD    | `test_innodb`.`order` | order_business_id_IDX |       3410 |         4 |        6 | 20, 20    || 41034805:3410:4:6 | 41034805    | X,GAP     | RECORD    | `test_innodb`.`order` | order_business_id_IDX |       3410 |         4 |        6 | 20, 20    |+-------------------+-------------+-----------+-----------+-----------------------+-----------------------+------------+-----------+----------+-----------+

lock_type列中出现了新的字眼GAP，这是为了避免新数据插入产生的一种锁，根据目前SQL执行的情况，在普通索引business_id (5, 20)会被锁，主键索引id (5,20) 也会被加上锁。这是由于Mysql 先通过普通索引business_id 符合搜索值为10的索引，这个过程左边第一个不符合的条件为5，右边第一个不符合的条件为20，而当中的每一次遍历都会同时锁上主键索引。

理论分析

索引基础知识

1. 通过非主键索引查询时，Mysql先找到非主键索引，再找到主键索引，再找到记录（B+树）
2. 非唯一索引搜索时，会向左向右找到第一个不符合条件的索引（二分查找）
3. Mysql Innodb的锁作用是索引
4. 一个SQL中如果有多次加锁操作，他们并不是在一个指令内完成的

Next-Key Lock

众所周知，使用了INNODB引擎后自然就会有行锁的概念，而行锁又分为S锁和X锁，分别代表了读锁和写锁，他们之间有如下关系：

SHARE MODE、FOR UPDATE 、UPDATE、DELETE、INSERT 都会产生不同的锁类型，具体可以参考MySQL的官方文档，这里我们暂时只需要了解是行锁即可

再有，大部分场景下我们都会沿用Mysql的默认事务隔离级别：Repeatable read，简称RR（可重复读），这个机制本身可以避免脏读、重复读的问题，不过无法解决幻读问题，除非将隔离级别上升到Serializable（可串行化），但是这样性能损耗太大。

MySQL为了更好地解决幻读问题，在INNODB引擎里的可重复读隔离级别下加入了Gap Lock（间隙锁）的机制。对于间隙锁可以先大致理解为，MySQL为了避免新的数据插入造成幻读，加大了行锁的粒度，锁住了某个索引的数据区间。

所以 MVCC（RR的特性） + Gap Lock + Row Lock 解决了脏读、重复读、幻读问题。

• 从而，这里就产生了 Next-Key Lock 的定义： Gap Lock + Row Lock，这是一个前开后闭的区间。

MVCC的特性比较好理解和分析，这里重点分析RR下的MySQL的加锁过程。首先，这里总结了几条MySQL的加锁和优化原则：

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

案例分析

• 等值查询间隙锁
• 非唯一索引等值锁
• 主键索引范围锁
• 非唯一索引范围锁
• 唯一索引范围锁
• 死锁的例子

附录

MVCC + Next-Key Lock 没有办法解决下面这种例外情况：

# 事务T1，REPEATABLE READ隔离级别下mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM hero WHERE number = 30;Empty set (0.01 sec)# 此时事务T2执行了：INSERT INTO hero VALUES(30, 'g关羽', '魏'); 并提交mysql> UPDATE hero SET country = '蜀' WHERE number = 30;Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> SELECT * FROM hero WHERE number = 30;+--------+---------+---------+| number | name    | country |+--------+---------+---------+|     30 | g关羽   | 蜀      |+--------+---------+---------+1 row in set (0.01 sec)

在REPEATABLE READ隔离级别下，T1第一次执行普通的SELECT语句时生成了一个ReadView，之后T2向hero表中新插入了一条记录便提交了，ReadView并不能阻止T1执行UPDATE或者DELETE语句来对改动这个新插入的记录（因为T2已经提交，改动该记录并不会造成阻塞），但是这样一来这条新记录的trx_id隐藏列就变成了T1的事务id，之后T1中再使用普通的SELECT语句去查询这条记录时就可以看到这条记录了，也就把这条记录返回给客户端了。因为这个特殊现象的存在，你也可以认为InnoDB中的MVCC并不能完完全全的禁止幻读。

参考：mp.weixin.qq.com/s/wSlNZcQka…