MySQL 'Lock wait timeout exceeded': find the blocker, not a retry loop

The checkout page hangs. Not for a second, for the better part of a minute, and then it throws. It happens to maybe one order in thirty, always around the same time of day, and it never reproduces on staging. Someone has already suggested restarting MySQL.

Every guide to this error ends the same way: wrap the transaction in a retry loop. That advice is not wrong, exactly. It is just the last step presented as the only step, and it converts a diagnosable problem into a permanent background noise that gets slowly worse until one afternoon the retries themselves are the load.

Error 1205 does not mean your database is busy. It means one specific transaction sat holding a row lock for longer than innodb_lock_wait_timeout while another one waited. That first transaction has an ID, a start time, a connection and usually a query. You can find it. Usually you can name the code that opened it.

There is also a detail about this error that catches people badly, and almost nothing written about it mentions it: by default the timeout rolls back only the failed statement, not the whole transaction. If your application catches the error and carries on, you can commit a half-finished transaction and never know.

SELECT * FROM sys.innodb_lock_waits\G

The exact error

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

First, make sure you are not looking at its cousin, which needs a different response entirely:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

If users are complaining about a page that hangs for the better part of a minute and then errors, that is 1205. Deadlocks fail fast.

Why this happens

InnoDB takes row-level locks on every row a write statement touches, and holds them until the transaction commits or rolls back. Not until the statement finishes. Until the transaction ends. That gap is where all of this lives.

When a second transaction needs a lock that the first one holds, it does not fail. It waits, quietly, for up to innodb_lock_wait_timeout seconds. The default is 50. Then it gives up with 1205.

So the question is never "why did the second transaction fail". It is "why did the first one hold a lock for 50 seconds". There are three realistic answers.

Cause 1: something slow happens between BEGIN and COMMIT

An external API call. A file upload to S3. A PDF render. A sleep that survived from debugging. Anything that takes seconds inside a transaction extends every lock that transaction holds for that whole duration. This is the most common cause I find in application code, and it is almost always accidental: an ORM opened the transaction earlier and wider than the developer realised.

Cause 2: an abandoned transaction on an idle connection

A connection that opened a transaction, hit an unhandled exception, and never committed or rolled back. Its locks are held until the connection is closed or killed. SHOW PROCESSLIST will show it as Sleep, which makes it look harmless, while information_schema.innodb_trx shows it as RUNNING with a trx_started from twenty minutes ago. Trusting SHOW PROCESSLIST alone here is how people conclude "nothing is running" and move on.

Cause 3: a write locking far more rows than it needs to

This one is invisible in the query text. Consider:

UPDATE invoices SET status = 'paid' WHERE customer_ref = 'ACME-2024';

If customer_ref has no index, InnoDB scans the table, and under the default REPEATABLE READ isolation level it locks the rows it examines rather than only the ones it changes, plus gaps between index records. A statement that logically affects three rows can lock a large part of the table. Add the index and the same statement locks three rows.

This is the fix that actually removes contention rather than rescheduling it, and it is the one most often left out of the "just retry" answers. If you run ERPNext or any Frappe app, the same reasoning applies to the big transactional tables, which I went through in indexing tabGL Entry and Stock Ledger Entry.

Diagnosis, in order

Step 1: See who is blocking whom

SELECT * FROM sys.innodb_lock_waits\G

This view exists in MySQL 5.7 and later and in MariaDB 10.6 and later. Expected output for a live block:

          wait_started: 2026-07-24 15:31:02
              wait_age: 00:00:19
         locked_table: `shop`.`invoices`
    waiting_trx_id: 4213908
        waiting_pid: 4471
      waiting_query: UPDATE invoices SET status = 'paid' WHERE id = 90412
   blocking_trx_id: 4213877
       blocking_pid: 4402
     blocking_query: NULL
sql_kill_blocking_query: KILL QUERY 4402

Read blocking_query: NULL carefully. It does not mean there is no blocker. It means the blocking connection is not currently executing anything: it holds locks from an earlier statement and is sitting idle inside an open transaction. That is cause 2, diagnosed in one line.

Step 2: Find out how old the blocking transaction is

SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds,
       trx_mysql_thread_id, trx_rows_locked, trx_rows_modified,
       LEFT(trx_query, 80) AS query
FROM information_schema.innodb_trx
ORDER BY trx_started;

Two columns decide your next move. age_seconds in the hundreds means an abandoned or over-long transaction. trx_rows_locked far larger than trx_rows_modified means a statement is locking rows it is not changing, which is the missing-index signature.

Step 3: Get the full picture when the views are not enough

SHOW ENGINE INNODB STATUS\G

Go to the TRANSACTIONS section. Look for TRX HAS BEEN WAITING lines and the RECORD LOCKS entries under each transaction, which name the index the lock is on. That index name is your evidence: a lock on PRIMARY for an UPDATE you thought was narrow tells you the optimiser is scanning.

On MySQL 8 you can go further with performance_schema.data_locks and performance_schema.data_lock_waits, which show individual lock records with their modes. MariaDB does not have those tables; use the sys view and SHOW ENGINE INNODB STATUS instead.

Step 4: Unblock production, then fix the cause

KILL 4402;

KILL QUERY id stops the running statement but leaves the transaction and its locks in place, which is exactly wrong when the blocker is idle. KILL id terminates the connection, which forces a rollback and releases the locks. That is what you want at 2am. It is also a tourniquet, not a treatment: if you do not fix the code, you will be doing it again next week.

The fixes that actually hold

1. Move slow work outside the transaction. Open the transaction as late as possible and commit as early as possible. Call the payment gateway, render the PDF, hit the third-party API, and only then start the transaction that records the result. This single change removes most 1205s in application code.
2. Index the columns in your UPDATE and DELETE WHERE clauses. Verify with EXPLAIN that the write uses an index rather than scanning. A write with type: ALL in the plan is locking far more than you think.
3. Lock rows in a consistent order. If two code paths touch the same set of rows, both should touch them in the same order, for example ordered by primary key. Inconsistent ordering produces deadlocks and lengthens waits.
4. Keep transactions short and specific. A transaction that updates one order and writes one ledger row is fine. A transaction that loops over 5,000 rows in application code, doing one round trip each, holds locks for the entire loop. Batch it, or commit in chunks.
5. Add retry with backoff last. Once the above are true, a small number of retries handles genuine concurrent contention. Retrying without them is how you paper over cause 2 forever.

The rollback detail that bites

SHOW VARIABLES LIKE 'innodb_rollback_on_timeout';

Default is OFF. With it off, a lock wait timeout rolls back only the statement that timed out. Your transaction is still open, still holds every lock it already took, and will happily commit whatever it did before the failure if your error handler swallows the exception and continues.

So your application code must explicitly roll back on 1205. Most ORMs do this correctly inside a transaction context manager, but hand-written code that catches the exception and logs it does not. Setting innodb_rollback_on_timeout=ON makes the server roll back the whole transaction instead, which is safer, but it is not dynamic: it requires a restart, and it changes behaviour for every application on that server. Fix the error handling first.

Verification

1. Confirm the blocker is gone:

   SELECT COUNT(*) FROM sys.innodb_lock_waits;

   Expected output: 0. Run it a few times over a minute rather than once.
2. Confirm no transaction is older than a few seconds:

   SELECT MAX(TIMESTAMPDIFF(SECOND, trx_started, NOW())) AS oldest
   FROM information_schema.innodb_trx;

   On a healthy OLTP system this should be single digits. A steady value in the hundreds means you still have an abandoned transaction, even if nothing is currently waiting on it.
3. Confirm the index is being used:

   EXPLAIN UPDATE invoices SET status = 'paid' WHERE customer_ref = 'ACME-2024';

   You want a named index in the key column and a small rows estimate, not type: ALL.
4. Watch it under real load. The honest test is the daily peak. If the slow query log is on, the blocking statement is often in it already; the slow query log playbook covers reading it without drowning.

What people get wrong

"Raise innodb_lock_wait_timeout." This is the most popular answer and it is close to the worst. Setting it to 120 or 300 does not remove contention, it makes each blocked request hold a connection and a web worker for longer. On a busy system you go from occasional errors to exhausting the connection pool, which takes the whole application down rather than one request. If anything, in a system with fast queries, lowering the timeout surfaces the problem sooner and fails faster.

"Just wrap it in a retry." Retrying is correct for deadlocks, where the database has already rolled back a victim and the conflict is genuinely transient. For 1205 caused by an abandoned transaction, every retry waits another 50 seconds and then fails, because the blocker is not going anywhere.

"Restart MySQL." It works, in the sense that killing every connection releases every lock. It also drops the buffer pool and every other connection, and it destroys the evidence you needed. Kill the one connection instead.

"Switch to READ UNCOMMITTED." Isolation level affects reads, not the write locks causing this. You will get dirty reads and keep the timeouts.

When it is still broken

1. Look for a transaction outside the application. A DBA's forgotten BEGIN in a psql-style session, a migration tool holding a lock, a backup running with locking enabled, or a stuck cron job. information_schema.innodb_trx shows all of them regardless of who opened them.
2. Check for implicit transactions from disabled autocommit. A client with autocommit=0 opens a transaction on the first statement, including a plain SELECT, and keeps it open. Confirm with SELECT @@autocommit; on the offending connection.
3. Look at the DDL. An ALTER TABLE on a large table, even an online one, takes metadata locks that queue behind and in front of everything else. That produces waiting behaviour that looks like row lock contention but is not.
4. Turn on innodb_print_all_deadlocks temporarily if you also see 1213s. It writes every deadlock to the error log, which turns "it happened again last night" into an artifact you can read in the morning.

The pattern behind all of this is worth stating plainly: a lock wait timeout is a report about a transaction you were not looking at. The failing query is a witness, not the suspect. Find the transaction that was holding the lock, work out why it was open so long, and the error stops happening rather than being caught more gracefully.