MySQL Error 1206: Lock Table Full
Fix MySQL Error 1206 by shrinking oversized transactions, batching row changes, checking query indexes, and sizing the InnoDB buffer pool.
On this page
MySQL Error 1206 (HY000, ER_LOCK_TABLE_FULL) means InnoDB needs more memory to manage the locks acquired by a transaction. The message is The total number of locks exceeds the lock table size. It commonly occurs during a large INSERT, UPDATE, or DELETE that locks many rows. See the MySQL 8.4 error reference.
Identify the transaction holding many locks
Review the statement that failed, then inspect current InnoDB transactions and locks:
SHOW ENGINE INNODB STATUS\G
If your account can read Performance Schema, this query counts lock records by transaction. It is a diagnostic clue, not a direct measurement of lock-memory bytes:
SELECT ENGINE_TRANSACTION_ID, COUNT(*) AS lock_records
FROM performance_schema.data_locks
WHERE ENGINE = 'INNODB'
GROUP BY ENGINE_TRANSACTION_ID
ORDER BY lock_records DESC;
Check the statement’s access path as well. An UPDATE or DELETE that scans many index records may acquire locks beyond the rows you intend to change. Use EXPLAIN and review its WHERE and join columns before adding or changing indexes.
Break large changes into batches
If the operation does not need to be atomic across the entire table, process a limited, deterministic set of rows and commit each batch. For example, with id as a primary key:
START TRANSACTION;
UPDATE event_log
SET archived = 1
WHERE archived = 0
ORDER BY id
LIMIT 500;
COMMIT;
Repeat the operation until no rows remain. If an explicit transaction spans every batch, the locks can still accumulate; commit between batches only when partial progress is valid for the application. For a change that must be all-or-nothing, plan the transaction and available server memory before running it.
Review buffer pool capacity carefully
MySQL’s guidance also lists increasing innodb_buffer_pool_size as a way to provide more memory for InnoDB locks. Check the server’s available memory and workload before changing this global setting; an oversized buffer pool can cause memory pressure or swapping. Prefer reducing the transaction’s lock footprint when the update or delete can safely be batched. See optimizing InnoDB transaction management and InnoDB buffer pool sizing.
Error 1206 is different from Error 1205, which is a lock wait timeout, and Error 1213, which is a deadlock. See how to troubleshoot Error 1205, how to troubleshoot Error 1213, and the MySQL transaction guide.