PostgreSQL — Topic 7: Locking

🔐

Mental model: Think of locks like a shared bathroom. A row lock (FOR UPDATE) is like a "Occupied" sign — one person at a time. A table lock is like the whole bathroom being reserved for cleaning. Deadlock is when two people are each waiting for the other to come out first — forever.

7.1

Row Locks

Lock individual rows to prevent concurrent modifications

▾

What are Row Locks?

Row locks allow you to lock specific rows inside a transaction so that other transactions cannot modify (or in some cases even read) those rows until your transaction finishes. PostgreSQL gives you four row-level lock modes.

FOR UPDATE

Strongest row lock. Prevents any other transaction from updating, deleting, or locking those rows. Used when you intend to update.

FOR NO KEY UPDATE

Like FOR UPDATE but allows foreign key checks from child tables. Useful when you update columns that are NOT primary/unique keys.

FOR SHARE

Allows multiple transactions to lock the same row for reading. Blocks updates and deletes. Good for read-before-write patterns.

FOR KEY SHARE

Weakest row lock. Only blocks operations that would change key values. Mainly used by foreign key checks internally.

FOR UPDATE — The Most Common Row Lock

SELECT ... FOR UPDATE is the pattern you use when you want to read a row and then update it, making sure nobody else changes it in between. This is the classic optimistic-to-pessimistic lock pattern.

Real example: Two users are trying to book the last concert ticket at the same time. Without a lock, both could see "1 ticket left" and both could "buy" it — overselling by 1.

❌ Without Lock — Race Condition

-- T1 reads: tickets = 1
SELECT tickets FROM inventory WHERE id = 1;

-- T2 also reads: tickets = 1
SELECT tickets FROM inventory WHERE id = 1;

-- T1 updates: tickets = 0 ✅
UPDATE inventory SET tickets = 0 WHERE id = 1;

-- T2 also updates: tickets = 0 ❌ (oversold!)
UPDATE inventory SET tickets = 0 WHERE id = 1;

✅ With FOR UPDATE — Safe

-- T1 locks the row
SELECT tickets FROM inventory
WHERE id = 1
FOR UPDATE;   -- T2 now BLOCKS here

-- T1 updates and commits
UPDATE inventory SET tickets = 0 WHERE id = 1;
COMMIT;

-- T2 unblocks, sees tickets = 0
-- T2 can then handle "out of stock"

-- Pattern: SELECT FOR UPDATE inside a transaction
BEGIN;

-- Step 1: Lock the row you're about to modify
SELECT id, tickets_remaining
FROM inventory
WHERE product_id = 42
FOR UPDATE;

-- Step 2: Check the value in your application code
-- If tickets > 0, proceed

-- Step 3: Update safely — no one else could change it
UPDATE inventory
SET tickets_remaining = tickets_remaining - 1
WHERE product_id = 42;

COMMIT;
          

SKIP LOCKED — Non-blocking Queue Pattern

In job queues, you want workers to grab jobs without blocking each other. SKIP LOCKED skips rows that are already locked by another transaction instead of waiting. This is the foundation of PostgreSQL-as-a-queue.

-- Job queue table
CREATE TABLE jobs (
  id        BIGSERIAL PRIMARY KEY,
  status    TEXT DEFAULT 'pending',
  payload   JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Worker picks ONE job without blocking other workers
BEGIN;
SELECT id, payload
FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;  -- skip rows other workers grabbed

-- Process the job, then mark done
UPDATE jobs SET status = 'done' WHERE id = :id;
COMMIT;
          

⚡

Used in production by: Sidekiq (Ruby), Django Q, FastAPI background workers, and any system that uses PostgreSQL as a lightweight task queue. SKIP LOCKED makes it possible to run 10 parallel workers on the same jobs table without blocking.

NOWAIT — Fail Fast Instead of Waiting

By default, FOR UPDATE blocks (waits) if the row is already locked. With NOWAIT, if the lock cannot be acquired immediately, PostgreSQL raises an error right away — perfect when you'd rather retry than wait.

-- Fail immediately if row is locked
BEGIN;
SELECT * FROM accounts WHERE id = 5 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "accounts"
-- Your app can catch this and retry or show "try again" message
ROLLBACK;
          

Interactive: Row Lock Simulator

Step through two concurrent transactions to see how FOR UPDATE works. Run steps in order and observe blocking behavior.

🔐 Row Lock Demo — Ticket Booking

Transaction 1 (T1) — Buyer A

Transaction 2 (T2) — Buyer B

-- Run T1 Step 1 and T2 Step 1 to start both transactions...

7.2

Table Locks

Lock entire tables for schema changes and bulk operations

▾

What are Table Locks?

Table locks apply to an entire table, not just specific rows. PostgreSQL acquires table locks automatically for most DDL (schema change) operations. You can also acquire them manually with LOCK TABLE. There are 8 table lock modes ranging from very permissive to fully exclusive.

⚠️

Important: In PostgreSQL, you rarely need to call LOCK TABLE manually. The database acquires the right lock automatically. The key skill is knowing which operations conflict so you can avoid blocking production traffic during deployments.

The 8 Table Lock Modes

From weakest (most permissive) to strongest (most exclusive). "Conflicts with" tells you which other locks it blocks:

Lock Mode	Acquired By	Allows Concurrent	Blocks
ACCESS SHARE	SELECT	Almost everything	ACCESS EXCLUSIVE only
ROW SHARE	SELECT FOR UPDATE / SHARE	Most reads & writes	EXCLUSIVE, ACCESS EXCLUSIVE
ROW EXCLUSIVE	INSERT, UPDATE, DELETE	Reads, other DML	SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE
SHARE UPDATE EXCLUSIVE	VACUUM, CREATE INDEX CONCURRENTLY	Reads, DML	Other schema changes
SHARE	CREATE INDEX (non-concurrent)	Reads only	Writes
SHARE ROW EXCLUSIVE	CREATE TRIGGER, some foreign key ops	Reads	Most writes and schema changes
EXCLUSIVE	Rare, explicit LOCK TABLE	SELECT only	Everything except ACCESS SHARE
ACCESS EXCLUSIVE	ALTER TABLE, DROP TABLE, TRUNCATE, VACUUM FULL	Nothing	Everything — including SELECT

Access Share — What SELECT Does

ACCESS SHARE is the weakest lock. A plain SELECT acquires it. It only conflicts with ACCESS EXCLUSIVE, meaning selects run freely even while writes are happening. This is why reads don't block writes in PostgreSQL (MVCC does the heavy lifting).

-- SELECT acquires ACCESS SHARE automatically
SELECT * FROM orders WHERE status = 'pending';
-- This lock is held only for the duration of the query
-- It will NOT block INSERT/UPDATE/DELETE on the same table
-- It WILL block: ALTER TABLE orders ADD COLUMN ... (ACCESS EXCLUSIVE)
          

Row Share — SELECT FOR UPDATE

ROW SHARE is acquired at the table level when you do SELECT ... FOR UPDATE or FOR SHARE. It's a weak table lock — it still allows other transactions to read and write normally. It only conflicts with full table EXCLUSIVE locks.

-- ROW SHARE acquired on the table, row lock on specific rows
BEGIN;
SELECT * FROM products WHERE id = 10 FOR UPDATE;
-- Table: ROW SHARE acquired (very permissive)
-- Row id=10: FOR UPDATE lock acquired (strong, exclusive on that row)
COMMIT;
          

Row Exclusive — What DML Acquires

Every INSERT, UPDATE, and DELETE acquires a ROW EXCLUSIVE table lock automatically. Multiple transactions can hold ROW EXCLUSIVE simultaneously — they don't block each other at the table level. The actual row-level locks prevent conflicts on individual rows.

-- Each of these acquires ROW EXCLUSIVE on the table
INSERT INTO orders (user_id, total) VALUES (1, 99.99);
UPDATE  orders SET status = 'shipped' WHERE id = 5;
DELETE FROM orders WHERE id = 3;

-- All three can run concurrently without blocking each other!
-- ROW EXCLUSIVE does not conflict with ROW EXCLUSIVE
          

Access Exclusive — The Dangerous One

ACCESS EXCLUSIVE is acquired by ALTER TABLE, DROP TABLE, TRUNCATE, and VACUUM FULL. It conflicts with every other lock, including plain SELECT. This is why running ALTER TABLE on a busy production table blocks all queries — a very common incident in production.

❌ Dangerous in Production

-- This will block ALL reads and writes
-- until it acquires the lock
ALTER TABLE orders ADD COLUMN notes TEXT;

-- If there are long-running queries,
-- this waits → queues up → outage!

✅ Safer Alternative

-- Use lock_timeout to fail fast
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN notes TEXT;

-- Or add column with a DEFAULT separately:
-- 1. ALTER TABLE orders ADD COLUMN notes TEXT;
-- 2. ALTER TABLE orders ALTER COLUMN notes
--    SET DEFAULT 'none';
-- In Postgres 11+, ADD COLUMN with DEFAULT
-- is instant (stored in catalog).

-- Always set lock_timeout for DDL in production
SET lock_timeout = '3s';   -- fail if we can't get lock in 3s
SET statement_timeout = '30s'; -- fail if ALTER takes > 30s

ALTER TABLE orders ADD COLUMN notes TEXT;

-- For truly zero-downtime column addition (PG11+)
-- Adding a column with a volatile DEFAULT is safe in PG11+
ALTER TABLE orders ADD COLUMN notes TEXT DEFAULT '';
-- PG11+ stores the default in catalog, doesn't rewrite the table
          

🚨

Production war story: A team ran ALTER TABLE users ADD COLUMN preferences JSONB DEFAULT '{}' on a 50M-row table during peak hours. It waited to acquire ACCESS EXCLUSIVE, which blocked all user-facing queries. The site was down for 4 minutes. Always use lock_timeout and deploy schema changes during low-traffic windows.

Lock Compatibility Matrix

This shows which lock modes conflict with each other. ✗ = conflict (blocks), ✓ = compatible (allowed concurrently).

Held ↓ / Requested →	AS	RS	RE	SUE	SH	SRE	EX	AE
ACCESS SHARE (AS)	✓	✓	✓	✓	✓	✓	✓	✗
ROW SHARE (RS)	✓	✓	✓	✓	✓	✓	✗	✗
ROW EXCLUSIVE (RE)	✓	✓	✓	✓	✗	✗	✗	✗
SHARE UPD EXC (SUE)	✓	✓	✓	✗	✗	✗	✗	✗
SHARE (SH)	✓	✓	✗	✗	✓	✗	✗	✗
SHARE ROW EXC (SRE)	✓	✓	✗	✗	✗	✗	✗	✗
EXCLUSIVE (EX)	✓	✗	✗	✗	✗	✗	✗	✗
ACCESS EXCLUSIVE (AE)	✗	✗	✗	✗	✗	✗	✗	✗

LOCK TABLE — Manual Table Locking

You can manually acquire a table lock when needed. The most common use case is ensuring consistency during bulk operations or batch inserts.

-- Manually lock a table in SHARE mode (allows reads, blocks writes)
BEGIN;
LOCK TABLE monthly_reports IN SHARE MODE;
-- Now you can safely read and aggregate without writes interfering
SELECT SUM(revenue) FROM monthly_reports;
COMMIT;

-- Lock in EXCLUSIVE mode during bulk load (blocks everything except SELECTs)
BEGIN;
LOCK TABLE staging_data IN EXCLUSIVE MODE;
TRUNCATE staging_data;
COPY staging_data FROM '/data/daily_load.csv' CSV;
COMMIT;
          

7.3

Deadlocks

Detection, resolution, and how to prevent them in production

▾

What is a Deadlock?

A deadlock happens when two or more transactions are each waiting for the other to release a lock — and none can proceed. It's a circular wait that would last forever if PostgreSQL didn't detect and resolve it.

💡

PostgreSQL automatically detects deadlocks. When it finds one, it picks one of the transactions as the "victim" and aborts it with an error: ERROR: deadlock detected. The victim transaction must then be retried.

Classic Deadlock Example

The most common cause: two transactions lock rows in different orders.

Transaction 1

BEGIN;
-- Locks Account A
UPDATE accounts
SET balance = balance - 100
WHERE id = 'A';  -- ✅ locked A

-- Now tries to lock B
-- But T2 already has B!
UPDATE accounts
SET balance = balance + 100
WHERE id = 'B';  -- ⏳ WAITING...

⇄

Transaction 2

BEGIN;
-- Locks Account B
UPDATE accounts
SET balance = balance - 50
WHERE id = 'B';  -- ✅ locked B

-- Now tries to lock A
-- But T1 already has A!
UPDATE accounts
SET balance = balance + 50
WHERE id = 'A';  -- ⏳ WAITING...

💀

Result: T1 holds A and waits for B. T2 holds B and waits for A. Neither can proceed. PostgreSQL detects this cycle and aborts one transaction with ERROR: deadlock detected DETAIL: Process 1234 waits for ShareLock on transaction 5678; blocked by process 9012.

Detection — How PostgreSQL Finds Deadlocks

PostgreSQL runs a background deadlock detector that periodically checks the lock wait graph for cycles. The detection delay is controlled by deadlock_timeout (default: 1 second). When a cycle is found, the "lightest" transaction (fewest locks held) is chosen as the victim and rolled back.

-- Check current deadlock_timeout setting
SHOW deadlock_timeout;
-- Default: 1s — PostgreSQL waits 1 second before checking for deadlocks

-- Error message you'll see in your application logs:
-- ERROR:  deadlock detected
-- DETAIL: Process 12345 waits for ShareLock on transaction 67890;
--         blocked by process 11111.
--         Process 11111 waits for ShareLock on transaction 12345;
--         blocked by process 12345.
-- HINT:  See server log for query details.

-- View who is currently waiting for locks
SELECT
  pid,
  wait_event_type,
  wait_event,
  state,
  query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';
          

Resolution — What Happens After Detection

PostgreSQL resolves deadlocks by aborting one transaction (the victim). The victim gets an error, its changes are rolled back, and the other transaction(s) can proceed. Your application code must handle this error and retry.

-- Python/psycopg2 pattern — retry on deadlock
import psycopg2
from psycopg2 import errors

def transfer_money(conn, from_id, to_id, amount, max_retries=3):
    for attempt in range(max_retries):
        try:
            with conn.cursor() as cur:
                cur.execute("BEGIN")
                cur.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, from_id)
                )
                cur.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to_id)
                )
                cur.execute("COMMIT")
                return  # success
        except errors.DeadlockDetected:
            conn.rollback()
            if attempt == max_retries - 1:
                raise   # all retries exhausted
            time.sleep(0.1 * (2 ** attempt))  # exponential backoff
          

Prevention — The Golden Rules

Most deadlocks are preventable by following simple discipline in your codebase:

Rule 1: Consistent Lock Order

Always lock rows/tables in the same order across all transactions. If T1 always locks A then B, and T2 also locks A then B, deadlock is impossible.

Rule 2: Short Transactions

The longer a transaction runs, the more time locks are held, increasing deadlock probability. Keep transactions as short as possible — do work, commit quickly.

Rule 3: Grab Locks Upfront

If you know you'll need multiple locks, acquire them all at the beginning of the transaction in a single SELECT FOR UPDATE rather than one at a time.

Rule 4: Use Lock Ordering

For transfers between entities (A→B), always sort by ID and lock the lower ID first. This enforces a global ordering that prevents circular waits.

The Fix: Consistent Lock Ordering

❌ Inconsistent order → Deadlock possible

-- Transfer A → B
UPDATE accounts SET balance = balance - 100
WHERE id = 'A';   -- locks A first
UPDATE accounts SET balance = balance + 100
WHERE id = 'B';   -- then B

-- Transfer B → A (in another transaction)
UPDATE accounts SET balance = balance - 50
WHERE id = 'B';   -- locks B first ← OPPOSITE!
UPDATE accounts SET balance = balance + 50
WHERE id = 'A';

✅ Sort by ID → Deadlock impossible

-- Always lock the LOWER ID first
-- Transfer A(1) → B(2)
SELECT * FROM accounts
WHERE id IN (1, 2)
ORDER BY id   -- ← sort ensures order 1, 2
FOR UPDATE;   -- locks both at once

-- Transfer B(2) → A(1)
SELECT * FROM accounts
WHERE id IN (1, 2)
ORDER BY id   -- ← same order: 1, 2
FOR UPDATE;   -- no circular wait!

-- Best practice: lock multiple rows in sorted order
BEGIN;

-- Lock BOTH accounts in consistent order (low id first)
SELECT id, balance
FROM accounts
WHERE id IN (1, 2)
ORDER BY id          -- ← critical: consistent ordering
FOR UPDATE;

-- Now safely update both
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;
          

Interactive: Deadlock Simulator

Watch a deadlock form step by step, then see PostgreSQL detect and resolve it.

💀 Deadlock Demo — Account Transfer

Transaction 1 — Transfer A→B

Transaction 2 — Transfer B→A

-- Start both transactions, then watch the deadlock form...

Finding Lock Waits in Production

These queries help you diagnose locking issues on a live database without restarting anything.

-- Find all queries currently waiting for a lock
SELECT
  pid,
  now() - query_start AS wait_duration,
  query,
  state,
  wait_event
FROM pg_stat_activity
WHERE state = 'active'
  AND wait_event_type = 'Lock'
ORDER BY wait_duration DESC;

-- Find WHAT is blocking each waiting query
SELECT
  blocked.pid          AS blocked_pid,
  blocked.query        AS blocked_query,
  blocking.pid         AS blocking_pid,
  blocking.query       AS blocking_query
FROM pg_stat_activity blocked
JOIN  pg_stat_activity blocking
  ON  blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- Kill a blocking query (use with care!)
SELECT pg_cancel_backend(12345);   -- graceful cancel
SELECT pg_terminate_backend(12345); -- force kill
          

✓

Topic 7 Summary

What you've learned + what to remember for production

▾

Key Takeaways

Concept	What it is	When to use
`SELECT FOR UPDATE`	Exclusive row lock — no one else can modify locked rows	Read-then-modify patterns (ticket booking, balance transfer, inventory)
`FOR UPDATE SKIP LOCKED`	Skip already-locked rows, don't wait	Job queues, parallel workers
`FOR UPDATE NOWAIT`	Fail immediately if row is already locked	When waiting is worse than failing fast
ACCESS SHARE	Table-level lock acquired by SELECT	Automatic — only blocked by ALTER TABLE / DROP
ROW EXCLUSIVE	Table-level lock acquired by INSERT/UPDATE/DELETE	Automatic — multiple transactions can hold simultaneously
ACCESS EXCLUSIVE	Full table lockdown by ALTER TABLE, TRUNCATE	DDL changes — always use `lock_timeout` in prod
Deadlock	Circular lock wait — PostgreSQL aborts one victim	Prevented by: consistent lock order, short transactions, upfront locking

Production Checklist

✓ For Row Locks

Always use SELECT FOR UPDATE inside a transaction when you plan to update a row. Never read outside, then update inside.

✓ For DDL in Prod

Always set lock_timeout before ALTER TABLE. Use CREATE INDEX CONCURRENTLY to avoid table locks during index creation.

✓ For Deadlocks

Lock rows in consistent order (sort by ID). Retry on deadlock detected error. Keep transactions short.

✓ For Debugging

Use pg_stat_activity and pg_blocking_pids() to find what's blocking what in production without any downtime.

🎯

Up next → Topic 8: PostgreSQL Internals & MVCC — This is the most important internal topic. You'll learn how PostgreSQL stores rows, manages versions, and why reads don't block writes (even without locks). MVCC is the engine behind everything you just learned about locking.