Today's big tell me a MySQL mass update reported the following error "MySQL error code 1206 (er_lock_table_full): The total number of locks exceeds the LOCK TABLE Size
Start Troubleshooting:
First, look at the 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)
This value is 18446744073709551615 large enough to be the main cause of the error.
Second, view 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 default Size, 8M
The development environment parameter is adjusted to 2G
#vi/etc/my.cnf
[Mysqld]
innodb_buffer_pool_size=2g
Restart the database to solve the problem
Summary of the cause of the problem:
The
InnoDB calls innodb_buffer_pool_size space to process data when a DDL operation that performs mass data on a table is executed, and the value is too small to be an error.
