一次MySql数据死锁的经历

死锁日志查看命令：

查询dba死锁日志命令：show engine innodb status;

死锁日志：

| InnoDB |      |
=====================================
2020-12-25 16:45:17 7f31e04d6700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 19235374 srv_active, 0 srv_shutdown, 10711359 srv_idle
srv_master_thread log flush and writes: 29946733
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 6605989
OS WAIT ARRAY INFO: signal count 5891458
Mutex spin waits 15317552, rounds 119388000, OS waits 2220310
RW-shared spins 5308462, rounds 133755985, OS waits 3835356
RW-excl spins 1381545, rounds 25755162, OS waits 409887
Spin rounds per wait: 7.79 mutex, 25.20 RW-shared, 18.64 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-12-25 14:35:01 7f2edd290700
*** (1) TRANSACTION:
TRANSACTION 289378442, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1
MySQL thread id 296455, OS thread handle 0x7f2ee3104700, query id 842142945 10.65.215.34 pvmuser statistics
select

    ID, PARTNER_ID, BUSIN_NO, MEMBER_ID, ACCOUNT_NO, ACCOUNT_TYPE, ACCOUNT_TOTAL_AMT,
    AVABLE_BALANCE, UNAVABLE_BALANCE, IS_SHARE, ACCOUNT_STATUS, RELATION_MEMBERID, GMT_CREATE,
    GMT_MODIFIED, OPERATOR_CREATE, OPERATOR_MODIFIED, REQ_NOTE, PURPOSE, MEMO, ACCT_ROLE, QJS_ACCT_NO, QJS_ACCT_TYPE, SUB_ACCOUNT_TYPE

        from t_pvm_acct
        where ID = '1496'
        for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 822 page no 32 n bits 144 index "PRIMARY" of table "pvm"."t_pvm_acct" trx id 289378442 lock_mode X locks rec but not gap waiting
Record lock, heap no 66 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
 ………………………………

*** (2) TRANSACTION:
TRANSACTION 289378441, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
76 lock struct(s), heap size 13864, 5 row lock(s), undo log entries 3
MySQL thread id 296448, OS thread handle 0x7f2edd290700, query id 842142961 10.65.215.34 pvmuser Searching rows for update
update t_pvm_trade_order
         SET TRADE_STATUS = 'TRADE_FINISHED',


                GMT_MODIFY = '2020-12-25 14:35:02.055',


                CLEARING_STATUS = 'Y',


                TRADE_FINISH_TIME = '2020-12-25 14:35:02.055',


                TRADE_PAY_TIME = '2020-12-25 14:35:02.055'
        where PARTNER_ID = '188888888888' and INNER_TRADE_NO = 'FT1225142504327218041'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 822 page no 32 n bits 144 index "PRIMARY" of table "pvm"."t_pvm_acct" trx id 289378441 lock_mode X locks rec but not gap
Record lock, heap no 66 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
 0: len 8; hex 80000000000005d8; asc         ;;
 ……………………

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 821 page no 99 n bits 112 index "PRIMARY" of table "pvm"."t_pvm_trade_order" trx id 289378441 lock_mode X locks rec but not gap waiting
Record lock, heap no 40 PHYSICAL RECORD: n_fields 47; compact format; info bits 0
 
 46: len 30; hex 687474703a2f2f66756e633135362e7666696e616e63652e636e2f737461; asc http://func156.vfinance.cn/sta; (total 59 bytes);

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------

表结构

• Table1 :
  • column1 唯一索引
  • column2 普通索引a
  • column3 普通索引b
• t_pvm_acct：
  • ID 主键primary

时间线分析

整理出的事务执行顺序如下：

序号	事务1	事务2
1	select * from Table1 where column1 = ‘c1’ for update
2		select * from Table1 where column1 = ‘c2’ for update
1	select * from t_pvm_acct where ID = ‘1496’ for update； udpate t_pvm_acct ……
2		select * from t_pvm_acct where ID = ‘1496’ for update； udpate t_pvm_acct ……
1	update Table1 set modify = sysdate() where column2=‘c1’ and column3 = ‘c2’

分析执行计划

分析死锁日志

主要是事务1做第三步update的时候，根据where条件中需要获取一个gap锁，会锁住所有符合条件的行，这些符合条件的行中包含了被事务2锁定的一条记录，事务2一直在等待事务1t_pvm_acct 锁定的提交，事务1在等待被事务2锁定的记录。
这样彼此等待，造成死锁。