// MySQL error reference

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
Error message
The total number of locks exceeds the lock table size

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, DELETE or INSERT ... SELECT over a very large table.
  • An undersized innodb_buffer_pool_size (the default is 128 MB).

How to fix it

  1. Process the rows in batches, committing between them.

    DELETE FROM events WHERE created_at < '2023-01-01' LIMIT 10000;
    -- repeat until 0 rows affected
  2. 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

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.

// fewer errors, faster fixes

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.