MySQL InnoDB执行DELETE报1206错误 锁表超限参数配置建议

ERROR 1206 (HY000): The total number of locks exceeds the lock table size报错的直接原因是InnoDB存储行锁信息的内存区域容量不足，无法容纳DELETE执行过程中需要加锁的所有行记录。
从你提供的SHOW ENGINE INNODB STATUS输出可以看到，当前InnoDB缓冲池总大小仅为8192个页（按InnoDB默认16KB页大小计算，总容量仅128MB），InnoDB默认会将缓冲池的1/16划分为锁表使用，对应锁可用内存仅8MB，完全无法支撑单次删除10000行的锁开销。同时你的服务器总内存达125GB左右，现有缓冲池配置严重偏低，不仅会触发锁报错，还会大幅拖慢数据库整体读写性能。

• 核心参数调整：如果这台服务器为MySQL专属服务器，将innodb_buffer_pool_size参数设置为96GB（约占总物理内存的75%，剩余内存预留给操作系统、复制线程、MySQL连接与日志开销）。调整后默认锁表可用容量可达6GB，完全覆盖单次删除10万行甚至更高批量操作的锁内存需求，直接解决1206报错，同时大幅提升数据库读写性能。如果服务器还部署有其他业务，可根据实际内存占用情况下调该参数，但最低不要低于16GB。
  参数修改方式：编辑MySQL配置文件my.cnf添加对应配置项后重启服务即可；若使用MySQL 8.0版本，支持在线动态调整，直接执行命令SET GLOBAL innodb_buffer_pool_size = 96 * 1024 * 1024 * 1024;无需重启即可生效。
• 删除操作优化：不建议单次执行LIMIT 10000及以上的批量删除，改为单次删除1000~2000行循环执行，既不会占用过多锁内存，也不会长时间持有锁阻塞业务请求、造成主从复制延迟。另外建议为billseconds字段创建索引，删除时可以快速定位目标行，避免全表扫描给无关记录加不必要的锁，进一步降低锁开销。
• 可选锁参数微调：如果因特殊原因无法调大缓冲池，可以单独调整innodb_lock_table_size参数（MySQL 5.7及以上版本支持），设置为256M即可满足当前批量删除的锁需求，但从长期性能考虑优先调整缓冲池大小。

参数调整完成后，执行SHOW VARIABLES LIKE 'innodb_buffer_pool_size';确认参数值符合预期，再测试原DELETE语句即可验证问题是否解决。日常运行时可定期查看SHOW ENGINE INNODB STATUS中TRANSACTIONS段落的锁结构、行锁数量，判断锁内存占用是否合理。

内容的提问来源于stack exchange，提问作者Ankit Doshi