mysql update操作时where条件中包含多个普通索引，多事务操作为什么会产生死锁

Question

1、建表语句：

• CREATE TABLE worknote_account (
  id int(11) NOT NULL AUTO_INCREMENT,
  worknote varchar(32) NOT NULL,
  accountID int(11) DEFAULT NULL,
  operSN varchar(32) NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY account_worknote (worknote),
  KEY worknote_account_operSN (operSN),
  KEY FK_WA_ACCOUNT (accountID)
  ) ENGINE=InnoDB CHARSET=utf8；

2、产生死锁的操作

• 多事务并发执行sql语句update worknote_account set errno=10,detailinfo='Failed:1, tcp connect failed',operatetime=1632907956 where accountid='10' and worknote='DMP202109295A503E' and opersn='80763634cb02'
  每个事务执行update操作时where条件worknote 和 opersn相同，accountid不同

3、sql语法分析

explain update worknote_account set errno=10,detailinfo='Failed:1, tcp connect failed',operatetime=1632907956 where accountid='10' and worknote='DMP202109295A503E' and opersn='80763634cb02'
+----+-------------+------------------+------------+-------------+--------------------------------------------------------+---------------------------------------+---------+------+------+----------+---------------------------------------------------------------------+
| id | select_type | table            | partitions | type        | possible_keys                                          | key                                   | key_len | ref  | rows | filtered | Extra                                                               |
+----+-------------+------------------+------------+-------------+--------------------------------------------------------+---------------------------------------+---------+------+------+----------+---------------------------------------------------------------------+
|  1 | UPDATE      | worknote_account | NULL       | index_merge | account_worknote,worknote_account_operSN,FK_WA_ACCOUNT | account_worknote,worknote_account_operSN | 5,98    | NULL |    1 |   100.00 | Using intersect(account_worknote,worknote_account_operSN); Using where |
+----+-------------+------------------+------------+-------------+--------------------------------------------------------+---------------------------------------+---------+------+------+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

4、死锁日志：

2021-09-29T17:32:39.345462+08:00 2029 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 58851313, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4994
mysql tables in use 3, locked 3
28 lock struct(s), heap size 3520, 6 row lock(s)
MySQL thread id 2029, OS thread handle 140194483357440, query id 302260 10.10.3.150 keyou updating
update worknote_account set errno=10,detailinfo='Failed:1, tcp connect to 70.10.71.117:2168 failed
',operatetime=1632907956 where accountid='42949' and worknote='DMP202109295A503E' and opersn='80763634cb02'
2021-09-29T17:32:39.345491+08:00 2029 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1062 page no 4732 n bits 664 index account_worknote of table `pvault`.`worknote_account` trx id 58851313 lock_mode X locks rec but not gap
Record lock, heap no 379 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 17; hex 444d503230323130393239354135303345; asc DMP202109295A503E;;
 1: len 4; hex 800e3852; asc   8R;;

Record lock, heap no 387 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 17; hex 444d503230323130393239354135303345; asc DMP202109295A503E;;
 1: len 4; hex 800e385a; asc   8Z;;

2021-09-29T17:32:39.345709+08:00 2029 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1062 page no 7552 n bits 160 index PRIMARY of table `pvault`.`worknote_account` trx id 58851313 lock_mode X locks rec but not gap waiting
Record lock, heap no 76 PHYSICAL RECORD: n_fields 33; compact format; info bits 0
 0: len 4; hex 800e385a; asc   8Z;;
 1: len 6; hex 00000381f284; asc       ;;
 2: len 7; hex 35000004552a24; asc 5   U*$;;
 3: len 17; hex 444d503230323130393239354135303345; asc DMP202109295A503E;;
 4: len 4; hex 8000a7be; asc     ;;
 5: len 12; hex 383037363336333463623032; asc 80763634cb02;;
 6: len 30; hex 314337454633374444383133443836353334363533463038374145423137; asc 1C7EF37DD813D86534653F087AEB17; (total 32 bytes);
 7: len 0; hex ; asc ;;
 8: SQL NULL;
 9: len 4; hex 80000000; asc     ;;
 10: len 4; hex 80000000; asc     ;;
 11: len 4; hex 80000002; asc     ;;
 12: len 4; hex 7fffffff; asc     ;;
 13: len 0; hex ; asc ;;
 14: len 4; hex e1542a5e; asc  T*^;;
 15: SQL NULL;
 16: len 0; hex ; asc ;;
 17: len 0; hex ; asc ;;
 18: SQL NULL;
 19: len 0; hex ; asc ;;
 20: SQL NULL;
 21: len 4; hex 80000000; asc     ;;
 22: len 12; hex 3137322e31362e312e313336; asc 172.16.1.136;;
 23: len 4; hex 80000000; asc     ;;
 24: SQL NULL;
 25: SQL NULL;
 26: SQL NULL;
 27: SQL NULL;
 28: SQL NULL;
 29: SQL NULL;
 30: SQL NULL;
 31: SQL NULL;
 32: len 4; hex 80000002; asc     ;;

2021-09-29T17:32:39.346434+08:00 2029 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)


2021-09-29T17:32:39.706642+08:00 2100 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 58852202, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 4981
mysql tables in use 3, locked 3
28 lock struct(s), heap size 3520, 3 row lock(s)
MySQL thread id 2100, OS thread handle 140194474039040, query id 304386 10.10.3.150 keyou updating
update worknote_account set errno=10,detailinfo='Failed:1, tcp connect to 70.10.71.127:2168 failed
',operatetime=1632907957 where accountid='43058' and worknote='DMP202109295A503E' and opersn='80763634cb02'
2021-09-29T17:32:39.706699+08:00 2100 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1062 page no 7554 n bits 160 index PRIMARY of table `pvault`.`worknote_account` trx id 58852202 lock_mode X locks rec but not gap
Record lock, heap no 20 PHYSICAL RECORD: n_fields 33; compact format; info bits 0
 0: len 4; hex 800e3897; asc   8 ;;
 1: len 6; hex 00000381f30c; asc       ;;
 2: len 7; hex 00000004462bc7; asc     F+ ;;
 3: len 17; hex 444d503230323130393239354135303345; asc DMP202109295A503E;;
 4: len 4; hex 8000a832; asc    2;;
 5: len 12; hex 383037363336333463623032; asc 80763634cb02;;
 6: len 30; hex 304631303838443739394145394544304241333231464135433139394137; asc 0F1088D799AE9ED0BA321FA5C199A7; (total 32 bytes);
 7: len 0; hex ; asc ;;
 8: SQL NULL;
 9: len 4; hex 80000000; asc     ;;
 10: len 4; hex 80000000; asc     ;;
 11: len 4; hex 80000002; asc     ;;
 12: len 4; hex 7fffffff; asc     ;;
 13: len 0; hex ; asc ;;
 14: len 4; hex e1541e95; asc  T  ;;
 15: SQL NULL;
 16: len 0; hex ; asc ;;
 17: len 0; hex ; asc ;;
 18: SQL NULL;
 19: len 0; hex ; asc ;;
 20: SQL NULL;
 21: len 4; hex 80000000; asc     ;;
 22: len 12; hex 3137322e31362e312e313336; asc 172.16.1.136;;
 23: len 4; hex 80000000; asc     ;;
 24: SQL NULL;
 25: SQL NULL;
 26: SQL NULL;
 27: SQL NULL;
 28: SQL NULL;
 29: SQL NULL;
 30: SQL NULL;
 31: SQL NULL;
 32: len 4; hex 80000002; asc     ;;

2021-09-29T17:32:39.707462+08:00 2100 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1062 page no 4732 n bits 664 index account_worknote of table `pvault`.`worknote_account` trx id 58852202 lock_mode X locks rec but not gap waiting
Record lock, heap no 445 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 17; hex 444d503230323130393239354135303345; asc DMP202109295A503E;;
 1: len 4; hex 800e3894; asc   8 ;;

2021-09-29T17:32:39.707641+08:00 2100 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

5、疑问
死锁产生的条件之一就是加锁顺序不一致，且从日志确实有加锁顺序不一致的情况。执行相同的sql，请问这个加锁顺序的差异是怎么产生的？

Answer 1

create index xx on worknote_account (accountid,opersn,worknote);