EC
1206
MariaDBERRORCommonLockingHIGH confidence

Total number of locks exceeds the lock table size

Production Risk

High — transaction is rolled back; large operations must be redesigned as batches.

What this means

ER_LOCK_TABLE_FULL (1206, SQLSTATE HY000) is raised when the number of row locks or lock memory used by InnoDB exceeds the innodb_buffer_pool_size allocation for lock structures. This causes the offending transaction to be rolled back.

Why it happens
  1. 1Very large transactions locking millions of rows without committing
  2. 2innodb_buffer_pool_size too small for the workload
  3. 3Missing indexes causing full table scans that lock every row
How to reproduce
trigger — this will error
trigger — this will error
-- Large update without commit locking many rows
START TRANSACTION;
UPDATE huge_table SET status = 'processed' WHERE created_at < '2020-01-01';
-- May produce ERROR 1206 if millions of rows are locked

expected output

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

Fix 1

Increase innodb_buffer_pool_size

Increase innodb_buffer_pool_size
-- In my.cnf:
-- innodb_buffer_pool_size = 4G
-- Then restart MySQL

Why this works

More buffer pool memory allows more lock structures to be maintained.

Fix 2

Process in smaller batches

Process in smaller batches
-- Process 10,000 rows at a time
UPDATE huge_table SET status = 'processed'
WHERE created_at < '2020-01-01' AND id BETWEEN 1 AND 10000;
COMMIT;

Why this works

Smaller transactions use fewer locks at any one time.

Sources
Official documentation ↗

MySQL 8.0 — 1206 ER_LOCK_TABLE_FULL

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All MariaDB errors