MySQL error 1206: Locks exceed the lock table size
InnoDB ran out of memory for row locks during a very large transaction. Commit in smaller batches or enlarge the buffer pool.
- Error number
1206- Name
ER_LOCK_TABLE_FULL- SQLSTATE
HY000- Category
- Transactions and locking
What it means
InnoDB keeps its lock structures in the buffer pool. A single statement or transaction that locks a huge number of rows, such as an UPDATE or DELETE over millions of rows, can exhaust that memory, and the statement fails with 1206. It's most common on servers with a small innodb_buffer_pool_size.
Common causes
- One
UPDATE,DELETEorINSERT ... SELECTover a very large table. - An undersized
innodb_buffer_pool_size(the default is 128 MB).
How to fix it
-
Process the rows in batches, committing between them.
DELETE FROM events WHERE created_at < '2023-01-01' LIMIT 10000; -- repeat until 0 rows affected
-
Give InnoDB more memory if the server has it.
SET PERSIST innodb_buffer_pool_size = 2 * 1024 * 1024 * 1024;
Related errors
More transactions and locking errors
1213Deadlock found when trying to get lock3572Lock couldn't be acquired with NOWAIT1792Write in a READ ONLY transaction3024Maximum statement execution time exceeded
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.