ERROR 1206 (HY000): The total number of locks exceeds the lock table size
来源:互联网 发布:淘宝网店铺装修 编辑:程序博客网 时间:2024/05/16 18:30
今天的大大告诉我一台MySQL大批量update报如下错误“MySQL error code 1206 (ER_LOCK_TABLE_FULL): The total number of locks exceeds the lock table size”
开始排查:
首先,查看max_write_lock_count
mysql> show global variables like 'max_write_lock_count';
+----------------------+----------------------+
| Variable_name | Value |
+----------------------+----------------------+
| max_write_lock_count | 18446744073709551615 |
+----------------------+----------------------+
1 row in set (0.00 sec)
这个值18446744073709551615足够大,应该不是报错主因
其次,查看innodb_buffer_poll_size
mysql> show global variables like 'innodb_buffer%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
1 row in set (0.00 sec)
innodb_buffer_pool_size默认大小,8M
随即调整开发环境参数为2G
#vi /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=2G
开始排查:
首先,查看max_write_lock_count
mysql> show global variables like 'max_write_lock_count';
+----------------------+----------------------+
| Variable_name | Value |
+----------------------+----------------------+
| max_write_lock_count | 18446744073709551615 |
+----------------------+----------------------+
1 row in set (0.00 sec)
这个值18446744073709551615足够大,应该不是报错主因
其次,查看innodb_buffer_poll_size
mysql> show global variables like 'innodb_buffer%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
1 row in set (0.00 sec)
innodb_buffer_pool_size默认大小,8M
随即调整开发环境参数为2G
#vi /etc/my.cnf
[mysqld]
innodb_buffer_pool_size=2G
重启数据库,问题解决
问题原因总结:
InnoDB表执行大批量数据的DDL操作时会调用innodb_buffer_pool_size空间来处理数据,该值过小就会报错。
1 0
- ERROR 1206 (HY000): The total number of locks exceeds the lock table size
- mysql报错"ERROR 1206 (HY000): The total number of locks exceeds the lock table size"的解决方法
- mysql报错-ERROR 1206 (HY000)- The total number of locks exceeds the lock table size-的解决方法
- mysql报错"ERROR 1206 (HY000): The total number of locks exceeds the lock table size"的解决方法
- mysql Error:The total number of locks exceeds the lock table size
- MySQL解决[Err] 1206 - The total number of locks exceeds the lock table size问题
- 异常:java.sql.SQLException: The total number of locks exceeds the lock table size
- The total number of locks exceeds the lock table size错误(已纠正)
- Mysql解决The total number of locks exceeds the lock table size错误
- Mysql解决The total number of locks exceeds the lock table size错误
- The total number of locks exceeds the lock table size错误
- 解决:Mysql exceeds the lock table size 不指定
- .\ethercat.axf: error: L6047U: The size of this image (33488 bytes) exceeds the maximum allowed for
- error: L6050U: The code size of this image exceeds the maximum allowed for this versio
- Error: The number of filter groups does not divide the total number of filters.
- ..\OBJ\LCD.axf: error: L6047U: The size of this image (34984 bytes) exceeds,KEIL5破解方法
- error: L6047U: The size of this image (32868 bytes) exceeds the maximum allowed for this version of
- Dealing with the “Bitmap Size Exceeds VM Budget” error
- thinkphp
- poj 2135 费用流
- AVAudioPlayer进入后台播放
- FFMPEG 源码分析
- php 常用时间函数
- ERROR 1206 (HY000): The total number of locks exceeds the lock table size
- 重写与重载
- 【semi join】IN/EXISTS子查询的区别及其优化方式
- 【LeetCode OJ 001】Two Sum
- 将数字字符串转换为数字eg:“1234”->1234
- 开始
- Lua中数据类型的源码实现
- hibernate空格导致的错误!
- php 常用文件处理函数 与 文件上传