来自《MYSQL内核:INNODB存储引擎 卷1》第九章的习题
作者的本意是两面的两组做笛卡尔积!!然后交换两组的先后顺序,再做笛卡尔积;
然后隔离级别rr rc都要做测试!!
===========RC级别==========
Session 1: update t set b=b+1 where a=1;
然后 Session 2: update t set c=c+1 where b=2;
冲突的原因:session1上对表加IX锁,然后对主键索引加锁,x locks but no gap,其实还包含对辅助索引b=2和b=3的隐式锁(因为改的是辅助索引上的列,prediacte lock要求–不改的话会导致冲突,另外注意隐式锁是包括了修改前和修改后两部分的值–之所以修改前的也要加隐式锁,是考虑了事务回滚的情况),x locks but no gap(log output此时还看不出来)
session2做update时等效于delete+insert ,
Session2:对表加IX锁,这个可以获得,
通过辅助索引加锁的语句–即where后面是辅助索引,首先对辅助索引记录加锁,然后对聚集索引记录加锁–这两个锁都是显式锁。然后有隐式锁的话最先做隐式锁转换。
对聚集索引记录加锁过程中,需要把记录上的implicit lock转化为explicit lock,即辅助索引b上要生成一个锁,x locks but no gap。–这个可以成功,但是锁的归属是原先的事务,不是本事务;–此时聚集索引上的加的锁还没有,先暂缓
对辅助索引b加锁,x locks but no gap,无法获得,原因是与上一步生成的辅助索引b的x锁冲突。
对聚集索引加锁–因为上步骤失败,这里也没执行
总结:
通过辅助索引加锁的语句顺序如下(也可见笔记:第九章勘误):
对行记录隐式锁转换(因为对于更新操作,流程中涉及对聚集索引加锁,需要先对行记录做隐式锁转换)
对辅助索引记录加锁
对聚集索引记录加锁
注意:
上面的语句显示不全:事务7986只打印出了等待的锁,而没有打印已经持有的锁。
另外注意图中箭头处,之所以是两个锁结构,是因为一个是table lock,另一个是record lock(见圆圈处)
后面还有测试到这种情况:单独执行session2的update语句也能在log output里能看出来两条锁记录,说明辅助索引加锁的语句,加锁过程中对主键加的是显式锁。
(另外,对以下语句做测试也是类似上面的结果:
Session 1: update t set b=b+1 where a=1;
然后 Session 2: update t set c=c+1 where b=3;
进一步说明了session1上辅助索引包含的隐式锁是修改前的+修改后的两部分)
———————————–
Session 1: update t set b=b+1 where a=1;
然后 Session 2: update t set b=b+1 where b=2;
冲突的原因:session1上对表加IX锁,然后对主键索引加锁,x locks but no gap,其实还包含对辅助索引b=2和b=3的隐式锁(因为改的是辅助索引上的列,prediacte lock要求–不改的话会导致冲突,另外注意隐式锁是包括了修改前和修改后两部分的值–之所以修改前的也要加隐式锁,是考虑了事务回滚的情况),x locks but no gap(log output此时还看不出来)
session2做update时对表加IX锁,这个可以获得,然后做隐式锁转换–生成一个辅助索引b的x locks but no gap,归属于原先的trx,
接下来想要获得辅助索引上的x locks but no gap,与会话1冲突,所以要等。
对聚集索引a加锁,由于上一步失败,此步没做。
———————————–
Session 1: update t set b=b+1 where a=1;
然后 Session 2: delete from t where a=2;
session1上对表加IX锁,然后对主键索引加锁,x locks but no gap,其实还包含对辅助索引b=2和b=3的隐式锁(因为改的是辅助索引上的列,prediacte lock要求–不改的话会导致冲突,另外注意隐式锁是包括了修改前和修改后两部分的值–之所以修改前的也要加隐式锁,是考虑了事务回滚的情况),x locks but no gap(log output此时还看不出来)
Session2的update操作只会产生对表的ix锁,查询未命中,不会冲突。
单独执行session2的语句的效果:
———————————–
Session 1: update t set b=b+1 where c=3;
然后 Session 2: update t set c=c+1 where b=2;
冲突的原因:session1上对表加了IX锁,主键索引上加的是x locks but no gap,其实还包含对辅助索引b=2和b=3的隐式锁(因为改的是辅助索引上的列,prediacte lock要求–不改的话会导致冲突,另外注意隐式锁是包括了修改前和修改后两部分的值–之所以修改前的也要加隐式锁,是考虑了事务回滚的情况),x locks but no gap(log output此时还看不出来)
注意:session1的操作不会锁表,做了优化。效果与锁定主键一样。
session2做update时对表加IX锁,然后:
对行记录隐式锁转换:成功
对辅助索引记录加锁:失败
对聚集索引记录加锁:未执行
———————————–
Session 1: update t set b=b+1 where c=3;
然后 Session 2: update t set b=b+1 where b=2;
session1上对表加了IX锁,主键索引上加的是x locks but no gap,其实还包含对辅助索引b=2和b=3的隐式锁(因为改的是辅助索引上的列,prediacte lock要求–不改的话会导致冲突,另外注意隐式锁是包括了修改前和修改后两部分的值–之所以修改前的也要加隐式锁,是考虑了事务回滚的情况),x locks but no gap(log output此时还看不出来)
注意:session1的操作不会锁表,做了优化。效果与锁定主键一样。
session2做update时对表加IX锁,然后:
对行记录隐式锁转换:成功
对辅助索引记录加锁:失败
对聚集索引记录加锁:未执行
———————————–
Session 1: update t set b=b+1 where c=3;
然后 Session 2: delete from t where a=2;
session1上对表加了IX锁,主键索引上加的是x locks but no gap,其实还包含对辅助索引b=2和b=3的隐式锁(因为改的是辅助索引上的列,prediacte lock要求–不改的话会导致冲突,另外注意隐式锁是包括了修改前和修改后两部分的值–之所以修改前的也要加隐式锁,是考虑了事务回滚的情况),x locks but no gap(log output此时还看不出来)
注意:session1的操作不会锁表,做了优化。效果与锁定主键一样。
session2做update时对表加IX锁,然后由于未命中,不产生其他锁,不发生冲突。
———————————–
Session 1: update t set a=a+1 where a=1;
然后 Session 2: update t set c=c+1 where b=2;
Session1上上对表加了IX锁,主键索引上加的是x locks but no gap,这里没有对辅助索引b的隐式锁(改的不是辅助索引的列),但是有对主键索引a=1和a=2的隐式锁–之所以修改前的也要加隐式锁,是考虑了事务回滚的情况
Session2上做update时对表加IX锁,然后:
对行记录隐式锁转换:不涉及
对辅助索引记录加锁:成功
对聚集索引记录加锁:失败
———————————–
Session 1: update t set a=a+1 where a=1;
然后 Session 2: update t set b=b+1 where b=2;
Session1上对表加了IX锁,主键索引上加的是x locks but no gap,这里没有对辅助索引b的隐式锁(改的不是辅助索引的列),但是有对主键索引a=1和a=2的隐式锁–之所以修改前的也要加隐式锁,是考虑了事务回滚的情况
Session2上做update时对表加IX锁,然后:
对行记录隐式锁转换:不涉及
对辅助索引记录加锁:成功
对聚集索引记录加锁:失败
———————————–
Session 1: update t set a=a+1 where a=1;
然后 Session 2: delete from t where a=2;
Session1里对表加了IX锁,主键索引上加的是x locks but no gap,这里没有对辅助索引b的隐式锁(改的不是辅助索引的列),但是有对主键索引a=1和a=2的隐式锁–之所以修改前的也要加隐式锁,是考虑了事务回滚的情况
Session2对聚集索引加锁前,先把a=2的行记录的隐式锁转化为显式锁,这个锁归属于最早的trx;
接下来对聚集索引加锁,与上一步产生的冲突。
另外注意不能按照trx id的大小来判断trx的先后顺序,见上图。
———————————–
Session 1: delete from t where a=1;
然后 Session 2: update t set c=c+1 where b=2;
Session1里对表加了IX锁,主键索引上加的是x locks but no gap,这里有对辅助索引b的隐式锁(因为需要把辅助索引的列删除)
Session2做update时对表加IX锁,然后:
对行记录隐式锁转换:成功
对辅助索引记录加锁:失败
对聚集索引记录加锁:未执行
———————————–
Session 1: delete from t where a=1;
然后 Session 2: update t set b=b+1 where b=2;
Session1里对表加了IX锁,主键索引上加的是x locks but no gap,这里有对辅助索引b的隐式锁(因为需要把辅助索引的列删除):
Session2做update时对表加IX锁,然后:
对行记录隐式锁转换:成功
对辅助索引记录加锁:失败
对聚集索引记录加锁:未执行
———————————–
Session 1: delete from t where a=1;
然后 Session 2: delete from t where a=2;
Session1里对表加了IX锁,主键索引上加的是x locks but no gap,这里有对辅助索引b的隐式锁(因为需要把辅助索引的列删除)
Session2里对表加了IX锁,未匹配到记录,没有其他锁;
=========剩余未完成:把两组的先后顺序交换,再做笛卡尔积;以及修改隔离级别为RR,再观察gap锁的情况====================
记忆:
Update/delete在前:考虑加隐式锁;
Update/delete在后:考虑隐式锁转换
Update/delete全程:通过辅助索引加锁的语句,考虑额外地主键索引上也要加锁
例子:
delete from t where a=2;
如果在前,考虑加隐式锁–因为更改的列包括辅助索引的列,要把它删除;
如果在后,考虑隐式锁转换,因为是对聚集索引记录加锁,需要先转换;
三条general rule:
=================================================================================
另外参考这篇文章: <https://www.aneasystone.com/archives/2017/12/solving-dead-locks-three.html>