一个奇怪的 MySQL 死锁：锁似乎优先加在主键上，而不是二级索引上

我们遇到在 MySQL 5.7 上遇到一个奇怪的死锁问题 (使用 InnoDB 引擎, 隔离级别 RR). show engine innodb status 结果如下

*** (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
...

表定义

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`)
);

二个 sql 中 where 条件中的 aid 是相同的，并且 in 子句中 eid 有重复. 因此推测可能是因为 2 个 sql 以不同顺序锁了相同一组 eid，此外加锁先加在二级索引 i_e_key 或 i_a_eid 上，之后是主键

问题 1：为什么死锁报告中的锁是加在主键上的，不应该优先加在二级索引上吗

以下节选自 淘宝 数据库内核月报 － 2016 / 01

执行SQL（通过二级索引查询）：update t1 set c = c +1 where b = 3; RC隔离级别：1. 锁住二级索引记录，为NOT GAP X锁；2.锁住对应的聚集索引记录，也是NOT GAP X锁。 RR隔离级别下：1.锁住二级索引记录，为LOCK_ORDINARY|LOCK_X锁；2.锁住聚集索引记录，为NOT GAP X锁

问题 2：in clause 是会用到索引的，explain 执行计划中显示用的是索引 i_e_key (当然可能因为数据或者其他因素，选择 i_a_eid) ; 这里问题是 MySQL/InnoDB 是按 eid 在 in 里的位置来一个一个加锁的吗?

顺便一提：锁在主键上的死锁并不好复现，在本地尝试用 2 个事务以不同顺序加锁，最后的死锁报告中，锁是加在二级索引上的