Kaushik's Notes

Serializable vs Non-Repeatable Isolation Level

2 min read

Non-Repeatable Read

Definition: Occurs when a transaction reads the same row twice and gets different values, because another transaction modified and committed the row between the two reads.

Example:

-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- returns ₹1000
 
-- Meanwhile, Transaction B
BEGIN;
UPDATE accounts SET balance = 1200 WHERE id = 1;
COMMIT;
 
-- Back to Transaction A
SELECT balance FROM accounts WHERE id = 1;  -- returns ₹1200 now
  • Issue: Transaction A saw two different values without making any changes.
  • Occurs in: Read Committed isolation level.
  • Prevented in: Repeatable Read and Serializable.

Serializable

Definition: The highest isolation level. Ensures that transactions are executed in a serial order, i.e., one after the other — even if executed concurrently. It prevents all anomalies, including dirty reads, non-repeatable reads, and phantom reads.

Guarantee:

The outcome is as if transactions were executed one at a time, not concurrently.

How it prevents non-repeatable reads:

  • Lock-based engines (strict 2PL, e.g. InnoDB SERIALIZABLE with autocommit off, where plain SELECT becomes SELECT ... FOR SHARE): reads take shared locks, so concurrent writes to those rows wait.
  • MVCC engines (Postgres SSI): each transaction reads its snapshot, so re-reads return the same data. Other transactions can still modify the rows; if that creates a conflict that breaks serial order, one transaction is aborted with a serialization error (usually at commit) and must be retried.
  • In Postgres a plain SELECT never waits for row locks, even under SERIALIZABLE; it returns the last committed version. Only locking reads (FOR SHARE/FOR UPDATE) and UPDATE/DELETE of the same row wait.

Comparison Table

FeatureNon-Repeatable ReadSerializable
What it isAn anomaly / problemAn isolation level (solution)
Problem or Solution?ProblemSolution
Prevents Dirty Read❌ Not necessarily✅ Yes
Prevents Non-repeatable Read❌ No✅ Yes
Prevents Phantom Read❌ No✅ Yes
Used InLower isolation levels (Read Committed)Highest level of isolation

🧠 TL;DR

  • Non-repeatable read is a problem caused by another transaction changing data between two reads.
  • Serializable is an isolation level that prevents this and other issues by ensuring transactions behave as if run one by one.

Graph View

Backlinks