Explicit Locking

While MVCC handles most concurrency needs automatically, there are times when you need Explicit Locking. PostgreSQL provides a rich set of lock modes for tables, rows, and even arbitrary application-defined resources.

[!IMPORTANT] The Golden Rule of Locking Two transactions cannot hold locks of conflicting modes on the same object at the same time. One will wait until the other releases the lock (commits or rolls back).

1. Table-Level Locks

These locks block access to an entire table. They are acquired automatically by most commands but can also be requested explicitly with LOCK TABLE.

Interactive Lock Matrix

Select two commands to see if they can run concurrently on the same table.

Compatibility Checker

Acquired by SELECT commands.
Acquired by SELECT commands.
Checking...

2. Row-Level Locks

Postgres uses fine-grained row locks for high concurrency. These are stored on the tuple header (remember xmax?), not in RAM, so you can lock millions of rows without consuming extra memory.

Locking Hierarchy (Weakest to Strongest)

  1. FOR KEY SHARE: (Weakest) Blocks DELETE and key updates. Used by foreign keys.
  2. FOR SHARE: Blocks UPDATE, DELETE, FOR NO KEY UPDATE.
  3. FOR NO KEY UPDATE: Acquired by UPDATE (that doesn’t change PK). Blocks FOR SHARE.
  4. FOR UPDATE: (Strongest) Acquired by DELETE or UPDATE (on PK). Blocks everything except reads.
BEGIN;
-- Lock rows where id=1. Other TXs can read, but not update/delete.
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Perform work...
UPDATE accounts SET bal = bal - 100 WHERE id = 1;
COMMIT;

[!TIP] Why FOR UPDATE? Use this when you select a row, calculate a new value in your app, and then update it. It prevents “Lost Updates” by ensuring no one else changes the row while you are calculating.

3. Deadlocks: The Embrace of Death

A Deadlock occurs when two transactions wait for each other.

The Cycle

  1. TX A locks Row 1.
  2. TX B locks Row 2.
  3. TX A tries to lock Row 2 (waits for B).
  4. TX B tries to lock Row 1 (waits for A).

Result: Neither can proceed.

Detection & Resolution

Postgres has a background “Deadlock Detector”. By default, after 1 second (deadlock_timeout), it wakes up, checks the dependency graph, finds the cycle, and kills one transaction with error 40P01.

Prevention Strategies

  1. Consistent Ordering: Always lock objects in the same order.
    • ❌ Bad: A locks (User, Account), B locks (Account, User).
    • ✅ Good: Both lock (Account, User) or sort by ID.
  2. Lock Tables Explicitly: If you update 1000 rows, maybe just LOCK TABLE accounts first (careful with concurrency!).
  3. FOR UPDATE SKIP LOCKED: Useful for queue processing. 
    -- Give me 10 jobs that no one else is working on
SELECT * FROM jobs
WHERE status = 'pending'
LIMIT 10
FOR UPDATE SKIP LOCKED;

4. Advisory Locks (Application Locks)

Sometimes you need to lock something abstract, like “Running End of Day Report”, that doesn’t map to a single row.

Postgres allows you to create locks on numbers.

-- Acquire lock on the number 12345
SELECT pg_advisory_lock(12345);

-- Critical section... only one session can hold lock 12345 at a time.

-- Release
SELECT pg_advisory_unlock(12345);

Use Case: Distributed locking for cron jobs across multiple app servers. Ensure only one server runs the “Daily Email” job.

5. Summary

  1. Table Locks: Mostly automatic. ACCESS EXCLUSIVE (DROP/ALTER) is the most dangerous as it blocks reads.
  2. Row Locks: FOR UPDATE is your friend for read-modify-write cycles.
  3. Deadlocks: Prevent them by ordering your locks consistently.
  4. Advisory Locks: Use Postgres as a fast, reliable distributed lock manager (alternative to Redis/Zookeeper).