Mastering Transaction Isolation: What Every Developer Needs to Know

Concurrent transactions are the bread‑and‑butter of modern applications, but if they run without any guardrails they can corrupt data in subtle ways.
When you move money, book tickets, or update inventory, you need guarantees that one operation won’t see or overwrite another’s intermediate state.
Understanding isolation levels lets you strike the right balance between safety and throughput.

TL;DR

• Isolation protects data by defining what a transaction can see from others.
• PostgreSQL offers four standard levels: Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
• Choose the lowest level that satisfies your business rules; higher levels increase locking and can hurt performance.
• A simple account‑transfer demo shows how lost updates occur at lower levels and disappear at Serializable.
• Common pitfalls: assuming defaults are safe, mixing levels in a session, and over‑relying on isolation without application‑level safeguards.

The Core Problem: Why Isolation Matters

When two or more transactions touch the same rows, they can interfere.
If Transaction A reads a balance, Transaction B updates it, and Transaction A writes back a new balance, the update from B may be lost—this is a lost update.
Other anomalies include:

• Dirty read – reading uncommitted data that may later roll back.
• Non‑repeatable read – a subsequent read inside the same transaction returns a different value.
• Phantom read – a query that returns a different set of rows between executions.

Isolation guarantees that each transaction sees a consistent snapshot of the database, preventing these anomalies from propagating into the application’s state.

The Four Classic Isolation Levels

Why PostgreSQL uses MVCC
PostgreSQL’s Multi‑Version Concurrency Control (MVCC) allows readers to see a snapshot without blocking writers. Higher isolation levels add lock checks on writes to enforce the guarantees above.

Real‑World Scenarios

These examples illustrate that the “right” level depends on the tolerance for inconsistency and the cost of locking.

Picking the Right Level

1. Start low – begin with Read Committed and only bump up if you observe anomalies.
2. Assess contention – high contention may turn a Serializable transaction into a bottleneck.
3. Use hints – PostgreSQL lets you set the isolation level per transaction or per session (SET TRANSACTION ISOLATION LEVEL ...).
4. Monitor – enable pg_stat_activity and look for long‑running locks or deadlocks; log anomalies when they happen.
5. Iterate – adjust level, re‑measure latency and throughput, and repeat until you hit an acceptable trade‑off.

Code in Action

Below is a minimal Python script using psycopg2 that demonstrates how the same transfer logic behaves under different isolation levels.
The demo intentionally runs two concurrent transfers that would conflict if isolation is too weak.

#!/usr/bin/env python3
import psycopg2
import threading
import time

DSN = "dbname=demo user=demo password=demo host=localhost"

# Create a simple accounts table for the demo
def setup():
    with psycopg2.connect(DSN) as conn:
        with conn.cursor() as cur:
            cur.execute("""
                DROP TABLE IF EXISTS accounts;
                CREATE TABLE accounts (
                    id SERIAL PRIMARY KEY,
                    balance NUMERIC NOT NULL
                );
                INSERT INTO accounts (balance) VALUES (100), (100);
            """)
        conn.commit()

# Transfer amount from src to dst inside a transaction
def transfer(conn, src_id, dst_id, amount):
    with conn:
        with conn.cursor() as cur:
            # Lock the source row for update
            cur.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE;", (src_id,))
            src_bal = cur.fetchone()[0]
            if src_bal < amount:
                raise Exception("Insufficient funds")

            cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s;",
                        (amount, src_id))
            cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s;",
                        (amount, dst_id))

# Run two transfers concurrently
def run_test(isolation_level):
    print(f"\n=== Isolation level: {isolation_level} ===")
    conn1 = psycopg2.connect(DSN)
    conn2 = psycopg2.connect(DSN)

    # Set the isolation level for each connection
    conn1.set_isolation_level(isolation_level)
    conn2.set_isolation_level(isolation_level)

    t1 = threading.Thread(target=transfer, args=(conn1, 1, 2, 60))
    t2 = threading.Thread(target=transfer, args=(conn2, 2, 1, 60))

    t1.start()
    t2.start()
    t1.join()
    t2.join()

    with psycopg2.connect(DSN) as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT id, balance FROM accounts ORDER BY id;")
            print(cur.fetchall())

if __name__ == "__main__":
    setup()
    # psycopg2 constants:
    # 0 = READ UNCOMMITTED (treated as READ COMMITTED by PostgreSQL)
    # 1 = READ COMMITTED
    # 2 = REPEATABLE READ
    # 3 = SERIALIZABLE
    run_test(psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED)
    run_test(psycopg2.extensions.ISOLATION_LEVEL_REPEATABLE_READ)
    run_test(psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE)

What the script does

1. Setup – two accounts, each with a balance of 100.
2. Transfer – each thread debits 60 from one account and credits it to the other.
3. Concurrency – the two threads run at the same time, potentially causing a lost update.
4. Isolation levels – the script runs the test three times, once per isolation level.

Observed behavior

The loss of 20 in the first two runs is a classic lost update.
Serializable prevented it by enforcing a strict order or aborting one of the conflicting transactions.

Common Pitfalls and Trade‑Offs

Key takeaways

• Isolation levels define the scope of consistency a transaction guarantees.
• PostgreSQL’s default Read Committed is a safe starting point but does not guard against lost updates.
• Repeatable Read protects against non‑repeatable reads but not phantoms; Serializable offers full isolation at a higher cost.
• Real‑world systems choose isolation based on tolerance for anomalies versus performance demands.
• The simple account‑transfer demo shows that only Serializable can guarantee correctness when two transactions modify the same rows concurrently.
• Always measure the impact of isolation changes on latency, lock contention, and deadlock frequency before deploying to production.