MySQL "Lock wait timeout exceeded" (Error 1205): Find the Blocker and Fix It

MySQL "Lock wait timeout exceeded" is the error you get when a query has waited too long for a row that another transaction is holding. The message reads ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction, and the advice in it is only half right. Restarting the transaction might work. But if something is sitting on those rows, the retry will wait another 50 seconds and fail the same way. The useful question is not "how do I make the error go away" but "who is holding the lock, and why so long?"

What actually happened

InnoDB locks rows that a transaction changes, and rows read with SELECT ... FOR UPDATE or FOR SHARE. Those locks stay until the transaction commits or rolls back. Another transaction that wants to write the same row has to wait. innodb_lock_wait_timeout sets how long: 50 seconds by default. After that the waiting statement gets error 1205.

Three details catch people out:

• Only the statement is rolled back, not the transaction. By default InnoDB undoes just the statement that timed out. Everything the transaction did before that is still there, uncommitted, and its locks are still held. If your code catches the error and carries on, or commits, you can end up with half a transaction in the database. Setting innodb_rollback_on_timeout changes this so the whole transaction is rolled back, but it can only be set at server startup.
• It is not a deadlock. In a deadlock two transactions wait for each other and neither can finish. InnoDB detects that right away and rolls one back with error 1213. A lock wait timeout means one transaction simply waited too long behind another. The blocker may finish eventually; it just didn't do it in time. We cover the PostgreSQL side of deadlocks in our guide to "deadlock detected".
• It is about row locks only. Waits on metadata locks, for example a query stuck behind an ALTER TABLE, are controlled by a separate variable, lock_wait_timeout, whose default is one year.

Step 1: find the blocker while it is happening

The sys schema has a view built for exactly this. sys.innodb_lock_waits shows every waiting transaction, what it is waiting for, and which session is blocking it:

SELECT wait_age_secs, locked_table_name, locked_index,
       waiting_pid, waiting_query,
       blocking_pid, blocking_query, sql_kill_blocking_connection
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;

The blocking_pid column is the processlist ID of the blocking session, and sql_kill_blocking_connection contains a ready KILL statement for it. Before you run it, look at blocking_query. If it is NULL, the blocking session is not running anything at the moment. That is the most common case and the most important clue: a transaction that started, changed or locked some rows, and then went quiet without committing.

If several sessions block each other in a chain, look for the one at the head of the chain: it blocks others but is not waiting for anything itself. Killing sessions in the middle of the chain doesn't help.

Step 2: look at the oldest open transactions

Even when nothing is waiting right now, long-open transactions are where the next timeout will come from. information_schema.innodb_trx lists every running InnoDB transaction:

SELECT trx_mysql_thread_id AS pid, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec,
       trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 10;

A transaction that is minutes or hours old with trx_query set to NULL almost always means application code that opened a transaction and never closed it. Typical culprits:

• A transaction that stays open while the code calls an external API, sends an email or waits for a user.
• autocommit turned off in a connection pool or ORM setting, so every connection silently holds an open transaction.
• An error path that returns the connection to the pool without a rollback.
• Someone's interactive client session with an UPDATE they forgot to commit before going to lunch.

Step 3: decide what to kill

Killing the blocker releases its locks, but it also rolls back everything that transaction did. For an idle session that forgot to commit, that is usually fine, and it is the fastest way out. For a large batch job that has been changing rows for twenty minutes, the rollback can take a long time and keep holding locks while it runs. In that case it may be quicker to stop new work from piling up and let the batch finish.

Don't kill the waiting sessions. They are the victims, not the cause. Each of them will be replaced by a new request waiting on the same rows. And if waits pile up fast enough, the next error you see will be "Too many connections"; see our runbook for Error 1040 for that situation.

Why transactions hold locks longer than you think

Some causes are less obvious than a forgotten commit:

• Missing indexes. InnoDB locks the index records it scans, not only the rows it changes. In the manual's words, if there is no suitable index and MySQL has to scan the whole table, every row of the table becomes locked, and inserts from other users are blocked. An UPDATE ... WHERE status = 'pending' without an index on status can lock far more than the few rows it changes.
• Gap locks. Under the default REPEATABLE READ isolation level, range conditions also lock the gaps between index records, so inserts into that range wait too.
• Big batch updates. One UPDATE or DELETE touching a million rows holds a million row locks until it commits. Split it into batches of a few thousand rows with a commit after each.
• Hot rows. Counters, balance rows or a "last ID" row that every request updates. Every transaction lines up for the same row, and anything slow inside one of them delays all the others.

How to make it rare

1. Keep transactions short. The manual's advice is to do as little work as possible between the first change and the commit. Do the slow work, such as API calls, file I/O and calculations, before you open the transaction or after you commit.
2. Retry the whole transaction, not just the statement. Lock waits and deadlocks are normal on a busy server, and the application should expect them. Catch 1205 and 1213, roll back explicitly, wait a short random interval, and run the whole transaction again a limited number of times.
3. Index the columns you filter on in UPDATE, DELETE and SELECT ... FOR UPDATE. Fewer scanned rows means fewer locks.
4. Use NOWAIT or SKIP LOCKED for queues. Workers that pick jobs from a table shouldn't queue behind each other:

   START TRANSACTION;
   SELECT id FROM jobs
   WHERE status = 'queued'
   ORDER BY id
   LIMIT 10
   FOR UPDATE SKIP LOCKED;
   -- process the rows, then
   COMMIT;

5. Alert on old transactions. A transaction older than a few minutes on an OLTP system is worth a notification long before it turns into a timeout storm. The innodb_trx query above is a ready-made check.