MySQL InnoDB更新百万行报错1206锁总数超锁表大小问题

云上老周

2026-8-28

现有操作的核心问题

对InnoDB引擎表使用LOCK TABLES contacts WRITE完全无法减少行锁占用。Server层的表写锁和InnoDB引擎层的行锁是两套独立的锁机制，显式加表锁不会让InnoDB跳过行加锁流程，执行UPDATE时InnoDB依然会对所有扫描到的记录加排他行锁、间隙锁，锁的总数量不会有任何减少，反而会因为持有表级写锁，阻塞期间所有对该表的读写请求，额外扩大故障影响范围。
单次更新的批量规模设置过大。LIMIT 1000000意味着单次更新要锁定100万行记录，锁结构占用的内存很容易超过InnoDB锁内存阈值，触发1206错误。
大概率存在索引缺失导致的不必要锁开销。如果field1、field2字段没有合适的联合索引，UPDATE语句会走全表扫描，InnoDB会对扫描过程中路过的所有记录（包括不符合WHERE条件的记录）加锁，实际加锁量会远大于符合条件的目标行数，进一步放大锁内存占用。
参数修改未验证生效。修改innodb_buffer_pool_size后如果没有重启实例，或没有执行动态生效命令，配置不会实际加载到运行中的数据库实例，调整参数的操作等于没做。

可落地的解决步骤

移除冗余的LOCK TABLES操作，InnoDB的行锁更新不需要显式加Server层表锁，该操作无任何正向收益。
为更新语句创建适配的联合索引，从根源减少不必要的锁。执行以下语句建索引，让更新语句可以精准定位到目标行，避免全表扫描加冗余锁：
```
ALTER TABLE contacts ADD INDEX idx_field1_field2 (field1, field2);
```
如果表数据量过大，建议使用在线DDL工具执行索引创建，避免长时间阻塞业务。
缩小单次更新的批次规模，用小批量循环更新替代单次大数量更新，单次批次控制在1000~10000行区间即可，这个量级的锁占用完全不会触发锁内存超限。参考实现逻辑如下：
```
-- 循环执行直到没有符合条件的待更新行
REPEAT
    UPDATE contacts 
    SET field1 = field2 
    WHERE field1 = 0 AND field2 > 0 
    ORDER BY id -- 按主键排序，避免重复扫描、减少间隙锁范围
    LIMIT 5000; -- 单次更新5000行，可根据服务器性能在1000-10000区间调整
UNTIL ROW_COUNT() = 0 END REPEAT;
```
循环更新过程中每次语句执行完会自动提交事务（autocommit开启状态下），释放上一批次的锁，锁内存占用会始终维持在很低的水平。
确认参数配置实际生效。执行以下命令检查innodb_buffer_pool_size的实际运行值：
```
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
```
如果返回值和你设置的4G（对应字节数4294967296）不符，对于支持在线调整参数的版本（MySQL5.7+、MariaDB10.2+）可直接执行动态生效命令，无需重启实例：
```
SET GLOBAL innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024;
```
注意innodb_buffer_pool_size不要设置超过服务器物理内存的70%，否则会触发系统OOM导致数据库进程被强制杀死。

内容的提问来源于stack exchange，提问作者Andrey Y.