开发者社区 问答 正文

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

展开
收起
1531513304772897 2021-01-05 14:18:33 827 分享 版权
阿里云 AI 助理回答

问题 1:InnoDB 引擎在执行更新操作时,确实会先锁定二级索引记录(如果查询基于二级索引),然后锁定对应的聚集索引记录。这是因为 InnoDB 使用的是簇索引(即主键索引)来存储实际的数据行,而二级索引则包含指向主键的指针。因此,任何对数据行的修改都必须确保对主键索引的记录也进行了锁定,以维护数据的一致性。

死锁报告中显示锁加在主键上,这并不意味着没有先加二级索引上的锁,而是因为在报告中通常只展示最终等待的锁状态,即事务在尝试获取下一个必要的锁时发生了阻塞。在这个过程中,之前的锁(包括可能在二级索引上的锁)已经被持有,但因为无法获得后续所需的锁(这里是主键上的锁),导致了死锁的发生。所以,尽管报告直接展示了主键上的锁等待情况,实际上之前的操作已经涉及到了二级索引的锁定。

问题 2:关于 IN 子句的处理,MySQL/InnoDB 的确可以利用索引来加速查询,但是它不会严格地按照 IN 列表中的顺序逐个加锁。加锁行为依赖于优化器选择的执行计划和索引访问路径。如果使用了索引扫描(如通过 i_e_keyi_a_eid),InnoDB 会尽可能高效地锁定满足条件的记录,但具体的加锁顺序和策略是内部机制决定的,并不公开承诺按特定顺序进行。

对于 IN 条件下的死锁复现难度较高,这可能是因为死锁的产生涉及到多个并发事务的执行时机、资源争用以及锁的获取顺序等复杂因素。即使两个事务看似以可预测的方式请求锁,数据库引擎内部的并发控制机制、锁的粒度管理以及查询优化策略等因素也可能导致预期之外的死锁场景。

解决这类问题的方法可能包括但不限于: - 优化事务内的SQL语句,尽量减少锁的范围和时间。 - 使用更严格的事务隔离级别可能会避免某些类型的死锁,但也会增加锁的竞争和降低并发性能。 - 在代码层面实现重试逻辑,当遇到死锁时重新执行事务。 - 调整查询或索引设计,使得锁的获取顺序更加一致和可预测,从而减少死锁发生的概率。

有帮助
无帮助
AI 助理回答生成答案可能存在不准确,仅供参考
0 条回答
写回答
取消 提交回答