从一些例子看 mysql innodb 在 RR 隔离级别的 next-key 锁的行为

前言

如果希望直接获取结论，请看末尾【一些结论】模块

• mysql 版本 5.7
• 引擎 innodb
• 隔离级别 RR （RepeatableRead）
• 需要了解 mysql 的 next-key 锁（间隙锁 + 索引记录锁）
• 本文加锁使用读取锁定（也可使用 delete/update … where … ）
• 设置 innodb_status_output_locks ，然后可以使用 show engine innodb status 查看详细的锁信息
• 需要了解聚簇索引和非聚簇索引
• 刚开始学习这块知识，有错误，欢迎指出

表结构(id 上建立了主键索引，price 列建立了普通索引，order_num 列建立了唯一索引)

CREATE TABLE `order_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `price` int(11) DEFAULT NULL,
  `order_num` int(11) DEFAULT NULL,
  `user_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_order_num` (`order_num`),
  KEY `idx_price` (`price`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

原始数据

+----+-------+-----------+---------+
| id | price | order_num | user_id |
+----+-------+-----------+---------+
|  1 |  1000 |      1010 |       1 |
|  2 |  2000 |      1020 |       5 |
|  3 |  3000 |      1030 |       2 |
|  4 |  4000 |      1040 |       1 |
|  5 |  6000 |      1050 |      10 |
+----+-------+-----------+---------+

一些 sql 解析

由于数据量比较少，所以，走非主键索引的时候，使用 force index
验证间隙锁的时候，需要保持第一个事务不释放

关于如何 show engine innodb status;
命令中查看锁的详细信息，我在文后附了几个链接，可以先了解下。推荐这个链接MySQL锁系列（二）之 锁解读

1.1、走主键索引：select * from order_info where id = 1 for update

查看锁信息：

1. 主键索引上申请了索引记录锁
2. 记录锁锁的 id 字段为 1

1.2、走主键索引：select * from order_info where id > 2 for update

注意： 这个测试之前，需要将 id 字段的 unsigned 撤销，不然无法设置 id 为 -1.测试完之后，需要再恢复为 unsigned

查看锁信息：

1.主键索引申请了 next-key 锁，锁的间隙(2,+∞)

1.3、无任何索引：select * from order_info where user_id = 1 for update

锁信息：

1.主键索引申请了 next-key 锁，锁的间隙为聚簇索引的所有间隙，即(-∞,1)(1,2)(2,3)(3,4)(4,5)(5,+∞)

1.4、无任何索引：select * from order_info where user_id >4 for update

锁信息：

1. 主键索引申请了 next-key 锁，锁的间隙为聚簇索引的所有间隙，即(-∞,1)(1,2)(2,3)(3,4)(4,5)(5,+∞)

1.5、普通索引：select * from order_info force index(idx_price) where price = 3000 for update

锁信息：

1. idx_price 索引申请了 next-key 锁（ 范围为 (2000,3000) (3000,4000) ）
2. 主键索引申请了索引记录锁

验证间隙锁范围：

price 为 2001和3999 的记录需要等待间隙锁才能插入，而 price 为 1999 和 4000的记录不需要获取间隙锁，可以直接插入

1.6、普通索引：select * from order_info force index(idx_price) where price > 3000 for update

锁信息：

1. idx_price 索引申请了 next-key 锁（范围：(3000,+∞)）
2. 主键索引申请了索引记录锁

新开一个事务，验证间隙锁范围：

插入price 为3001，4001，9999 的记录需要申请间隙锁；插入插入price 为 2999，100的记录不需要申请间隙锁

1.7、唯一索引：select * from order_info force index(idx_order_num) where order_num = 1030 for update

锁信息：

1. 唯一索引 idx_order_num 上申请索引记录锁
2. 主键索引申请了索引记录锁

1.8、唯一索引：select * from order_info force index(idx_order_num) where order_num > 1030 for update

锁信息：

1. 索引 idx_order_num 申请了间隙锁和索引记录锁
2. 主键索引申请了索引记录锁

一些结论

1. innodb 通过 next-key 锁可以解决幻读问题
2. 针对主键索引
   1. 精确查询只申请索引记录锁
   2. 范围查询会申请 next-key 锁
3. 针对普通索引
   1. 精确查询会申请 next-key 锁
   2. 范围查询会申请 next-key 锁
4. 针对唯一索引
   1. 精确查询会申请 索引记录锁
   2. 范围查询会申请 next-key 锁

Reference

• 解决死锁之路（终结篇） - 再见死锁
• 【MySQL】如何阅读死锁日志
• MySQL锁系列（二）之 锁解读
• 【原创】惊！史上最全的select加锁分析(Mysql)