The Problem: A Simple Cleanup Gone Wrong
I recently tried to purge old data from a production logs table. The table had about 40 million rows, and I wanted to remove anything older than six months. It seemed like a straightforward task, so I ran this query:
DELETE FROM activity_logs WHERE log_date < '2023-06-01';
Everything looked fine for about five minutes. Then, the operation suddenly failed with this error:
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
The database wasn't down, but my cleanup task was dead in the water.
Why This Happens
InnoDB handles locking differently than many other database engines. Instead of escalating thousands of row locks into a single table lock to save memory, InnoDB tracks every single row lock individually. These locks live inside the InnoDB Buffer Pool.
Each row lock consumes roughly 150 bytes of memory. If you try to delete 10 million rows in one go, MySQL needs about 1.5 GB of free space in the buffer pool just to store the lock metadata. If your innodb_buffer_pool_size is set to a small value—like the default 128MB—the engine runs out of room and kills the process to protect the system memory.
Quick Fix: Bump the Buffer Pool Size
If your server has spare RAM, the fastest way to resolve this is to give the lock table more breathing room. You can often fix the immediate bottleneck by increasing the buffer pool size dynamically.
1. Check your current allocation
SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS size_mb;
2. Increase it on the fly (MySQL 5.7.5+)
Suppose you have 16GB of total system RAM and your buffer pool is only at 1GB. You can safely bump it to 4GB without a restart:
SET GLOBAL innodb_buffer_pool_size = 4294967296;
Keep an eye on your OS memory usage. If you set this higher than your available physical RAM, the Linux OOM killer might step in and crash your entire MySQL service.
The Better Solution: Batching (Chunking)
Throwing more RAM at the problem is a temporary fix. The professional way to handle large datasets is to break the work into smaller, manageable pieces. Batching prevents the lock table from filling up and keeps the database responsive for other users.
Method 1: The DELETE Loop
Instead of one massive DELETE, use a loop that processes 5,000 rows at a time. This allows MySQL to commit the transaction and release locks after every chunk.
-- Run this in your SQL client or a script
REPEAT
DELETE FROM activity_logs
WHERE log_date < '2023-06-01'
LIMIT 5000;
SELECT ROW_COUNT() INTO @rows;
-- A tiny sleep helps prevent CPU spikes on busy servers
DO SLEEP(0.05);
UNTIL @rows = 0 END REPEAT;
Method 2: Primary Key Range Updates
If you are updating millions of rows, avoid using LIMIT with large offsets, as it gets slower over time. Instead, target specific ID ranges. This is significantly faster because it uses the clustered index directly.
-- Process ID ranges of 10,000
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01' AND id BETWEEN 1 AND 10000;
UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01' AND id BETWEEN 10001 AND 20000;
Making Configuration Changes Permanent
To ensure your settings survive a server reboot, update your my.cnf or my.ini file. For a dedicated database server, a common rule of thumb is to allocate 70-80% of total RAM to the buffer pool.
[mysqld]
# Example for a server with 8GB RAM
innodb_buffer_pool_size = 6G
# Use 1 instance per GB for better concurrency
innodb_buffer_pool_instances = 6
Verification
How do you know if your batching is working? Run this command during your next big operation:
SHOW ENGINE INNODB STATUS\G
Check the TRANSACTIONS section. You want to see the "row locks" count stay low (matching your batch size) rather than climbing toward the millions. If the count stays under 10,000, you are in the safe zone.
Summary Checklist
- **The Band-aid:** Increase `innodb_buffer_pool_size` if you have the RAM.
- **The Real Fix:** Rewrite queries to process data in chunks of 5,000 to 10,000 rows.
- **The Safety Net:** Monitor `innodb_row_lock_current_waits` to spot locking bottlenecks before they crash your queries.