The total number of locks exceeds the lock table size error (corrected)

mysql resolves the total number of locks exceeds the lock table size error

Online Google search related issues, found a foreign cow to explain this:

If you ' re running a operation on a large number of rows within a table that uses the InnoDB storage engine, you might This error:

ERROR 1206 (HY000): The total number of locks exceeds the lock table size
MySQL is trying to tell your IT doesn ' t have enough room to store all of the row locks that it would need to execute Y Our query. The only way to fix it for sure are to adjust innodb_buffer_pool_size and restart MySQL. By default, which is set to only 8MB, the which is too small for anyone The who are using InnoDB to do anything.
If you are need a temporary workaround, reduce the amount of rows you ' re manipulating in one query. For example, if your need to delete a million rows from a table, try to delete the records in chunks of 50,000 or 100,000 R oWS. If you are inserting many rows, try to insert portions of the the data in a single time.

The original is InnoDB table to perform a large amount of data update, INSERT, delete operation will appear this problem, need to adjust InnoDB global innodb_buffer_pool_size value to solve this problem, and restart the MySQL service.

View the current database storage engine and use the Engine=innodb type when it is created.

The default innodb_buffer_pool_size=8m

To modify the value of Innodb_buffer_pool_size:

Vim/etc/my.cnf

Click (here) to collapse or open innodb_buffer_pool_size=64m

Restart the MySQL server again, perform a table operation, completed successfully.

Reference:

http://blog.csdn.net/slvher/article/details/9532107