I encountered this error when executing the SQL below.
Googled to find a resolution and found this link where the resolution is to increase the innodb_buffer_pool_size variable in /etc/my.cnf. The default value is 8M, so I set it to 256M, restart the mysqld service (service mysqld restart), and the problem is resolved.
I want to understand more about the InnoDb lock table, so a little more research will need to be done.
This entry was posted on Friday, October 20th, 2006 at 23:08 pm and is filed under Administration, MySQL.	You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
I have a large table (4GB) that I want to trim down. Everytime I try to perform an insert from this table to an archive table, I get the dreaded error 1206. Other than bumping up the memory of the machine, what are my options for a work around?
My knowledge in this area of MySQL is severely limited. I can only tell you from the experience I encountered, increasing the memory parameter solved my problem.
If I encountered your situation, I might try to move a small number of rows at a time. Not sure if that option is available to you or not. As I said, my knowledge with MySQL is very limited.
Very good approach of the issue.
Thanks for posting the info about the buffer pool size. There are a couple of workarounds that have just got me past this problem without changing any settings for the database.
Of course, you have to run the statement over and over until it deletes 0 rows, but it wouldn’t be a workaround if it was perfect!
Thanks for posting this useful information. If one is in a situation where a change to database settings cannot be immediately made, this workaround would certainly come in handy.
Thanks, Mike! You saved my day.
Thanks, helped to me too.
I really enjoyed reading this post, keep up creating such interesting stuff.
Hi I reach this site by mistake when i was searching bing for this issue, I have to say your site is really helpful I also love the design, its amazing!. I don’t have the time at the moment to fully read your site but I have bookmarked your site and also add your RSS feeds. I will be back in a day or two. thanks for a great site.
Thanks for the nice article. I am facing similar problem and now i have solution from you.
I do not think there is any other soln other than changing value of innodb_buffer_pool_size in my.cnf file.
I have the same problem, but I´m inserting in a temp table, there is no other way ? That table is mine, no one can use it, exclusive lock on the table didn´t help.
Which are the concecuences of increasing innodb_buffer_pool_size ?
I have the same problem. I have 102.000.000 record in table. I want to delete 50.000.000 but i can’t. Erros 1206. “The total number of locks exceeds the lock table size”.
My ram 8G. I changed innodb_buffer_pool_size :1g, but it’s not work.