很烦，又一次遇到数据库死锁，不过有了前车之鉴，这次很容易就发现了死锁的原因。
业务场景：批量并发操作（约1000w+的数据迁移），从横表迁移到纵表。
死锁日志如下
死锁日志
show engine innodb status 查看innodb死锁日志后 发现是并发insert for update 导致(以下日志做了删减)
------------------------
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5127 page no 71121 n bits 144 index UNIQ_KEY of table `xiaodai_asset`.`compensatory_amount_detail` trx id 8751986772 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 74 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 80000000028b1317; asc
1: len 18; hex 636f6d70656e7361746f7279416d6f756e74; asc compensatoryAmount;;
2: len 6; hex 434f4d503034; asc COMP04;;
3: len 8; hex 000000000041c4d4; asc A ;;
*** (2) TRANSACTION:
TRANSACTION 8751986770, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 32374, OS thread handle 139957178423040, query id 92147697 10.238.160.34 u_asset_rw update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5127 page no 71121 n bits 144 index UNIQ_KEY of table `xiaodai_asset`.`compensatory_amount_detail` trx id 8751986770 lock_mode X locks gap before rec
Record lock, heap no 74 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 80000000028b1317; asc ;;
1: len 18; hex 636f6d70656e7361746f7279416d6f756e74; asc compensatoryAmount;;
2: len 6; hex 434f4d503034; asc COMP04;;
3: len 8; hex 000000000041c4d4; asc A ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5127 page no 71121 n bits 144 index UNIQ_KEY of table `xiaodai_asset`.`compensatory_amount_detail` trx id 8751986770 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 74 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 8; hex 80000000028b1317; asc ;;
1: len 18; hex 636f6d70656e7361746f7279416d6f756e74; asc compensatoryAmount;;
2: len 6; hex 434f4d503034; asc COMP04;;
3: len 8; hex 000000000041c4d4; asc A ;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
分析日志
事务1 持有GAP间隙锁，所以事务1处于阻塞状态，等待事务2释放插入意向锁。
事务2 持有GAP间隙锁，等待事务1释放插入意向锁，所以事务2也处与等待状态。
事务1代偿事务2 ，事务2等待事务1，所以产生死锁。
record lock（RK）锁直接加在索引记录上面，锁住的是key
gap lock（GK）间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
next key lock（NK）RK+GK
insert intention lock（IK）如果插入前，该间隙已经有gap锁，那么Insert会申请插入意向锁。因为了避免幻读，当其他事务持有该间隙的间隔锁，插入意向锁就会被阻塞。
网友评论