InnoDB引擎下MySQL多值INSERT的内部原理及锁机制相关问题

锁机制结论 #

你给出的普通多值INSERT语句默认不会加表级锁，InnoDB会使用细粒度的行级锁保证并发性能，只有极少数特殊场景才会触发表锁：

• 显式执行LOCK TABLES MyTable WRITE手动加表级写锁
• 触发元数据锁（MDL）：所有DML操作都会持有极短时间的MDL写锁，仅用来阻塞DDL操作，不会影响其他正常DML的并发执行
• 插入操作涉及全表扫描的特殊场景（比如INSERT...SELECT不带WHERE条件的情况，你给出的静态多值INSERT不会触发）

如果插入过程中触发唯一键冲突，InnoDB只会对冲突的已有行加排他行锁，锁粒度依旧是行级，不会升级为表锁。

原子性保证的底层逻辑 #

多值INSERT的全成功/全失败原子性，是InnoDB通过Undo日志+Redo日志的事务机制实现的：

• 执行插入前，InnoDB会先写Undo日志，记录本次插入操作对应的反向操作（删除对应插入行）。如果执行中途报错、或者事务主动回滚，会直接执行Undo日志中的反向操作，清理已经写入的行，保证没有残留的部分插入数据
• 所有插入的行数据会统一在事务提交时刷盘持久化，要么全部提交成功，要么全部回滚，不会出现部分生效的情况

普通多值INSERT的完整执行流程 #

你给出的示例SQL：

INSERT INTO MyTable ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )

属于最基础的静态多值插入，底层执行逻辑如下：

1. 先申请表的MDL写锁，校验表结构是否存在、字段是否匹配，校验完成后MDL写锁降级为MDL读锁（整个过程耗时极短）
2. 逐行校验待插入数据是否符合表约束（非空、字段类型、外键、唯一键规则等），任意一行校验不通过直接终止操作，回滚所有已执行的步骤
3. 如果表包含自增主键，会一次性申请连续的自增ID（批量插入的优化特性，性能远高于单条插入逐次申请ID），这个过程持有的自增锁是轻量级锁，申请完成后立即释放，不是表级锁
4. 逐行生成待插入的行数据，同时对每个插入位置对应的索引Gap加插入意向锁（属于Gap锁的一种，不会阻塞其他事务在同一个Gap区间插入其他位置的数据，仅用来避免幻读、保证插入顺序）
5. 所有行数据写入Buffer Pool的脏页，同时写入Redo日志和Undo日志
6. 如果开启了自动提交，直接提交事务，释放所有持有锁，返回执行成功；如果是显式事务，会等待用户执行COMMIT后再释放锁、提交数据到磁盘。

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