MySQL ERROR 1206: The total number of locks exceeds the lock table size
When querying a table with a very large number of rows using the InnoDB storage engine, you may get this error:
ERROR 1206 (HY000): The total number of locks exceeds the lock table sizeERROR 1206 means that MySQL does not have enough space to store all the row locks needed to execute the query. Fix it by increasing innodb_buffer_pool_size (default 8MB) and restarting MySQL. As an immediate workaround, reduce the number of rows affected by each query. For example, if deleting 500,000 rows produces “ERROR 1206 (HY000): The total number of locks exceeds the lock table size”, delete 10,000 rows at a time instead. For inserts, however, insert in one operation.
