“The total number of locks exceeds the lock table size”
I encountered this error when executing the SQL below.
DELETE perfdata_service_raw
FROM perfdata_service_raw, perfdata_host
WHERE perfdata_service_raw.host_name = perfdata_host.host_name AND
perfdata_host.is_deleted = 1
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.
[mysqld]
set-variable=innodb_buffer_pool_size=256M
The part I want to understand is what this variable has to do with table locks. The description of the innodb_buffer_pool_size variable in the MySQL 4.x Documentation states:
“The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system.”
I continue to search, but not finding anything at the moment that will help me understand how lock table size and innodb_buffer_pool_size are related. The quest for an answer continues …
Update (21-Oct-2006)
Found an explanation here which says:
“The SQL statement sets locks on all the records it scans. If you have a small buffer pool, then the InnoDB lock table may indeed grow so big that it does not fit in the buffer pool. “
I want to understand more about the InnoDb lock table, so a little more research will need to be done.
Update (23-Oct-2006)
Found what I was looking for about the InnoDB lock table and it’s behavior at MySQL Performance Blog. It gives some background on the InnoDB lock table:
“in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record allocated for each hash and for each row locked on that page bit can be set.”
References
Karol Zielinski: Just a tech stuff
James Wallace: blog.ffff.ca
Mike R's Blog 
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?
Bob,
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.
[…] posts by users experiencing the same problem:https://mrothouse.wordpress.com/2006/10/20/mysql-error-1206/http://www.zabbix.com/forum/showthread.php?t=1546http://lists.mysql.com/mysql/193440Other resources […]
[…] Google search led me to a blog post over at Mike R’s blog, where he explained things […]
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.
I see from the notes on DELETE that you can specify a LIMIT clause, so that reduces the number of locks needed. One of my big deletes (is 4 million rows big? Maybe not…) relied on a left join to spot orphaned records. That wasn’t compatible with LIMIT, so I use a lookup in the where clause instead:
DELETE FROM A WHERE (SELECT ID FROM B WHERE A.ID = B.ID) IS NULL LIMIT 100000;
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!
Sean,
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.
Mike
Thanks, Mike! You saved my day.
Grig
Thanks, helped to me too.
I really enjoyed reading this post, keep up creating such interesting stuff.
[…] time.Further reading:MySQL Bug #15667 – The total number of locks exceeds the lock table sizeMySQL Error 1206 » Mike R’s Blog 0 Comments. Posted by Major Hayden on Tuesday, February 16, 2010 at 12:00 pm. Filed under […]
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.
Hi
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.
Regards
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 ?
Thanks in advance
Hi there! I could have sworn I’ve been to this blog
before but after browsing through some of the posts I realized it’s new to me.
Anyhow, I’m definitely happy I discovered it and I’ll be
bookmarking it and checking back frequently!
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.
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
#innodb_log_arch_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 – 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 4G
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
The maximum player in the online poker roulette is 5. To use the phrase genius with Iblis and Asmoday is short changing these two “enzymes”. And at the cash sport tables, restrict is a popular fashion of betting.