0Pricing
SQL Academy · Lesson

Isolation Levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE

Compare the SQL standard isolation levels, PostgreSQL's implementation, and pick the right level for your workload.

Isolation Levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Four Standard Levels

SQL standard defines four isolation levels:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ
  • SERIALIZABLE

PostgreSQL implements three (READ UNCOMMITTED behaves like READ COMMITTED).

READ COMMITTED (PostgreSQL Default)

Each statement sees data committed before the statement started. You may see different snapshots within a single transaction.

-- Inside one transaction:
SELECT balance FROM accounts WHERE id = 1;  -- 100
-- meanwhile another tx commits balance=200
SELECT balance FROM accounts WHERE id = 1;  -- 200  (non-repeatable read)

REPEATABLE READ (Snapshot Isolation)

Sees a snapshot taken at the start of the transaction. Re-reading the same row always returns the same value. PostgreSQL's REPEATABLE READ also prevents phantoms.

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- 100
-- another tx commits balance=200
SELECT balance FROM accounts WHERE id = 1;  -- still 100
COMMIT;

SERIALIZABLE

Strongest: as if transactions ran one after another. PostgreSQL uses Serializable Snapshot Isolation (SSI) — detects concurrent dependencies and aborts conflicting transactions:

BEGIN ISOLATION LEVEL SERIALIZABLE;
...
COMMIT;
-- ERROR: could not serialize access due to read/write dependencies

Setting the Isolation Level

Per transaction, per session, or globally:

-- Per transaction:
BEGIN ISOLATION LEVEL SERIALIZABLE;

-- Per session:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Default for new transactions in this session:
SET default_transaction_isolation = 'repeatable read';

-- Global default in postgresql.conf:
-- default_transaction_isolation = 'read committed'

Anomalies Allowed by Each

LevelDirtyNon-RepeatablePhantom
READ UNCOMMITTEDmaybemaybemaybe
READ COMMITTEDnomaybemaybe
REPEATABLE READnonono (in PG)
SERIALIZABLEnonono

Performance Tradeoffs

  • READ COMMITTED — minimal overhead, default
  • REPEATABLE READ — same plan, snapshot consistency, no perf hit unless write conflicts
  • SERIALIZABLE — extra bookkeeping, can abort transactions; retry logic needed in app

Serializable Retry Pattern

SERIALIZABLE may abort with code 40001. Retry in a loop:

for (let i = 0; i < 3; i++) {
  try {
    await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
    // ... work ...
    await client.query('COMMIT');
    break;
  } catch (e) {
    if (e.code === '40001') continue;
    throw e;
  }
}

Reading a Consistent Snapshot

For long-running analytical queries that need consistent data, REPEATABLE READ is the right balance.

Write Conflict Behaviour

If two transactions modify the same row:

  • READ COMMITTED — the second waits, then re-reads the new row and tries again
  • REPEATABLE READ — the second aborts with "could not serialize" — app must retry

Pick the Right Level

Rules of thumb:

  • OLTP — READ COMMITTED (default)
  • Reports needing consistency — REPEATABLE READ
  • Money / inventory — SERIALIZABLE + retry, OR explicit locks (FOR UPDATE)

Explicit Locks Beat Isolation Sometimes

Often SELECT ... FOR UPDATE on READ COMMITTED is simpler than SERIALIZABLE for a single hot row.

Recap

Isolation levels trade safety for concurrency.

  • READ COMMITTED is the default
  • REPEATABLE READ for consistent reads
  • SERIALIZABLE for "as if serial" — needs retry logic

Quick Check

Which is PostgreSQL's default isolation level?

Frequently asked questions

Is the “Isolation Levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE” lesson free?

Yes — the full text of “Isolation Levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Isolation Levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE”?

Compare the SQL standard isolation levels, PostgreSQL's implementation, and pick the right level for your workload. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Isolation Levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. ACID Properties and Anomalies
  2. Isolation Levels: READ COMMITTED, REPEATABLE READ, SERIALIZABLE
  3. Deadlocks: Detection and Avoidance
  4. Optimistic vs Pessimistic Locking
← Back to SQL Academy