访问不同行时出现InnoDB死锁问题咨询

从给出的InnoDB死锁日志来看，两个事务的查询语句分别为：

事务1执行的SQL：

select  r.*
    from  reservations r
    where  r.workspaceSid = 'WS0c9faa70166e42f4cf3a8df4b11b4d73'
      and  r.workerSid = 'WK89d7521c91805d7f0a8a2d38a6ec7ab4'
      and  r.status IN (0, 6) for update

事务2执行的SQL：

select  r.*
    from  reservations r
    where  r.workspaceSid = 'WS0c9faa70166e42f4cf3a8df4b11b4d73'
      and  r.workerSid = 'WK53a59d20955be23871feb083639ecd30'
      and  r.status IN (0, 6) for update

核心冲突原因 #

两个事务都依赖fk_workspace_sid这个**单列索引（仅包含workspaceSid字段）**来定位数据，引发了锁范围过大的问题：

1. 执行for update查询时，InnoDB会先通过该索引扫描所有符合workspaceSid = 'WS0c9faa70166e42f4cf3a8df4b11b4d73'的索引记录，并对这些记录加X锁，之后才回表校验workerSid和status条件，过滤出真正需要的数据行。
2. 事务2先获取了该索引页上的一批X锁，其中包含事务1目标行对应的索引记录；同时事务1在等待获取该索引页的锁，而事务2也在等待事务1持有的某条锁（日志中事务2的等待信息未完整显示，但结合死锁场景可推断循环等待关系）。
3. 虽然最终两个事务访问的实际数据行不同，但因为索引无法精准定位到目标行，导致它们在索引层面锁定了同一批记录，形成循环等待，触发死锁。

优化方向 #

• 检查reservations表的索引配置，当前的单列索引无法缩小锁范围。
• 建议创建复合索引(workspaceSid, workerSid, status)，让查询可以直接通过索引定位到目标行，仅对匹配的行加锁，避免批量锁冲突。

内容的提问来源于stack exchange，提问作者ash_4u