MySQL "Lock wait timeout exceeded" — Who Holds the Lock and Who to KILL?

Finding the blocking transaction using performance_schema, safe KILL decision matrices, and permanent locking reduction.

Reviewed by:Senior Database Architect & SRE (dataiotech Labs)

Tested Versions: MySQL 8.0 / 8.4 LTS

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptom

plaintext

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

Unlike a deadlock, there is no cycle. A transaction simply waited longer than innodb_lock_wait_timeout (default 50s).

▸Find the Blocker

sql

SELECT w.requesting_engine_transaction_id, w.blocking_engine_transaction_id,
       b.trx_mysql_thread_id, TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS duration_sec,
       b.trx_query AS blocker_query, r.trx_query AS waiting_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

•Validated versions: MySQL 8.0 / 8.4 LTS

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (20)

MYSQL4 min

MySQL ERROR 1045 Access denied: root Password Reset Guide

Four causes of MySQL 1045, meaning of using password: YES/NO, and safe credential recovery via --init-file.

MYSQL5 min

MySQL Deadlock ERROR 1213: Graph Analysis and Permanent Prevention

Root causes of MySQL 1213 deadlock, parsing LATEST DETECTED DEADLOCK output, gap lock concurrency issues, and resilient application retry design.

MYSQL5 min

MySQL 1452 Foreign Key Constraint Fails: Cause and Fix

Distinction between 1452 and 1451, charset and data type mismatch, finding orphan rows, and safe bulk data loading procedures.