Row-lock contention is the incident that never shows up on a CPU graph. The database looks calm, disks are quiet, and yet application latency is climbing because dozens of sessions are parked behind one transaction that holds the rows they want. When someone finally runs SHOW PROCESSLIST, half the connections say "updating" and nobody can tell which session is the actual problem.
MySQL 8.0 changed the tooling for this. The old information_schema tables, innodb_locks and innodb_lock_waits, are gone. Their replacements, performance_schema.data_locks and performance_schema.data_lock_waits, are better in almost every way: they show all held locks rather than only contended ones, they cost less to query, and they expose enough detail to reconstruct exactly what InnoDB is doing. These are my field notes on using them under pressure.
What changed in 8.0 and why it matters
Before 8.0, you could only see locks that were already in conflict. The new data_locks table shows every lock currently held or requested by every transaction, whether or not anyone is waiting. That sounds like trivia until you are debugging a lock you cannot explain: now you can open two sessions, run the suspect statement in one, and read the exact locks it took from the other. It is the single best way I know to learn InnoDB locking behavior on your own schema instead of trusting folklore.
A quick inspection looks like this:
SELECT engine_transaction_id,
object_schema,
object_name,
index_name,
lock_type,
lock_mode,
lock_status,
lock_data
FROM performance_schema.data_locks
WHERE object_schema = 'app'
ORDER BY engine_transaction_id, lock_type;Two columns carry most of the meaning. The lock_type column is either TABLE for intention locks or RECORD for row-level locks. The lock_mode column encodes the real story: X,REC_NOT_GAP is an exclusive lock on a single record, X by itself is a next-key lock covering a record and the gap before it, X,GAP locks only a gap, and S variants are the shared equivalents. The lock_data column shows the actual index key values being locked, including the pseudo-record "supremum" when a range lock runs off the end of an index. If you regularly see GAP and next-key modes where you expected plain record locks, that is a transaction isolation story, which I dug into in MySQL gap locks vs PostgreSQL SSI.
data_lock_waits pairs every waiter with its blocker
When contention is live, performance_schema.data_lock_waits gives you the edge list of the wait graph: one row per waiting lock request, with the transaction and thread ids of both the requester and the holder. Joining it back to data_locks and information_schema.innodb_trx turns those ids into something actionable:
SELECT r.trx_mysql_thread_id AS waiting_pid,
r.trx_query AS waiting_query,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds,
b.trx_mysql_thread_id AS blocking_pid,
b.trx_query AS blocking_query,
b.trx_state AS blocking_trx_state,
TIMESTAMPDIFF(SECOND, b.trx_started, NOW()) AS blocking_trx_age
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r
ON r.trx_id = w.requesting_engine_transaction_id
JOIN information_schema.innodb_trx b
ON b.trx_id = w.blocking_engine_transaction_id
ORDER BY wait_seconds DESC;Read blocking_query with suspicion. It shows what the blocking transaction is running right now, which is frequently NULL because the blocker already finished its statements and is idle inside an open transaction. A NULL there does not mean "no problem"; it means the lock holder is sleeping on its locks, and blocking_trx_age tells you for how long. That pattern, active waiters behind an idle holder, is the signature of a connection leak or an application path that does slow work between DML and COMMIT.
sys.innodb_lock_waits is the first-response view
During an incident I usually start one level higher, with the sys schema view that packages this join and adds conveniences:
SELECT wait_age_secs,
locked_table_name,
locked_index,
locked_type,
waiting_pid,
waiting_query,
blocking_pid,
blocking_query,
sql_kill_blocking_connection
FROM sys.innodb_lock_waits
ORDER BY wait_age_secs DESC;The sql_kill_blocking_connection column contains a ready-to-run KILL statement for the blocker. I treat it as a suggestion, not a reflex. Killing a transaction rolls it back, and a transaction that has modified millions of rows can take longer to roll back than it took to run, holding its locks the whole time. Check trx_rows_modified in information_schema.innodb_trx before you kill anything large.
Build the blocker tree
Pairwise views mislead you when chains form. If session A blocks B, and B blocks C, D, and E, the pair view shows B as a blocker, and an on-call engineer might kill B, which fixes nothing because A still holds the root lock. What you want is the tree, with every waiter attributed to its root blocker. MySQL 8.0 has recursive CTEs, so you can compute it directly:
WITH RECURSIVE chain AS (
SELECT waiting_pid, blocking_pid, 1 AS depth
FROM sys.innodb_lock_waits
UNION ALL
SELECT c.waiting_pid, w.blocking_pid, c.depth + 1
FROM chain c
JOIN sys.innodb_lock_waits w
ON w.waiting_pid = c.blocking_pid
)
SELECT c.blocking_pid AS root_blocker_pid,
COUNT(DISTINCT c.waiting_pid) AS victims,
MAX(c.depth) AS longest_chain
FROM chain c
WHERE NOT EXISTS (
SELECT 1
FROM sys.innodb_lock_waits w
WHERE w.waiting_pid = c.blocking_pid
)
GROUP BY c.blocking_pid
ORDER BY victims DESC;The recursive part walks each wait edge upward until it reaches a session that is not itself waiting, which is by definition a root of the tree. The output ranks root blockers by how many sessions they are ultimately holding up. In a real pileup this query collapses forty confusing processlist rows into one line: pid 8123 is the root blocker for 39 victims. That is the session to investigate and, if necessary, kill. One caveat: if the graph contains an actual cycle, InnoDB resolves it as a deadlock on its own, a different failure mode with its own workflow that I compared across engines in deadlock diagnosis in MySQL vs PostgreSQL.
From evidence to fix
The blocker tree tells you who; the fix depends on why. A few patterns cover most cases I meet. Idle-in-transaction holders point at application code or pool settings, and the fix is committing promptly or closing the session. Hot single rows, such as a counter or account balance updated by every request, need a schema or queueing change because no amount of database tuning makes one row concurrent. Large UPDATE or DELETE statements that lock wide ranges usually need batching and an index that narrows the scan, since InnoDB locks every row the statement examines, not just the rows it changes. And waits on the supremum pseudo-record or GAP modes mean range locking under REPEATABLE READ is amplifying contention beyond the rows you think you are touching.
Also set expectations with innodb_lock_wait_timeout, which defaults to 50 seconds. Applications that would rather fail fast and retry should lower it per session; batch jobs that must win eventually can raise it deliberately.
One more distinction saves confusion during triage: data_locks covers InnoDB row and table locks only. If sessions are stuck in "Waiting for table metadata lock", that is the MDL subsystem, visible in performance_schema.metadata_locks rather than here, and it has its own diagnosis path. When a pileup mixes both, resolve the metadata lock first, because a queued DDL statement blocks every new query on the table regardless of what the row locks underneath are doing.
Watching lock waits continuously, and where MonPG fits
All of these queries share a weakness: they only show the present. Lock pileups build and clear in seconds, and the postmortem question is always "how long had that transaction been holding locks before the alert?" Answering it requires sampling data_lock_waits and innodb_trx on an interval and keeping history, which is monitoring work rather than incident work. A solid MySQL monitoring setup treats lock-wait counts, oldest-transaction age, and root-blocker identity as first-class time series.
To be clear about where MonPG stands: MonPG is a PostgreSQL monitoring platform today, and MySQL support has not shipped yet. It is being built now, and this lock-wait workflow, including sampled blocker trees and lock-wait history, is part of the design target; the MySQL monitoring (coming soon) page tracks the rollout. If PostgreSQL is also part of your fleet, MonPG already does the equivalent job there, from lock chains to long-transaction alerts, and you can start there while MySQL support lands.