Mysql非主键索引加锁_一个奇怪的 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：为什么死锁报告中的锁是加在主键上的，不应该优先加在二级索引上吗

执行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 个事务以不同顺序加锁，最后的死锁报告中，锁是加在二级索引上的