Mysql1206SQLSTATE: HY000 (ER_LOCK_TABLE_FULL) problem _ MySQL

Mysql1206SQLSTATE: HY000 (ER_LOCK_TABLE_FULL) problem bitsCN.com

One of the requirements of a project recently is that data in some databases needs to be deleted regularly (certain conditions are met, such as data not retained seven days ago)

Initial execution method:

Use Quartz to regularly perform database operations and delete data. use the delete statement for database operations.

When the data volume is small at first, there is no problem. later, as the data volume increases, an error is reported in the background when this task is executed. the specific error message is:

1206 SQLSTATE: HY000 (ER_LOCK_TABLE_FULL) problem

Google gave us a solution.

First:

View the values of the max_write_lock_count and innodb_buffer_pool_size parameters, modify the value of innodb_buffer_pool_size, and increase it to meet the requirements. For more information about setting values, see values:

Second:

Use Limit to Limit the number of lines written each time, but I have already done it in the code, so this should not solve my problem. Practice has proved that it is unreliable to use delete to delete large data. Finally, the implementation method is modified to achieve the goal of deletion. Perform the following steps to create a stored procedure: 1) create a backup table with the same structure as the target table, and select the data to be retained in the target table (and those that do not meet the deletion criteria). 2) run the truncate command to clear the target Table. 3) import all the backup table data prepared in step 1 to the empty Target table. the above stored procedure successfully completes the target table, the deletion record task is executed stably every day. Return to the original question: 1206 SQLSTATE: HY000 (ER_LOCK_TABLE_FULL) mysql reported this error. you can see the explanation of this error type on the mysql official website:

Error: 1206 SQLSTATE: HY000 (ER_LOCK_TABLE_FULL)

Message: The total number of locks exceeds the size of the locked table.

When mysql tables will be locked and what should be done when the tables will be locked? continue to study ....

Reference: http://www.gosoa.com.cn/mysql-%E9%94%81%E8%A1%A8%E6%9C%BA%E5%88%B6%E5%88%86%E6%9E%90

BitsCN.com