13.1 Two-Phase Locking, Lock Granularity, and Deadlock Diagnosis

---

Metadata Card

• Prerequisites: Chapter 12, MVCC and Isolation Levels
• Keywords: 2PL, Strict 2PL, Multigranularity Locking, Deadlock, pg_blocking_pids
• Language: SQL (PostgreSQL)

---

MVCC allows ordinary reads to access older versions, but it does not eliminate all locking requirements. When two transactions modify the same row, ordering is still necessary. DDL operations must prevent objects from being deleted while in use. Reads that depend on the latest state of data require locking or alternative conflict control mechanisms.

2PL Constrains the Order of Lock Acquisition and Release

Two-Phase Locking (2PL) divides a transaction into two phases:

Growth phase: locks can be acquired, but not released
Lock point: the moment the last lock is obtained
Shrink phase: locks can be released, but no new locks can be acquired

When all transactions follow basic 2PL, the resulting schedule is conflict serializable; the order of lock points can be used to construct an equivalent serial execution order. However, 2PL does not guarantee deadlock freedom and does not automatically prevent reading uncommitted data.

Common variants should be clearly distinguished:

• strict 2PL: exclusive locks are held until commit or rollback, preventing other transactions from reading or overwriting uncommitted writes;
• rigorous/strong strict 2PL: both shared and exclusive locks are held until the transaction completes.

Different textbooks occasionally misuse terminology, so technical documentation should explicitly state "which locks are held until when," rather than relying solely on abbreviations.

PostgreSQL and InnoDB are not systems that implement pure 2PL to explain all read and write operations. Instead, they combine MVCC snapshots, row locks, table locks, and product-specific conflict detection mechanisms.

Why Intention Locking is Needed for Multi-Granularity Locking

The finer the lock granularity, the greater the potential for concurrency, but also the higher the cost of management and scanning. In theory, a multi-granularity hierarchy might look like: database → table → page → row.

If transaction T1 already holds a row-level lock in a table, and transaction T2 requests an exclusive lock on the entire table, the lock manager cannot feasibly scan every row to check for conflicts. Intention locks solve this by declaring intent to lock at a finer granularity on a parent node:

• IS: Intends to acquire a shared lock at a more granular level;
• IX: Intends to acquire an exclusive lock at a more granular level;
• SIX: Currently holds a shared lock at this level, and intends to acquire an exclusive lock at a finer level.

A commonly used compatibility matrix is as follows:

Already Held \ Requested	IS	IX	S	SIX	X
IS	✓	✓	✓	✓	✗
IX	✓	✓	✗	✗	✗
S	✓	✗	✓	✗	✗
SIX	✓	✗	✗	✗	✗
X	✗	✗	✗	✗	✗

This matrix helps understand the behavior of multi-granularity locking protocols and should not be directly mapped onto PostgreSQL's table-level lock names. PostgreSQL has its own set of eight table-level lock modes (such as ACCESS SHARE and ROW EXCLUSIVE), each with its own conflict matrix. Modes containing ROW remain table-level locks.

Lock Escalation Is a Product Choice, Not a Mandatory Rule

Some systems upgrade large numbers of row locks into page or table locks to reduce memory usage by the lock manager. The trade-off is a sudden drop in concurrency. InnoDB bases its row locks on index structures and does not perform conventional lock escalation. PostgreSQL likewise does not automatically escalate to table-level exclusive locks simply because many rows are locked.

PostgreSQL stores row lock information primarily within row versions, so the max_locks_per_transaction parameter cannot be used to infer a maximum number of rows that can be locked. This parameter applies to object locks in shared lock tables, not to the upper limit of row locks.

Deadlocks Arise from Waiting Cycles

If two transfer transactions acquire locks on accounts in opposite orders, they may form a waiting cycle:

T1 holds account=1, waiting for account=2
T2 holds account=2, waiting for account=1

The database will terminate one transaction and allow the other to proceed. The application layer must still roll back and retry the victim transaction.

Common strategies to reduce deadlock probability:

• Enforce a stable global lock order for similar resources;
• Shorten transactions and avoid calling remote services while holding locks;
• Ensure filtering conditions have appropriate indexes to reduce unintended scans and lock scope;
• Declare the set of locks upfront and access them in a fixed, deterministic order;
• Implement bounded retries for deadlock and serialization failures.

Lock timeouts are not a substitute for deadlock detection. A timeout only indicates that a wait has exceeded a threshold; there is no guarantee that a waiting graph contains a cycle. A deadlock, by contrast, means that if no participant is interrupted, no transaction in the affected group can progress.

How PostgreSQL Triggers Deadlock Detection

PostgreSQL does not have a background process that scans all transactions every second. Instead, a session triggers an expensive deadlock check when lock waiting reaches deadlock_timeout, which is typically set to 1 second by default. When log_lock_waits is enabled, the same threshold also affects long lock wait logging.

SHOW deadlock_timeout;
SHOW lock_timeout;

lock_timeout controls the maximum duration a single lock wait can last; statement_timeout controls the total time for an entire statement. These three parameters address different questions.

First find the blocker, then look at the specific lock

When diagnosing blocking chains, prioritize using PostgreSQL's built-in pg_blocking_pids() instead of manually writing a long series of equality joins for pg_locks nullable fields. Standard = fails to correctly match lock identifiers that are NULL on both sides, easily leading to missed detections or false matches.

SELECT
  blocked.pid AS blocked_pid,
  blocked.usename AS blocked_user,
  blocked.wait_event_type,
  blocked.wait_event,
  clock_timestamp() - blocked.query_start AS statement_age,
  blocked.query AS blocked_query,
  p.blocker_pid AS blocker_pid,
  blocker.query AS blocker_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS p(blocker_pid)
LEFT JOIN pg_stat_activity AS blocker
  ON blocker.pid = p.blocker_pid
ORDER BY statement_age DESC;

statement_age measures statement age, not exact lock waiting time; use pg_locks.waitstart for the start of a lock wait. pg_blocking_pids may return PID 0 for a prepared transaction, so LEFT JOIN keeps blockers without an active session. Viewing other users' full SQL requires suitable monitoring privileges.

This query lists locks held by the current connection and runs inside a practice transaction. To inspect another session, replace pg_backend_pid() with its verified PID:

SELECT
  pid,
  locktype,
  relation::regclass AS relation,
  transactionid,
  mode,
  granted,
  waitstart
FROM pg_locks
WHERE pid = pg_backend_pid()
ORDER BY pid, granted, locktype;

Production troubleshooting should also record: transaction start time, state, xact_start, query_start, application name, and call chain. The current SQL is short, which does not mean the transaction did not acquire locks earlier.

Reproducible Two-Session Deadlock

Use PostgreSQL 16+ and the two accounts from 11.1, each holding at least 20. Run A's first UPDATE, B's first UPDATE, A's second UPDATE, then B's second UPDATE. A's second statement waits, so switch to B to continue. Two motionless progress bars rarely improve when you click Run again.

-- Session A
BEGIN;
UPDATE account SET balance = balance - 10 WHERE account_id = 1;
-- After B locks account 2, execute:
UPDATE account SET balance = balance + 10 WHERE account_id = 2;

-- Session B
BEGIN;
UPDATE account SET balance = balance - 20 WHERE account_id = 2;
-- After A locks account 1, execute:
UPDATE account SET balance = balance + 20 WHERE account_id = 1;

Expect one session to receive SQLSTATE 40P01 and the other to resume; the victim is not fixed. Afterwards, ROLLBACK in both sessions: clear the victim's failed transaction state and release the survivor's locks while undoing its changes. Then modify both sessions to lock in ascending order of account_id, and compare the results.

References

• PostgreSQL: Explicit Locking
• PostgreSQL: Lock Management
• PostgreSQL: pg_blocking_pids
• MySQL 8.4: InnoDB Locking

The next section compares optimistic concurrency control without preemption, timestamp-ordered protocols, and the actual guarantees provided by version numbers.