🚨 MySQL Error Guide: 'ERROR 1206 (HY000)' The Total Number of Locks Exceeds the Lock Table Size

Exact Error Message

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

The statement that hit the limit is rolled back, and the InnoDB status log records the lock-memory exhaustion.

What the Error Means

InnoDB stores row locks in memory allocated from the buffer pool. When a single transaction needs more lock structures than InnoDB can allocate, it cannot take the next lock and aborts the statement with ERROR 1206. This is almost always a sign that one transaction is trying to lock an enormous number of rows at once — a multi-million-row DELETE, a giant UPDATE without a selective index, or a bulk load wrapped in one transaction.

Unlike ERROR 1205 (lock wait timeout) or 1213 (deadlock), 1206 is not about contention between transactions. A single transaction can hit it with no other connection active. The fix is to lock fewer rows per transaction, or to give InnoDB more memory for lock structures.

Common Causes

• A bulk DELETE or UPDATE affecting millions of rows in one transaction.
• An unindexed WHERE clause forcing InnoDB to scan and lock far more rows than logically targeted.
• A SELECT ... FOR UPDATE over a very large range.
• A data migration or backfill run as one giant transaction instead of chunked commits.
• An innodb_buffer_pool_size so small that even moderate lock counts exhaust the available lock memory.
• Autocommit disabled, so successive statements accumulate locks in one ever-growing transaction.

How to Reproduce the Error

On a server with a small buffer pool, run a large unindexed delete inside a single transaction:

START TRANSACTION;
DELETE FROM events WHERE created_at < '2025-01-01';

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

If events has tens of millions of matching rows and no usable index on created_at, InnoDB tries to lock them all and runs out of lock memory.

Diagnostic Commands

Check the buffer pool size, which governs how much lock memory is available (read-only):

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

A 128 MB buffer pool is small and easily exhausted by large lock sets. Inspect transactions and how many rows they are locking:

mysql -e "SELECT trx_id, trx_rows_locked, trx_rows_modified, trx_query FROM information_schema.innodb_trx ORDER BY trx_rows_locked DESC LIMIT 5;"

Confirm the offending statement uses an index rather than a full scan:

mysql -e "EXPLAIN DELETE FROM events WHERE created_at < '2025-01-01';"

A type: ALL with a NULL key means a full table scan, which locks far more than necessary. Check overall lock pressure:

mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A3 "TRANSACTIONS"

Step-by-Step Resolution

1. Stop running the operation as one giant transaction. The primary fix is chunking, not raising memory.
2. Add or use a selective index on the WHERE column so InnoDB locks only matching rows. Verify with EXPLAIN that the plan uses the index.
3. Rewrite the bulk operation to delete or update in batches, committing between chunks:

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

Repeat the limited delete in a loop until zero rows are affected; each batch is its own short transaction holding few locks.

4. Ensure autocommit is on (or commit explicitly) so locks are released between batches rather than accumulating.
5. If chunking is genuinely impossible and the workload needs it, increase innodb_buffer_pool_size to give InnoDB more lock memory, then restart. This is a secondary mitigation, not the real fix.
6. Re-run the operation and confirm trx_rows_locked stays bounded per batch.

Prevention and Best Practices

• Always chunk large DELETE/UPDATE/backfill jobs into batches of a few thousand rows with commits between them.
• Index the columns used in bulk-operation WHERE clauses so the lock footprint matches the logical row count.
• Size innodb_buffer_pool_size appropriately for the host (commonly 50-75% of RAM on a dedicated database server), which also benefits caching, not just lock memory.
• Run large maintenance jobs during low-traffic windows and monitor information_schema.innodb_trx for unexpectedly large trx_rows_locked.
• Prefer purpose-built tools (such as pt-archiver) for archiving/purging large tables, since they chunk and throttle automatically.

Related Errors

• ERROR 1205 (HY000): Lock wait timeout exceeded — contention between transactions waiting on locks, a different failure mode.
• ERROR 1213 (HY000): Deadlock found when trying to get lock — a lock cycle between transactions.
• ERROR 1114 (HY000): The table is full — out of space for the table itself, not lock memory.
• Out of memory errors in the OS log — when the buffer pool is set larger than physical RAM allows.

Frequently Asked Questions

Does raising innodb_buffer_pool_size fix this permanently? It raises the ceiling, but a transaction locking millions of rows is still fragile and slow. Chunking is the durable fix; more memory is a stopgap.

Why does a DELETE lock so many rows? InnoDB locks every row it examines, not just rows it changes. Without an index on the WHERE column it scans and locks the whole table.

Is this the same as a deadlock? No. A deadlock (1213) requires two or more transactions in a cycle. ERROR 1206 can happen with a single transaction and no contention at all.

Will autocommit help? Yes, indirectly — with autocommit on, each chunked statement commits and frees locks immediately, so they never accumulate into one oversized lock set.

How big should each batch be? A few thousand to ten thousand rows is typical; tune so each batch commits quickly and trx_rows_locked stays low. For a fast triage of a stuck bulk job, see more MySQL guides.