如何排查MySQL中由于内联子查询导致的非预期事务锁定？-mysql教程-PHP中文网

内联子查询会意外加锁，因其在innodb中被视为dml上下文，对扫描到的所有候选行加锁（如x锁或间隙锁），而非仅最终匹配行；常见诱因包括非唯一索引、范围扫描、未走索引、order by + limit及相关子查询等。

如何排查mysql中由于内联子查询导致的非预期事务锁定？

内联子查询为什么会意外加锁？

内联子查询（比如 UPDATE ... WHERE id IN (SELECT ...) 或 DELETE ... WHERE x = (SELECT ...)）在 MySQL 中不是“只读取”，它可能被 InnoDB 视为需要加锁的 DML 上下文。尤其当子查询走的是非唯一索引、范围扫描，或未命中索引时，InnoDB 会为扫描到的**所有候选行**加锁（lock_mode X locks rec but not gap 或更糟的 gap before rec），而不仅限于最终匹配的那几行。

常见诱因包括：子查询里用了 ORDER BY + LIMIT（但外层没加 FOR UPDATE）、子查询条件未覆盖索引最左前缀、子查询引用了被更新表本身（即相关子查询），这些都会触发更宽泛的锁范围。

怎么确认是内联子查询惹的祸？

别急着改 SQL —— 先定位。核心动作是抓取死锁或锁等待发生时的真实执行路径：

运行 SHOW ENGINE INNODB STATUS\G，重点看 LATEST DETECTED DEADLOCK 里两个事务的 SQL，检查是否一方含 IN (SELECT ...) 或 = (SELECT ...) 结构
查 information_schema.INNODB_TRX，过滤 trx_state = 'LOCK WAIT' 的事务，用 trx_query 字段确认是否正在执行带子查询的 UPDATE/DELETE
对可疑 SQL 手动执行 EXPLAIN FORMAT=TRADITIONAL，注意 type 是否为 ALL 或 index，key 是否为 NULL，Extra 是否含 Using temporary; Using filesort —— 这些都暗示锁范围失控

MySQL 8.0+ 怎么查子查询实际锁了哪些行？

MySQL 5.7 的 INNODB_LOCKS 表在 8.0+ 已废弃，必须转向 performance_schema.data_locks。关键点在于：子查询产生的锁不会单独标记“这是子查询的锁”，而是和主语句一起出现在同一事务的锁记录中。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

执行以下查询，能直观看到当前事务持有的所有锁（含子查询引入的）：

SELECT
  ENGINE_TRANSACTION_ID,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_DATA
FROM performance_schema.data_locks
WHERE ENGINE_TRANSACTION_ID IN (
  SELECT trx_id FROM information_schema.INNODB_TRX
  WHERE trx_query LIKE '%IN (SELECT%' OR trx_query LIKE '%= (SELECT%'
);

如果 LOCK_DATA 返回大量非预期主键值（比如你要删 id=100，结果锁了 id 从 50 到 150 的整段），基本可断定子查询扫描范围过大。

如何避免内联子查询引发的锁扩散？

根本解法不是禁用子查询，而是控制其执行计划和生命周期：

把内联子查询拆成两步：先 SELECT ... INTO @var 获取 ID 列表，再用 WHERE id IN (@var1, @var2, ...) —— 注意 @var 不能存多值，需改用临时表或应用层拼接
确保子查询 **强制走索引**：给子查询 WHERE 条件字段建联合索引，且满足最左前缀；避免在子查询里用函数（如 DATE(created_at)）或隐式类型转换
若子查询结果集小（JOIN 替代 IN，例如 UPDATE t1 JOIN t2 ON t1.id = t2.ref_id SET ... —— JOIN 在大多数情况下锁范围更精准
在事务里执行带子查询的 DML 前，显式加 SELECT ... FOR UPDATE 锁住子查询结果，避免后续 UPDATE 重复扫描加锁