遇到ERROR 1206 (HY000): The total number of locks exceeds the lock table size这个报错很多朋友第一反应是“锁太多不够用了”然后就去搜参数、改配置。但实际排错下来这个错误背后牵扯的东西比字面意思要多得多。它既是 InnoDB 存储引擎锁管理机制的一个硬限制也是大事务、低效 SQL、甚至业务模型设计问题的间接信号。我最早在 MySQL 5.6 上踩到它时也是先被“锁表大小”这几个字带偏了方向绕了不少弯路。这篇文章我会从锁机制的原理说起把错误触发条件、排查路径、参数调整思路和根治方案一条条理清楚最后附上我在实际运维和开发中积累的问题速查表。无论你是 DBA、后端开发还是刚接手 MySQL 运维的初学者都能从里面找到直接能上手的排查和恢复方案。1. 错误本质与触发场景1.1 InnoDB 的锁到底存在哪要理解 1206 报错先得搞清楚 InnoDB 的锁信息存在哪里。InnoDB 的行锁、表锁、意向锁等信息并不是直接散落在内存中的任意位置而是由一个内部的锁管理模块统一登记。早期版本MySQL 5.5、5.6 以及 5.7 的部分小版本会为锁管理模块预分配一块独立的内存区域这块区域通常被称为“锁表”lock table。InnoDB 每产生一条锁记录就要在这块区域内占用一个槽位。这里容易混淆的是报错文案里的“lock table size”不是说某张业务表有多少行数据、占了多少空间而是指 InnoDB 内部用来登记锁信息的这块内存结构的总容量。你可以把它理解成一个固定大小的“登记簿”所有事务产生的锁记录都要往这本登记簿上写。当某个瞬间需要登记的锁记录总数超过了登记簿的容量InnoDB 就直接抛错The total number of locks exceeds the lock table size意思是“锁记录总数超过了锁表容量”。在 MySQL 5.7 及更早版本里这块锁表的大小与innodb_buffer_pool_size之间存在间接关联但又不是简单的“ buffer pool 越大锁表越大”的比例关系。不同版本内部实现有差异这就导致很多人盲目调大 buffer pool 之后发现 1206 依然时不时冒出来。因为在锁量峰值那一下可能确实触顶了也可能根本原因在于某一笔大事务的锁数量异常参数调得再大也只是推迟报错并不能根治。1.2 哪些操作最容易踩中 1206根据我接触过的真实故障以下几类场景是最常见的引爆点大事务批量 DML一个事务里 UPDATE、DELETE 几万甚至几十万行。行锁数量直接等于处理行数再算上二级索引上的锁锁记录数量轻松突破锁表容量。长时间运行的写事务事务没提交锁一直持有着同时又有其他事务不断请求新锁锁记录只增不减最终触顶报错。热点行并发更新多线程并发更新同一批热点数据每个更新语句都尝试对同一批行加锁锁等待让锁记录堆积同样会触发 1206。一次操作跨多张表一个事务里同时更新多张大表每张表的锁都登记在同一个全局锁表里多张表的锁数量叠加之后更容易爆。我还遇到过一种很容易被忽略的情况一张表的数据量并不大但 WHERE 条件没走索引UPDATE 扫描了几十万行InnoDB 在执行过程中会把这些行都锁上。等语句执行完才发现锁数量已经超过锁表容量。这种“看似只操作了一张小表、实际锁了几十万行”的情况才是 1206 在开发环境里反复出现的常见原因。2. 排查思路与定位方法2.1 拿到报错后的第一件事报错出现时业务 SQL 会直接失败事务通常不会自动提交但已经加上的锁不会立刻释放。此时不要急着改配置或重启 MySQL先做两件事通过SHOW ENGINE INNODB STATUS\G看当前 InnoDB 内部状态重点看 TRANSACTIONS 段。查询information_schema.INNODB_TRX找出当前正在运行的长事务。SHOW ENGINE INNODB STATUS\GSELECT trx_id, trx_state, trx_started, trx_rows_modified, trx_query FROM information_schema.INNODB_TRX\G这两步能帮你快速判断是“正在运行的某个事务锁爆炸”还是有大量连接都在等待锁。如果trx_rows_modified数值很大或者trx_started时间非常早大概率就是长事务在作祟。2.2 用系统表定位锁的具体来源确认有活跃事务后再去查 InnoDB 的锁等待关系。MySQL 5.7 可以使用下面两张系统表SELECT * FROM information_schema.INNODB_LOCKS; SELECT * FROM information_schema.INNODB_LOCK_WAITS;MySQL 8.0 中这些表改名为performance_schema.data_locks和performance_schema.data_lock_waitsSELECT * FROM performance_schema.data_locks; SELECT * FROM performance_schema.data_lock_waits;通过这些查询能看到锁对应的表、锁类型RECORD、TABLE 等以及锁模式X、S、IX 等。定位到具体是哪个事务在持有大量锁之后就可以针对性处理要么让业务侧尽快提交或回滚要么直接KILL掉已经失联或卡死的连接。提示5.7 的INNODB_LOCKS在锁数量极大的情况下查询本身也可能变慢但相比盲目重启来说这是成本最低的信息获取方式。2.3 判断锁数量是否逼近上限确定错误是锁数量触顶后还需要估算当前到底产生了多少个锁记录。一个比较实用的方法是看INNODB_TRX里trx_rows_locked字段5.7 有8.0 中名称可能变化它直接反映了事务当前锁定行数。再结合业务操作的行数就能判断锁记录数量级。更简单的估算方式是利用EXPLAINEXPLAIN SELECT * FROM orders WHERE user_id 10086 AND create_time 2024-01-01;如果 type 列是 ALL 或索引扫描rows 列的估算值就是这条 SQL 可能扫描的行数。UPDATE、DELETE 执行时加锁的行数通常不小于这个扫描行数。比如 EXPLAIN 显示 rows80000那这 8 万行在 InnoDB 内部可能产生 8 万条以上的锁记录。若同时还有二级索引参与锁记录数还会翻倍。3. 解决方案与参数调整3.1 调整 innodb_buffer_pool_size 的利与弊先澄清一个误区MySQL 没有一个直接的“锁表大小”参数可以单独调。网上很多文章说调innodb_buffer_pool_size是因为早期 InnoDB 会根据 buffer pool 大小来计算锁表容量。调大 buffer pool 确实可以提升可用的锁管理内存但这属于“间接扩容”副作用也很明显服务器物理内存有限buffer pool 调太大容易触发内存交换锁管理内存只是 buffer pool 的一部分调大 buffer pool 不一定线性增加锁表容量修改innodb_buffer_pool_size通常需要重启实例才能完全生效5.7 开始支持在线调整但锁表的重新分配仍需重启。所以我的建议是如果确认是偶发的大事务触顶临时调大 buffer pool 可以救急但如果 1206 频繁出现一定要回到 SQL 和事务层面找根本原因。参数调整只在紧急恢复时用不能当成日常解决方案。# my.cnf 示例 innodb_buffer_pool_size 4G修改后重启 MySQL再用SHOW VARIABLES LIKE innodb_buffer_pool_size;确认生效。3.2 常用根治方案拆事务、缩范围真正能杜绝 1206 的办法是减少单个事务持有的锁数量。这里分享几个我在生产环境验证过的具体思路。分批提交是最直接有效的手段。比如要把一张历史表里一年前的数据状态统一改成“已归档”不要用一条 UPDATE 处理全部行而是按照自增主键范围拆分-- 一次只处理 5000 行 UPDATE archive_data SET status archived WHERE id BETWEEN 1 AND 5000; COMMIT;每批操作后立即提交释放所有锁。然后继续下一批。这样单事务锁数量就被限制在 5000 行以内几乎不可能触发 1206。缩小锁范围同样重要。以最常见的翻页更新为例用 LIMIT 分批 UPDATE 时要保证每批只更新目标行UPDATE big_table SET flag 1 WHERE id IN ( SELECT id FROM big_table WHERE flag 0 LIMIT 2000 );注意MySQL 对子查询中 LIMIT 的限制比较严格如果碰到“You cant specify target table for update in FROM clause”的报错可以先用临时表或 JOIN 变通。更稳妥的做法是先用 SELECT 查出主键 ID 列表再按主键批量 UPDATESELECT id FROM big_table WHERE flag 0 LIMIT 2000; -- 拿到 2000 个 id 后执行 UPDATE big_table SET flag 1 WHERE id IN (...);这其实就是把一次性的“大锁”拆成多次“小锁”锁持有时间短、锁总数量少1206 自然不会再冒出来。3.3 提交时机与隔离级别的细节事务拆分之后还要注意事务的提交时机。InnoDB 默认隔离级别是 REPEATABLE READ它除了对操作行加锁还会对查询涉及的范围加间隙锁Gap Lock或临键锁Next-Key Lock。间隙锁也会占用锁表空间实际锁记录数往往比想象中多。如果业务上允许把隔离级别调整为 READ COMMITTED 可以从根本上减少间隙锁的开销transaction-isolation READ-COMMITTED或者在会话级别临时调整SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;但这种方式需要业务配合不是所有场景都适用。偏保守的做法是在 UPDATE / DELETE 的 WHERE 条件中尽量让条件命中到明确的主键范围或唯一索引避免让 InnoDB 扫描过大的间隙和索引区间。条件越精确加的锁越少锁表占用也越低。4. 常见问题与排查技巧实录4.1 典型问题速查表现象直接原因处理方式单条 UPDATE 报 1206扫描行数过大锁数量爆炸拆分 SQL按主键分批更新一个事务内多条 SQL 后报错事务累积锁未释放缩小事务粒度及时提交并发高时偶发 1206大量会话争抢锁锁记录堆积定位长事务并 KILL优化热点行更新逻辑已调大 buffer pool 仍报错参数只扩大容量没减少锁的产生回到 SQL 层做分批与优化8.0 版本极少报 1206新版本锁管理结构已重构若仍报错检查是否有自定义参数或异常插件这张表是我在实际排障过程中反复使用的对照清单。遇到 1206先在上面找最接近的场景能少走很多弯路。4.2 定位大事务的实操技巧要高效定位大事务除了INNODB_TRX还要会用 performance_schema 里的 events_statements_summary_by_digest 表它可以按 SQL 模板统计执行次数、扫描行数、锁时间等信息SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_ROWS_EXAMINED FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 20;这个查询能帮你快速找出扫描行数最多、影响行数最大的几条 SQL 模板。如果某条 SQL 的SUM_ROWS_EXAMINED远大于SUM_ROWS_AFFECTED说明它在执行过程中多读了大量数据锁的范围也被迫变大这种 SQL 就是 1206 的常客。另外我还习惯打开慢查询日志并设置较短的long_query_time专门捕获执行时间超过 1 秒的 UPDATE / DELETE 语句slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1配合mysqldumpslow工具把日志里的 TOP 10 慢 SQL 汇总出来优化优先级就非常清晰了。4.3 压测环境里的一个隐藏陷阱很多 1206 是在压测时出现的但压测环境的数据量和线上并不一致。压测库的表如果只有几万行然后测试脚本用UPDATE ... WHERE status 0这种条件去跑本来一次只锁几千行不太会触发问题。但有些压测工具会并发开启几十个会话同时对同一张表做全表扫描更新每个会话都锁全表锁记录就会在瞬间堆满引发 1206。这种时候不仅要从 SQL 层面拆分还要控制并发度。我习惯在压测脚本中对大事务类操作加上并发限制比如把并发线程数控制在 10 以内或者对同一张表的批量更新操作加分布式锁让同一时刻只有一个线程在跑大事务。压测要模拟的是线上真实流量不是模拟“把所有最坏情况同时触发”这个思路对排查 1206 很有帮助。4.4 我在这类故障上的一些真实体会处理过几次 1206 之后我有一个很明显的感觉这个报错就是一个“报警器”真正值得重视的是它背后的大事务和慢 SQL。参数调优只能优化容忍度不能消除病因。如果每次遇到 1206 第一反应就是调innodb_buffer_pool_size那过段时间业务量一涨同样的问题还会回来的。所以我现在遇到 1206排查顺序固定是这样的先查INNODB_TRX确认是否有长事务没提交再用INNODB_LOCK_WAITS/data_lock_waits看锁等待关系找出源头会话用 events_statements_summary_by_digest 找出最耗锁的 SQL 模板根据 SQL 特征决定分批方案还是索引优化最后才考虑临时调大 buffer pool 救急。这个顺序走下来大部分 1206 都能在不动参数的情况下处理掉。5. 从 ERROR 1206 延伸出来的几条运维建议既然 1206 和大事务深度绑定我在日常巡检里也会重点盯几类指标提前发现隐患活跃事务数正常情况下个位数或十几如果长期维持几十个以上说明有连接没及时提交。单事务影响行数用审计日志或业务日志统计单事务处理的记录数超过 1 万行就要关注。锁等待时长SHOW GLOBAL STATUS LIKE Innodb_row_lock_time%;能看到平均锁等待时间数值持续走高往往意味着锁竞争加剧。SHOW GLOBAL STATUS LIKE Innodb_row_lock%;这些指标能帮助你在 1206 真正爆发之前就发现问题。尤其是开发环境的数据库表结构刚上线时通常不会有大锁问题运行几个月后数据增长、索引失效、SQL 走偏之后1206 就会开始频繁出现。定期跑一遍上述指标比反复处理线上告警要省心得多。最后再分享一个小技巧如果遇到“某条 SQL 单看没问题但一并发就跑 1206”的情况多半是二级索引和主键索引的锁叠加导致锁记录数暴增。此时可以尝试强制走主键索引比如在 UPDATE 的 WHERE 条件里显式带上主键范围或者用 FORCE INDEX 让优化器不要选错索引。多试几种索引组合往往能发现锁数量骤降的方案。