Database Transaction Isolation Levels Explained
A practical breakdown of read uncommitted, read committed, repeatable read and serializable: the anomalies each prevents and what they cost you.
Transaction isolation levels determine what data a transaction can see while other transactions are running concurrently. Choose too low, and your application reads inconsistent data. Choose too high, and your database serializes everything, destroying throughput. Understanding the trade-offs is essential for building correct concurrent systems.
Most developers use whatever the database default is and never think about it. That works until it does not — until a reporting query reads a partially-updated order, or two concurrent transfers overdraw an account.
The Four Isolation Levels
SQL defines four isolation levels, each protecting against progressively more anomalies. Higher isolation means more safety but less concurrency.
-- PostgreSQL: Set isolation level for a transaction
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- ... your queries ...
COMMIT;
-- Or set the default for the session
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;// TypeScript with a query builder — setting isolation per transaction
async function transferFunds(
db: Database,
fromId: string,
toId: string,
amount: number
): Promise<void> {
await db.transaction(
async (tx) => {
const from = await tx.query(
'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
[fromId]
);
if (from.rows[0].balance < amount) {
throw new Error('Insufficient funds');
}
await tx.query(
'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
[amount, fromId]
);
await tx.query(
'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
[amount, toId]
);
},
{ isolationLevel: 'serializable' }
);
}Read Uncommitted: See Everything
Read uncommitted allows a transaction to read data that another transaction has written but not yet committed. This is the "dirty read" problem. If the other transaction rolls back, your transaction read data that never existed.
-- Session A: Starts a transaction, updates a price
BEGIN;
UPDATE products SET price = 999.99 WHERE id = 42;
-- Has NOT committed yet
-- Session B (READ UNCOMMITTED): Can see the uncommitted price
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT price FROM products WHERE id = 42;
-- Returns 999.99 (the UNCOMMITTED value)
-- Session A: Rolls back!
ROLLBACK;
-- Session B just used a price that never existed.
-- If this was an order calculation, the total is wrong.In practice, almost no one uses read uncommitted. PostgreSQL does not even implement it — setting it gives you read committed instead. The risk of dirty reads is too high for virtually any use case.
Read Committed: The Common Default
Read committed is the default in PostgreSQL and most databases. Each statement within a transaction sees only data committed before that statement started. No dirty reads. But you can still see different results if you run the same query twice within one transaction.
-- The "non-repeatable read" problem with READ COMMITTED
-- Session A:
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- Returns 1000
-- Session B (in between A's two queries):
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;
-- Session A (same transaction, second read):
SELECT balance FROM accounts WHERE id = 1;
-- Returns 500 (different from the first read!)
COMMIT;
-- Session A saw two different values for the same row
-- in the same transaction. This is a non-repeatable read.// ❌ Bug: read committed allows non-repeatable reads
async function generateReport(db: Database): Promise<Report> {
// Read committed (default)
const totalOrders = await db.query(
'SELECT SUM(amount) FROM orders WHERE status = $1',
['completed']
);
// Another transaction inserts a completed order HERE
const orderCount = await db.query(
'SELECT COUNT(*) FROM orders WHERE status = $1',
['completed']
);
// totalOrders / orderCount gives wrong average
// because the two queries saw different data
return {
total: totalOrders.rows[0].sum,
count: orderCount.rows[0].count,
average: totalOrders.rows[0].sum / orderCount.rows[0].count,
};
}
// ✅ Fix: use repeatable read for consistent snapshots
async function generateReport(db: Database): Promise<Report> {
return db.transaction(async (tx) => {
const totalOrders = await tx.query(
'SELECT SUM(amount) FROM orders WHERE status = $1',
['completed']
);
const orderCount = await tx.query(
'SELECT COUNT(*) FROM orders WHERE status = $1',
['completed']
);
// Both queries see the same snapshot — consistent results
return {
total: totalOrders.rows[0].sum,
count: orderCount.rows[0].count,
average: totalOrders.rows[0].sum / orderCount.rows[0].count,
};
}, { isolationLevel: 'repeatable read' });
}Repeatable Read: Snapshot Consistency
Repeatable read gives each transaction a consistent snapshot of the database as of the transaction's start. No dirty reads, no non-repeatable reads. But you can still see "phantom" rows — new rows inserted by other transactions that match your query predicate.
-- The "phantom read" problem (in databases that don't use MVCC)
-- Session A (REPEATABLE READ):
BEGIN;
SELECT * FROM orders WHERE total > 100;
-- Returns 5 rows
-- Session B:
INSERT INTO orders (total) VALUES (150);
COMMIT;
-- Session A:
SELECT * FROM orders WHERE total > 100;
-- In strict SQL standard: might return 6 rows (phantom row)
-- In PostgreSQL: still returns 5 rows (MVCC prevents phantoms)
COMMIT;PostgreSQL's implementation of repeatable read actually prevents phantom reads too, because it uses Multi-Version Concurrency Control (MVCC). Each transaction sees a snapshot from its start time, and new rows from other transactions are invisible regardless of the query.
-- PostgreSQL REPEATABLE READ also detects write conflicts
-- Session A:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Session B:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- Session A commits first:
COMMIT; -- Succeeds
-- Session B tries to commit:
COMMIT;
-- ERROR: could not serialize access due to concurrent update
-- Session B must retry the entire transactionSerializable: Maximum Safety
Serializable guarantees that the result of concurrent transactions is the same as if they ran one after another. This is the strongest guarantee. It prevents all anomalies, including write skew and other subtle bugs that repeatable read does not catch.
// Write skew example: two doctors trying to go off-call
// At least one doctor must always be on call
// ❌ With repeatable read — write skew is possible
async function goOffCall(db: Database, doctorId: string): Promise<void> {
await db.transaction(async (tx) => {
const onCallCount = await tx.query(
'SELECT COUNT(*) FROM doctors WHERE on_call = true'
);
if (Number(onCallCount.rows[0].count) > 1) {
// "Safe" to go off call — at least one other doctor is on call
await tx.query(
'UPDATE doctors SET on_call = false WHERE id = $1',
[doctorId]
);
}
// BUT: if two doctors run this simultaneously, both see count=2,
// both go off call, and nobody is on call!
}, { isolationLevel: 'repeatable read' });
}
// ✅ With serializable — write skew is prevented
async function goOffCall(db: Database, doctorId: string): Promise<void> {
await db.transaction(async (tx) => {
const onCallCount = await tx.query(
'SELECT COUNT(*) FROM doctors WHERE on_call = true'
);
if (Number(onCallCount.rows[0].count) > 1) {
await tx.query(
'UPDATE doctors SET on_call = false WHERE id = $1',
[doctorId]
);
}
}, { isolationLevel: 'serializable' });
// Serializable detects the dependency between the read and write
// One transaction succeeds, the other gets a serialization error
// and must retry — ensuring at least one doctor stays on call
}Choosing the Right Level
The decision depends on your specific use case. There is no universal "correct" level.
// Guidelines for choosing isolation level
const isolationGuide = {
readCommitted: {
useWhen: [
'Most CRUD operations',
'Writes that affect independent rows',
'Performance is critical and slight inconsistency is acceptable',
],
avoidWhen: [
'Reports that join multiple tables',
'Any read-then-write pattern',
],
},
repeatableRead: {
useWhen: [
'Reports and analytics queries',
'Any operation that reads the same data twice',
'Batch operations that need a consistent view',
],
avoidWhen: [
'High-contention write workloads (frequent retries)',
],
},
serializable: {
useWhen: [
'Financial transactions',
'Constraint enforcement that spans multiple rows',
'Any operation where correctness is non-negotiable',
],
avoidWhen: [
'High-throughput write workloads where retries are expensive',
'Read-only queries (repeatable read is sufficient)',
],
},
};Key Takeaways
- Read committed is safe for most operations — but any read-then-write pattern needs a higher level or explicit locking
- Repeatable read prevents non-repeatable reads — use it for reports, analytics, and any multi-query read operation
- Serializable prevents all anomalies — use it for financial transactions and multi-row constraint enforcement
- PostgreSQL's MVCC makes repeatable read stronger — it also prevents phantom reads, unlike the SQL standard minimum
- Higher isolation means more retries — serializable transactions will occasionally fail with serialization errors; your code must handle retries
- Default to read committed, escalate intentionally — know why you are choosing a higher level, not just "to be safe"
