How to fix ERROR 1206 (HY000): The total number of locks exceeds the lock table size in MySQL

MySQLINTERMEDIATEHIGHUpdated Dec 23, 2025· 271 views

ERROR 1206 occurs when InnoDB exhausts the lock table buffer space during large transactions or high-concurrency operations. The primary fix is increasing innodb_buffer_pool_size, though breaking large operations into smaller batches provides a quick workaround.

Quick fix

Fixes 1206

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
  1. Check current innodb_buffer_pool_size setting
  2. Increase innodb_buffer_pool_size in MySQL configuration
  3. Verify the change took effect
  4. Break large operations into smaller batches (temporary workaround)
mysql — error
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

What this error means

This error indicates that the InnoDB storage engine has run out of memory allocated for managing row locks. InnoDB stores lock tables within the innodb_buffer_pool_size allocation, and when you perform operations that affect many rows (such as bulk updates, deletes, or large inserts), the lock memory can become exhausted. By default, MySQL allocates only 128MB for this buffer pool, which is insufficient for most real-world applications performing large transactions. The error commonly occurs during: - Importing large data files (multi-gigabyte loads) - Bulk deletion of tens of thousands of rows with cascading constraints - CREATE TABLE...SELECT operations with full-table locks - High-concurrency scenarios where multiple connections lock overlapping row ranges - Long-running transactions holding many locks simultaneously

Typical symptoms

  • Error message "Total number of locks exceeds lock table size" appears during bulk operations
  • INSERT, UPDATE, or DELETE operations fail suddenly after processing some rows
  • Large data import scripts halt unexpectedly
  • Lock-related errors occur even after restarting (if buffer pool size is small)
  • Application experiences intermittent failures during peak load

Common causes

  1. innodb_buffer_pool_size is too small (default 128MB is insufficient for large operations)
  2. Bulk operation affects too many rows in a single transaction
  3. Multiple long-running transactions holding locks simultaneously
  4. CREATE TABLE...SELECT statements using full-table locks
  5. Cascading deletes triggering locks on many dependent rows
  6. Inefficient queries requiring full-table scans with locks

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

1Check current innodb_buffer_pool_size setting

Run this query to see your current buffer pool allocation:

sql
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Note the value (typically shown in bytes). If it's less than 1GB on a production server, it's likely too small.

2Increase innodb_buffer_pool_size in MySQL configuration

Edit your MySQL configuration file (/etc/my.cnf, /etc/mysql/my.cnf, or /etc/mysql/mysql.conf.d/mysqld.cnf depending on your system):

ini
[mysqld]
innodb_buffer_pool_size = 2G

For a dedicated database server, allocate 50-80% of available RAM. For shared systems, start with 1-2GB and monitor memory usage.

Common sizing:
- Development: 256MB-512MB
- Small production: 1-2GB
- Medium production: 4-8GB
- Large production: 16GB+

After editing, restart MySQL:

bash
sudo systemctl restart mysql
# or
sudo service mysql restart
3Verify the change took effect

After restarting, confirm the new buffer pool size:

sql
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

The value should now match your configuration change.

4Break large operations into smaller batches (temporary workaround)

If you cannot restart MySQL immediately, refactor your operation to process rows in chunks. Instead of:

sql
UPDATE large_table SET status = 'inactive' WHERE created_at < '2020-01-01';

Use a loop with LIMIT:

sql
SET @batch_size = 10000;
WHILE EXISTS (
  SELECT 1 FROM large_table
  WHERE created_at < '2020-01-01'
  LIMIT 1
) DO
  DELETE FROM large_table
  WHERE created_at < '2020-01-01'
  LIMIT @batch_size;
  COMMIT;
END WHILE;

In application code (Python example):

python
batch_size = 10000
while True:
  cursor.execute(
    "DELETE FROM large_table WHERE created_at < %s LIMIT %s",
    ('2020-01-01', batch_size)
  )
  rows_deleted = cursor.rowcount
  db.commit()
  if rows_deleted == 0:
    break

This releases locks frequently, preventing lock table exhaustion.

5Consider reducing transaction isolation level (read-only operations)

For SELECT operations on read-uncommitted data, temporarily reduce the isolation level:

sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM large_table WHERE condition;

This avoids gap locks on range scans, reducing lock memory usage. Use only when dirty reads are acceptable.

6Rebuild the table (if applicable)

If the table has undergone many delete/truncate operations, fragmentation may require more locks. Rebuild it:

sql
ALTER TABLE large_table ENGINE=InnoDB;

This reclaims wasted space and may reduce lock requirements. Warning: This is a blocking operation on large tables.

Advanced notes

Multiple Buffer Pool Instances: On systems with large buffer pools (>1GB), split the buffer into multiple instances to reduce contention:

ini
[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8

InnoDB Row-Level Locking vs Table Locks: This error is specific to InnoDB's row-level locking. MyISAM tables use table-level locks and won't encounter this error, though they have different concurrency limitations.

Monitoring Lock Memory: Use Performance Schema to monitor lock usage:

sql
SELECT * FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%innodb_lock%';

Production Impact: Buffer pool size changes require a restart. Plan this during maintenance windows. For zero-downtime adjustments on MySQL 5.7+, consider using a replica: resize the replica's buffer pool, promote it to primary, then demote and resize the old primary.

Frequently asked questions

What does "1206" mean?
This error indicates that the InnoDB storage engine has run out of memory allocated for managing row locks. InnoDB stores lock tables within the innodb_buffer_pool_size allocation, and when you perform operations that affect many rows (such as bulk updates, deletes, or large inserts), the lock memory can become exhausted. By default, MySQL allocates only 128MB for this buffer pool, which is insufficient for most real-world applications performing large transactions.
How do I fix "1206"?
ERROR 1206 occurs when InnoDB exhausts the lock table buffer space during large transactions or high-concurrency operations. The primary fix is increasing innodb_buffer_pool_size, though breaking large operations into smaller batches provides a quick workaround.
What causes "1206"?
innodb_buffer_pool_size is too small (default 128MB is insufficient for large operations) Bulk operation affects too many rows in a single transaction Multiple long-running transactions holding locks simultaneously
Is the "1206" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading