Diagnosing Lock Contention: Finding Blocking Sessions

1. What sets lock contention apart from slow queries

Lock contention occurs when multiple transactions try to access the same rows or tables at the same time and end up blocking each other. Unlike a plainly slow query, where the database is actually doing computational work, a query affected by lock contention sits idle waiting for another transaction to release its lock. The symptom looks similar, but the cause and therefore the fix are fundamentally different.

The decisive diagnostic difference: with lock contention, an explain plan usually shows an efficient access path, meaning the query would be fast in isolation. Only by looking at concurrently running sessions does it become apparent that another transaction already holds a lock on the same row or table. Anyone who wants to diagnose lock contention must therefore shift away from looking at the single query and toward looking at the entire session landscape at the time of the incident.

In practice, lock contention often shows up as a sudden spike in response time combined with low CPU and I/O utilization. The database is not overloaded, it is waiting. Exactly this pattern, high latency at low resource usage, is the most reliable first clue that locks rather than compute power are the actual problem.

2. Finding blocking sessions in MySQL

MySQL with the InnoDB engine gives direct insight into current locks and their relationships through performance_schema. The data_lock_waits table shows exactly which transaction is waiting on which other one, including the respective thread and transaction IDs. Combined with data_locks, this reveals the exact locked resource, whether a single row or an entire table.

The older, but still useful alternative is SHOW ENGINE INNODB STATUS, whose TRANSACTIONS section lists running transactions along with their held and requested locks. For a quick live diagnosis of lock contention, this command is often the most pragmatic first step, because it is immediately available without any additional configuration.

-- MySQL: who is blocking whom? (performance_schema, MySQL 8.0+)
SELECT
    waiting_pid.thread_id      AS waiting_thread,
    waiting_pid.processlist_id AS waiting_connection,
    blocking_pid.thread_id     AS blocking_thread,
    blocking_pid.processlist_id AS blocking_connection,
    w.blocking_engine_transaction_id AS blocking_trx_id,
    w.requesting_engine_transaction_id AS waiting_trx_id
FROM performance_schema.data_lock_waits w
JOIN performance_schema.threads waiting_pid
  ON w.requesting_thread_id = waiting_pid.thread_id
JOIN performance_schema.threads blocking_pid
  ON w.blocking_thread_id = blocking_pid.thread_id;

-- Show the concrete locked resource
SELECT object_schema, object_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks
WHERE engine_transaction_id IN (
    SELECT blocking_engine_transaction_id FROM performance_schema.data_lock_waits
);

3. pg_locks and pg_stat_activity in PostgreSQL

PostgreSQL solves the same diagnostic task through the system view pg_locks combined with pg_stat_activity. pg_locks lists all currently held and requested locks in the system, while pg_stat_activity provides the associated session details such as query text, user and connection time. The self join over pg_locks shows which waiting lock is blocked by which held lock, a pattern every experienced PostgreSQL administrator knows for diagnosing lock contention.

A particularly useful shortcut is the pg_blocking_pids() function, which since PostgreSQL 9.6 directly returns a list of blocking process IDs for a given process ID, without having to write the manual self join. This makes it possible to determine within seconds whether a hanging session is actually blocked by lock contention or whether a different cause is at play.

-- PostgreSQL: find blocking sessions with full context
SELECT
    blocked.pid       AS blocked_pid,
    blocked.query     AS blocked_query,
    blocking.pid      AS blocking_pid,
    blocking.query    AS blocking_query,
    now() - blocked.query_start AS waiting_since
FROM pg_stat_activity blocked
JOIN pg_locks bl
  ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks kl
  ON kl.locktype = bl.locktype
 AND kl.database IS NOT DISTINCT FROM bl.database
 AND kl.relation IS NOT DISTINCT FROM bl.relation
 AND kl.page IS NOT DISTINCT FROM bl.page
 AND kl.tuple IS NOT DISTINCT FROM bl.tuple
 AND kl.pid != bl.pid
 AND kl.granted
JOIN pg_stat_activity blocking
  ON blocking.pid = kl.pid
ORDER BY waiting_since DESC;

-- More compact with pg_blocking_pids() (PostgreSQL 9.6+)
SELECT pid, query, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

4. Row level locks vs. table level locks

Both InnoDB and PostgreSQL prefer fine grained row level locks over table level locks, because they allow concurrent access to different rows of the same table. Still, certain operations escalate to broader locks: an ALTER TABLE without online DDL support, a missing index on a foreign key column, or a full table scan inside a transaction using SELECT FOR UPDATE can lock far more rows than the application logic actually needs.

A frequently overlooked case with InnoDB: gap locks and next key locks lock not only existing rows but also gaps between index values, to prevent phantom reads under the Repeatable Read isolation level. This means a seemingly harmless WHERE clause with a range condition can generate significantly more lock contention than the developer expected when writing the query. Not knowing this leads to hours spent diagnosing the wrong row as the cause.

5. Measuring wait times and spotting trends

Single point in time snapshots of pg_locks or data_lock_waits only show the current state, not the development over time. To diagnose lock contention systematically, wait time needs to be captured continuously, for example through periodic sampling every few seconds with storage in a history table. This builds a picture of whether lock wait times systematically increase at certain times of day, for example during a nightly batch job competing with live traffic for the same rows.

PostgreSQL offers log_lock_waits as a built in alternative to manual sampling: once enabled, the server automatically logs every wait that exceeds deadlock_timeout directly to the server log. This provides a passively collected but very reliable history of lock contention incidents, without needing an external monitoring tool.

-- postgresql.conf: log wait times automatically
log_lock_waits = on
deadlock_timeout = '1s'

-- Periodic sampling into a history table (e.g. via a cron job every 5s)
INSERT INTO lock_wait_history (sampled_at, blocked_pid, blocking_pid, wait_seconds)
SELECT
    now(),
    blocked.pid,
    blocking.pid,
    EXTRACT(EPOCH FROM (now() - blocked.query_start))
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

6. Long transactions as the most common cause

By far the most common cause of lock contention in practice is long open transactions. A transaction that locks a row early on and then waits on an external API call, a user interaction, or another unrelated statement, holds the lock far longer than the actual data change would require. Every other transaction that wants to touch the same row must wait during that time.

The remedy is conceptually simple, but requires discipline to implement: keep transactions as short as possible, execute all external calls (HTTP requests, filesystem operations, queue publishes) outside the transaction, and request locks as late as possible in the transaction flow. Anyone trying to diagnose lock contention and repeatedly finding the same transaction as the blocker should first check its runtime and the operations inside it, rather than optimizing the query itself.

7. Missing indexes as a hidden lock amplifier

A missing index amplifies lock contention in two ways at once. First, the query takes longer because a full table scan is required instead of an index lookup, which automatically extends the lock duration. Second, without a suitable index, InnoDB tends to lock more rows than necessary, because during the scan it must lock at least briefly every row it examines, to guarantee consistent results within the transaction.

Especially critical are missing indexes on foreign key columns: a DELETE or UPDATE on the parent table checks the child table for referential integrity, and without an index on the foreign key column that check happens with a full table scan of the child table, which drastically extends the lock duration and significantly increases the likelihood of lock contention with concurrent write operations.

8. Resolving lock contention in application code

Diagnosis alone does not solve the problem, it only provides the basis for the right countermeasure. The most effective countermeasures against lock contention apply at the application code level: a fixed, consistent locking order when locking multiple rows across all transactions, shorter transaction boundaries, and the deliberate use of SELECT ... FOR UPDATE SKIP LOCKED in queue like workloads, to avoid waiting sessions entirely instead of merely shortening them.

For reporting or analytics queries that do not need transactional consistency with the most recent write, READ UNCOMMITTED or an explicit snapshot read against replicas is an effective strategy to completely avoid lock contention with the primary write path. This separation of transactional OLTP traffic from read access for reporting is one of the most reliable architectural levers against chronic locking problems.

9. Diagnostic tools compared

Depending on the database system, different built in tools are available to diagnose lock contention. The following table compares the most important options by effort and level of detail.

For acute incidents, the live views are the first point of call, for long term trend analysis, historical sampling or log_lock_waits is indispensable. Both layers together give a complete picture, allowing lock contention to be reduced structurally rather than just fixed once.

10. Summary

Diagnosing lock contention starts with understanding that it is not a performance problem of the query itself, but wait time for another transaction. performance_schema.data_lock_waits in MySQL and pg_locks combined with pg_stat_activity in PostgreSQL directly show which session blocks which other one. Long transactions and missing indexes are the most common causes, short transaction boundaries and suitable indexes the most reliable countermeasure.

Historical sampling or log_lock_waits turn one off diagnoses into repeatable insight about recurring patterns, for example conflicts between batch jobs and live traffic. Whoever does not just fix lock contention in the moment but understands it structurally significantly reduces the frequency of future incidents and makes the entire database more predictable under load.

11. FAQ: Diagnosing Lock Contention