ACID and Transaction Isolation Levels in PostgreSQL

← Back to the section

A database that "loses money on a crash" is not a database — it's a cache with ambitions. Let's start from scratch: what a transaction is, what ACID guarantees, and why choosing an isolation level is not theory but a practical decision with consequences.

What a transaction is and why you need it

Without transactions, any crash in the middle of an operation leaves the data in a broken state. Imagine a money transfer:

UPDATE account SET balance = balance - 100 WHERE id = 1;
-- the server crashes right here
UPDATE account SET balance = balance + 100 WHERE id = 2;

The first line ran, the second did not. The money vanished.

A transaction wraps several operations into a single indivisible action: either all of them apply, or none.

BEGIN;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

Now on any crash PostgreSQL either applies both lines or rolls both back.

ACID — the four transaction guarantees

ACID is an acronym describing exactly what a database guarantees.

A — Atomicity

A transaction is an indivisible action. If something goes wrong before COMMIT, all changes are rolled back automatically.

PostgreSQL provides this through the WAL (Write-Ahead Log) — a log of changes. Every operation is first written to the log, then applied to the data. On a server crash PostgreSQL reads the log and replays everything that made it to a commit.

It does not have to undo the unfinished work, and that sets PostgreSQL apart from many other databases. Changes of an uncommitted transaction physically sit in the table stamped with its number, and that transaction's status — committed or aborted — is stored next to it. A reading query looks at the status and simply does not see rows of an aborted transaction: nothing to roll back, no separate undo log. VACUUM collects the garbage later.

C — Consistency

After a transaction the database stays in a correct state — all constraints are satisfied: NOT NULL, FOREIGN KEY, UNIQUE, CHECK.

INSERT INTO product (category_id, price, name) VALUES (99, 200, 'Cake');
-- ERROR: insert or update on table "product" violates foreign key constraint
-- The whole transaction is rolled back

PostgreSQL only checks the constraints declared in the schema: what counts as "correct" data is decided by the architect, not the database.

I — Isolation

Concurrent transactions must not interfere with each other. This is the trickiest letter — in practice there are several isolation levels with different trade-offs between speed and strictness. We'll cover it in detail below.

D — Durability

If COMMIT returned success, the changes survive any failure: a server crash, a power outage, a reboot.

The same WAL: on COMMIT the log is flushed to disk (fsync), and only then does the client get a response. The data is on disk — even if the data pages haven't been written yet.

The synchronous_commit = off parameter speeds up commits but breaks this guarantee: a confirmed transaction can be lost on a crash. Appropriate only for non-critical data — metrics, access logs.

How PostgreSQL keeps transactions from blocking each other's reads — MVCC

The naive way to isolate transactions is locks: while one transaction writes, another can't read. That's slow.

PostgreSQL uses a different approach — MVCC (Multi-Version Concurrency Control). Every row stores several versions. An UPDATE doesn't change the row in place — it creates a new version, and the old one stays on disk. A concurrent transaction that started before the writer committed sees the old version. No read locks — each transaction sees its own consistent snapshot of the data.

Old row versions are removed by the background VACUUM process once they become invisible to all active transactions. This is where the difference between isolation levels comes from: the versions on disk are the same ones, and it's the level that decides when to take a snapshot.

The four isolation levels

Full isolation is the ideal, but an expensive one. The SQL standard defines four levels: the higher the level, the stricter the isolation and the larger the overhead.

The anomalies in the table are the "surprises" a transaction gets during concurrent work. Let's go through each.

Read Uncommitted — in PostgreSQL behaves like Read Committed

The standard allows this level to read uncommitted changes (dirty read): a transaction sees data that another hasn't saved yet. PostgreSQL never does this at any level — thanks to MVCC only committed versions are visible, and Read Uncommitted here equals Read Committed.

Read Committed — the default level

Each query inside a transaction sees the data committed as of the start of that query. Between queries the data may change.

Non-repeatable read — the same query returns different values twice: exactly what the first half of the loop above shows. Between two SELECTs of T1 someone else's UPDATE gets committed, and the second answer is a different one.

Phantom read — the same range returns a different number of rows:

-- T1
BEGIN;
SELECT COUNT(*) FROM product WHERE category_id = 1;
-- → 3

-- T2 inserted a new row and committed

-- T1 continues
SELECT COUNT(*) FROM product WHERE category_id = 1;
-- → 4  ← within the same transaction, more rows
COMMIT;

When it fits: most CRUD services with short transactions — read one row, update it, commit. If all the logic fits into a single query, no anomalies occur.

Repeatable Read — a snapshot for the whole transaction

The transaction takes a snapshot of the data at the first query and keeps it until the end — exactly what the second half of the loop above shows. Non-repeatable read and phantom read disappear.

Additionally: if two transactions try to update the same row, the second one gets a could not serialize access due to concurrent update error. The application must catch it and retry the transaction.

Write skew — what Repeatable Read doesn't catch

This is a subtle anomaly: two transactions read the same data, make independent decisions, and together break a rule that each one alone satisfied.

Example: the rule is "the total price of products in the 'Sweets' category doesn't drop below 250". Right now the total is 50 + 70 + 150 = 270 — a slack of just 20.

-- T1: lowers the candy price by 20
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(price) FROM product WHERE category_id = 1;
-- → 270, slack of 20 — lowering by 20 is allowed
UPDATE product SET price = 130 WHERE id = 3;

-- T2 in parallel: lowers the marmalade price by 20
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(price) FROM product WHERE category_id = 1;
-- → 270 (T1 hasn't committed yet, T2's snapshot doesn't see it)
UPDATE product SET price = 50 WHERE id = 2;

-- T1 COMMIT → total: 50 + 70 + 130 = 250, the rule holds
-- T2 COMMIT → total: 50 + 50 + 130 = 230  ← violation!

Both transactions changed different rows, so there was no UPDATE conflict — Repeatable Read didn't see the problem.

When it fits: analytical queries, period reports where you need a consistent slice of data as of the start.

Serializable — full isolation

Transactions execute as if they ran strictly one after another, in some order. No anomalies, including write skew.

PostgreSQL implements this through SSI (Serializable Snapshot Isolation) — tracking dependencies between transactions. If PostgreSQL sees that two concurrent transactions form a conflict, one of them is rolled back:

-- The same example, T1 committed first:
-- T2 COMMIT → ERROR: could not serialize access due to read/write dependencies
--                    among transactions

T2 is rolled back; on the retry it sees the new data after T1 and decides correctly.

The cost: PostgreSQL doesn't hold physical locks, but it rolls back transactions more often. The application must be able to retry a transaction on the SQLSTATE 40001 (serialization_failure) error. Read-only transactions on Serializable are cheaper than writing ones, but not free: they also take part in conflict tracking and can be aborted. SERIALIZABLE READ ONLY DEFERRABLE avoids that entirely — such a transaction waits for a provably safe snapshot and after that never aborts; the price is the wait at the start.

When it fits: money operations with invariants across several rows, uniqueness checks before insertion, any situation with write skew.

A snapshot is just a boundary

Every row version remembers two numbers: xmin — who created it, xmax — who replaced it (zero for a live one). A snapshot is a boundary: a version is visible when xmin is not above the boundary and xmax is either zero or above it. The whole difference between the levels is when that boundary is taken:

live example

import java.util.List;

public class SnapshotDemo {

    record RowVersion(long xmin, long xmax, int price) {}

    static Integer visible(List<RowVersion> row, long snapshot) {
        for (RowVersion v : row) {
            boolean born = v.xmin() <= snapshot;
            boolean dead = v.xmax() != 0 && v.xmax() <= snapshot;
            if (born && !dead) {
                return v.price();
            }
        }
        return null;
    }

    public static void main(String[] args) {
        long t1Start = 99;
        List<RowVersion> before = List.of(new RowVersion(50, 0, 150));
        List<RowVersion> after = List.of(
                new RowVersion(50, 150, 150),
                new RowVersion(150, 0, 200));

        System.out.println("T1, first SELECT:        " + visible(before, t1Start));
        System.out.println("Read Committed, second:  " + visible(after, 150));
        System.out.println("Repeatable Read, second: " + visible(after, t1Start));
    }
}

Read Committed takes the boundary again before every query — number 150 falls inside it, so 200 is visible. Repeatable Read keeps the original boundary 99 — 150 is visible.

Which level to choose when

You raise the level from the bottom up: each next one is more expensive. That gives a simple rule of thumb, and it answers the eternal "how do I transfer money correctly". If the rows to protect are known up front — the sender's account, the receiver's — take plain READ COMMITTED and lock them with FOR UPDATE, always in the same order everywhere in the code, or you get a deadlock. Cheap, predictable, visible in the logs. SERIALIZABLE is for cases where there is nothing to lock: the rule is checked over a set of rows, and the dangerous row is the one that doesn't exist yet — "no more than five active orders per customer". There the database tracks the conflict itself and aborts one of the transactions, and you retry it.

In short

• ACID — the four transaction guarantees: atomicity (all or nothing), consistency (constraints satisfied), isolation (concurrent transactions don't interfere), durability (data isn't lost after COMMIT).
• WAL provides atomicity and durability — the log is flushed to disk before the client is acknowledged.
• MVCC — each transaction sees a consistent snapshot without read locks; PostgreSQL never shows a dirty read at any level.
• Read Committed (default) — a snapshot per query; allows non-repeatable read and phantom read.
• Repeatable Read — a snapshot for the whole transaction; eliminates non-repeatable and phantom reads, but not write skew.
• Serializable — full isolation, including write skew; requires handling serialization errors in the application.

What to read next

• Isolation levels and anomalies — in more depth: error 40001, retries, stuck idle in transaction.
• Locks in PostgreSQL — SELECT FOR UPDATE, SKIP LOCKED, deadlocks.
• WAL in PostgreSQL — the log that atomicity and durability grow out of.
• VACUUM, autovacuum and bloat — where old row versions go.