SQL Isolation Levels Explained: Read Committed, Snapshot, Repeatable Read, and Serializable

When multiple transactions touch the same rows at the same time, the database has to decide how much each one is allowed to see of the others' in-flight changes. That decision is controlled by the transaction SQL isolation level — and choosing one is a trade-off between data consistency and concurrency, not a search for the single "best" setting. The right level is usually the weakest one that still guarantees the correctness of the operation, not the strongest one available.

The Main Isolation Levels

The standard levels, roughly weakest to strongest, are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, snapshot isolation, and SERIALIZABLE. Exact behavior differs between engines — PostgreSQL, MySQL/InnoDB, SQL Server, and Oracle all implement these concepts slightly differently — but the underlying ideas are shared across dialects.

READ UNCOMMITTED

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

This is typically the weakest level: a transaction may read another transaction's changes before they're committed, which means dirty reads are possible. It buys very high concurrency and low locking overhead, at the cost of dirty reads, non-repeatable reads, phantom reads, and inconsistent calculations. It's occasionally acceptable for approximate, non-critical reads — operational dashboards, rough monitoring stats — but should generally be avoided for financial or business-critical logic.

READ COMMITTED

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

READ COMMITTED prevents a transaction from seeing another transaction's uncommitted changes, eliminating dirty reads. The same query can still return different results at different points in the same transaction if another transaction commits in between — but for most ordinary workloads (CRUD APIs, user management, standard web applications, order workflows) this is a reasonable default.

REPEATABLE READ

REPEATABLE READ gives a transaction a stable view of any row it has already read: re-reading that row later in the same transaction returns the same result. It prevents dirty reads and non-repeatable reads; phantom-row behavior (new rows matching a previous query's filter) varies by engine. It's the right choice when a transaction reads the same records more than once and later logic depends on that value staying consistent — financial calculations, batch processing, inventory checks, multi-step workflows.

Snapshot Isolation (MVCC)

Snapshot isolation is commonly implemented with multi-version concurrency control (MVCC): rather than making readers wait for writers, the database keeps multiple versions of a row and gives each transaction a consistent snapshot as of when it started.

For example, a transaction that reads A = 100, B = 200, C = 300 will keep seeing B = 200 even if another transaction commits B = 500 partway through — that value belongs to a different snapshot. This reduces reader/writer blocking, which makes it well suited to reports, analytics, long-running reads, exports, and dashboards that need a consistent point-in-time view rather than data shifting mid-query.

Snapshot Isolation Is Not Serializable

A consistent snapshot is not the same guarantee as full transaction safety — the classic example is write skew. Say two doctors are on call, and the rule is that at least one must remain on call at all times. Transaction A reads "both on call" and sets Doctor A to off. At the same time, Transaction B reads the same snapshot and sets Doctor B to off. Both transactions touch different rows, so both commit — and the result is both doctors off call, silently violating a rule neither transaction individually broke. Snapshot isolation and serializable isolation solve different problems.

SERIALIZABLE Isolation

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SERIALIZABLE is generally the strongest standard isolation level: concurrent transactions must produce a result equivalent to running them one at a time in some valid order (A → B → C, not an invalid interleaving). It protects against dirty reads, non-repeatable reads, phantom reads, and many write-skew scenarios — but the guarantee isn't free. It can mean more locking, more conflict detection, transaction aborts, retries, deadlocks, and higher latency. That's not a reason to avoid it; it's a reason to reserve it for the cases that actually need it: seat reservations, limited-inventory allocation, wallet transfers, financial settlements, and other logic where a concurrent decision must never violate a business rule.

A Lighter Option: SELECT FOR UPDATE

Sometimes you don't need to raise the isolation level for an entire transaction — locking the specific row you're about to modify is enough:

BEGIN;
SELECT stock FROM products
WHERE id = 100
FOR UPDATE;

UPDATE products
SET stock = stock - 1
WHERE id = 100;
COMMIT;

FOR UPDATE is useful whenever you know a row you've just read is about to be modified — inventory, wallet balances, queue processing, counters, reservation systems — giving targeted protection without applying the strongest isolation level to every query in the transaction.

Isolation Levels at a Glance

Exact behavior varies by engine, so always verify against the database you're actually running.

Matching the Level to the Workload

• Approximate monitoring — Read Uncommitted or a weak read
• Standard CRUD — Read Committed
• Multi-step consistent reads — Repeatable Read
• Reporting and analytics — Snapshot
• Inventory reservation — Repeatable Read, Serializable, or row locking
• Financial calculations — Snapshot or Serializable
• Seat booking or critical resource allocation — Serializable or explicit locking

These are starting points, not universal rules — stronger isolation means more coordination, which means more potential blocking or retries and lower maximum concurrency; weaker isolation trades that coordination away for higher concurrency and a greater risk of anomalies. Simply setting every transaction to SERIALIZABLE is rarely the right architecture.

Isolation Is One Layer, Not the Whole Strategy

Isolation level works best combined with other database-level protections: a UNIQUE(email) constraint protects uniqueness, a CHECK(balance >= 0) constraint protects valid account balances, and an atomic update like UPDATE products SET stock = stock - 1 WHERE id = 100 AND stock > 0; removes a whole class of application-level race conditions on its own. See our piece on database constraints as a defense layer for more on why constraints matter even when application code tries to enforce the same rules. A robust system usually combines the right isolation level with real constraints, atomic SQL, targeted locking, short transactions, and retry logic — not any single one of those alone.

The same discipline matters when a schema itself is changing underneath live traffic: an expand/contract migration that adds columns or backfills data has to reason about exactly the same read-consistency questions covered here, just applied to DDL instead of application queries.

Final Thoughts

Transaction isolation is fundamentally a trade-off between correctness and concurrency. READ COMMITTED is often sufficient for ordinary application operations; snapshot isolation suits long-running reports and calculations that need a consistent view; and SERIALIZABLE or explicit locking earns its cost when concurrent transactions must never violate a critical business rule. The guiding principle is simple: use the lowest isolation level that still guarantees correctness for the transaction at hand — it usually gives you the best balance of data integrity, performance, and scalability.

Planning a schema change that has to hold up under concurrent writes? Self-host FoxSchema to diff your schema and generate the migration SQL, or browse the docs to see how it fits into your workflow.