Advanced Transactions & Isolation

Reviewed & published by Brayan K

By the end of this lesson you'll be able to reason about what happens when two users hit the same data at once — choosing the right isolation level for each job, predicting dirty, non-repeatable and phantom reads, picking optimistic vs pessimistic locking, recovering part of a transaction with savepoints, and explaining why deadlocks happen.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Name the four isolation levels and the exact anomaly each one stops
• Spot dirty reads, non-repeatable reads, and phantom reads in real timelines
• Explain MVCC and why readers and writers don't block each other
• Choose between optimistic and pessimistic concurrency control
• Use SAVEPOINT and ROLLBACK TO for partial rollback
• Describe how a deadlock forms and how the database breaks it

Our Sample Table: accounts

Most examples move money between these two bank accounts. A version column is included for the optimistic-locking section. Keep the starting balances in mind as you read.

1. A Transaction Is an All-or-Nothing Unit

A transaction wraps several statements so they either all take effect or none do. You open one with BEGIN, make it permanent with COMMIT, or throw it all away with ROLLBACK. This is the "A" (atomicity) in ACID — the four guarantees (Atomicity, Consistency, Isolation, Durability) a database makes about your data.

A transaction is like a bank transfer at the counter. Debiting Alice and crediting Bob must happen together. If the till jams after the debit, the clerk tears up the slip (ROLLBACK) so money never vanishes into thin air. Only when both halves are done does the clerk stamp it (COMMIT).

-- The tables this lesson uses. Every later block queries them, and the
-- "Try it Yourself" button carries this setup along so each snippet runs.
CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    name    TEXT,
    balance REAL,
    version INTEGER          -- bumped on every write; optimistic locking reads it
);
INSERT INTO accounts (id, name, balance, version) VALUES
    (1, 'Alice', 500.00, 1),
    (2, 'Bob',   300.00, 1),
    (3, 'Cara',  120.00, 1);

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total       REAL,
    status      TEXT
);
INSERT INTO orders (id, customer_id, total, status) VALUES
    (1, 1,  49.98, 'paid'),
    (2, 2, 158.00, 'paid'),
    (3, 3,  32.00, 'pending');

CREATE TABLE payments (
    id     INTEGER PRIMARY KEY,
    payer  TEXT,
    amount REAL,
    status TEXT
);
INSERT INTO payments (id, payer, amount, status) VALUES
    (1, 'Alice',  80.00, 'settled'),
    (2, 'Bob',   150.00, 'settled'),
    (3, 'Cara',   45.00, 'pending'),
    (4, 'Alice',  99.00, 'settled'),
    (5, 'Bob',   240.00, 'pending');

-- A transaction groups several statements into ONE all-or-nothing unit
BEGIN;                                   -- open the transaction

UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- take £100 from Alice
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- give £100 to Bob

COMMIT;                                  -- make BOTH changes permanent, together

-- If anything failed before COMMIT you would run ROLLBACK instead,
-- and NEITHER update would happen. The account totals can never end up
-- half-applied — that is the "atomic" A in ACID.

2. Isolation Levels — the Correctness ↔ Speed Dial

The "I" in ACID is isolation: how much one running transaction is shielded from the changes of others happening at the same time. SQL defines four levels. Turn the dial up and you prevent more anomalies but pay with more waiting, blocking, and retries; turn it down and you go faster but expose yourself to weirder results.

Think of it like noise-cancelling headphones. SERIALIZABLE is full cancellation — total quiet, but it drains the battery. READ UNCOMMITTED is headphones off — zero overhead, but every distraction leaks in.

An anomaly is a surprising result you can only get when transactions overlap. There are three classic ones, and each isolation level is defined by which it forbids:

• Dirty read — you read another transaction's uncommitted change, which may later be rolled back (so you read data that never officially existed).
• Non-repeatable read — you read a row twice and get different values, because someone committed an UPDATE in between.
• Phantom read — you run the same WHERE twice and a new row appears (or vanishes), because someone committed an INSERT/DELETE in between.

2a. READ UNCOMMITTED — allows dirty reads

The weakest level imposes almost no isolation: you can see changes other transactions have made but not yet committed. If they roll back, you acted on a value that was never real — a dirty read.

-- READ UNCOMMITTED — the weakest level. Allows a DIRTY READ:
-- you can see another transaction's changes BEFORE it commits.

-- Session B:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- sees 0 ...
-- ... but Session A (which set it to 0) might ROLLBACK!
-- Then the 0 you read never truly existed → a "dirty read".
COMMIT;

-- Almost never the right choice. PostgreSQL doesn't even
-- implement it — asking for it silently gives READ COMMITTED.

2b. READ COMMITTED — the sensible default

Now you only ever see committed data, so dirty reads are gone. But every statement takes a fresh look at the database, so reading the same row twice can still return different values: a non-repeatable read. This is PostgreSQL's default and the right starting point for most apps.

-- READ COMMITTED — the PostgreSQL default. Prevents dirty reads,
-- but each statement sees a FRESH snapshot, so re-reading can change.

BEGIN;  -- defaults to READ COMMITTED
SELECT balance FROM accounts WHERE id = 1;   -- sees 1000

-- Meanwhile another transaction COMMITs: balance = 500

SELECT balance FROM accounts WHERE id = 1;   -- now sees 500!
-- Same query, same transaction, different answer:
-- this is a "non-repeatable read".
COMMIT;

2c. REPEATABLE READ — frozen snapshot

Here your transaction takes one consistent snapshot and reuses it: any row you read keeps the same value all the way to COMMIT, killing non-repeatable reads. The SQL standard still permits phantom rows at this level — though PostgreSQL's implementation happens to block those too.

-- REPEATABLE READ — the MySQL/InnoDB default.
-- Your snapshot is FROZEN at the first read, so rows you already
-- read keep their values for the whole transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1;   -- sees 1000

-- Another transaction COMMITs balance = 500

SELECT balance FROM accounts WHERE id = 1;   -- STILL sees 1000
-- Non-repeatable reads are gone. (In PostgreSQL this level also
-- blocks phantom rows; the SQL standard only requires SERIALIZABLE to.)
COMMIT;

2d. SERIALIZABLE — as if it ran alone

The strongest level guarantees the outcome is identical to running the transactions one at a time in some order — so it blocks all three anomalies, phantoms included. The price: when the database can't preserve that illusion, it aborts a transaction with a serialization failure, and your application must catch that error and retry. Reach for it only when correctness is non-negotiable (financial postings, inventory that must never oversell).

-- SERIALIZABLE — the strongest level. The database guarantees the
-- result is as if every transaction ran one-after-another, alone.
-- It prevents dirty, non-repeatable AND phantom reads.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 10
INSERT INTO orders (status) VALUES ('pending');
COMMIT;
-- If a concurrent transaction would break "as-if-sequential", PostgreSQL
-- aborts one with a serialization_failure (SQLSTATE 40001).
-- Your app must catch that error and RETRY the whole transaction.

Teaching device: two sessions on one timeline

The clearest way to see an anomaly is to lay two sessions side by side and read top to bottom — each row is a moment in time. Here is a non-repeatable read under READ COMMITTED: Session A reads the same row before and after Session B commits a change.

At t6 Session A re-reads and gets a different number than at t2 — the non-repeatable read. Run the very same timeline under REPEATABLE READ and t6 would still return 1000, because A's snapshot was frozen at t2.

Reaching for READ UNCOMMITTED "for speed." The performance gain is negligible on modern engines, and you risk acting on rolled-back data. Start at READ COMMITTED and only raise the level when a real anomaly bites.

Your Turn: pick the isolation level

Read the scenario in the comments and fill in the blank with the isolation level that fits. The expected answer (and the reasoning) is in the comments so you can check yourself.

-- 🎯 YOUR TURN — pick the right isolation level, then press "Try it Yourself"
-- Scenario: a nightly report runs many SELECTs and must see ONE consistent
-- snapshot of the whole database — no row may change underneath it, and no
-- new matching rows may appear — but it never writes anything.

SET TRANSACTION ISOLATION LEVEL ___;   -- 👉 the strongest level that guarantees
                                       --    an "as-if-alone" view (blocks phantoms too)
BEGIN;
SELECT SUM(total) FROM orders;
SELECT COUNT(*)   FROM orders;
COMMIT;

-- ✅ Expected: SERIALIZABLE
--    (REPEATABLE READ also freezes rows you already read, but the SQL
--     standard only guarantees SERIALIZABLE blocks phantom rows.)

3. MVCC vs Locking — How Snapshots Are Built

How does a database give each transaction its own snapshot? Two strategies exist. The old approach is locking: a reader takes a shared lock so writers must wait, which is correct but slow under load. The modern approach — used by PostgreSQL and MySQL's InnoDB — is MVCC (Multi-Version Concurrency Control): keep multiple versions of each row, and show each transaction the version that was current when its snapshot began.

The headline benefit of MVCC is that readers never block writers and writers never block readers. The catch is that superseded row versions ("dead tuples") accumulate and must be cleaned up by VACUUM (PostgreSQL's autovacuum usually handles this for you).

-- MVCC = Multi-Version Concurrency Control.
-- Instead of locking rows for readers, the engine keeps OLD VERSIONS
-- of each row and shows every transaction a consistent snapshot.

-- A row carries hidden columns: xmin (txn that created it),
--                               xmax (txn that deleted/replaced it).

-- Txn 100 inserts:   (data,     xmin=100, xmax=null)        ← current
-- Txn 200 updates:   (old_data, xmin=100, xmax=200)         ← now "dead"
--                    (new_data, xmin=200, xmax=null)        ← current

-- A reader that started at txn 150 still sees old_data,
-- because it was created before 150 (xmin=100) and replaced
-- after 150 (xmax=200). No locks, no waiting.

-- The payoff:
--   readers never block writers, writers never block readers.
-- The cost: dead row versions pile up and must be cleaned by VACUUM.
VACUUM ANALYZE orders;   -- reclaim dead tuples + refresh planner stats

4. Pessimistic vs Optimistic Concurrency

When two transactions might fight over the same row, you choose a strategy for handling the clash. The choice comes down to a single question: how often do you actually expect a conflict?

• Pessimistic — "a conflict is likely, so lock first." Use SELECT … FOR UPDATE to lock the rows before you touch them. Others wait. Best when contention is high (a single hot inventory row everyone buys).
• Optimistic — "a conflict is rare, so don't lock; just check at write time." Keep a version column and only update if the version is unchanged. If zero rows update, someone beat you — re-read and retry. Best when contention is low.

-- PESSIMISTIC concurrency: assume a conflict WILL happen, so lock first.
-- SELECT ... FOR UPDATE locks the matched rows until you COMMIT.

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;  -- lock row 1
-- Any other transaction that does FOR UPDATE on row 1 now WAITS here.
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- lock released

-- Good when conflicts are frequent (e.g. hot inventory rows):
-- you pay the wait cost up front instead of retrying.

-- OPTIMISTIC concurrency: assume conflicts are RARE, don't lock —
-- detect a clash at write time using a version column.

-- Read the row AND its version:
SELECT balance, version FROM accounts WHERE id = 1;   -- balance 1000, version 7

-- Write only if nobody changed it since (version still 7):
UPDATE accounts
SET    balance = balance - 100,
       version = version + 1
WHERE  id = 1 AND version = 7;

-- If 0 rows were updated, someone else got there first →
-- re-read and try again. Great for low-contention workloads.

5. SAVEPOINTs — Partial Rollback

A savepoint is a named bookmark inside an open transaction. ROLLBACK TO savepoint_name rewinds the work done after that bookmark while keeping everything before it — and crucially, the transaction stays open. It is an "undo" button for one step of a larger operation. RELEASE SAVEPOINT simply forgets a bookmark you no longer need.

-- SAVEPOINT = a named bookmark INSIDE a transaction.
-- ROLLBACK TO undoes work back to that bookmark WITHOUT ending the txn.

BEGIN;

INSERT INTO orders (id, customer_id, total, status)
VALUES (1001, 42, 300.00, 'processing');         -- step 1: always keep this

SAVEPOINT before_coupon;                          -- bookmark here

UPDATE orders SET total = total * 0.80 WHERE id = 1001;  -- step 2: try a discount
-- Oops — the coupon turned out to be invalid. Undo ONLY step 2:
ROLLBACK TO before_coupon;                         -- total is back to 300.00
-- The order row from step 1 is still here and the transaction is still open.

UPDATE orders SET status = 'confirmed' WHERE id = 1001;  -- step 3
COMMIT;   -- keeps step 1 + step 3; the rolled-back step 2 is gone.

-- Without savepoints, ANY error forces you to ROLLBACK the WHOLE thing.

Your Turn: add a savepoint

Two blanks: create a savepoint, then roll back to it so the bad fee is undone but the payment survives.

-- 🎯 YOUR TURN — add a SAVEPOINT, then roll back to it.
-- Goal: insert a payment, bookmark, then UNDO a bad fee line — but KEEP the payment.

BEGIN;
INSERT INTO payments (id, order_id, amount) VALUES (5, 1001, 240.00);  -- keep this

___ after_payment;                 -- 👉 create a savepoint named after_payment

UPDATE payments SET amount = amount + 9999 WHERE id = 5;   -- a mistaken fee
___ TO after_payment;              -- 👉 undo back to the savepoint (amount = 240 again)

COMMIT;

-- ✅ Expected: blank 1 = SAVEPOINT, blank 2 = ROLLBACK.
--    Final row: payment 5 with amount 240.00 (the +9999 fee is gone).

6. Deadlocks — When Two Transactions Wait Forever

A deadlock happens when two transactions each hold a lock the other one needs, so neither can move. The database detects the cycle and breaks it by aborting one transaction (the "victim") with a deadlock detected error; your app should catch it and retry. Read this timeline top to bottom:

Common Errors (and the fix)

• "could not serialize access due to concurrent update" (SQLSTATE 40001): expected under SERIALIZABLE/REPEATABLE READ — it isn't a bug. Catch it and retry the whole transaction from BEGIN.
• "deadlock detected": two transactions locked rows in opposite orders. Lock in a consistent order (e.g. lowest id first) and keep transactions short; then retry the victim.
• "SAVEPOINT can only be used in transaction blocks": you ran SAVEPOINT outside a transaction. Open one with BEGIN first — savepoints live inside a transaction.
• ROLLBACK TO a name that doesn't exist: "savepoint does not exist" — the name is misspelt, or you already did RELEASE SAVEPOINT (or rolled past it). Re-create the savepoint before rolling back to it.
• "idle in transaction" connections piling up: you ran BEGIN but never COMMIT/ROLLBACK. Open locks and old snapshots block VACUUM and other writers — always close every transaction.

📘 Quick Reference — isolation levels × anomalies

* Per the SQL standard. PostgreSQL also prevents phantom reads at REPEATABLE READ. "Possible" means the level allows the anomaly; "Prevented" means it forbids it.

Frequently Asked Questions

Q: Which isolation level should I use by default?

READ COMMITTED (PostgreSQL's default) is the right starting point for most applications. Only raise it to REPEATABLE READ or SERIALIZABLE when a specific anomaly would corrupt your logic — and be ready to retry on serialization failures.

Q: What's the difference between SERIALIZABLE and just locking everything?

Locking forces transactions to wait. SERIALIZABLE under MVCC lets them run concurrently and only aborts one if the result wouldn't match some serial order. You usually get more throughput, at the cost of having to retry the aborted transaction.

Q: Optimistic or pessimistic — how do I choose?

Estimate how often two writers hit the same row. Rare clashes favour optimistic (a cheap version check, retry on the odd miss). Frequent clashes favour pessimistic (FOR UPDATE), so you wait once instead of retrying repeatedly.

Q: Does ROLLBACK TO a savepoint end my transaction?

No. ROLLBACK TO only undoes work done after the savepoint; the transaction stays open and you keep going. A bare ROLLBACK (no TO) is what discards the entire transaction.

Mini-Challenge: Safe, Recoverable Transfer

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a Postgres playground to confirm.

-- 🎯 MINI-CHALLENGE — money transfer that is safe AND recoverable
-- Using ONLY what this lesson covered (BEGIN/COMMIT, SAVEPOINT, ROLLBACK TO):
--   1. Open a transaction.
--   2. Move £50 from account 1 to account 2 (two UPDATEs).
--   3. Set a SAVEPOINT called before_fee.
--   4. Charge a £2 fee to account 1, then change your mind and
--      ROLLBACK TO before_fee so the fee is undone.
--   5. COMMIT so the £50 transfer (but not the fee) is saved.
--
-- ✅ Expected: account 1 is down £50 (not £52), account 2 is up £50.

-- your transaction here

🎉 Lesson Complete

• ✅ A transaction is all-or-nothing: BEGIN … COMMIT / ROLLBACK
• ✅ The four isolation levels each forbid one more anomaly — dirty, non-repeatable, then phantom reads
• ✅ MVCC keeps row versions so readers and writers don't block each other (and VACUUM cleans up)
• ✅ Choose pessimistic locks for high contention, optimistic version checks for low
• ✅ SAVEPOINT + ROLLBACK TO give you partial rollback within a transaction
• ✅ Next: Locking & Deadlocks — go deeper on lock modes and how to prevent the cycles you saw here

Practice quiz

Continue this course

• Previous: Stored Procedures, Functions & Triggers (Advanced Use-Cases)
• Next: Locking, Deadlocks & High-Concurrency Performance Patterns — Understand row locks, table locks, and how to detect and prevent deadlocks
• Quick reference: SQL cheat sheet