Fix: ERROR 1206 (HY000): The total number of locks exceeds the lock table size

ERROR 1206 means a single statement tried to hold more locks than MySQL had room to record. Every row a write touches must be tracked as a lock until the statement or transaction ends. When a statement affects a huge number of rows at once, a mass DELETE, a sweeping UPDATE, or a large operation on a MyISAM table, the lock tracking structure fills up and MySQL aborts the statement with 1206. Unlike ERROR 1205, this is not about waiting on another transaction: it is one operation exceeding lock capacity by itself.

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

What this error means

MySQL must remember every lock it holds. On InnoDB, row locks live in the buffer pool, so the practical ceiling is tied to innodb_buffer_pool_size. When one statement locks millions of rows before committing, the memory needed to track those locks can exceed what the buffer pool has spare, and InnoDB raises 1206. On MyISAM, the lock structure is far more limited and lacks row level locking altogether, so large operations hit the ceiling much sooner. In both cases the answer is either to hold fewer locks at a time or to give the engine more room to track them.

Common causes

• A single DELETE or UPDATE that touches millions of rows in one statement, accumulating a lock per row until commit.
• Tables still using the MyISAM storage engine, whose lock table is small and inflexible compared to InnoDB.
• A bulk data cleanup or migration run as one unbounded statement rather than in batches.
• innodb_buffer_pool_size left small on a server now handling large writes.
• Large INSERT ... SELECT operations that lock the full source range while writing.
• Loading or purging historical data in a single transaction instead of chunking it.

How to fix it

STABILIZE first. If a mass write just failed, do not simply rerun it larger. Break it into bounded chunks so no single statement exhausts the lock table.

1. STABILIZE and FIX the operation: batch the big write into small ranges that each commit on their own. For a mass delete, loop over a bounded LIMIT until no rows remain.

DELETE FROM events WHERE created_at < '2025-01-01' LIMIT 10000;

Repeat until the affected row count is zero. Committing between batches releases locks and keeps each statement small.

2. DIAGNOSE: check the storage engine of the table involved. MyISAM tables are the classic 1206 trigger.

SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = 'mydb' AND table_name = 'events';

3. FIX the engine if the table is MyISAM. Converting to InnoDB gives you row level locking and lock tracking backed by the buffer pool.

ALTER TABLE events ENGINE = InnoDB;

Test on a copy first for large tables, since the conversion rewrites the table and takes time.

4. FIX the capacity for InnoDB workloads. Raise innodb_buffer_pool_size so the engine can track more row locks. On MySQL 8.0 this is dynamic and does not require a restart.

SET GLOBAL innodb_buffer_pool_size = 4294967296;

Make it permanent in the config so it survives a restart:

[mysqld]
innodb_buffer_pool_size = 4G

5. FIX large INSERT ... SELECT by chunking the source range the same way, inserting a bounded number of rows per statement and committing between chunks.

6. Verify the batched job completes without 1206 and confirm the new buffer pool size is live.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

How to prevent it

• Write mass deletes and updates as bounded batches with a LIMIT, committing between chunks, never as one unbounded statement.
• Standardize on InnoDB; treat any remaining MyISAM table as tech debt to migrate.
• Size innodb_buffer_pool_size for the machine’s RAM and the size of your write operations.
• Run data purges and backfills as scheduled, chunked jobs rather than ad hoc giant statements.
• Test large migrations against production sized data so you find the lock ceiling before customers do.

When to call a senior engineer

Call for help when a required migration or purge cannot easily be chunked, when converting a large MyISAM table risks downtime, or when 1206 recurs even after batching and a bigger buffer pool. A senior engineer can design a safe batched migration, plan the InnoDB conversion around your uptime needs, and size the buffer pool for your workload. Erzon can run these large data operations without locking your database out from under live traffic.