MySQL error 1206 when updating

Question

I am receiving the following error in MySQL when I attempt to update any records: ERROR 1206 (HY000): The total number of locks exceeds the lock table size

Based on all of the relevant questions I could find on stackoverflow, I gather that this error occurs when the innodb_buffer_pool_size variable is set too small relative to how many records a statement is affecting.

However, I am receiving this error even when I attempt to update a single record (or even zero records), which is really confusing me. I should also point out that I've been updating records on this table for months without ever seeing this error until yesterday. Any ideas on what could be causing this error even when only updating a single record?

For reference, I'm using MySQL 5.6. When I execute the statment SHOW VARIABLES LIKE 'innodb_buffer%'; I get the following:

+-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | innodb_buffer_pool_dump_at_shutdown | OFF | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 8 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | OFF | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_size | 12582912 | +-------------------------------------+----------------+

Any ideas are highly appreciated!

EDIT: I should also point out that I can insert records with no problems. I'll also admit that I'm using Windows.

EDIT 2: It looks like increasing innodb_buffer_pool_size did the trick. However, that solution goes against the prevailing wisdom elsewhere on stackoverflow that one can simply limit how many records are accessed in a query (since I was only trying to update a single record).

You did try and restart mysqld? Try also checking SHOW ENGINE InnoDB STATUS command. — LSerni
– LSerni, Commented Feb 2, 2016 at 18:29

Suanbit · Accepted Answer · 2018-03-23 08:35:08Z

1

After reading all the post related to this Mysql Error what solved it was to Increase the innodb_buffer_pool_size to 16106127360

Hope to help someone ;)

answered Mar 23, 2018 at 8:35

Suanbit

4911 gold badge5 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL error 1206 when updating

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related