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

Run this query to see your current buffer pool allocation:

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.

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

[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:

sudo systemctl restart mysql
# or
sudo service mysql restart

After restarting, confirm the new buffer pool size:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

The value should now match your configuration change.

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

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

Use a loop with LIMIT:

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):

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.

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

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.

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

ALTER TABLE large_table ENGINE=InnoDB;

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