A puzzled deadlock: it seems that the lock is hold on primary index first

We got a puzzled deadlock on MySQL 5.7 (Engine: InnoDB, Isolation Level: RR). The report result of show engine innodb status as below shown

*** (1) TRANSACTION:  TRANSACTION 1739954050, ACTIVE 0 sec starting index read  mysql tables in use 1, locked 1  LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s)  MySQL thread id 4253877, OS thread handle 47904135608064, query id 4259685238 jacky Searching rows for update            UPDATE fruit_setting set          value = CASE              WHEN eid = '?' and key = '?' THEN '?'              WHEN eid = '?' and key = '?' THEN '?'              WHEN eid = '?' and key = '?' THEN '?'              END              WHERE aid = '?' and eid in ('?', '?', '?', '?', '?', '?') and key = '?'                *** (1) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 533 page no 65378 n bits 0 index PRIMARY of table `jacky`.`fruit_setting` trx id 1739954050 lock_mode X locks rec but not gap waiting  ...      *** (2) TRANSACTION:  TRANSACTION 1739954049, ACTIVE 0 sec fetching rows  mysql tables in use 1, locked 1  LOCK WAIT 94 lock struct(s), heap size 1136, 184 row lock(s)  MySQL thread id 4257460, OS thread handle 47904340621056, query id 4259685231  jacky Searching rows for update            UPDATE fruit_setting set value = CASE              WHEN eid = '?' and key = '?' THEN '?'              WHEN eid = '?' and key = '?' THEN '?'              WHEN eid = '?' and key = '?' THEN '?'              END              WHERE aid = '?' and eid in ('?', '?', '?', '?', '?', '?') and key = '?'  *** (2) HOLDS THE LOCK(S):  RECORD LOCKS space id 533 page no 65378 n bits 0 index PRIMARY of table `jacky`.`fruit_setting` trx id 1739954049 lock_mode X locks rec but not gap  Record lock, heap no 105 PHYSICAL RECORD: n_fields 10; compact format; info bits 0   ...    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:  RECORD LOCKS space id 533 page no 46944 n bits 0 index PRIMARY of table `jacky`.`fruit_setting` trx id 1739954049 lock_mode X locks rec but not gap waiting  Record lock, heap no 58 PHYSICAL RECORD: n_fields 10; compact format; info bits 0  ...  

The table looks like this

CREATE TABLE `fruit_setting` (    `id` bigint(20) NOT NULL AUTO_INCREMENT,    `aid` varchar(32) NOT NULL,    `eid` varchar(32) NOT NULL,    `key` varchar(32) NOT NULL,    `value` varchar(32) DEFAULT NULL,    PRIMARY KEY (`id`),    UNIQUE KEY `i_e_key` (`eid`, `key`),    KEY `i_a_eid` (`aid`, `eid`)  );  

Note that the aid in two sql are the same and the eid in the two in clause could be overlap; And thus we guess that the deadlock occurred since the locks are acquired in reverse order on index i_e_key or i_a_eid.

Question 1: why the lock in report is not on index i_e_key or i_a_eid, but in primary index. As I know, the secondary index would be locked before primary index if the searching for update used the secondary index.

Question 2: Does MySQL acquire lock on index record one by one based on the order of eid appeared in in clause during execute update ... where eid in (...)?

By the way, it's hard to reproduce, I tried to lock on eid in different order, however, the new deadlock report showed that the lock is on secondary index.

https://stackoverflow.com/questions/65519414/a-puzzled-deadlock-it-seems-that-the-lock-is-hold-on-primary-index-first December 31, 2020 at 06:08PM