2020-05-19-数据库死锁问题2

很烦，又一次遇到数据库死锁，不过有了前车之鉴，这次很容易就发现了死锁的原因。

业务场景：批量并发操作（约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会申请插入意向锁。因为了避免幻读，当其他事务持有该间隙的间隔锁，插入意向锁就会被阻塞。