一、上期问题再跟进
现有SQL如下:
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;
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
上期SQL问题如下:
begin;
select * from t where d=5 for update;
commit;
这个语句是怎么加锁的?
二、逐层拆解
🏁:1.首先明白什么是幻读?【打死都要记住】
幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。
🏁:2.幻读出现的场景是什么?
在RR隔离级别下,快照读不会看到别的事务插入的行数,只有当前读才会出现幻读的现象。
PS:幻读针对的是“新插入行”。
⭐️:3.幻读带来的问题是什么?(RR隔离级别下)
-
语义问题
session A 在 T1 时刻就声明了,“我要把所有 d=5 的行锁住,不准别的事务进行读写操作”。而实际上,这个语义被破坏了。 -
数据一致性问题(⭐️锁设计的初衷)
对session A 在 T1 时刻再加一个更新语句,即:update t set d=100 where d=5。
加上这一句话会有什么问题呢? 如果仅从执行过程中没有发现问题,但是如果从库根据binlog回放和数据同步的时候会发现数据不一致。即,所有d=5的行,d改成100。
update t set d=5 where id=0; /*(0,0,5)*/ update t set c=5 where id=0; /*(0,5,5)*/ insert into t values(1,1,5); /*(1,1,5)*/ update t set c=5 where id=1; /*(1,5,5)*/ update t set d=100 where d=5;/**/
⭐️问题的本质在于:锁只锁住了id=5这一行,所以对于其他事物的更新和插入【幻读】是不起作用的。
🏁:4.至此,MySQL的Innodb引擎是如何解决幻读?由于,行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。所以MySQL引入了间隙锁(Gap lock)
举例:比如文章开头的表 t,初始化插入了 6 个记录,这就产生了 7 个间隙。
🏁:5.Gap lock的特点。打死都要记住!!!!
- 跟间隙锁存在冲突关系的,是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
- 间隙锁和行锁合称 next-key lock,每个 next-key lock 是前开后闭区间。
- 间隙锁的引入会造成并发度的下降。具体原因见第6项。
🤔️:6.一个有意思的问题
业务逻辑这样的:任意锁住一行,如果这一行不存在的话就插入,如果存在这一行就更新它的数据,代码如下:
/假设N=9/
begin;
select * from t where id=9 for update; – a1
/如果行不存在/
insert into t values(9,9,9); --a2
/如果行存在/
update t set d=9 set id=9; --a3
commit;
现象是:这个逻辑一旦有并发,就会碰到死锁。你一定也觉得奇怪,这个逻辑每次操作前用 for update 锁起来,已经是最严格的模式了,怎么还会有死锁呢?
⚠️:这个不是 insert … on duplicate key update 就能解决吗?但其实在有多个唯一键的时候,这个方法是不能满足这位提问同学的需求的。后面会有解释为什么?此处自己也不明白。-- mark一下。
答案:当并发执行a1时会加间隙锁(5-10],到a2时刻就会造成两个线程锁相互等待(PS:这里根本到不了a3时刻)。
🏁:7.间隙锁是在可重复读隔离级别下才会生效的。所以,你如果把隔离级别设置为读提交的话,就没有间隙锁了。但同时,你要解决可能出现的数据和日志不一致问题,需要把 binlog 格式设置为 row。
🏁:8.业务选择的标准,不需要可重复读的保证,这样考虑到读提交下操作数据的锁范围更小(没有间隙锁),这个选择是合理的。
三、课后题
这里 session B 和 session C 的 insert 语句都会进入锁等待状态。你可以试着分析一下,出现这种情况的原因是什么?PS:其中 session C 被锁住这个分析是有点难度的。
四、自古评论出人才
🏁:9.在同一个事务中,两次读取到的数据不一致的情况称为幻读和不可重复读。幻读是针对insert导致的数据不一致,不可重复读是针对 delete、update导致的数据不一致。
🏁:10.innodb_locks_unsafe_for_binlog 这个参数就是这个意思 “不加gap lock”。(PS:8.0就没有了)
🏁:11.怎么开启的锁日志?set global innodb_status_output_locks=1;
然后使用show engine innodb status\G 查看,TRANSACTIONS 相关的信息下,就能看到锁信息。
🤔️:12?不懂。读提交隔离级别一般没有gap lock,不过也有例外情况, 比如insert 出现主键冲突的时候,也可能加间隙锁
🤔️:13?不懂。mysql官方提到自增锁是个表级锁,以及实际项目中高并发insert是否需要避免自增主键?
innodb_auroinc_lock_mode设置为2,binlog_formate设置成row就行,没有表锁问题。参考:https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html