MySQL InnoDB 隔离级别并发更新为什么出现间隙锁等待怎样在批处理代码中减少额外分配

MySQL InnoDB 并发更新出现“间隙锁等待”，通常不是某一行被简单锁住，而是事务在默认 REPEATABLE READ 下扫描了索引范围，拿到了 next-key lock：它同时覆盖索引记录和记录前面的间隙。另一个事务即使只想插入一个当前不存在的值，也可能等到前一个事务提交。

处理这类问题要分两层：先用正确的索引和条件把数据库锁范围缩窄，再把批次拆小并缩短事务持有时间；应用代码中复用参数容器只能减少 Go 堆分配，不能直接消除 InnoDB 的锁等待。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html

结论：完整唯一索引等值更新更接近“只锁记录”，非唯一条件或范围扫描在 REPEATABLE READ 下可能锁住间隙；如果业务允许，可以评估 READ COMMITTED，但仍要保留外键检查、重复键检查和幻读语义的边界。

先用一个并发场景还原等待点

假设订单表按租户和状态批量推进状态，应用同时还允许新订单写入同一个租户：

-- 事务 A：按非唯一条件扫描一段索引，并保持事务不提交
START TRANSACTION;
UPDATE orders
SET status = 'processing'
WHERE tenant_id = 7 AND status = 'pending' AND id > 1000 AND id 

这段 SQL 不是为了制造一个固定的线上复现结果。实际锁范围还取决于表结构、索引选择、执行计划和事务隔离级别。要点是：UPDATE 扫描的不是抽象的“行集合”，而是某个索引访问路径上的记录与区间。

如果等待方是对已有记录的更新，优先怀疑记录锁；如果等待方是范围内插入，优先检查范围扫描、next-key lock 以及是否使用了非唯一索引。两者在日志里都可能只表现为“lock wait”，排查时不能只看 SQL 的 WHERE 文本。

为什么隔离级别会把等待扩大到间隙

InnoDB 默认使用 REPEATABLE READ。在这个级别下，唯一索引的完整等值条件通常只需要锁住找到的索引记录；非唯一条件、范围条件或只命中复合唯一索引部分列的查询，则可能锁住扫描到的索引范围。next-key lock 可以理解为“记录锁 + 记录前面的间隙锁”，它用来阻止范围内出现新的索引记录，从而避免幻读。

因此下面两类写法的并发影响不同：

-- 更容易收窄为单条记录：条件覆盖完整主键或唯一键
UPDATE orders
SET status = 'done'
WHERE id = 1208;

-- 可能扫描并锁住一段范围：status 通常不是唯一键
UPDATE orders
SET status = 'processing'
WHERE tenant_id = 7 AND status = 'pending';

没有合适索引时，InnoDB 仍然需要通过索引记录完成锁定；表没有显式索引时还可能使用隐藏的聚簇索引。真正要看的不是“我写了几个条件”，而是这些条件是否形成了稳定、足够窄的索引访问路径。

把隔离级别切到 READ COMMITTED 后，普通搜索和索引扫描的间隙锁会被关闭，更新和删除主要锁索引记录，且不匹配的记录锁可以更早释放。这对批量更新降低锁覆盖很有帮助，但它允许并发事务在间隙中插入新记录，可能出现幻读；外键约束检查和重复键检查仍可能使用间隙锁，所以它不是“所有等待都消失”的开关。

-- 只影响当前事务，避免把全局默认值悄悄改掉
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE orders
SET status = 'processing'
WHERE tenant_id = 7 AND status = 'pending' AND id > 1000 AND id 

批处理怎样让 SQL 走窄索引范围

批处理最容易犯的错误，是每一轮都从同一个起点按状态扫描，或者只按时间字段取一大片数据。更稳的做法是给筛选条件和稳定游标准备复合索引，例如 (tenant_id, status, id)，每批记住上一次最大的 id，下一批从这个位置继续。

-- 复合索引让租户、状态和游标组成连续访问范围
CREATE INDEX idx_orders_tenant_status_id
ON orders (tenant_id, status, id);

-- 每批只处理游标之后的一小段，完成后尽快提交
START TRANSACTION;
UPDATE orders
SET status = 'processing'
WHERE tenant_id = 7
  AND status = 'pending'
  AND id > 1000
ORDER BY id
LIMIT 200;
COMMIT;

生产代码需要把本批最后一个 id 保存为下一轮游标；如果状态字段会在更新后改变，下一轮的筛选集合也会变，不能把“影响行数”直接当作游标。若业务要求严格不重复、不漏处理，应使用明确的主键游标、固定排序和可重试的状态迁移。

批次大小没有通用最优值。批次太大，单次锁持有时间和回滚成本都会上升；太小，则往返次数和提交开销增加。可以先按一个较小上限运行，再结合锁等待时长、单批耗时和吞吐量调整。先收窄索引范围，再讨论批次大小，通常比只改提交频率更有效。

怎样从锁等待信息判断真正的范围

不要只看应用返回的超时错误。MySQL 官方文档给出的快速入口是：

-- 在有权限的诊断会话中查看 InnoDB 当前状态和最近的锁信息
SHOW ENGINE INNODB STATUS\G

重点关注等待事务和持锁事务使用的索引名称、锁模式以及是否出现 locks rec but not gap 等信息。看到具体索引后，再对照 SQL 的完整条件：是否命中了唯一索引，是否只使用了复合唯一索引的部分列，是否出现了范围扫描或无索引过滤。

如果不同租户的批处理也互相等待，通常说明索引前导列没有把租户边界放在前面，或者事务一次扫描了过宽的范围；如果只有插入等待而已有行更新很快，则更应检查 gap lock，而不是一味增加连接池或重试次数。

Go 批处理代码怎样减少额外分配

数据库锁范围由 SQL、索引和事务决定，但应用批处理仍可能在每轮循环中反复创建参数切片。可以把固定数量的参数放进数组，按切片传给预处理语句；这样不会为“组装五个参数”反复扩容。下面的示例只展示参数容器复用，连接、事务和驱动的实际分配仍应以基准测试为准。

package batch

import (
    "context"
    "database/sql"
)

// UpdateChunk 复用固定参数数组，把数据库锁优化和应用内存优化分开。
func UpdateChunk(ctx context.Context, stmt *sql.Stmt, tenantID, lastID int64, limit int) (int64, error) {
    // 数组的容量固定，切片只是一个不拥有底层存储的视图。
    args := [5]any{"processing", tenantID, "pending", lastID, limit}

    // SQL 应包含 ORDER BY id LIMIT ?，让每批边界稳定且可继续推进。
    result, err := stmt.ExecContext(ctx, args[:]...)
    if err != nil {
        // 交给上层决定回滚、重试或记录锁等待上下文。
        return 0, err
    }
    affected, err := result.RowsAffected()
    if err != nil {
        return 0, err
    }
    return affected, nil
}

如果代码还需要读取本批最后一个主键，可以复用一个结构体或预分配的结果缓冲区，但不要为了追求“零分配”把事务提交、错误处理和游标推进省略。应用侧分配下降只会减轻 GC 和 CPU 压力；事务仍然长、索引仍然宽时，间隙锁等待不会因为参数数组复用而消失。

一份可以落地的选择清单

最终判断顺序可以固定为：先确认等待双方，再确认持锁索引和锁模式，接着检查隔离级别与索引条件，最后才调批次大小和应用分配。这样能避免把数据库并发问题误判成 Go 内存问题，也避免为了消除一个间隙锁而无意中改变业务一致性。

相关问题

为什么完整主键等值更新通常不会锁前面的间隙？ 因为 InnoDB 可以用唯一索引定位唯一记录，官方文档说明这种搜索通常只需要索引记录锁；但复合唯一索引没有提供完整列条件时，仍可能发生间隙锁。

READ COMMITTED 能不能彻底解决间隙锁等待？ 不能。它关闭普通搜索和索引扫描的间隙锁，但外键检查和重复键检查仍有间隙锁语义，并且幻读边界会改变。

把批次改小是不是一定更快？ 不是。它通常缩短单次持锁时间，却增加提交和网络往返次数；要和索引范围、单批耗时、等待时长一起测量。

复用 Go 参数数组能减少数据库锁吗？ 不能。它主要减少应用侧临时容器的分配；锁等待仍由索引访问路径、隔离级别和事务提交边界决定。