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

Have you ever found yourself debugging a phantom read at 2 AM? If you are reading this, you probably know the struggle. Or maybe you have tried tracing a deadlock through ten different layers of ORM magic.

Now, you probably already understand that concurrency in this database is deceptively simple on the surface, yet deeply nuanced once you get into it.

I want to help you out, so I will break down exactly how concurrent access is handled in PostgreSQL. I will talk about what each isolation level actually guarantees, and I will show you exactly where real-world race conditions like to hide.

Let's dig in.

What Are the Isolation Levels, and What Does Each One Guarantee?

The SQL standard defines four isolation levels. In PostgreSQL, only three of them 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.

How Do You Pick the Right One?

Most applications run fine on Read Committed.

Upgrade to Repeatable Read when you need consistent reads within a single transaction. Generating a report where every query must reflect the same point in time is the obvious case.

Use Serializable when correctness outweighs throughput: financial transactions, inventory reservations, or anything built on a read-then-write pattern.

But there is (of course) a catch, and it is a steep one. PostgreSQL will abort any transaction it detects would cause a serialization anomaly, so your application has to be ready to retry.

Now, this retry logic is not trivial to build (at all). You need to re-read the state, recompute the operation, and re-execute it. 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.

How Does PostgreSQL Avoid Locking Everything?

PostgreSQL uses Multi-Version Concurrency Control (MVCC), which lets readers and writers work at the same time without blocking each other.

Each row version carries metadata, xmin and xmax, tracking which transaction created or deleted it. When you read, PostgreSQL checks visibility rules against your transaction's snapshot.

Three rules follow from that design:

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

So, what is the primary tradeoff here? The answer is simple. You get an accumulation of dead tuples, which are old row versions no longer visible to any transaction. Luckily, VACUUM is there to reclaim that wasted space. For how MVCC versioning works under the hood and how VACUUM gets the dead tuples back, see PostgreSQL MVCC and VACUUM Internals.

Understanding MVCC changes how you reason about query behaviour.

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 a write-heavy table.

What Does a Real Race Condition Look Like?

Let's look at the classic double-spend scenario: a balance check happening right 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. The result is a balance of -100. This is a classic lost update, or 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.

How Do You Prevent These Anomalies?

You have three 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.

Naturally, each strategy comes with different performance characteristics.

Pessimistic locking is, of course, the simplest one to implement, but it reduces concurrency on heavily accessed rows. Optimistic locking scales better under low contention and burns CPU on retries when contention is high. Serializable isolation is the most correct and demands 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

Why Do Two Transactions Freeze Each Other?

Deadlocks happen when two transactions each hold a lock the other needs.

Fortunately, PostgreSQL detects them automatically and resolves the standoff by aborting one transaction with ERROR: deadlock detected. Remember, this is not a bug. It is an important 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, for example by ascending account ID. If both transactions lock account 1 first and account 2 second, they can never deadlock, because the second transaction waits for the first to finish before acquiring anything.

Now, in practice, Object-Relational Mappers make consistent ordering quite difficult, because they abstract away the underlying SQL execution order. If you are seeing deadlocks in production, query pg_stat_activity to identify the blocked queries and work out whether an ordering convention would resolve them.

When Do You Need Application-Level Coordination?

When row-level locks are not granular enough, PostgreSQL offers advisory locks: application-defined locks that do not 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);

They are useful for coordinating background jobs, preventing duplicate processing, or implementing a distributed mutex inside a single database. The most common pattern is making sure 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.

Advisory locks come in two flavours. Session-level locks (pg_advisory_lock) persist until the session ends or the lock is explicitly released. Transaction-level locks (pg_advisory_xact_lock) are released automatically at COMMIT or ROLLBACK.

I always recommend transaction-level locks because they are much safer. You literally cannot accidentally leak them.

How Do You Track Down Lock Contention?

When queries are slow and you suspect lock contention, PostgreSQL gives you good visibility into what is actually holding what:

-- 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 notice the same rows appearing over and over in lock contention, that is a strong sign your access pattern is serializing on a hot row. Consider redesigning the schema, sharding that 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 vacuum overhead 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 when transactions actually overlap.