前言
如果希望直接获取结论,请看末尾【一些结论】模块
- 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
查看锁信息:
- 主键索引上申请了索引记录锁
- 记录锁锁的 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
锁信息:
- 主键索引申请了 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
锁信息:
- idx_price 索引申请了 next-key 锁( 范围为 (2000,3000) (3000,4000) )
- 主键索引申请了索引记录锁
验证间隙锁范围:
price 为 2001和3999 的记录需要等待间隙锁才能插入,而 price 为 1999 和 4000的记录不需要获取间隙锁,可以直接插入
1.6、普通索引:select * from order_info force index(idx_price) where price > 3000 for update
锁信息:
- idx_price 索引申请了 next-key 锁(范围:(3000,+∞))
- 主键索引申请了索引记录锁
新开一个事务,验证间隙锁范围:
插入price 为3001,4001,9999 的记录需要申请间隙锁;插入插入price 为 2999,100的记录不需要申请间隙锁
1.7、唯一索引:select * from order_info force index(idx_order_num) where order_num = 1030 for update
锁信息:
- 唯一索引 idx_order_num 上申请索引记录锁
- 主键索引申请了索引记录锁
1.8、唯一索引:select * from order_info force index(idx_order_num) where order_num > 1030 for update
锁信息:
- 索引 idx_order_num 申请了间隙锁和索引记录锁
- 主键索引申请了索引记录锁
一些结论
- innodb 通过 next-key 锁可以解决幻读问题
- 针对主键索引
- 精确查询只申请索引记录锁
- 范围查询会申请 next-key 锁
- 针对普通索引
- 精确查询会申请 next-key 锁
- 范围查询会申请 next-key 锁
- 针对唯一索引
- 精确查询会申请 索引记录锁
- 范围查询会申请 next-key 锁