ERROR 1206 (HY000): The total number of locks exceeds the lock table size

今天的大大告诉我一台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

重启数据库，问题解决

问题原因总结：

InnoDB表执行大批量数据的DDL操作时会调用innodb_buffer_pool_size空间来处理数据，该值过小就会报错。