Find MySQL InnoDB Lock Waits and the Blocking Query
Uses the sys schema to show waiting and blocking sessions with lock modes. Resolves who is holding up whom in a single query.
SELECT wait_started, wait_age,
locked_table, locked_index, locked_type,
waiting_pid, waiting_query,
blocking_pid, blocking_query, blocking_lock_mode
FROM sys.innodb_lock_waits
ORDER BY wait_age DESC;Verify in a staging environment prior to running in production. This is a read-only diagnostic query; it does not acquire exclusive locks.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 5.7 (sys şeması)).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Other MYSQL Diagnostic Scripts
View All (100)Find Long-Running Queries in MySQL
Lists active queries running longer than 60 seconds from the processlist. Fast diagnosis for slowdowns and lock pile-ups.
List the Largest Tables and Disk Usage in MySQL
Ranks tables by data, index and free space size. Core input for capacity planning and archiving decisions.
Detect Unused Indexes in MySQL
Uses sys.schema_unused_indexes to list indexes that were never used. Helps safely drop indexes that slow down writes.
Slowest MySQL Query Digests with Performance Schema
Groups similar queries to show total time, average time and no-index executions. Finds the costliest query patterns without enabling the slow log.