MySQL Error 1206

“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

Advertisements

20 Responses to MySQL Error 1206

  1. Bob says:

    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?

  2. mrothouse says:

    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.

  3. Vinicius says:

    Very good approach of the issue.

  4. […] Google search led me to a blog post over at Mike R’s blog, where he explained things […]

  5. Sean says:

    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!

  6. mrothouse says:

    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

  7. Grig Gheorghiu says:

    Thanks, Mike! You saved my day.

    Grig

  8. arcartscava says:

    I really enjoyed reading this post, keep up creating such interesting stuff.

  9. […] 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 […]

  10. win your ex back says:

    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.

  11. Jeremiah says:

    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

  12. Gustavo says:

    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

  13. 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!

  14. Jones Smith says:

    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

  15. Priscilla says:

    top post I’m a huge bingo playerfan from Holland

  16. uzman seo says:

    Google’da ararken makalenizi buldum yazılarınız ilgimi çekti teşekkürler

  17. Hmm is anyone else encountering problems with the images on this blog loading?
    I’m trying to determine if its a problem on my end
    or if it’s the blog. Any suggestions would be greatly appreciated.

  18. Actually when someone doesn’t know then its up to other users that they
    will assist, so here it occurs.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: