CODEWITHSUNDEEP
mysqlsql
zkstine
zkstine

Reputation: 55

MySQL error 1206 when updating

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).

Upvotes: 2

Views: 8250

Answers (1)

Suanbit
Suanbit

Reputation: 491

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 ;)

Upvotes: 1

Related Questions