MySQL: The total number of locks exceeds the lock table size | major.io

MySQL: The total number of locks exceeds the lock table size | major.io:

MYSQL: THE TOTAL NUMBER OF LOCKS EXCEEDS THE LOCK TABLE SIZE

If you’re running an operation on a large number of rows within a table that uses the InnoDB storage engine, you might see this error:

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

MySQL is trying to tell you that it doesn’t have enough room to store all of the row locks that it would need to execute your query. The only way to fix it for sure is to adjust innodb_buffer_pool_size and restart MySQL. By default, this is set to only 8MB, which is too small for anyone who is using InnoDB to do anything.

If you need a temporary workaround, reduce the amount of rows you’re manipulating in one query. For example, if you need to delete a million rows from a table, try to delete the records in chunks of 50,000 or 100,000 rows. If you’re inserting many rows, try to insert portions of the data at a single time.