Transactions and Locking

Overview

Transaction: A group of statements that commit or roll back as one unit — the “A” in ACID
From SQL: BEGIN / COMMIT / ROLLBACK, SAVEPOINT, isolation levels, explicit row locks (FOR UPDATE, SKIP LOCKED), advisory locks
Postgres Model: MVCC — readers never block writers and writers never block readers; only writers on the same row wait for each other
Theory Notes: Concurrency Control, Shared vs Exclusive Lock, Two-phase Locking, Deadlock, Phantom Reads, Serializable vs Non-Repeatable Isolation Level
Sample Data: E-commerce Practice Queries

BEGIN, COMMIT, ROLLBACK

Basics

Autocommit: Without BEGIN, every statement is its own transaction
BEGIN: Starts a transaction block (START TRANSACTION is the standard spelling)
COMMIT: Makes changes durable and visible to others
ROLLBACK: Discards every change since BEGIN
Aborted State: After any error inside a block, Postgres rejects further statements (current transaction is aborted) until ROLLBACK (or rollback to a savepoint)

-- Transfer stock between two products atomically
BEGIN;
UPDATE products SET stock = stock - 5 WHERE sku = 'ACC-001';
UPDATE products SET stock = stock + 5 WHERE sku = 'ACC-003';
SELECT sku, stock FROM products WHERE sku IN ('ACC-001', 'ACC-003') ORDER BY sku;
ROLLBACK;   -- COMMIT in real life

SAVEPOINT

What: A named point inside a transaction you can roll back to without losing earlier work
Use: Try a risky step, recover from an expected error, continue
Cost: Each savepoint creates a subtransaction — thousands in one transaction hurt performance (subtrans SLRU overflow)

BEGIN;
INSERT INTO categories (name) VALUES ('Toys');
 
SAVEPOINT before_dupe;
INSERT INTO categories (name) VALUES ('Books');      -- violates UNIQUE(name) → error, txn now aborted
ROLLBACK TO SAVEPOINT before_dupe;                   -- recovered: 'Toys' insert still alive
 
INSERT INTO categories (name) VALUES ('Garden');
SELECT name FROM categories WHERE name IN ('Toys', 'Garden', 'Books') ORDER BY name;
RELEASE SAVEPOINT before_dupe;
ROLLBACK;

Isolation Levels

Read Phenomena

Dirty Read: See another transaction’s uncommitted data
Non-Repeatable Read: Re-reading a row gives a different value (someone committed an update)
Phantom Read: Re-running a range query returns new/missing rows
Serialization Anomaly: Result impossible under any serial order (e.g. write skew)

Postgres Behaviour

Level	Dirty	Non-Repeatable	Phantom	Anomaly	Postgres Notes
READ UNCOMMITTED	No	Possible	Possible	Possible	Treated as READ COMMITTED
READ COMMITTED (default)	No	Possible	Possible	Possible	New snapshot per statement
REPEATABLE READ	No	No	No (stricter than standard)	Possible	One snapshot per transaction; concurrent update → error 40001
SERIALIZABLE	No	No	No	No	SSI — detects dangerous patterns, aborts one txn with 40001

Setting the Level

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders;      -- snapshot taken at the first query
-- other sessions' commits are invisible from here on
SELECT count(*) FROM orders;      -- same answer, guaranteed
COMMIT;
 
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;   -- must be the first statement in the txn
SHOW transaction_isolation;
COMMIT;
 
-- Read-only reporting transaction that can't fail with serialization errors
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT sum(amount) FROM payments WHERE status = 'succeeded';
COMMIT;

Lost Update and How to Prevent It

READ COMMITTED, two sessions both run "read stock → subtract in app → write":
 
Session A: SELECT stock FROM products WHERE id = 5;         -- 10
Session B: SELECT stock FROM products WHERE id = 5;         -- 10
Session A: UPDATE products SET stock = 9 WHERE id = 5;  COMMIT;
Session B: UPDATE products SET stock = 9 WHERE id = 5;  COMMIT;   -- A's sale is lost (should be 8)

Fix	How	Trade-off
Atomic UPDATE	`SET stock = stock - 1`	Best when logic fits in SQL
SELECT … FOR UPDATE	Lock the row before reading	Pessimistic; others wait
Optimistic Version Column	`UPDATE … WHERE id = $1 AND version = $2` → 0 rows = retry	No locks held; app must retry
REPEATABLE READ / SERIALIZABLE	DB raises 40001 on conflict	App must retry the whole txn

BEGIN;
-- Atomic decrement with a guard: 0 rows updated = out of stock
UPDATE products SET stock = stock - 1
WHERE id = 5 AND stock > 0
RETURNING stock;                                  -- USB-C Hub has 0 → returns nothing
ROLLBACK;

Explicit Row Locks

Lock Strengths

Clause	Blocks	Use
FOR UPDATE	Other `FOR UPDATE/SHARE`, `UPDATE`, `DELETE` of the row	You will update/delete the row
FOR NO KEY UPDATE	Same, but allows `FOR KEY SHARE` (FK checks)	Updating non-key columns (what plain `UPDATE` takes)
FOR SHARE	Writers, but not other sharers	Ensure the row doesn’t change while you read
FOR KEY SHARE	Only deletes / key changes	Taken by FK checks on the parent
NOWAIT	—	Error immediately instead of waiting
SKIP LOCKED	—	Silently skip locked rows (queues!)

BEGIN;
-- Pessimistic: lock the order and its items' products before changing them
SELECT o.id, o.status
FROM orders o
WHERE o.id = 10
FOR UPDATE;                                      -- other txns trying to lock/update order 10 now wait
 
UPDATE orders SET status = 'paid' WHERE id = 10;
ROLLBACK;
 
-- Lock only one table of a join
BEGIN;
SELECT p.id, p.stock
FROM products p JOIN order_items oi ON oi.product_id = p.id
WHERE oi.order_id = 10
FOR UPDATE OF p NOWAIT;                          -- error 55P03 if someone else holds it
ROLLBACK;

Job Queue With FOR UPDATE SKIP LOCKED

Pattern: Many workers poll the same table; each grabs a different unlocked job — no double processing, no blocking
Why It Works: SKIP LOCKED ignores rows already locked by other workers’ open transactions
Index: A partial index on pending jobs keeps the poll cheap

CREATE TABLE IF NOT EXISTS jobs (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kind        text NOT NULL,
    payload     jsonb NOT NULL DEFAULT '{}',
    status      text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'running', 'done', 'failed')),
    attempts    int NOT NULL DEFAULT 0,
    run_after   timestamptz NOT NULL DEFAULT now(),
    locked_by   text,
    created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS jobs_queued_idx ON jobs (run_after, id) WHERE status = 'queued';
 
INSERT INTO jobs (kind, payload)
SELECT 'send_receipt', jsonb_build_object('order_id', id)
FROM orders WHERE status = 'delivered';

-- Worker loop body (each worker runs this; claim + mark in one statement)
BEGIN;
WITH next_job AS (
    SELECT id
    FROM jobs
    WHERE status = 'queued' AND run_after <= now()
    ORDER BY run_after, id
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running', attempts = attempts + 1, locked_by = 'worker-1'
FROM next_job
WHERE j.id = next_job.id
RETURNING j.id, j.kind, j.payload;
 
-- … do the work in the app …
UPDATE jobs SET status = 'done' WHERE locked_by = 'worker-1' AND status = 'running';
COMMIT;
 
SELECT status, count(*) FROM jobs GROUP BY status ORDER BY status;

Two Workers at Once: Worker 2 running the same claim while worker 1’s transaction is open gets the next job, not a wait (verified with two concurrent sessions: A claimed job 2, B immediately got job 3)
Crash Safety: If you claim and process inside one transaction, a crash rolls back to queued. If you commit the claim first (short transactions), add a timeout sweeper that resets stale running jobs
At Scale: Fine for thousands of jobs/sec; beyond that, or for fan-out, consider a dedicated broker

Advisory Locks

Concept

What: Application-defined locks on an arbitrary bigint key — Postgres just tracks who holds the number
Session-level: pg_advisory_lock(key) — held until unlocked or the session ends (careful with connection pools — see Database Connection Pooling)
Transaction-level: pg_advisory_xact_lock(key) — released automatically at commit/rollback (safer)
Try Variants: pg_try_advisory_lock(key) returns false instead of waiting

Use Case	Example
Single-runner cron	Only one instance of a nightly job runs
Serialize per entity	One balance recalculation per user at a time
Migrations	Tools like Flyway/Rails take an advisory lock so two deploys don’t migrate together

-- Only one process may rebuild the report for user 42 at a time
BEGIN;
SELECT pg_advisory_xact_lock(hashtext('rebuild_report'), 42);   -- two-int form: (namespace, id)
-- … rebuild …
COMMIT;                                                         -- lock released
 
-- Cron guard: skip if another instance is already running
SELECT pg_try_advisory_lock(777) AS got_lock;     -- true for the first caller, false for others
SELECT pg_advisory_unlock(777);
 
-- See who holds advisory locks
SELECT locktype, classid, objid, pid, granted FROM pg_locks WHERE locktype = 'advisory';

Table Locks and DDL

Every Statement Takes a Table Lock: SELECT → ACCESS SHARE, UPDATE/DELETE/INSERT → ROW EXCLUSIVE, most ALTER TABLE / DROP / TRUNCATE → ACCESS EXCLUSIVE
The Queue Problem: An ALTER TABLE waiting behind a long SELECT blocks every later query on the table — always SET lock_timeout for migrations
Explicit: LOCK TABLE t IN SHARE ROW EXCLUSIVE MODE; (rarely needed)

BEGIN;
SET LOCAL lock_timeout = '2s';           -- give up instead of stalling the app
SET LOCAL statement_timeout = '30s';
ALTER TABLE products ADD COLUMN IF NOT EXISTS weight_g int;
ROLLBACK;

Deadlocks

Cause: A locks row 1 then wants row 2; B locks row 2 then wants row 1
Postgres Response: After deadlock_timeout (1s) it detects the cycle and aborts one transaction with error 40P01
Prevention: Lock rows in a consistent order (e.g. ORDER BY id … FOR UPDATE), keep transactions short — see Deadlock

BEGIN;
-- Lock all rows you need up front, in id order
SELECT id FROM products WHERE id IN (7, 3, 9) ORDER BY id FOR UPDATE;
ROLLBACK;

Retry Logic for 40001 / 40P01

for attempt in 1..5:
    try:
        BEGIN ISOLATION LEVEL SERIALIZABLE
        ... statements ...
        COMMIT
        break
    except SQLSTATE in ('40001' serialization_failure, '40P01' deadlock_detected):
        ROLLBACK
        sleep(random backoff)

Interview Questions

“Default isolation level in Postgres?”: READ COMMITTED (per-statement snapshot)
“How does Postgres avoid readers blocking writers?”: MVCC — readers see a snapshot of committed row versions
“Build a job queue in Postgres?”: SELECT … FOR UPDATE SKIP LOCKED LIMIT 1 inside a transaction
“Prevent lost updates?”: Atomic UPDATE … SET x = x - 1, FOR UPDATE, optimistic versioning, or SERIALIZABLE + retry
“What is an advisory lock?”: An app-defined mutex keyed by a number, session- or transaction-scoped
“Why did my migration take the site down?”: ACCESS EXCLUSIVE lock queued behind a long query — use lock_timeout

Summary

Transactions: BEGIN/COMMIT/ROLLBACK; errors abort the block; SAVEPOINT for partial recovery
Isolation: READ COMMITTED default; REPEATABLE READ = transaction snapshot; SERIALIZABLE = full safety + retries
Row Locks: FOR UPDATE / FOR SHARE / NOWAIT / SKIP LOCKED — SKIP LOCKED powers job queues
Advisory Locks: Application-level mutexes; prefer the xact variant with poolers
Operational Safety: lock_timeout for DDL, consistent lock ordering, retry on 40001/40P01

Kaushik's Notes

Explorer

Transactions and Locking

Overview

BEGIN, COMMIT, ROLLBACK

Basics

SAVEPOINT

Isolation Levels

Read Phenomena

Postgres Behaviour

Setting the Level

Lost Update and How to Prevent It

Explicit Row Locks

Lock Strengths

Job Queue With FOR UPDATE SKIP LOCKED

Advisory Locks

Concept

Table Locks and DDL

Deadlocks

Retry Logic for 40001 / 40P01

Interview Questions

Summary

Graph View

Table of Contents

Backlinks