MySQL INSERT执行时，为何会阻塞其他事务的SELECT？

Question

在MySQL中，INSERT操作本身通常不会阻塞普通SELECT（尤其是READ COMMITTED或READ UNCOMMITTED隔离级别下的快照读），但**在特定条件下确实会阻塞其他事务的SELECT**：当SELECT使用的是**当前读（current read）**——例如显式加锁（SELECT ... FOR UPDATE / LOCK IN SHARE MODE）、或执行在REPEATABLE READ隔离级别下且需进行间隙锁/临键锁（next-key lock）判断时，InnoDB可能因INSERT触发的**二级索引唯一约束检查、插入意向锁（Insert Intention Lock）与已有间隙锁冲突**，导致锁等待。更典型的是：若INSERT引发唯一键冲突回滚，或在无主键/无索引表上执行，InnoDB被迫升级为表级锁（尤其MyISAM引擎，但即使InnoDB在极端DDL或隐式锁升级场景也可能出现）。此外，长事务未提交导致undo日志保留，亦可能拖慢MVCC快照构建，间接“感知”为SELECT变慢。本质并非INSERT直接锁SELECT，而是**锁机制、隔离级别与索引设计共同作用下的并发控制表现**。

Answer 1

```html

一、现象层：INSERT“看似”阻塞SELECT的典型场景

在生产监控中，DBA常观察到：一条慢INSERT执行期间，另一事务的SELECT ... FOR UPDATE突然卡住数秒甚至超时。此时SHOW ENGINE INNODB STATUS显示WAITING FOR THIS LOCK TO BE GRANTED，锁等待链清晰可见——但普通SELECT（无LOCK IN SHARE MODE）仍可瞬时返回。这印证了核心前提：INSERT不直接阻塞快照读，但会干扰当前读与锁资源分配。

二、机制层：InnoDB锁体系的三级联动

• 插入意向锁（Insert Intention Lock）：INSERT前在目标间隙申请的轻量级间隙锁，兼容其他插入意向锁，但不兼容已有间隙锁或临键锁；
• 间隙锁（Gap Lock）与临键锁（Next-Key Lock）：REPEATABLE READ下，SELECT ... WHERE id > 100 AND id < 200会锁定(100,200)间隙；新INSERT若落在该区间，则插入意向锁被阻塞；
• 唯一约束检查锁（Unique Check Lock）：二级索引插入前需对唯一键值加S锁校验，若另一事务正持有该值的X锁（如UPDATE未提交），则INSERT等待——此锁亦会阻塞同值的SELECT ... FOR UPDATE。

三、隔离级别层：READ COMMITTED vs REPEATABLE READ的分水岭

行为	READ COMMITTED	REPEATABLE READ
间隙锁	仅在FK检查、唯一约束冲突时使用	所有范围查询默认启用
插入意向锁冲突概率	低（间隙锁范围窄）	高（间隙锁覆盖广）
SELECT ... FOR UPDATE阻塞风险	仅当精确匹配已锁定行	可能因间隙锁扩展至相邻空位而阻塞

四、索引设计层：无主键/无索引表的“隐式表锁”陷阱

当表无主键且无任何索引时，InnoDB无法使用行锁，INSERT必须获取TABLE LOCK以保证一致性（通过聚簇索引模拟，但实际退化为表级协调）。此时：
- SELECT ... FOR UPDATE需等待INSERT释放表锁；
- 即使是SELECT * FROM t WHERE id=1（id为普通列），因无索引，全表扫描+当前读将触发隐式锁升级；
- 验证命令：SELECT * FROM information_schema.INNODB_TRX WHERE TRX_MYSQL_THREAD_ID IN (SELECT ID FROM information_schema.PROCESSLIST WHERE COMMAND='Sleep');

五、长事务层：undo日志膨胀引发的MVCC性能衰减

graph LR
A[长事务未提交] --> B[InnoDB保留旧版本undo日志]
B --> C[新事务构建Read View时遍历更长undo链]
C --> D[快照读SELECT延迟上升]
D --> E[运维误判为“INSERT导致SELECT变慢”]

六、冲突诊断层：5步精准定位锁瓶颈

1. 执行SELECT * FROM performance_schema.data_locks;捕获实时锁信息；
2. 关联performance_schema.data_lock_waits查看等待关系；
3. 用SELECT trx_id, trx_state, trx_started, trx_query FROM information_schema.INNODB_TRX ORDER BY trx_started;定位最早未提交事务；
4. 检查information_schema.INNODB_METRICS中innodb_row_lock_time_avg是否突增；
5. 分析慢日志中的Lock_wait_time字段确认锁等待耗时。

七、解决方案层：从架构到SQL的七维优化

• 索引治理：为WHERE/ORDER BY/FOR UPDATE条件列强制添加复合索引，消除间隙锁滥用；
• 事务瘦身：将大事务拆分为多段，避免长时间持有锁与undo；
• 隔离降级：非强一致性场景改用READ COMMITTED，关闭间隙锁；
• 应用重试：对INSERT唯一冲突、锁等待异常增加指数退避重试；
• DDL规避：禁止在业务高峰期对大表添加唯一索引（会触发全表S锁）；
• 监控告警：设置INNODB_TRX.trx_state = 'LOCK WAIT'持续超3s告警；
• 引擎选型：高并发点查场景评估TokuDB或RocksDB替代方案（非标准MySQL但解决根本锁争用）。

```