Lock Waits: Finding What Is Blocking and Why
Lock waits are one of those problems that often look like something else. The application slows down. Queries start timing out. Connection counts climb. And somewhere in the middle of all of it, one transaction is sitting on a lock that everything else is queued behind.
InnoDB tracks this. The lock wait information lives in information_schema and tells you exactly which transaction is waiting, which transaction is blocking it, what they are both doing, and how long the blocker has been running.
The second query surfaces all long-running transactions — not just ones currently causing waits. A transaction open for several minutes that is not doing anything is still holding its locks.
Loading…
On MySQL 8.0+,
innodb_lock_waitsmay be underperformance_schemainstead. Use:performance_schema.data_lock_waitsandperformance_schema.data_locks
Reading the Results
Lock Waits
| Column | What to Check |
|---|---|
waiting_thread | The thread being blocked — this is the query timing out |
waiting_query | What it is trying to do |
blocking_thread | The thread holding the lock |
blocking_query | What the blocker was doing when it took the lock |
blocking_started | When the blocker’s transaction began |
blocking_seconds | How long the blocking transaction has been open |
Open Transactions
| Column | What to Check |
|---|---|
open_seconds | How long this transaction has been open |
rows_locked | How many rows it currently holds locks on |
rows_modified | Changes made so far — indicates work that would be lost on rollback |
trx_state | RUNNING, LOCK WAIT, or ROLLING BACK |
What to Watch For
| Signal | What It Means |
|---|---|
| Lock wait chain (multiple waiters) | One blocker is causing cascading waits across several threads |
Blocker with trx_query = NULL | Transaction is open but idle — application forgot to commit |
High rows_locked with low rows_modified | A SELECT ... FOR UPDATE holding many locks without doing work |
| Blocker running for many minutes | Long-running transaction — consider killing it with KILL [thread_id] |
| Same application user always blocking | Application logic issue — transactions held open too long |
Gareth Winterman