Quick answer
A database isolation level controls which effects of concurrent transactions are visible to each other. Stronger isolation removes more anomalies, but may add locking, aborts, latency, or reduced concurrency.
The familiar SQL levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Their names do not guarantee identical behavior across database engines. PostgreSQL uses multiversion concurrency control and treats Read Uncommitted as Read Committed; MySQL InnoDB defaults to Repeatable Read and adds next-key locking for some statements. Choose from the behavior documented by your engine, then test the business invariant under real concurrency.
Why isolation matters
Transactions give atomic commit and rollback, but two correct transactions can still produce a wrong combined result. Imagine two doctors are on call. Each transaction reads that the other doctor is available, then marks its own doctor off call. Both commits can succeed under snapshot isolation, leaving nobody on call. This is write skew.
Isolation is therefore about interleavings. The database must decide whether a transaction reads a statement-level snapshot, a transaction-level snapshot, locked rows, or a serialization order. The application must decide which anomalies would violate its domain.
The main anomalies
A dirty read observes another transaction’s uncommitted value. If the writer rolls back, the reader acted on data that never became durable.
A nonrepeatable read happens when the same transaction reads a row twice and sees a committed change made between those statements.
A phantom appears when a repeated predicate query returns a different set of rows because another transaction inserted, deleted, or updated matching data.
A lost update occurs when two writers read the same value and one overwrites the other’s change. Atomic updates, version checks, locks, and engine-specific conflict detection can prevent it.
Write skew happens when transactions update different rows after reading a shared condition. Row-level write conflicts may not detect it because neither transaction writes the row written by the other.
Serialization anomalies are the broader category: the committed result cannot be explained by any serial order of the transactions.
Read Uncommitted
The SQL standard permits dirty reads at Read Uncommitted. It offers little protection and is uncommon for application workflows. PostgreSQL internally provides Read Committed behavior when Read Uncommitted is requested because its MVCC architecture does not expose dirty tuples that way.
Do not select this level merely to make a slow report faster. A replica, a warehouse, an index, or a tuned query is usually a clearer way to isolate analytical load without allowing logically impossible observations.
Read Committed
Read Committed commonly gives each statement a fresh snapshot of rows committed before that statement began. It prevents dirty reads, but two queries in one transaction can see different committed states.
PostgreSQL defaults to Read Committed. An UPDATE may wait for a concurrent writer and then re-evaluate its predicate against the new row version. That is useful, but check-then-write logic across multiple statements can still race.
Use Read Committed when each statement can stand alone or correctness is enforced with atomic SQL and constraints:
UPDATE inventory
SET available = available - 1
WHERE sku = :sku
AND available > 0;The affected-row count decides whether the reservation succeeded. A prior SELECT followed by an unconditional update would be weaker.
Repeatable Read
Repeatable Read generally keeps a stable snapshot for the transaction, preventing nonrepeatable reads. Vendor behavior differs beyond that statement.
PostgreSQL Repeatable Read uses snapshot isolation and does not allow the classic phantom behavior within the snapshot, but serialization anomalies such as write skew remain possible. Conflicting attempts to update a row changed since the snapshot may abort.
MySQL InnoDB Repeatable Read is the default. Consistent reads use the transaction’s snapshot, while locking reads and writes use locks including next-key locks in relevant index ranges. The exact result depends on query type and indexes. Never assume a PostgreSQL example proves MySQL behavior, or vice versa.
Long-lived snapshots also have operational cost. They can delay cleanup of old row versions, retain undo or WAL-related resources, and return increasingly stale data. Keep business transactions short even when the snapshot is stable.
Serializable
Serializable aims to make committed transactions equivalent to some one-at-a-time order. It is the strongest standard level, but “strongest” does not mean “never fails.” A database may block transactions, detect cycles, or abort one with a serialization failure. The application must retry the complete transaction from the beginning.
PostgreSQL implements Serializable Snapshot Isolation. It tracks dependencies that could form dangerous structures and aborts a transaction when needed. This can protect multi-row predicates without manually locking every possible row, but retry handling is part of the contract.
Serializable is valuable when an invariant spans changing predicates or multiple rows and is awkward to encode as a constraint. It is not a substitute for uniqueness constraints, foreign keys, check constraints, or correct transaction boundaries.
Reproducing write skew
Suppose the invariant is “at least one doctor remains on call”:
CREATE TABLE doctor_on_call (
doctor_id bigint PRIMARY KEY,
on_call boolean NOT NULL
);
-- Transaction A and B both run:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctor_on_call WHERE on_call = true;
UPDATE doctor_on_call SET on_call = false WHERE doctor_id = :me;
COMMIT;If A updates doctor 1 and B updates doctor 2, there is no same-row write conflict. Both may see a count of two and commit, depending on engine and isolation semantics.
Options include Serializable with bounded retries, locking an explicit shared schedule row, redesigning the invariant into a constrained representation, or serializing commands through one owner. SELECT ... FOR UPDATE helps only if all transactions lock a set of rows that actually conflicts.
Spring transaction example
Spring’s @Transactional can declare an isolation level, but the database driver and engine enforce it:
@Service
public class ScheduleService {
private final JdbcTemplate jdbc;
public ScheduleService(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
@Transactional(isolation = Isolation.SERIALIZABLE)
public void leaveOnCall(long doctorId) {
Integer remaining = jdbc.queryForObject(
"select count(*) from doctor_on_call where on_call = true",
Integer.class);
if (remaining == null || remaining <= 1) {
throw new IllegalStateException("At least one doctor must remain on call");
}
jdbc.update(
"update doctor_on_call set on_call = false where doctor_id = ?",
doctorId);
}
}The call must pass through the Spring proxy; self-invocation can bypass transactional interception. External HTTP calls should not occur inside this transaction. A production retry wrapper should catch only the translated transient serialization/deadlock category, use a small attempt limit and jitter, and rerun the entire method with no non-transactional side effect in between.
Node.js transaction example
With node-postgres, every statement in a transaction must use the same checked-out client:
export async function leaveOnCall(pool, doctorId) {
const client = await pool.connect();
try {
await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
const result = await client.query(
"select count(*)::int as count from doctor_on_call where on_call = true"
);
if (result.rows[0].count <= 1) {
throw new Error("At least one doctor must remain on call");
}
await client.query(
"update doctor_on_call set on_call = false where doctor_id = $1",
[doctorId]
);
await client.query("COMMIT");
} catch (error) {
await client.query("ROLLBACK");
throw error;
} finally {
client.release();
}
}PostgreSQL reports serialization failure with SQLSTATE 40001. Retry at the boundary that owns the complete unit of work. Do not issue BEGIN through pool.query() and later statements through arbitrary pool connections.
Choosing an isolation level
Start with invariants, not a universal preference. For each workflow, list the rows and predicates read, rows written, database constraints, acceptable conflicts, and side effects.
Read Committed plus atomic conditional updates is often excellent for counters, inventory decrements, and state transitions. Repeatable Read is useful for a coherent multi-query view when its engine-specific anomalies are acceptable. Serializable is appropriate for hard multi-row invariants when retryable aborts are engineered and measured. Explicit pessimistic locks fit short, highly contended critical sections with a known lock order.
Run concurrent integration tests against the same database engine and major version used in production. An in-memory substitute rarely reproduces MVCC snapshots, predicate locks, gap locks, or vendor error codes.
Make the test deterministic with barriers: start both transactions, complete both reads, then release both writes together. Assert the invariant after every run and repeat enough times to catch scheduler variation. Keep this suite separate from fast unit tests, but run it before database or driver upgrades.
Common mistakes
The first mistake is believing @Transactional automatically means Serializable. It normally uses the configured database default.
The second is changing isolation globally to fix one workflow. That can add contention everywhere. Prefer the narrowest correct boundary.
The third is retrying only the final SQL statement after a serialization failure. The transaction’s reads are invalid; restart the whole unit.
The fourth is mixing database work and remote calls. A rollback cannot undo an email or payment provider request.
The fifth is relying on an ORM test with one thread. Concurrency bugs require coordinated overlapping transactions.
Practical checklist
- State the business invariant before choosing a level.
- Confirm the production engine’s documented semantics and default.
- Use constraints and atomic statements wherever possible.
- Keep transactions short and exclude remote calls.
- Use one connection/session for the whole transaction.
- Detect lost updates with version checks where appropriate.
- Give locking queries supporting indexes.
- Standardize lock order for multi-row updates.
- Handle
40001, deadlock, and vendor-equivalent errors narrowly. - Retry the whole idempotent transaction with a bound and jitter.
- Measure aborts, lock waits, transaction age, and pool occupancy.
- Test write skew and other expected anomalies with concurrent sessions.
Frequently asked questions
Is Repeatable Read the same in PostgreSQL and MySQL?
No. Both use MVCC, but snapshots, locking reads, range locking, and anomaly handling differ. Read the exact engine documentation.
Does Serializable lock the whole database?
Not generally. Modern engines use row/range locks, MVCC dependency tracking, or combinations. The cost appears as waits, extra tracking, and aborts rather than necessarily one global lock.
Can optimistic locking replace isolation?
It detects stale updates to a versioned record. It does not automatically protect predicates or invariants spanning multiple rows.
When should I use SELECT FOR UPDATE?
Use it when a transaction must lock known rows before a decision. Keep the transaction short, index the predicate, and acquire locks in a consistent order.
Why did a serializable transaction fail?
An abort is a normal way to preserve serializability under concurrency. Treat the documented transient error as a bounded retry signal.
Sources and further reading
- PostgreSQL, Transaction Isolation.
- MySQL 8.4, InnoDB Transaction Isolation Levels.
- Spring Framework, Transaction Management.
- node-postgres, Transactions.
Related reading
Read Database Transactions Explained for ACID boundaries, Optimistic Locking Explained for version-based conflicts, and Database Deadlocks Explained for lock cycles and retry handling. Database Connection Pooling Explained covers the capacity cost of long transactions.