Transactions & Concurrency Control

ACID Properties

Every PostgreSQL transaction guarantees four properties. Understanding them is essential for building reliable systems that handle concurrent access correctly.

Isolation Levels

PostgreSQL supports three isolation levels. Higher isolation prevents more anomalies but increases the chance of serialization failures that require retry logic.

-- Read Committed (default): each statement sees committed data
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 1;
-- Another transaction commits a change to this row...
SELECT balance FROM accounts WHERE id = 1;  -- may see new value
COMMIT;

-- Repeatable Read: snapshot at first query; no phantom reads
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM orders WHERE status = 'pending';
-- Same query returns same rows even if others commit changes
COMMIT;

-- Serializable: strongest; detects all anomalies
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- PostgreSQL will abort this transaction if it detects
-- a conflict that would break serializability
COMMIT;

Locking Strategies

Row-level locks prevent concurrent modifications. Use them carefully to avoid blocking or deadlocks.

-- FOR UPDATE: lock the row for modification
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- FOR SHARE: allow concurrent reads, block writes
SELECT * FROM products WHERE id = 5 FOR SHARE;

-- NOWAIT: fail immediately instead of waiting for lock
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- SKIP LOCKED: skip rows locked by other transactions
-- Perfect for job queues!
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

Building a Job Queue with SKIP LOCKED

-- Create a simple job queue table
CREATE TABLE job_queue (
  id SERIAL PRIMARY KEY,
  payload JSONB NOT NULL,
  status TEXT DEFAULT 'pending',
  locked_at TIMESTAMP,
  completed_at TIMESTAMP
);

-- Worker claims a job (safe for concurrent workers)
WITH next_job AS (
  SELECT id FROM job_queue
  WHERE status = 'pending'
  ORDER BY id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE job_queue SET
  status = 'processing',
  locked_at = NOW()
FROM next_job
WHERE job_queue.id = next_job.id
RETURNING job_queue.*;

Deadlock Prevention & Advisory Locks

-- Deadlock example: two transactions lock rows in different order
-- Transaction A: locks row 1, then tries row 2
-- Transaction B: locks row 2, then tries row 1
-- FIX: always lock rows in the same order (e.g., by primary key)

BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
-- Now process both accounts safely
COMMIT;

-- Advisory locks: application-level cooperative locking
-- Great for ensuring only one process runs a task
SELECT pg_advisory_lock(12345);  -- blocks until acquired
-- ... do exclusive work ...
SELECT pg_advisory_unlock(12345);

-- Try without blocking
SELECT pg_try_advisory_lock(12345);  -- returns true/false