ACID & Isolation Levels in Database Systems

ACID properties are the contract your database makes with you. Isolation levels let you decide how strict that contract is — and the tradeoffs are more nuanced than most developers realize.

Introduction

So I’m a Frontend guy but lately been diving into Backend and here is what I learned from Chapter one of Hussien Nasser’s database course.

Every time you hit “transfer” in your banking app, something invisible has to go right. Your balance drops, the other person’s balance rises, and the whole thing either works or it doesn’t. No halfway states. No phantom money.

That guarantee comes from your database’s ACID properties — and more specifically, from how it handles transaction isolation. Most developers have a vague sense of what ACID means, but when you ask them to explain the difference between Repeatable Read and Serializable?

Topics Covered

• The four ACID properties — with concrete examples
• What transactions actually are
• The four isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable
• Dirty reads, non-repeatable reads, and phantom reads
• How PostgreSQL’s snapshot isolation goes beyond the SQL standard
• Repeatable Read vs. Serializable — the real difference

What Is ACID?

ACID is an acronym for four properties that database transactions must guarantee to keep your data safe. Let’s walk through each one.

Atomicity — All or Nothing

A transaction is a single unit of work. Either every operation in it succeeds, or none of them do. There’s no “half a transfer.”

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

If the second UPDATE fails (say, account 2 doesn’t exist and there’s a constraint), PostgreSQL rolls back the first one too. The money doesn’t vanish.

Atomicity: A transaction is indivisible. It either fully completes or fully rolls back — nothing in between.

Consistency — Valid State to Valid State

The database moves from one valid state to another. Constraints, foreign keys, triggers — all of them must hold after a transaction finishes. If a transaction would violate any rule, it gets rejected.

-- Suppose we have: CHECK (balance >= 0)
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
-- If account 1 only has 3000, this violates the CHECK constraint
-- PostgreSQL aborts the transaction
COMMIT;

You define the rules. The database enforces them.

Isolation — Transactions Don’t Step on Each Other

This is the big one. When two transactions run at the same time, isolation controls how much they can see of each other’s work. We’ll spend most of this article here.

Durability — Committed Means Committed

Once a transaction is committed, it’s permanent. Even if the server crashes a millisecond later, that data is safe. PostgreSQL achieves this through Write-Ahead Logging (WAL) — changes are written to a log on disk before the transaction reports success.

Transactions: A Quick Refresher

A transaction is a sequence of SQL statements that execute as a single logical unit. In PostgreSQL, you wrap them explicitly:

BEGIN;
-- your SQL statements here
COMMIT;   -- or ROLLBACK; to undo everything

If you don’t use BEGIN, PostgreSQL wraps each statement in its own implicit transaction. That’s fine for single operations, but the moment you need two things to happen together — that’s when explicit transactions matter.

The Isolation Problem

OK so we know what ACID is. But the tricky part is isolation. When multiple transactions run concurrently, three specific problems can appear:

Dirty Read

You read data that another transaction wrote but hasn’t committed yet. If that transaction rolls back, you just read data that never actually existed.

sequenceDiagram
    participant TxA as Transaction A
    participant DB as Database
    participant TxB as Transaction B

    TxA->>DB: UPDATE balance = 500 (not committed)
    TxB->>DB: SELECT balance
    DB-->>TxB: 500 (dirty!)
    TxA->>DB: ROLLBACK
    Note over TxB: TxB read a value that<br/>never actually existed

Non-Repeatable Read

You read a row, another transaction modifies and commits it, then you read it again — and get a different value. Same row, same transaction, different results.

sequenceDiagram
    participant TxA as Transaction A
    participant DB as Database
    participant TxB as Transaction B

    TxA->>DB: SELECT balance WHERE id=1
    DB-->>TxA: 100
    TxB->>DB: UPDATE balance = 200 WHERE id=1
    TxB->>DB: COMMIT
    TxA->>DB: SELECT balance WHERE id=1
    DB-->>TxA: 200 (different!)
    Note over TxA: Same row, same transaction,<br/>two different values

Phantom Read

You run a query, another transaction inserts (or deletes) rows that match your WHERE clause, and when you run the same query again — you get different rows back.

sequenceDiagram
    participant TxA as Transaction A
    participant DB as Database
    participant TxB as Transaction B

    TxA->>DB: SELECT * WHERE age > 20
    DB-->>TxA: 5 rows
    TxB->>DB: INSERT (age=25)
    TxB->>DB: COMMIT
    TxA->>DB: SELECT * WHERE age > 20
    DB-->>TxA: 6 rows (phantom!)
    Note over TxA: A new row appeared<br/>mid-transaction

These three anomalies are what isolation levels exist to prevent. The question is: how many of them do you want to block, and what performance cost are you willing to pay?

The Four Isolation Levels

The SQL standard defines four isolation levels, each preventing more anomalies than the last. Let’s walk through them with PostgreSQL examples.

Read Uncommitted — The Wild West

A transaction can read data that other transactions haven’t committed yet. This is the lowest isolation level.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

The problem: dirty reads. You might read data that gets rolled back a moment later.

Here’s the thing though — PostgreSQL doesn’t actually implement Read Uncommitted. If you set it, PostgreSQL silently upgrades you to Read Committed. The PostgreSQL team decided dirty reads are never worth it, and honestly? They’re right. You’ll almost never find a good reason to read uncommitted data.

PostgreSQL quirk: READ UNCOMMITTED behaves identically to READ COMMITTED in PostgreSQL. You cannot get dirty reads no matter what you do.

Read Committed — The PostgreSQL Default

This is what you’re using right now if you haven’t changed anything. A transaction only sees data that’s been committed at the time each statement runs.

-- This is the default, but you can be explicit:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

No dirty reads. But you can get non-repeatable reads. Let’s see it in action.

Open two psql terminals:

Terminal 1:

BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Returns: 1000

Terminal 2:

BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

Terminal 1 (continued):

SELECT balance FROM accounts WHERE id = 1;
-- Returns: 500 (changed!)
COMMIT;

The first transaction read the same row twice and got different values. That’s a non-repeatable read. Each SELECT in Read Committed sees a fresh snapshot of committed data at the time that statement runs.

Read Committed: Each statement sees a new snapshot. You always read committed data, but what’s committed can change between statements.

For most CRUD apps, this is perfectly fine. But when you need consistency within a transaction — keep reading.

Repeatable Read — Snapshot Isolation

Now it gets interesting. Repeatable Read gives your transaction a snapshot of the database taken at the start of the transaction. Every query inside that transaction sees the same consistent snapshot, no matter what other transactions do.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Let’s redo the experiment:

Terminal 1:

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

Terminal 2:

BEGIN;
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

Terminal 1 (continued):

SELECT balance FROM accounts WHERE id = 1;
-- Still returns: 1000!
COMMIT;

Same experiment, different result. Transaction A sees 1000 both times because it’s reading from its snapshot — it’s completely oblivious to Transaction B’s commit.

PostgreSQL Goes Beyond the Standard

Here’s where PostgreSQL gets interesting. The SQL standard says Repeatable Read must prevent dirty reads and non-repeatable reads, but it’s allowed to have phantom reads (new rows appearing). PostgreSQL’s implementation actually prevents phantoms too.

Why? Because PostgreSQL uses Snapshot Isolation (SI) for Repeatable Read. You get a snapshot of the entire database at transaction start — not just the rows you’ve read, but everything. New rows inserted by other transactions are invisible to you.

-- Terminal 1
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM users WHERE age > 20;
-- Returns: 5

-- Terminal 2 (meanwhile)
-- INSERT INTO users (name, age) VALUES ('Alice', 25);
-- COMMIT;

-- Terminal 1 (continued)
SELECT count(*) FROM users WHERE age > 20;
-- Still returns: 5 (no phantom!)
COMMIT;

PostgreSQL’s Repeatable Read uses snapshot isolation, which prevents phantom reads — even though the SQL standard doesn’t require it.

But There’s a Catch

Snapshot Isolation isn’t free. If your transaction tries to modify a row that another transaction has already modified and committed since your snapshot was taken, PostgreSQL will abort your transaction with a serialization error:

ERROR: could not serialize access due to concurrent update

You’ll need to retry the transaction. This is the price of consistency.

Serializable — Full Correctness

Serializable is the highest isolation level. It guarantees that the result of concurrent transactions is equivalent to some serial ordering of those transactions. As if they ran one at a time, one after another.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

So what does Serializable catch that Repeatable Read doesn’t? This is the question everyone asks, and the answer is subtle.

Repeatable Read vs. Serializable — The Real Difference

Both prevent dirty reads, non-repeatable reads, and phantom reads (in PostgreSQL). So what’s left?

The answer: write skew anomalies.

The Write Skew Problem

Write skew happens when two transactions read overlapping data, each make a decision based on what they read, and their combined writes create an inconsistent state — even though each transaction individually looks correct.

Here’s the classic example. Imagine a hospital scheduling system where at least one doctor must always be on call:

-- Setup
CREATE TABLE doctors (
    name TEXT PRIMARY KEY,
    on_call BOOLEAN
);
INSERT INTO doctors VALUES ('Alice', true), ('Bob', true);
-- Rule: at least one doctor must be on_call

Now watch what happens with Repeatable Read:

sequenceDiagram
    participant TxA as Tx A (Alice)
    participant DB as Database
    participant TxB as Tx B (Bob)

    Note over DB: Alice=on_call, Bob=on_call

    TxA->>DB: SELECT count(*) FROM doctors<br/>WHERE on_call = true
    DB-->>TxA: 2 (safe to go off-call)

    TxB->>DB: SELECT count(*) FROM doctors<br/>WHERE on_call = true
    DB-->>TxB: 2 (safe to go off-call)

    TxA->>DB: UPDATE doctors SET on_call=false<br/>WHERE name='Alice'
    TxB->>DB: UPDATE doctors SET on_call=false<br/>WHERE name='Bob'

    TxA->>DB: COMMIT
    TxB->>DB: COMMIT

    Note over DB: Alice=off, Bob=off<br/>Nobody is on call!

Terminal 1:

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true;
-- Returns: 2 — safe to take Alice off call
UPDATE doctors SET on_call = false WHERE name = 'Alice';

Terminal 2 (simultaneously):

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true;
-- Returns: 2 — safe to take Bob off call
UPDATE doctors SET on_call = false WHERE name = 'Bob';

Both commit. Now nobody is on call. Each transaction looked at the same snapshot, both saw 2 doctors on call, both decided it was safe to remove one. Neither transaction modified a row the other modified, so there’s no conflict detected. Repeatable Read allows this.

Serializable Catches It

Now let’s try the same scenario with Serializable:

Terminal 1:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call = true;
-- Returns: 2
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT;
-- Success!

Terminal 2:

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call = true;
-- Returns: 2
UPDATE doctors SET on_call = false WHERE name = 'Bob';
COMMIT;
-- ERROR: could not serialize access due to read/write dependencies
-- among transactions

PostgreSQL detects that these two transactions, if run serially in any order, would produce a different result than running them concurrently. It aborts one of them. Your application retries the failed transaction, re-reads the count (now 1), and correctly decides Bob can’t go off-call.

The key difference: Repeatable Read prevents you from seeing changes. Serializable prevents you from making changes that would be impossible under any serial execution order.

PostgreSQL implements Serializable using Serializable Snapshot Isolation (SSI) — it tracks read and write dependencies between transactions and aborts any transaction that would create a cycle in the dependency graph.

The Anomaly Summary Table

* PostgreSQL treats Read Uncommitted as Read Committed, so in practice these don’t happen.

** The SQL standard allows phantoms at Repeatable Read, but PostgreSQL’s snapshot isolation prevents them.

When to Use What

Read Committed (the default) — Fine for most applications. CRUD operations, web apps, anything where individual statements don’t need to see a consistent view across the transaction. This is the right choice 90% of the time.

Repeatable Read — When you need consistent reads across a transaction. Financial reports where totals must add up. Balance calculations. Any time you read data, make a decision, and read more data — and all of it needs to agree.

Serializable — When correctness is non-negotiable and you can handle retries. Financial transfers. Inventory management. Booking systems. Anything where a write skew could cause a real-world problem.

Read Uncommitted — Almost never. PostgreSQL treats it as Read Committed anyway. If another database offers it and you’re considering using it… don’t.

Design principle: Start with Read Committed. Move to Repeatable Read when you need snapshot consistency. Move to Serializable when you need behavioral correctness across transactions. Always build retry logic when using higher isolation levels.

Key Takeaways

• ACID is the set of guarantees that make database transactions reliable: Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (transactions don’t interfere), Durability (committed data survives crashes).
• Isolation levels are the tunable knob within ACID — you trade performance for correctness.
• Read Committed sees fresh committed data per-statement. Good default.
• Repeatable Read gives you a snapshot at transaction start. PostgreSQL’s version also prevents phantom reads.
• Serializable ensures transactions behave as if they ran one at a time. It catches write skew that Repeatable Read misses.
• PostgreSQL’s implementations often exceed the SQL standard’s requirements — Read Uncommitted becomes Read Committed, Repeatable Read prevents phantoms.
• Higher isolation = more aborted transactions. Always implement retry logic when using Repeatable Read or Serializable.