Isolation Levels

~14 min · transactions, isolation

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

What isolation actually controls

Isolation level decides what a transaction sees of other transactions' in-progress work. Tighter isolation = stronger guarantees + more potential conflicts. Looser isolation = better concurrency + more chances to see weird intermediate states.

The four standard levels

Level	Dirty read	Non-repeatable read	Phantom read
READ UNCOMMITTED	Possible (not in PG)	Possible	Possible
READ COMMITTED (PG default)	No	Possible	Possible
REPEATABLE READ	No	No	No (in PG)
SERIALIZABLE	No	No	No

PostgreSQL specifics

READ UNCOMMITTED behaves identically to READ COMMITTED in PostgreSQL — there are no dirty reads. REPEATABLE READ in PostgreSQL prevents phantom reads (it's actually snapshot isolation). SERIALIZABLE adds true serializability via the SSI algorithm — pay for it with potential serialization failures that you must retry in your application.

Code

Set isolation explicitly·sql

BEGIN ISOLATION LEVEL REPEATABLE READ;
-- All statements in this transaction see a consistent snapshot
SELECT * FROM accounts;
-- ... time passes, other transactions commit ...
SELECT * FROM accounts;  -- same view as the first SELECT
COMMIT;

SERIALIZABLE — handle the retry·python

def transfer_with_retry(c, from_id, to_id, amount, attempts=5):
    for i in range(attempts):
        try:
            with c.transaction(isolation_level="serializable"):
                c.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, from_id))
                c.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, to_id))
            return  # success
        except psycopg.errors.SerializationFailure:
            continue
    raise RuntimeError("transfer failed after %d attempts" % attempts)

External links

PostgreSQL Isolation Levels

Exercise

Open two psql sessions. In session A: BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT a row. In session B: UPDATE the same row, COMMIT. Back in A: SELECT again — same value? Now in A: try to UPDATE the same row. What happens?

Progress

Progress is local-only — sign in to sync across devices.

← PreviousBEGIN, COMMIT, ROLLBACK Next →MVCC: How PostgreSQL Handles Concurrency

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.