文章目录
1.面试问题
MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗
可以
2.什么是幻读
SELECT * FROM t_test WHERE id > 100;
只要 T1 和 T2 时刻执行产生的结果集是不相同的,那就发生了幻读的问题
- T1 时间执行的结果是有 5 条行记录,而 T2 时间执行的结果是有 6 条行记录,那就发生了幻读的问题。
- T1 时间执行的结果是有 5 条行记录,而 T2 时间执行的结果是有 4 条行记录,也是发生了幻读的问题。
3.如何结局幻读
- 针对
快照读
(普通 select 语句),是通过MVCC
方式解决了幻读,因为可重复读隔离级别下,事务执行过程中看到的数据,一直跟这个事务启动时看到的数据是一致的,即使中途有其他事务插入了一条数据,是查询不出来这条数据的,所以就很好了避免幻读问题。 - 针对
当前读
(select … for update 等语句),是通过next-key lock
(记录锁+间隙锁)方式解决了幻读,因为当执行 select … for update 语句的时候,会加上 next-key lock,如果有其他事务在 next-key lock 锁范围内插入了一条记录,那么这个插入语句就会被阻塞,无法成功插入,所以就很好了避免幻读问题。
4.实验验证
用户表(t_user):
现在有一个 A 事务执行了一条查询语句,查询到年龄大于 20 岁的用户共有 6 条行记录
然后, B 事务执行了一条删除 id = 2 的语句
此时,B 事务的删除语句就陷入了等待状态,说明是无法进行删除的。
因此,MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读问题
5.加锁分析
A 事务在执行 select … for update 语句时,具体加了什么锁呢
我们可以通过 select * from performance_schema.data_locks\G; 这条语句,查看事务执行 SQL 过程中加了什么锁。
- 表锁(LOCK_TYPE: TABLE):X 类型的意向锁;
- 行锁(LOCK_TYPE: RECORD):X 类型的 next-key 锁;
图中 LOCK_TYPE 中的 RECORD 表示行级锁,而不是记录锁的意思:
- 如果 LOCK_MODE 为 X,说明是 next-key 锁;
- 如果 LOCK_MODE 为 X, REC_NOT_GAP,说明是记录锁;
- 如果 LOCK_MODE 为 X, GAP,说明是间隙锁;
因此,此时事务 A 在主键索引(INDEX_NAME : PRIMARY)上加了 10 个 next-key 锁,如下:
X 型的 next-key 锁,范围:(-∞, 1]
X 型的 next-key 锁,范围:(1, 2]
X 型的 next-key 锁,范围:(2, 3]
X 型的 next-key 锁,范围:(3, 4]
X 型的 next-key 锁,范围:(4, 5]
X 型的 next-key 锁,范围:(5, 6]
X 型的 next-key 锁,范围:(6, 7]
X 型的 next-key 锁,范围:(7, 8]
X 型的 next-key 锁,范围:(8, 9]
X 型的 next-key 锁,范围:(9, +∞]
这相当于把整个表给锁住了,其他事务在对该表进行增、删、改操作的时候都会被阻塞。
事务 A 的这条查询语句是全表扫描,锁是在遍历索引的时候加上的,并不是针对输出的结果加锁。
在线上在执行 update、delete、select … for update 等具有加锁性质的语句,一定要检查语句是否走了索引,如果是全表扫描的话,会对每一个索引加 next-key 锁,相当于把整个表锁住了,
如果对 age 建立索引,事务 A 这条查询会加什么锁呢
因为表中有两个索引,分别是主键索引和 age 索引,所以会分别对这两个索引加锁。
事务 A 加上锁后,事务 B、C、D、E 在执行以下语句都会被阻塞。
6.总结
在 MySQL 的可重复读隔离级别下,针对「当前读」的查询语句会对索引加记录锁+间隙锁,这样可以避免其他事务执行「增、删、改」时导致幻读的现象。
有一点要注意的是,在执行 update、delete、select … for update 等具有加锁性质的语句,一定要检查语句是否走了索引,如果是全表扫描的话,会对每一个索引加 next-key 锁,相当于把整个表锁住了,这是挺严重的问题。