Diagnosing MySQL ERROR 1205 Lock wait timeout: Distinguishing Lock Waits from Deadlocks

Hands-on Diagnosis of MySQL ERROR 1205 Lock wait timeout: How to Tell a Lock Wait from a Deadlock

If transactions suddenly stall in a production service and you see the following message, this post is exactly for you.

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

Just because the message ends with try restarting transaction does not mean you should keep retrying. The root cause remains, and the same incident will keep coming back. One more distinction: this is a completely different angle from the earlier post on PostgreSQL too many clients (connection-pool exhaustion — a shortage of connection resources). Here, connections are perfectly alive, but transactions have frozen waiting on each other's locks.

1205 and 1213 are entirely different problems

The first job is to put the two error strings side by side and tell them apart.

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The takeaway is this. 1205 is a one-way wait caused by someone holding a lock too long and never releasing it. 1213 is a two-way deadlock where two transactions grab each other's locks and form a cycle. If you hit 1205, track who is holding the lock too long. If you hit 1213, look at which two queries collided on lock order.

Reproduce it yourself (Session A / B)

If you only understand the concept in your head, you will get confused in production. Open two sessions and create it yourself.

-- 준비
CREATE TABLE acct (id INT PRIMARY KEY, balance INT);
INSERT INTO acct VALUES (1,100),(2,100);

(A) Lock wait → reproduce 1205

-- Session A
BEGIN;
UPDATE acct SET balance = balance - 10 WHERE id = 1;  -- id=1 락 보유, 커밋 안 함

-- Session B (A가 안 놔주면 50초 후 실패)
BEGIN;
UPDATE acct SET balance = balance + 10 WHERE id = 1;
-- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

(B) Deadlock → reproduce 1213

-- Session A
BEGIN;
UPDATE acct SET balance = balance - 10 WHERE id = 1;  -- id=1 잠금

-- Session B
BEGIN;
UPDATE acct SET balance = balance - 10 WHERE id = 2;  -- id=2 잠금

-- Session A: 이제 id=2를 원함 (대기)
UPDATE acct SET balance = balance + 10 WHERE id = 2;

-- Session B: 이제 id=1을 원함 → 순환 발생!
UPDATE acct SET balance = balance + 10 WHERE id = 1;
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

The only difference is order, yet the outcome splits into 1205 vs 1213. If both sessions always lock in the same order (always smallest id first), deadlocks go away. That is the core of the prevention section later.

5-minute diagnosis: a three-query kit for tracking blocking transactions

Starting with MySQL 8.0, the old INNODB_LOCKS was removed and performance_schema.data_locks / data_lock_waits became the standard. The join query below shows who is blocking whom in a single row.

-- ① 블로킹 ↔ 대기 트랜잭션을 한 번에
SELECT
  w.blocking_trx_id      AS blocking_trx,
  bt.trx_mysql_thread_id AS blocking_thread,
  bt.trx_query           AS blocking_query,
  w.requesting_trx_id    AS waiting_trx,
  wt.trx_mysql_thread_id AS waiting_thread,
  wt.trx_query           AS waiting_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx bt
  ON bt.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx wt
  ON wt.trx_id = w.requesting_trx_id;

-- ② 가장 오래된 active 트랜잭션 (장기 미커밋 범인 찾기)
SELECT trx_id, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS run_sec,
       trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;

-- ③ 어떤 락을 쥐고 있나
SELECT object_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks;

And the deadlock "autopsy report" is always left here.

SHOW ENGINE INNODB STATUS\G

Example of the LATEST DETECTED DEADLOCK block in the output:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-06-13 10:42:11 0x7f...
*** (1) TRANSACTION:
TRANSACTION 4211, ACTIVE 6 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136
UPDATE acct SET balance = balance + 10 WHERE id = 2
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS ... index PRIMARY of table `test`.`acct` ... id=1
*** (2) TRANSACTION:
TRANSACTION 4212, ACTIVE 4 sec
UPDATE acct SET balance = balance + 10 WHERE id = 1
*** WE ROLL BACK TRANSACTION (2)

Read HOLDS THE LOCK(S) (held locks) and WAITING FOR THIS LOCK (waited-for locks) on both transactions and the lock-order collision is right there. Note that SHOW ENGINE INNODB STATUS shows only the most recent incident, so on RDS/Aurora or production servers we recommend turning on innodb_print_all_deadlocks = ON so every deadlock is written to the error log.

Three cause patterns and immediate actions

In the field, 90% of 1205/1213 cases are one of the following three.

1. Long-running uncommitted transactions: The application delays COMMIT after BEGIN because of an external API call or similar. In query ② above, the transaction with an abnormally large run_sec is the culprit.
2. Gap locks / next-key locks expanding because of missing indexes: A WHERE on an unindexed column full-scans and takes wide-ranging locks.
3. Lock-order collisions in batch jobs: Two jobs update rows in opposite order → deadlock.

Number 2 in particular can be confirmed immediately with EXPLAIN.

-- 인덱스 없음: 풀스캔 → 많은 행 잠금
EXPLAIN SELECT * FROM acct WHERE balance = 100 FOR UPDATE;
-- type: ALL, rows: 전체  → 넥스트키락이 테이블 전체로 확대

ALTER TABLE acct ADD INDEX idx_balance (balance);

-- 인덱스 추가 후: type: ref, rows: 소수만 잠금
EXPLAIN SELECT * FROM acct WHERE balance = 100 FOR UPDATE;

Adding an index often shrinks locked rows from tens of thousands to a handful, and lock contention itself disappears.

Immediate action — choosing the KILL target: From query ① above, pick blocking_thread (= trx_mysql_thread_id) and kill it. The selection criterion is the transaction that has been holding the longest while blocking the most others.

KILL 8821;   -- blocking_thread 값

Timeout-tuning trade-offs:

-- 세션 단위 (권장: 특정 배치만)
SET innodb_lock_wait_timeout = 5;
-- 전역 (영향 범위 큼, 신중히)
SET GLOBAL innodb_lock_wait_timeout = 20;

A note from the field: The temptation to raise the timeout to put out the immediate fire is strong, but I prefer the opposite — shorten it and put retry logic in the application. Lengthening the timeout lets waiting threads accumulate for that long, and at some point the connection pool collapses along with them. "Fast fail + explicit retry" is far better for operational visibility than "slow fail."

For reference, innodb_deadlock_detect is ON by default so deadlocks are detected immediately, but in extremely high-concurrency environments the detection cost itself can become a bottleneck. In that case a strategy is to turn detection off and keep innodb_lock_wait_timeout short so they are handled as timeouts.

Recurrence-prevention checklist

• Keep transactions short: Never put external API calls or waiting for user input between BEGIN and COMMIT.
• Shrink lock range with indexes: Index WHERE/JOIN columns to stop gap-lock / next-key-lock expansion.
• Consistent access order: When updating multiple rows, always access in the same sort order (e.g. PK ascending) to eliminate circular waits at the source.
• Revisit isolation level: If gap locks are a burden, consider READ COMMITTED (but check replication and consistency impact).
• Always-on deadlock logging: Record every deadlock with innodb_print_all_deadlocks = ON.

FAQ

Q. ERROR 1205 fired — can't I just retry the transaction? A. Retry is only a stopgap. 1205 is a signal that someone is holding a lock too long, so you must find long-running uncommitted transactions in information_schema.innodb_trx and remove the root cause (long transactions, missing indexes) or it will recur.

Q. I can't see the INNODB_LOCKS table in MySQL 8.0. A. It was removed in 8.0. Use performance_schema.data_locks and data_lock_waits instead. They show held locks and wait relationships more accurately.

Q. How do I quickly tell 1205 from 1213 from logs alone? A. Distinguish by SQLSTATE. HY000 means lock wait (1205); 40001 means deadlock (1213). A deadlock immediately rolls back one side and leaves a record in LATEST DETECTED DEADLOCK of SHOW ENGINE INNODB STATUS, whereas a lock wait quietly fails only the waiting side after the timeout.