Advertisement
database • A Lock wait timeout exceeded error happens when one transaction wants a row locked by another transaction, but the first transaction gives up after waiting too long. This guide shows how to diagnose the blocker, fix the immediate problem, and apply practical fixes so the error stops happening

Fixing “Lock wait timeout exceeded” (MySQL / InnoDB) — Step-by-Step Guide for Beginners

5 Min Read Verified Content

Hello — if you’ve ever seen:

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

— you’re in the right place. This is a common InnoDB concurrency problem. It doesn’t mean MySQL is broken; it means two (or more) transactions are colliding. I’ll walk you through diagnosis, immediate fixes, and long-term prevention — step-by-step and beginner-friendly.


✅ Quick checklist (what you’ll do in this guide)


  1. Understand what the error means

  2. Inspect current locks and transactions

  3. Identify the blocking transaction(s)

  4. Kill or resolve the blocker (immediate relief)

  5. Apply fixes: short transactions, proper indexing, retry logic, isolation tuning, and safer locking patterns

  6. Test and monitor


1) What does the error mean (simple)

  • InnoDB uses row-level locks for updates.

  • Transaction A requests a lock on a row that Transaction B already holds.

  • Transaction A waits. If the wait exceeds innodb_lock_wait_timeout seconds (default ~50s), MySQL aborts A with that error.

  • MySQL chooses not to block forever — it fails early so your application can react.


2) Immediate diagnosis — see who's waiting and who's blocking

Open a MySQL shell (or use your favorite admin tool) and run these queries.

a) Show running transactions (MySQL 5.7+/8.0)

SELECT trx_id, trx_state, trx_started, trx_wait_started, trx_mysql_thread_id,
       trx_query
FROM information_schema.INNODB_TRX\G

Look for transactions with trx_state = 'LOCK WAIT' or with a large trx_wait_started.

b) See lock relationships (MySQL 5.7+)

-- rows waiting to acquire locks and which transaction holds the lock
SELECT r.trx_id waiting_trx_id,
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id,
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

This is the most useful query — it directly tells you which transaction is waiting and which one is blocking it.

c) Old method (SHOW ENGINE INNODB STATUS)

SHOW ENGINE INNODB STATUS\G

Search the output for LATEST DETECTED DEADLOCK and the list of transactions and locks. Useful when INFORMATION_SCHEMA views are unavailable.



3) Immediate remediation (how to free things now)

Important: Only kill/terminate the blocking transaction if it’s safe — killing the wrong transaction may abort an important job.

  1. Identify the blocking thread id from the queries above (look at blocking_thread or trx_mysql_thread_id).

  2. Kill it (MySQL will rollback that transaction and release locks):

KILL <thread_id>;

If the blocker is a client connection (app server), you may need to stop the offending request on the app side or restart the app worker.

  1. Retry the failed operation after a short wait.



4) Why blockers happen — common root causes (short)

  • Long-running transactions (idle in transaction, long processing inside txn)

  • Transactions that lock many rows (e.g., large UPDATE or DELETE)

  • Missing indexes so UPDATE/DELETE lock many rows/scan ranges

  • Application pattern: two processes updating rows in opposite order (causes lock cycles)

  • Use of SELECT ... FOR UPDATE combined with business logic delays

  • Too many concurrent writers on hot rows



5) Long-term fixes (detailed, with examples)

A — Keep transactions short and commit quickly

Bad:

START TRANSACTION;
-- do long processing in the app (sleep, web request)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Good:

  • Move as much work as possible outside transaction.

  • Begin transaction immediately before DB changes; commit immediately after.

B — Add proper indexes so row locks are targeted

If you're doing UPDATE orders SET ... WHERE user_id = X, ensure user_id is indexed. Without index, InnoDB scans and locks many rows.

CREATE INDEX idx_orders_user ON orders(user_id);


C — Make updates deterministic and consistent order

If multiple places update multiple rows, always update rows in the same order (e.g., by primary key). This avoids cycles.


Bad (could deadlock / lock wait):

  • Worker1: UPDATE A then B

  • Worker2: UPDATE B then A


Good:

  • Always UPDATE min(id) first, then max(id), or always update by ascending id.


D — Use narrower transactions (row level, not table level)

Avoid UPDATE without WHERE or with very wide WHERE that affects many rows. Batch large updates:

-- bad
UPDATE logs SET status='archived' WHERE created_at < '2024-01-01';

-- better: small batches
UPDATE logs SET status='archived'
WHERE created_at < '2024-01-01'
ORDER BY id
LIMIT 1000;

Repeat until done.


E — Use SELECT ... FOR UPDATE / SKIP LOCKED / NOWAIT carefully (MySQL 8.0+)

  • FOR UPDATE locks rows; use when you need it.

  • To avoid waiting you can use SKIP LOCKED to skip locked rows (useful for job queues).

  • NOWAIT fails immediately if locked.

-- take next unlocked job (skip locked)
SELECT id FROM queue
WHERE status='pending'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

This avoids long waits by skipping locked rows.


F — Use retry logic in application

When Lock wait timeout exceeded occurs, catch the exception and retry the transaction a few times with small backoff.

Pseudocode:

for tries in 1..3:
  begin transaction
  try:
     perform updates
     commit
     break
  except LockWaitTimeout:
     rollback
     sleep(random small backoff)

This is a common, robust approach.


G — Tune innodb_lock_wait_timeout (only as last resort)

You can raise the timeout (seconds) to allow longer waits:

SET GLOBAL innodb_lock_wait_timeout = 120; -- seconds

But raising timeout is not a real fix — it masks the underlying problem and can increase contention.

H — Change isolation level if appropriate

Default InnoDB isolation is REPEATABLE READ. Switching to READ COMMITTED can reduce gap locks in some patterns:

SET GLOBAL transaction_isolation = 'READ-COMMITTED';

Test carefully — isolation changes affect correctness.


I — Reduce lock footprint: use optimistic concurrency where appropriate

Instead of locking rows, use versioning (a version column) and retry if the row changed (compare-and-swap semantics).

Example pattern:

  • UPDATE table SET col = new WHERE id = ? AND version = old_version;

  • If rows affected = 0, detect conflict and retry.



6) Example workflow — diagnose + fix (real commands)

  1. See waiting/blocking pairs:

SELECT r.trx_id waiting_trx_id, r.trx_query waiting_query,
       b.trx_id blocking_trx_id, b.trx_query blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

  1. Kill blocking thread if safe:

KILL <blocking_thread_id>;

  1. Review application code that opened the blocking transaction. Find where a transaction is left open or does long processing. Fix code to commit faster.

  2. Add required index if EXPLAIN shows full scan:

EXPLAIN UPDATE orders SET status='x' WHERE user_id = 123;
-- if no index -> add:
CREATE INDEX idx_orders_user_id ON orders(user_id);

  1. Replace blocking patterns with SKIP LOCKED where applicable (for worker queues):

BEGIN;
SELECT id FROM jobs WHERE state='ready' LIMIT 1 FOR UPDATE SKIP LOCKED;
-- do work
UPDATE jobs SET state='processing' WHERE id = <id>;
COMMIT;

  1. Add retry logic in app.



7) Monitoring & prevention tips


  • Regularly check INNODB_LOCK_WAITS and INNODB_TRX for long waits.

  • Use slow query/lock monitoring tools (Percona Monitoring, PMM, or custom cron).

  • Log and alert on Lock wait timeout exceptions in your application.

  • Keep transactions < 1 second where feasible.

  • Use connection pooling sensibly, and avoid keeping connections idle in a transaction.



8) Quick FAQ (beginner)


Q — Should I always kill the blocker?
A — No. If the blocker is doing essential work, killing may cause user impact. Prefer to identify why blocker is long and fix code. Killing is a short-term relief.

Q — Is increasing innodb_lock_wait_timeout safe?
A — It’s a temporary bandaid. It can let requests wait longer, which may block more clients and worsen contention.

Q — Can SELECT cause locks?
A — SELECT normally doesn’t lock. But SELECT ... FOR UPDATE or LOCK IN SHARE MODE will take locks. Also some statements (non-indexed SELECT+UPDATE patterns) can cause implicit locking.


9) Checklist to stop seeing the error


  • Monitor INNODB_LOCK_WAITS and INNODB_TRX for repeats


10) Conclusion — what to do next


Start by diagnosing a current incident with the INNODB_LOCK_WAITS query above. If you must, kill the blocking thread to restore service, but then fix the root cause: short transactions, indexes, or logic changes. Masking with longer timeouts or more hardware rarely solves the root cause.

Advertisement
Back to Database