Update加锁分析

来自《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>

发表评论

电子邮件地址不会被公开。