为何会数据不一致?
这是我们假设“select * from t where d=5 for update
这条语句只给d=5这一行,也就是id=5的这一行加锁”导致的。
所以我们认为,上面的设定不合理,要改。
那怎么改?
把扫描过程中碰到的行,也都加上写锁,再来看看执行效果。
- 假设扫描到的行都被加上了行锁
- 由于session A把所有行都加了写锁,所以session B在执行第一个update语句时就被锁住。需要等到T6时session A提交后,session B才能继续执行。
这样对于id=0这行,在DB的最终结果还是 (0,5,5)。在binlog里执行序列是这样:
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;/*所有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)*/
可见按日志顺序执行,id=0这行的最终结果也是(0,5,5)。所以,id=0这行问题解决了。
但id=1这一行,在DB里结果是(1,5,5),而根据binlog的执行结果是(1,5,100),即幻读依旧。
为何把所有记录都上锁了,还是阻止不了id=1这行的插入和更新?
在T3时,我们给所有行加锁时,id=1这行还不存在,不存在也就加不上锁。
即使把所有记录都加锁,还是阻止不了新插入的记录,这也是为什么“幻读”会被单独拿出来解决。
InnoDB解决幻读
- 幻读的原因
行锁只能锁行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读,InnoDB只好引入间隙锁(Gap Lock),两个值之间的空隙。比如文章开头的表t,初始化插入了6个记录,这就产生了7个间隙。 - 表t主键索引上的行锁和间隙锁
- 当执行 select * from t where d=5 for update时,就不止是给数据库中已有的6个记录加上了行锁,还同时加了7个间隙锁。这样就确保了无法再插入新记录。
即在一行行扫描过程中,不仅给行加上了行锁,还给行两边的空隙加上了间隙锁。
数据行是可以加上锁的实体,数据行之间的间隙,也是可以加上锁的实体。
两种行锁间的冲突关系
- 跟行锁有冲突关系的是“另外一个行锁”。
但间隙锁不一样,跟间隙锁存在冲突关系的,是“往这个间隙中插入一个记录”这个操作。
间隙锁之间不存在冲突关系。
举个例子: - 间隙锁之间不互锁
- session B不会被堵住。因为表t里并没c=7记录,因此session A加的间隙锁(5,10)。而session B也是在这个间隙加的间隙锁。它们有共同的目标,即:保护这个间隙,不允许插入值。但它们之间不冲突。
间隙锁和行锁合称next-key lock,每个next-key lock是前开后闭区间。即我们的表t初始化以后,如果用select * from t for update要把整个表所有记录锁起来,就形成了7个next-key lock,分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
本文没有特别说明,就把间隙锁记为开区间,把next-key lock记为前开后闭区间。
- supremum是啥玩意?
因为+∞
是开区间,代码实现上,InnoDB给每个索引加了不存在的最大值supremum,就符合后闭区间了。
间隙锁和next-key lock解决了幻读,但也带来“困扰”。
案例
需求
任意锁住一行,若:
- 该行不存在,就插入
- 存在,就更新数据
实现
begin; select * from t where id=N for update; /*如果行不存在*/ insert into t values(N,N,N); /*如果行存在*/ update t set d=N set id=N; commit;
就这?
insert … on duplicate key update
不就能解决。但在有多个唯一键时,该方法无法满足该需求。
逻辑分析
该逻辑一旦有并发,就可能死锁。可这个逻辑每次操作前用for update
锁了,已经是最严格模式了,怎么还有死锁?
模拟
两个session并发,假设N=9。
- 间隙锁导致的死锁
- 不需要用到后面的update语句,就已经死锁。
session A 执行select … for update,由于id=9这行不存在,因此会加间隙锁(5,10)
session B 执行select … for update,同样加间隙锁(5,10),间隙锁之间不冲突,因此可执行成功
session B 试图插入(9,9,9),被session A的间隙锁挡住,进入等待
session A试图插入(9,9,9),被session B的间隙锁挡住
session互相等待形成死锁。当然,InnoDB的死锁检测马上就发现了这对死锁关系,让session A的insert语句报错返回了。
所以间隙锁的引入可能导致同样语句锁住更大范围。
为解决幻读,引入这么多内容,有更简单方法吗?
没有特别说明,本文分析都是可重复读,间隙锁在可重复读隔离级别下才生效。
所以,你如果把隔离级别设置为读提交,就没间隙锁。
但同时,要解决可能出现的数据和日志不一致问题,要把binlog格式设为row。这也是很多公司使用的配置。
如果读提交隔离级别够用,即业务无需保证可重复读,考虑到读提交下操作数据的锁范围更小(无间隙锁),这个选择就是合适的。
如果大家都用读提交,可是逻辑备份时,mysqldump为什么要把备份线程设置成可重复读?
然后,在备份期间,备份线程用的是可重复读,而业务线程用的是读提交。同时存在两种事务隔离级别,会不会有问题?
进一步地,这两个不同的隔离级别现象有什么不一样的,关于我们的业务,“用读提交就够了”这个结论是怎么得到的?
如果业务开发和运维团队这些问题都没有弄清楚,那么“没问题”这个结论,本身就是有问题的。
总结
即使给所有行加上行锁,仍无法解决幻读,因此引入间隙锁。
行锁确实比较直观,判断规则也相对简单,间隙锁的引入会影响系统的并发度,也增加了锁分析的复杂度,但也有章可循。