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.
Further reading:
What would you recommend for a innodb_buffer_pool_size? What affect will this have on my system? Thanks for the help!
I am encountering same issue. I am trying to update a table with 2.7 million tuples. I am not aware of what goes beneath in MySQL, wouldnt it be possible to have selective row locking and an automic update. This might just require creating a temporary table somewhere, but since hardware is cheap this might be plausible?
For example:
UPDATE xyz SET xyz_name=SUBSTRING(file_name, 1, LENGTH(file_name)-9);
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
This worked, i change the configuration file my.cnf in my centos, restart de deamon and work, I tried change the variabla in executiuon time and this is only readable, but change the configuration file and work, sorry my English. I recomended
Hey Hey, it works,thanks a lot for sharing the fix.
Thanks
Dhanesh Mane
Great advice, thanks!
I needed to convert a big ~2.5mln rows InnoDB table to MyIsam and got this error.
Setting innodb_buffer_pool_size to 50Mb did the trick easily. Convertion itself took very little time after that.