insert into死锁分析

不管事务管理级别如何设置，只要存在唯一性约束并且可能insert/delete/update同一key值的记录，死锁都将无法100%的避免，只能是概率降低

唯一键冲突回滚造成的死锁, 不管是主键冲突,唯一键冲突,还是唯一联合索引冲突都会造成死锁, 但是加锁方式不同

在插入之前，会先在插入记录所在的间隙加上一个插入意向gap锁

成功插入后, insert会对插入成功的行加上排它锁

关于意向gap锁:

在insert操作之前，还会加一种锁，官方文档称它为insertion intention gap lock，也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时，只要插入的记录不是gap间隙中的相同位置，则无需等待其他session就可完成，这样就使得insert操作无须加真正的gap lock。
假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

假设发生了一个唯一键冲突错误，那么将会在重复的索引记录上加读锁。当有多个session同时插入相同的行记录时，如果另外一个session已经获得该行的排它锁，那么将会导致死锁(解释:发生重复唯一键冲突，各自请求的排他记录锁(LOCK_X | LOCK_REC_NOT_GAP)转成共享记录锁(LOCK_S | LOCK_REC_NOT_GAP)。)

1.以下是标准的主键冲突,回滚造成的死锁

2016-07-21 19:34:23 700000a3f000
*** (1) TRANSACTION:
TRANSACTION 36728, ACTIVE 199 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 13, OS thread handle 0x700000b0b000, query id 590 localhost root update
insert into aa values(6, 'test', 12, 3)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36728 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 36729, ACTIVE 196 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 14, OS thread handle 0x700000a3f000, query id 591 localhost root update
insert into aa values(6, 'test', 12, 3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36729 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36729 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)

 

 

实际生产环境中的一个死锁, 网络延迟导致多次触发 再来一单, 事物一先加购物车, 事物二和事物三唯一键重复被阻塞, 因为再来一单中商品有限购, 所以事务一先前插入的数据回滚, 事务二和事务三形成死锁.

2.这个是唯一联合索引冲突回滚造成的死锁日志

*** (1) TRANSACTION:
TRANSACTION 13737791, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 141846, OS thread handle 140142144308992, query id 12409272 10.66.157.186 saas_titanteam update
insert into shop_cart (bar_code, checked, item_id, item_uid, quantity, specification_id, user_id) values ('2429829170', 1, 71, 'd76ec80fbe5b6585fe2764aa63c7ee0ddf314baa', 1, 72, 40)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2846 page no 4 n bits 536 index shop_cart_user_id_specification_id_uindex of table `db_pro20180528001_wencai`.`shop_cart` trx id 13737791 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 75 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 800000000000002c; asc        ,;;
 1: len 8; hex 800000000000024b; asc        K;;
 2: len 8; hex 8000000000000126; asc        &;;

*** (2) TRANSACTION:
TRANSACTION 13737790, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 141840, OS thread handle 140142119016192, query id 12409274 10.66.157.186 saas_titanteam update
insert into shop_cart (bar_code, checked, item_id, item_uid, quantity, specification_id, user_id) values ('2429829170', 1, 71, 'd76ec80fbe5b6585fe2764aa63c7ee0ddf314baa', 1, 72, 40)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2846 page no 4 n bits 536 index shop_cart_user_id_specification_id_uindex of table `db_pro20180528001_wencai`.`shop_cart` trx id 13737790 lock_mode X locks gap before rec
Record lock, heap no 75 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 800000000000002c; asc        ,;;
 1: len 8; hex 800000000000024b; asc        K;;
 2: len 8; hex 8000000000000126; asc        &;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2846 page no 4 n bits 536 index shop_cart_user_id_specification_id_uindex of table `db_pro20180528001_wencai`.`shop_cart` trx id 13737790 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 75 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 800000000000002c; asc        ,;;
 1: len 8; hex 800000000000024b; asc        K;;
 2: len 8; hex 8000000000000126; asc        &;;

*** WE ROLL BACK TRANSACTION (2)

 

死锁日志分析:

lock_mode X locks gap before rec 表示为gap锁,Gap锁是用来防止insert的 

lock_mode X locks rec but not gap”表示为记录锁，非gap锁

lock_mode X   表示Next-Key Locks
lock_mode X locks gap before rec insert intention 表示Insert Intention Locks
  

其他的insert导致的死锁案例https://blog.csdn.net/asdfsadfasdfsa/article/details/83033756

gap锁,nextkey锁不兼容insertintention锁, 这也是gap锁和nextkey锁实现防止幻读产生的原因

insertintention兼容gap锁, nextkey锁

insert成功, 持有该记录的排他锁和insertintention锁

insertintion加锁成功但是执行插入不成功, 持有共享锁, (这是唯一现有x锁后再持有s锁成功的案例)

 gap lock的前置条件：
1 事务隔离级别为REPEATABLE-READ，innodb_locks_unsafe_for_binlog参数为0，且sql走的索引为非唯一索引

2 事务隔离级别为REPEATABLE-READ，innodb_locks_unsafe_for_binlog参数为0，且sql是一个范围的当前读操作，这时即使不是非唯一索引也会加gap lock