MySQL Error 1206 - “The total number of locks exceeds the lock table size”

Published by Gareth | Filed under Technical Articles

Background:

This information in this post applies to MySQL tables using the InnoDB storage engine. I encountered this error when performing a DELETE against a table with millions of rows using a single column WHERE clause that didn’t leverage any indexes and fairly stock MySQL configuration.

Summary:

InnoDB implements row-level locking by placing locks on index records (called next-key locking). These locks are housed in a special locking hash table located within the InnoDB buffer pool. Locking rows consumes a few bits per row (something on the order of 3-4 bits per row). It’s important to remember that InnoDB will not only lock the matching rows but also the gaps between rows.UPDATE and DELETE statements against an InnoDB table that do no use an index for columns referenced in their WHERE clause will cause all rows in the table to be locked exclusively. To avoid this behavior, an index should be created to accommodate the WHERE clause.

Possible Solutions:

• Break the operation into smaller units of work (i.e. DELETE with a LIMIT clause)
• Create an index on the columns used in the WHERE clause
• Increase the InnoDB Buffer Pool Size (I personally think this should be a last resort)

Related Posts:

• “MySQL Error 1206″ on Mike R’s Blog