PostgreSQL Concurrency Control: Isolation Levels, Locks, and Real-World Race Conditions

If you've ever debugged a phantom read at 2 AM or traced a deadlock through ten layers of ORM magic, you know that concurrency in PostgreSQL is deceptively simple on the surface, and deeply nuanced underneath.

This article breaks down how PostgreSQL handles concurrent access, what each isolation level actually guarantees, and where real-world race conditions hide.

The Isolation Level Spectrum

PostgreSQL supports four SQL-standard isolation levels, but only three behave distinctly:

• Read Uncommitted: behaves identically to Read Committed in PostgreSQL. Dirty reads are never allowed.
• Read Committed: the default. Each statement sees only data committed before that statement began.
• Repeatable Read: the transaction sees a snapshot from the start of the transaction. No phantom reads within the snapshot.
• Serializable: the strongest guarantee. Transactions behave as if they executed sequentially, even under concurrent load.

Choosing the Right Level

Most applications run fine on Read Committed. Upgrade to Repeatable Read when you need consistent reads within a single transaction (e.g., generating a report where all queries must reflect the same point in time). Use Serializable when correctness outweighs throughput: financial transactions, inventory reservations, or anything involving read-then-write patterns.

The cost of Serializable is that PostgreSQL will abort transactions that it detects would cause serialization anomalies. Your application must be prepared to retry aborted transactions. This retry logic is non-trivial: you need to re-read any state, recompute the operation, and re-execute. Without automatic retry handling, Serializable isolation creates more problems than it solves.

-- Set isolation level per transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
 
COMMIT;
-- If this transaction conflicts with another, PostgreSQL raises:
-- ERROR: could not serialize access due to concurrent update
-- Your application must catch this and retry.

MVCC: How PostgreSQL Avoids Locks

PostgreSQL uses Multi-Version Concurrency Control (MVCC) to allow readers and writers to operate without blocking each other. Each row version carries metadata (xmin, xmax) that tracks which transaction created or deleted it. When you read, PostgreSQL checks visibility rules against your transaction's snapshot.

This means:

• Readers never block writers.
• Writers never block readers.
• Writers only block other writers touching the same row.

The tradeoff is dead tuple accumulation: old row versions that are no longer visible to any transaction. The VACUUM process reclaims this space. For a deep dive into how MVCC versioning works under the hood and how VACUUM reclaims dead tuples, see PostgreSQL MVCC and VACUUM Internals.

Understanding MVCC changes how you reason about query behavior. A long-running SELECT does not block any writes, but it does prevent VACUUM from cleaning up row versions that are still visible to that transaction's snapshot. A reporting query that runs for 30 minutes can cause significant bloat on write-heavy tables.

Real-World Race Condition: The Double-Spend

Consider a balance check before a withdrawal:

-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- returns 100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
 
-- Transaction B (concurrent)
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- also returns 100
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Under Read Committed, both transactions see balance = 100 and both succeed. Result: balance is -100. This is a classic lost update / double-spend.

The fix is to use SELECT ... FOR UPDATE to acquire a row-level lock, or upgrade to Serializable isolation where PostgreSQL detects the conflict automatically.

Prevention Strategies

• Pessimistic locking: SELECT ... FOR UPDATE acquires an exclusive row lock. The second transaction blocks until the first completes, then re-reads the current balance.
• Optimistic locking: Use a version column and check it in the WHERE clause of your UPDATE. If the version changed, no rows are updated and your application retries.
• Serializable isolation: PostgreSQL detects serialization anomalies and aborts one of the conflicting transactions.

Each strategy has different performance characteristics. Pessimistic locking is simplest but reduces concurrency on hot rows. Optimistic locking scales better under low contention but burns CPU on retries under high contention. Serializable isolation is the most correct but requires retry logic throughout your application.

-- Pessimistic: explicit row lock
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Transaction B is now blocked, waiting for this lock
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
 
-- Optimistic: version check
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND version = 3;
-- If 0 rows affected: version changed, re-read and retry

Deadlocks

Deadlocks occur when two transactions each hold a lock the other needs. PostgreSQL detects deadlocks automatically and aborts one of the transactions with ERROR: deadlock detected. This is not a bug in PostgreSQL; it's a safety mechanism.

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- waits for row 2
 
-- Transaction B (concurrent)
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- locks row 2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- waits for row 1 → DEADLOCK

The fix is consistent lock ordering: always acquire locks in the same order (e.g., by ascending account ID). If both transactions lock account 1 first, then account 2, they will never deadlock because the second transaction will wait for the first to finish before acquiring any locks.

In practice, ORMs make consistent ordering difficult because they abstract away the SQL execution order. If you're seeing deadlocks in production, query pg_stat_activity to identify the blocked queries and determine whether a consistent ordering convention would resolve them.

Advisory Locks for Application-Level Coordination

When row-level locks aren't granular enough, PostgreSQL offers advisory locks: application-defined locks that don't correspond to any table row:

-- Acquire an advisory lock (blocks until available)
SELECT pg_advisory_lock(12345);
 
-- Do critical work...
 
-- Release the lock
SELECT pg_advisory_unlock(12345);

Advisory locks are useful for coordinating background jobs, preventing duplicate processing, or implementing distributed mutexes within a single database. A common pattern is to use advisory locks to ensure only one instance of a cron job runs at a time:

-- Try to acquire without blocking. Returns true if acquired, false if not.
SELECT pg_try_advisory_lock(hashtext('nightly-report-job'));
-- If false, another instance is already running. Exit gracefully.

There are two flavors: session-level locks (pg_advisory_lock) that persist until the session ends or the lock is explicitly released, and transaction-level locks (pg_advisory_xact_lock) that are automatically released at COMMIT or ROLLBACK. Transaction-level locks are safer because you can't accidentally leak them.

Debugging Lock Contention

When queries are slow and you suspect lock contention, PostgreSQL provides visibility into active locks:

-- Find blocked queries and what's blocking them
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocked.wait_event_type
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks al ON al.locktype = bl.locktype
    AND al.relation = bl.relation
    AND al.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = al.pid
WHERE NOT bl.granted;

If you see the same rows appearing repeatedly in lock contention, it's a sign that your access pattern is serializing on a hot row. Consider redesigning the schema (sharding the hot row into buckets) or switching from pessimistic to optimistic locking.

Key Takeaways

• Default to Read Committed, but understand when to upgrade.
• Serializable is the safest but requires retry logic. Without it, your application will throw errors under contention instead of handling them gracefully.
• MVCC lets readers and writers coexist, but creates maintenance overhead (vacuum) and makes long-running queries a bloat risk.
• Race conditions hide in read-then-write patterns. Use FOR UPDATE, optimistic locking, or Serializable isolation.
• Prevent deadlocks with consistent lock ordering. Always acquire locks in the same order across all transactions.
• Advisory locks bridge the gap between row-level locking and application-level coordination. Prefer transaction-level locks to avoid leaks.
• Monitor lock contention in production. Query pg_stat_activity and pg_locks to identify blocking chains.
• Profile under realistic concurrency. Isolation bugs only surface under load.