mysql SQL执行流程中的锁定与并发控制

mysql锁在存储引擎层访问数据时按需加，非解析或计划阶段统一加；innodb行锁在遍历到满足条件的记录时才申请，受隔离级别和语句类型直接影响。

MySQL 执行 SQL 时，锁到底在哪个阶段加？

锁不是在“解析完 SQL”或“生成执行计划后”统一加的，而是在存储引擎层访问具体数据行/页/表时按需加。InnoDB 的行级锁（如 RECORD LOCK、GAP LOCK）只在真正遍历到满足条件的记录时才申请，且受事务隔离级别和语句类型（select ... for UPDATE vs UPDATE）直接影响。

常见误解是“UPDATE t SET x=1 WHERE id=5 一执行就锁住整张表”，实际中：若 id 是主键且有索引，InnoDB 只加一条 RECORD LOCK；若 WHERE 条件无索引，可能触发全表扫描 + 每行都加锁，甚至升级为表级锁（尤其在 READ COMMITTED 以下级别）。

• SELECT ... LOCK IN SHARE MODE 和 FOR UPDATE 显式加锁，锁范围由执行计划中的访问路径决定
• INSERT 在唯一键冲突检测时会加 INSERT INTENTION LOCK，用于协调 GAP 锁间隙
• delete 和 UPDATE 先定位再加锁，若用到覆盖索引且不回表，可能只锁索引记录而非聚簇索引

RR 隔离级别下为什么还会出现幻读？

可重复读（REPEATABLE READ）通过 GAP LOCK + NEXT-KEY LOCK 阻止其他事务在查询范围插入新行，但仅对“当前已执行的查询条件”生效。它不锁定未来可能出现的新值，也不阻止其他事务修改未被当前查询覆盖的索引区间。

典型漏锁场景：

• 查询条件是 WHERE status = 'pending'，但没索引 —— InnoDB 无法确定哪些间隙该锁，可能只锁扫描过的行，不锁 gap
• 使用 SELECT ... FOR UPDATE 查 WHERE id > 100，但 id=105 被另一个事务刚插入并提交，当前事务下次查仍看不到它（符合 RR），但如果该事务自己执行 INSERT INTO t VALUES (105, ...)，就会触发唯一冲突或死锁
• UPDATE 语句若走索引但条件匹配多行，InnoDB 加的是 NEXT-KEY LOCK（record + gap），但 gap 锁不会阻塞不同索引路径的插入（比如另一个事务用 name 字段插入，而你锁的是 id 索引）

如何快速判断一条 SQL 会持有哪些锁？

靠猜不行，得看 INFORMATION_SCHEMA.INNODB_TRX、INNODB_LOCKS（MySQL 5.7）或 performance_schema.data_locks（8.0+）。更实用的是结合 SHOW ENGINE INNODB STATUSG 中的 TRANSACTIONS 和 LATEST DETECTED DEADLOCK 区块，观察 lock_mode、lock_type、lock_trx_id、lock_rec

实操建议：

• 执行可疑 SQL 后立刻查：

  SELECT * FROM performance_schema.data_locks WHERE THREAD_ID = (SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = CONNECTION_ID());

• 注意 LOCK_DATA 字段：显示被锁的具体值（如 105）或间隙（如 100, 200 表示 (100,200) 区间）
• LOCK_MODE 为 X,GAP 表示间隙锁，X,REC_NOT_GAP 是纯行锁，X,NEXT_KEY 是前后都锁
• 若看到 lock_mode X locks rec but not gap 却又发生死锁，大概率是另一方持有 GAP 锁，而你正在插入该 gap 内的值

并发更新同一行时，锁等待超时还是死锁？怎么区分？

两者现象相似（SQL 卡住然后报错），但根源不同：Lock wait timeout exceeded 是单向等待超时（默认 50 秒，由 innodb_lock_wait_timeout 控制）；Deadlock found when trying to get lock 是 InnoDB 主动检测到循环等待并回滚了其中一方。

关键区别点：

• 死锁日志里一定包含至少两个事务的完整 lock info，以及哪条语句被选为 victim（ROLLING BACK）
• 锁等待超时不会写入 Error log，但死锁一定会（SHOW ENGINE INNODB STATUS 里最新一次死锁详情保留约 1 分钟）
• 如果两个事务都执行 UPDATE t SET a=1 WHERE id=1，先拿到锁的事务 commit 前，后一个会等；若后一个等的过程中，又去锁另一行（比如 id=2），而第一个事务也反过来锁 id=2，就构成死锁
• 避免误判：不要只看错误码，要查 SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 是否非空

最易被忽略的是：即使语句相同、顺序相同，只要事务内操作的索引顺序不同（比如一个先锁 PRIMARY 再锁 idx_name，另一个反过来），就可能在高并发下随机触发死锁——这和应用层逻辑强相关，不能只靠数据库配置解决。