ERROR 1205: Lock Wait Timeout Exceeded

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction means your transaction waited for a row lock, another transaction never released it, and InnoDB gave up waiting:

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

The advice in the message is misleading. Restarting your transaction rarely helps, because your transaction is the victim, not the cause. Something else has been holding a lock for at least 50 seconds, and until you find it the error will keep happening.

Why This Happens

InnoDB takes row-level locks on every row a transaction modifies and holds them until commit or rollback. When another transaction needs one of those rows, it waits — up to innodb_lock_wait_timeout, which defaults to 50 seconds. Past that, the waiter gets ERROR 1205.

Two details make this worse than it looks:

• By default only the timed-out statement is rolled back, not the transaction. Your transaction stays open and keeps every lock it already holds. An application that catches the error and carries on becomes the next blocker in the chain.
• The blocker is often doing nothing at all. A connection that ran an UPDATE and never committed holds its locks while sitting idle. This is the most common cause by a wide margin.

Fix 1: Find the Blocking Transaction

Do this before changing any setting. On MySQL 8:

SELECT
  r.trx_id            AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query         AS waiting_query,
  b.trx_id            AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_state         AS blocking_state,
  b.trx_started       AS blocking_started,
  b.trx_query         AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.innodb_trx b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;

On MySQL 5.7 and later the sys schema wraps the same joins more readably:

SELECT * FROM sys.innodb_lock_waits\G

The column that matters most is blocking_started. If it is minutes or hours old, you have found an abandoned transaction.

A blocking_query of NULL is the signature of the idle-transaction case: the transaction is open and holding locks but is not currently executing anything. Confirm with:

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

Then map the thread back to a real connection so you know which application to fix:

SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE id = <blocking_thread>;

A command of Sleep with a large time confirms it: the connection is idle inside an open transaction.

Fix 2: Clear the Blocker

This step destroys work. Killing a transaction rolls back everything it has done since it began, and the owning application receives an error. For an abandoned transaction that is exactly what you want. For a large in-flight batch, the rollback itself can take as long as the original work and will hold locks while it runs — so check trx_rows_locked and trx_query before you kill anything, and prefer asking the owning application to commit or disconnect.

KILL <blocking_thread>;

Use the thread id from trx_mysql_thread_id, not the transaction id. KILL ends the connection and rolls its transaction back; KILL QUERY stops only the running statement and leaves the transaction — and its locks — in place, which usually does not help here.

Killing the blocker is first aid, not a fix. If you do not address why the transaction was left open, it will recur.

Fix 3: Address the Cause, by Category

An application leaves transactions open. The usual culprits are code that opens a transaction and then waits on something slow — user input, an HTTP call, a queue — or an ORM that starts a transaction implicitly and only commits on request completion. Move every non-database operation outside the transaction and commit as soon as the writes are done.

Autocommit is off and nobody commits. A session with autocommit=0 opens a transaction on the first statement, including a plain SELECT on InnoDB. A developer running an interactive session can hold locks for hours without realising.

SELECT @@autocommit;

Connections are pooled and reused dirty. If a pooled connection is returned without a commit or rollback, the next borrower inherits the open transaction. Configure the pool to roll back on return and to close idle connections.

A long-running statement is the blocker. A batch UPDATE over millions of rows holds locks for its whole duration. Break it into bounded chunks that commit between batches:

-- Repeat until no rows are affected, committing between iterations
UPDATE orders SET status = 'archived'
WHERE status = 'closed' AND id BETWEEN ? AND ?;

A schema change is in the way. ALTER TABLE on a large table can block writes for a long time. Running one on a busy production table is a downtime event unless you have verified the algorithm is online for your MySQL version and workload — check ALGORITHM=INSTANT or INPLACE support first, or use a tool that copies the table in the background.

Foreign keys widen the blast radius. Inserting a child row takes a shared lock on the referenced parent row, so a transaction updating a parent blocks inserts on every child table pointing at it. If 1205 appears on a table nobody seems to be writing, check the parent.

Missing indexes turn narrow locks into wide ones. If an UPDATE ... WHERE cannot use an index, InnoDB examines and locks far more rows than it changes. Check the plan:

EXPLAIN UPDATE orders SET status = 'x' WHERE customer_ref = 'abc';

A full table scan here means the statement locks the table's rows as it goes. Adding the index shrinks both the lock footprint and the duration.

About Raising innodb_lock_wait_timeout

innodb_lock_wait_timeout is dynamic and settable per session, so raising it is easy — which is why it is the most common wrong answer:

SELECT @@innodb_lock_wait_timeout;   -- default 50

Raising it globally does not remove the blocker. It makes every waiting transaction wait longer while continuing to hold its own locks, so a single stuck transaction stalls more sessions for longer, and the pile-up can exhaust your connection limit before anything times out. The legitimate uses are narrow: a known batch job that must wait out a busy period, set for that session only.

SET SESSION innodb_lock_wait_timeout = 120;   -- this connection only

innodb_rollback_on_timeout=ON is worth considering as a safety net: it makes a timeout roll back the entire transaction rather than one statement, so a victim releases its locks instead of becoming the next blocker. It is a global, non-dynamic setting, so changing it requires a restart — plan that as a maintenance window, and check that your application handles a fully rolled-back transaction correctly first.

Verify the Fix

Watch for transactions that live too long, rather than waiting for the next error:

SELECT COUNT(*) AS long_running
FROM information_schema.innodb_trx
WHERE trx_started < NOW() - INTERVAL 60 SECOND;

On a healthy OLTP system this should be zero almost all the time. For live lock detail:

SHOW ENGINE INNODB STATUS\G

Read the TRANSACTIONS section — it lists each active transaction, its age, and what it is waiting on.

Prevent It Coming Back

• Keep transactions short. Open late, commit early, and never hold one across a network call or user interaction.
• Alert on transaction age, not just on errors. A query for trx_started older than 60 seconds catches leaks before users notice.
• Set an idle timeout on the connection pool so an abandoned connection cannot hold locks indefinitely.
• Index the columns in your WHERE clauses so updates lock the rows they change and not the ones they scan.
• Chunk large writes with commits between batches.
• Enable innodb_print_all_deadlocks so lock conflicts are recorded in the error log rather than only visible while they happen — see where MySQL logs are stored.

Next Steps

• Where are MySQL logs stored — for deadlock records and slow query output
• Fix "MySQL server has gone away" if connections drop rather than stall
• Fix ERROR 1045 Access denied if you cannot connect at all