insert into ... select 由于SELECT表引起的死锁情况分析

前提

说法一:在RR隔离级别下 INSERT SELECT 会对 SELECT 表中符合条件的数据加上 LOCK_S 锁。

说法二:(主键自增锁模式应该为0或1)

情景一:insert into table1 ...select * from table2:table1锁表,table2逐步锁(扫描一个锁一个)

情景二:insert into table1 ...select * from table2 order by 主键:table1锁表,table2逐步锁(扫描一个锁一个)

情景三:insert into table1 ...select * from table2 order by 非主键:table1锁表,table2一开始就锁全表

 

insert into ... select容易造成死锁的原因,后面的select语句对后表会逐步加s锁,前面的insert数量不一定,导致锁住另一个表整表auto-inc锁。 锁越多越容易出现死锁问题

 

模拟下面两个并发事务:

TX1TX2
begin;-
update b set name2='test' where id=2999;-
-insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999);
update b set name2='test' where id=999;

 

场景一

  • TX1:执行update将表b主键id=2999的记录加上LOCK_X

  • TX2:执行insert...select语句b表上的记录(996,997,998,999,2995,2996,2997,2998,2999)会申请加上LOCK_S, 但是id=2999已经加上LOCK_X,显然不能获得只能等待.

  • TX1:执行update需要获得表b主键id=999的LOCK_X显然这个记录已经被TX2加锁LOCK_S,只能等待,触发死锁检测

如下图红色记录为不能获得锁的记录:

场景二

这种情况比较极端只能在高并发上出现

  • TX1:执行update将表b主键id=2999的记录加上LOCK_X

  • TX2:执行insert...select语句b表上的记录(996,997,998,999,2995,2996,2997,2998,2999)会申请加上LOCK_S,因为上锁是有一个逐步加锁的过程,假设此时加锁到2997前那么TX2并不会等待

  • TX1:执行update需要获得表b主键id=999的LOCK_X显然这个记录已经被TX2加锁LOCK_S,只能等待

  • TX2:继续加锁LOCK_S 2997、2998、2999 发现2999已经被TX1加锁LOCK_X,只能等待,触发死锁检测

如下图红色记录为不能获得锁的记录:

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页