今天的大大告诉我一台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空间来处理数据,该值过小就会报错。