FAQ: Error with MySQL: "The total number of locks exceeds the lock table size"


When I try to build a database, or view reports, I get an error, "The total number of locks exceeds the lock table size". How can I fix this?

Short Answer

Increase the innodb_buffer_pool_size in my.cnf (my.ini), to 256M.

Long Answer

This occurs when MySQL runs out of locks, which for an InnoDB database occurs when the buffer pool is full. You can fix this by increasing the size of the buffer pool, by editng the innodb_buffer_pool_size option in my.cnf (my.ini), to set innodb_buffer_pool_size to a number higher than the default (which is typically 8M); for instance:

  innodb_buffer_pool_size = 256M

Then, restart MySQL, and try the Sawmill operation again.