前提
说法一:在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锁。 锁越多越容易出现死锁问题
模拟下面两个并发事务:
TX1 | TX2 |
---|---|
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,只能等待,触发死锁检测
如下图红色记录为不能获得锁的记录:
参考:https://blog.csdn.net/asdfsadfasdfsa/article/details/83030011