MySQL Lock Wait Timeout — Kill Blocker, Add Index

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. It lands mid-incident, usually during a sale, a batch job, or a migration — dozens of app threads stuck, all showing the same error, none of them actually slow. Your first instinct is to blame the query, add a bigger timeout, and move on. That instinct is wrong often enough to cost real money.

Error 1205 is a waiting-room problem, not a speed problem. Your transaction asked InnoDB for a row lock, another transaction held it, and yours waited the full innodb_lock_wait_timeout (50 seconds by default) before giving up. The query would have finished in milliseconds if the lock had been free.

This guide shows the full playbook: confirming it's a lock wait and not a deadlock, finding the exact blocking transaction with information_schema, killing it safely, fixing the missing index that usually caused the pile-up, and adding retry discipline so one stuck session never pages you again. Keep this page open during the incident — the two diagnostic queries in section two resolve most 1205s in under five minutes.

Error 1205 Is a Blocked Query, Not a Slow Query

Read the message literally: lock wait timeout exceeded means your transaction spent the whole timeout waiting for a lock, then gave up. The query itself might run in 3 milliseconds on an idle server — you'll never see it in the slow log, the CPU stays flat, and EXPLAIN looks innocent. That's the signature that separates 1205 from genuine slowness: system metrics look bored while app threads stack up.

Its close relative is error 1213, deadlock found. A deadlock is a cycle — A waits on B waits on A — which InnoDB detects in about a second and resolves by killing one side. A lock wait has no cycle, just a holder that won't let go, so InnoDB waits the full timeout. If you see 1213s, you reorder work or shorten transactions; if you see 1205s, you find the holder. Mixing up the two sends you optimizing a query plan when you should be hunting a session.

Start every 1205 incident by confirming the timeout value and InnoDB's own status output. SELECT @@GLOBAL.innodb_lock_wait_timeout shows the cap everyone waits under, and SHOW ENGINE INNODB STATUS names the current waiters and holders. Those two outputs frame the whole investigation: how long waiters suffer, and who is doing the holding.

-- The cap every waiter suffers under (default 50 seconds)
SELECT @@GLOBAL.innodb_lock_wait_timeout;
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- InnoDB's own view: TRANSACTIONS section names waiters + holders
SHOW ENGINE INNODB STATUS;

-- 1205 = waited and gave up; 1213 = deadlock, InnoDB killed one side
-- Flat CPU + stacked app threads + 1205s = blocked, not slow.

Find the Blocker With innodb_trx and innodb_lock_waits

Two performance_schema-adjacent tables in information_schema carry the whole story. innodb_trx lists every open InnoDB transaction: its id, state, start time, thread id, and current statement. Ordering by trx_started puts the likeliest blockers — the oldest transactions — at the top. A row whose trx_query is NULL but whose state isn't COMMITTING is idle-in-transaction: connected, holding locks, doing nothing.

innodb_lock_waits completes the picture by pairing each waiting transaction with the one blocking it. Join it to innodb_trx on the blocking id and you get the blocker's thread id and SQL in a single result set — no log archaeology, no guessing from timestamps. During an incident, run the pair of queries back to back: the age-ordered transaction list for context, the waits join for the precise edge.

Save both outputs into the incident ticket. Blocker thread ids get reused, so 'we killed 48213' is meaningless tomorrow without the query text and start time attached. The habit that separates fast teams here is screenshotting the waits join within the first five minutes, before anyone kills anything and destroys the evidence. During a real pile-up, run the waits join first — it names the single thread to act on while the age-ordered list loads context around it.

-- All open transactions, oldest first (blockers float to the top)
SELECT trx_id, trx_state, trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
  trx_mysql_thread_id, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- Exact waiter -> blocker edges with the blocker's thread + SQL
SELECT w.requesting_trx_id AS waiter,
  w.blocking_trx_id AS blocker,
  t.trx_mysql_thread_id AS blocker_thread,
  t.trx_query AS blocker_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx t ON t.trx_id = w.blocking_trx_id;

KILL the Blocker Without Making Things Worse

KILL in MySQL takes a thread id — the trx_mysql_thread_id from your blocker query — and rolls back that thread's open transaction. For an idle-in-transaction console holding locks with zero uncommitted changes, the rollback is instant and checkouts recover in seconds. For a thread mid-way through a 2-million-row batch, the rollback itself can take minutes and hold worse locks while it unwinds. Always check trx_rows_modified before you pull the trigger, and prefer killing during a lull if the blocker is a legitimate batch you can rerun.

Use KILL <thread_id> (statement kill) rather than KILL CONNECTION when the thread belongs to your app pool — severing pooled connections creates reconnect storms on top of the lock incident. After the kill, re-run the innodb_trx query to confirm the thread is gone; a KILLED flag that lingers means rollback is still unwinding, and patience beats a second kill.

Treat every kill as a follow-up ticket, not a resolution. The kill restores service; the ticket asks why a 26-minute idle transaction was possible and adds the guardrail — idle timeouts on admin tools, statement timeouts on reports, autocommit defaults — so you never kill the same class of blocker twice.

-- Check rollback cost BEFORE killing (big = slow unwind)
SELECT trx_mysql_thread_id, trx_rows_modified, trx_query
FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id = 48213;

-- End the blocker (rolls back its open transaction)
KILL 48213;

-- Confirm it's actually gone (lingering KILLED = rollback unwinding)
SELECT trx_id, trx_state, trx_mysql_thread_id
FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id = 48213;

The Missing Index That Turned One Row Into a Table Lock

Here's why 1205s recur without any idle villain. An UPDATE ... WHERE status = 'paid' with no index on status must examine every row, and InnoDB locks the rows it examines — so a one-row change briefly locks the whole table. Under light traffic nobody notices; under sale traffic every checkout queues behind every other checkout, and the 50-second cap starts firing across the fleet.

EXPLAIN is the confirmation: type ALL with rows in the tens of thousands on a write statement means a table-wide lock footprint. The fix is an index on the filter columns, which narrows both the scan and the lock set to matching rows. After CREATE INDEX, re-run EXPLAIN to confirm the ref or range access, then watch the lock-wait counters fall off a cliff.

Mind the online-DDL cost on the fix itself: building an index on a 100M-row table locks more than you'd like on older MySQL, so use ALGORITHM=INPLACE where supported or an online schema-change tool during a quiet window. And index foreign-key columns as a standing rule — cascading child-table checks without indexes are the other classic full-scan lock source that pages teams at 3 AM. After the index lands, confirm the win with SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_waits' — the counter should stop climbing as throughput recovers.

-- Prove the table-wide lock footprint first
EXPLAIN UPDATE orders SET status = 'paid' WHERE status = 'new' AND id = 881;

-- Narrow the scan AND the lock set to matching rows only
CREATE INDEX idx_orders_status ON orders (status);

-- Verify the plan changed from ALL to ref/range
EXPLAIN UPDATE orders SET status = 'paid' WHERE status = 'new' AND id = 881;

-- Online build on huge tables (avoid blocking writes while fixing)
-- ALTER TABLE orders ADD INDEX idx_orders_status (status), ALGORITHM=INPLACE, LOCK=NONE;

Retry Discipline: Teach the App to Survive 1205

Even healthy systems hit transient lock waits — two checkouts racing for the last item, a deploy restarting workers mid-transaction. The app should treat 1205 (and 1213) as retryable: catch the error code, back off with jitter, and replay the transaction a bounded number of times. Three attempts with 50–200 ms jittered delays absorb nearly all benign collisions without amplifying a real incident the way unbounded retries do. Cap it: infinite retry on a genuine 26-minute blocker is a self-inflicted DDoS.

Keep transactions short enough that retries stay cheap. Fetch everything you need, then open the transaction, write, and commit immediately — never hold one open across user input, HTTP calls, or queue publishes. Each statement inside the transaction should touch indexed rows so the lock window is milliseconds, and interactive paths can set a shorter session timeout so they fail fast instead of camping in the pool.

Make retries idempotent or don't retry at all. A checkout replay must not charge twice: gate the write on a unique idempotency key or a state precondition (WHERE status='new') so a replayed commit is a harmless no-op. Retry without idempotency converts a locking incident into a double-charge incident, which is strictly worse.

-- Fail fast on interactive paths instead of camping 50s in the pool
SET SESSION innodb_lock_wait_timeout = 10;

-- Short, idempotent transaction: precondition makes replays safe
START TRANSACTION;
UPDATE orders SET status = 'paid'
WHERE id = 881 AND status = 'new';
-- app checks affected-rows: 0 = already paid (safe replay), 1 = just paid
COMMIT;

-- Retryable errors the app should catch with capped backoff + jitter:
-- ERROR 1205 (lock wait timeout), ERROR 1213 (deadlock).

Prevention: Short Transactions, Alerts, and Reviews

The lasting fix is observability plus habits. Alert on transaction age, not query time: any transaction open longer than 60 seconds deserves a page, because the damage scales with age, not with what the SQL says. A five-line cron check against innodb_trx feeding your alerting catches idle consoles, forgotten shells, and runaway reports before the sale starts — not during it.

Review every write path for its lock footprint before it ships. EXPLAIN each UPDATE and DELETE in the migration PR, require indexes on filter and foreign-key columns, and flag transactions that span multiple statements or service calls. Most 1205-prone code is visible in review: a loop that updates rows one by one inside a single transaction, or a handler that calls a payment API between BEGIN and COMMIT.

Finally, give humans safe defaults. Admin consoles and notebooks should run with autocommit on and an idle-session timeout, reporting jobs get statement timeouts and read-only replicas, and deploy pipelines verify no transaction older than a minute exists before shifting traffic. Boring guardrails, zero 3 AM pages — that's the trade you're buying. Put the transaction-age check on the same dashboard as pool saturation: the two graphs spike together, and either one alone can mislead you.

# Cron every minute: page on any transaction open > 60s
#!/bin/bash
set -euo pipefail
COUNT=$(mysql -u monitor -h db-primary -N -e "
SELECT COUNT(*) FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;")
if [ "$COUNT" -gt 0 ]; then
  mysql -u monitor -h db-primary -e "
  SELECT trx_mysql_thread_id, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
  trx_query FROM information_schema.innodb_trx
  WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;"
  echo "PAGE: $COUNT long-running transactions"
  exit 1
fi
echo "OK: no long transactions"