Database field guide · 15

Locks, Blocking, and Deadlocks

Read wait graphs across database engines

Diagnose lock modes, blocker chains, deadlock cycles, and intentional coordination in Oracle, PostgreSQL, and distributed SQL.

Franck Pachot7 chapterslocks · blockers · deadlocks · diagnostics

Build the mental model before choosing the mechanism.

This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.

A lock protects a fact

Locks represent a transaction's claim over rows, keys, relations, or application-defined resources. The mode expresses which concurrent claims remain compatible.

Waiting is not itself a defect.
Object and row locks protect different invariants.
MVCC reduces read/write blocking but does not remove write conflicts.

Read the compatibility matrix

Shared, exclusive, intention, key-share, and update modes differ by engine. Translate the protected operation before comparing product names.

Oracle TM modes describe table-level DML coordination.
PostgreSQL relation and tuple modes have separate matrices.
Distributed lock managers may expose key ranges and tablet identity.

Build the blocker graph

A blocked session points to a holder, which may itself wait. The useful diagnostic is a graph from final blocker through all victims, with transaction age and SQL at every node.

Capture the holder before killing it.
Transaction start often matters more than query start.
Idle-in-transaction sessions can retain consequential locks.

A deadlock is a cycle

Deadlock detection chooses a victim because no participant can progress. The error trace is evidence of incompatible resource order, not random failure.

Retry the complete transaction.
Acquire equivalent resources in a stable order.
Single statements can deadlock through multi-row execution order.

Lock intentionally

SELECT FOR UPDATE, advisory locks, and SKIP LOCKED are coordination tools when a schema constraint cannot directly encode the invariant.

Lock the row or key that represents the rule.
Keep work after lock acquisition short.
Advisory locks require every participant to honor the protocol.

Reduce the contention domain

Indexes, partition keys, transaction boundaries, and batch order determine how much state must remain protected and for how long.

An index can make conflict discovery precise.
Large batches enlarge deadlock surfaces.
Fix the access path before raising lock timeouts.

Field manual

Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.

Find PostgreSQL blocker chains

pg_blocking_pids returns direct blockers. Join session metadata and transaction age before deciding which session is the cause or victim.

SELECT pid, now()-xact_start AS xact_age, wait_event_type, wait_event,
       pg_blocking_pids(pid) blockers, application_name, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Map PostgreSQL lock objects

pg_locks exposes granted and waiting claims. Relation, transactionid, virtualxid, tuple, advisory, and distributed extensions require different identity columns.

SELECT pid, locktype, mode, granted, relation::regclass,
       page, tuple, transactionid, virtualxid
FROM pg_locks
ORDER BY granted, pid;

Find Oracle final blockers

V$SESSION carries direct and final blocker identifiers. Add transaction age and current/previous SQL before following V$LOCK details.

SELECT sid, serial#, event, blocking_session, final_blocking_session,
       sql_id, prev_sql_id, module
FROM v$session
WHERE state='WAITING' AND blocking_session IS NOT NULL;

Design deadlock reproduction

Use two sessions, explicit transaction boundaries, and opposite resource order. The deadlock victim receives an error, but the application must rollback before retrying the whole unit.

-- Session A: UPDATE account SET ... WHERE id=1; then id=2;
-- Session B: UPDATE account SET ... WHERE id=2; then id=1;
-- Capture PostgreSQL log or Oracle deadlock trace before changing code.

Source articles

Optional deep dives with the complete experiments and product-version context behind this guide.

Single-statement deadlock in Oracle and ORA-00060dev.to · 2023-12-19 Investigating Oracle lock issues with event 10704dbi-services · 2014-03-14 Oracle — Table lock modesmedium · 2019-01-18 Waiting for row lock. But which row is locked?dbi-services · 2015-09-13 Oracle locks: Identifying blocking sessionsdbi-services · 2014-11-17 Pessimistic locking, Read Committed, and all Isolation Levelsdev.to · 2024-09-02 More details in pg_locks for YugabyteDBdev.to · 2024-12-29 Advisory/Custom/Application Lock with YugabyteDBdev.to · 2024-05-09 Isolation Levels - part XIII: Explicit Locking with SELECT (FOR UPDATE) intentiondev.to · 2024-01-05 Can writes be blocked by reads in YugabyteDB?dev.to · 2023-10-29