Transaction Isolation Levels

When multiple transactions execute concurrently in a relational database, they can interfere with one another in subtle and damaging ways. Transaction isolation is the "I" in the four ACID properties — Atomicity, Consistency, Isolation, and Durability — and it governs exactly how and when the effects of one transaction become visible to others. Without isolation controls, two transactions reading and writing the same data simultaneously can produce results that neither would have produced alone, corrupting application logic and business data alike.

The SQL standard defines four isolation levels, each offering a different point on a spectrum between maximum concurrency and maximum correctness. Choosing the right level is a deliberate architectural decision: too permissive and your application may act on stale or phantom data; too strict and your database may become a bottleneck under heavy load. Understanding what each level permits, what it prevents, and what it costs is therefore fundamental to building reliable, performant data-driven systems.

Isolation is one of the four ACID properties, but unlike atomicity or durability — which are largely binary guarantees — isolation is a sliding scale. A transaction running at the weakest level may see nearly everything happening around it; a transaction running at the strongest level behaves as if it were the only transaction in the system. The four standard levels, from weakest to strongest, are: Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

Each level is defined not by what it does mechanically, but by which concurrency anomalies it allows. There are three classical anomalies: dirty reads, non-repeatable reads, and phantom reads. Every isolation level is essentially a policy statement about which of these anomalies the database engine is permitted to let through and which it must prevent. Before examining each level in depth, it is worth defining these anomalies precisely, because everything else flows from them.

A dirty read occurs when Transaction A reads a row that Transaction B has modified but not yet committed. If B subsequently rolls back, A has been making decisions based on data that never officially existed. Imagine a banking system where B tentatively debits $500 from an account and A reads that reduced balance to decide whether to approve a loan — if B rolls back, A's decision was based on fiction.

A non-repeatable read occurs when Transaction A reads the same row twice within a single transaction and gets different values because Transaction B committed an update to that row between the two reads. The row existed and was valid both times, but its content changed underneath A. A report transaction that sums a set of figures at the beginning and then reads individual line items at the end may find that the totals no longer reconcile.

A phantom read occurs when Transaction A executes the same query twice and the second execution returns rows that did not appear in the first — or is missing rows that did — because Transaction B inserted or deleted matching rows and committed between A's two executions. The individual rows A already read are stable; it is the set of rows that has shifted beneath it.

With these anomalies defined, each isolation level can be examined carefully.

Read Uncommitted is the most permissive level. A transaction operating at this level may read any data page in the database, including rows that are in the middle of being modified by other, still-open transactions. The database engine applies no shared read locks, or acquires and immediately releases them, so readers never block writers and writers never block readers. This produces the highest possible concurrency and the lowest possible locking overhead.

The price is that all three anomalies — dirty reads, non-repeatable reads, and phantom reads — are possible. The dirty read risk is the most dangerous because a transaction can act on data that will vanish. Consider an inventory system: a fulfillment transaction begins writing a reservation for the last unit of a product but has not yet committed. A reporting query at Read Uncommitted sees that reservation and reports zero available stock. If the fulfillment transaction rolls back, the report was wrong, and any downstream action taken on it (halting a reorder, declining a customer inquiry) was based on a lie.

Read Uncommitted is appropriate only in narrow circumstances: approximate analytics where slight inaccuracy is tolerable, diagnostic queries on live systems where a developer needs a rough picture without stalling production workloads, or log-tail readers that must never block a writer. It is almost never appropriate for transactional application logic where correctness matters.

Read Committed is the default isolation level in PostgreSQL, Oracle, SQL Server, and many other major databases. At this level, a transaction only ever sees rows whose modifying transaction has already committed. Uncommitted changes are invisible. This eliminates dirty reads entirely.

The mechanism varies by database: some engines (SQL Server in its default mode) acquire and release shared locks on each row as it is read, so uncommitted rows are locked out of view; others (PostgreSQL, Oracle) use multiversion concurrency control (MVCC), maintaining a snapshot of committed data and routing readers to the most recently committed version of each row without taking any read locks at all.

However, because the "most recently committed" version of a row can change at any moment, non-repeatable reads and phantom reads remain possible. If Transaction A reads a customer's credit limit at the start of a multi-step process and then reads it again later in the same transaction, Transaction B might have committed an update to that credit limit in between. A's second read returns the new value, and if A's logic assumed the value was stable, it may behave incorrectly.

For many workloads this is entirely acceptable. A web application that reads a user's profile to render a page does not care if a concurrent edit lands between two reads of the profile within the same HTTP request — the user will see the updated version on the next load. Read Committed strikes a practical balance between correctness and performance, and its default status in most databases reflects how well it fits general-purpose transactional workloads.

Repeatable Read strengthens the guarantee by ensuring that any row a transaction has read will return the same values for the duration of that transaction. Once Transaction A reads a row, no other transaction can modify or delete that row until A commits or rolls back. In lock-based systems, A holds shared locks on every row it has read until the end of the transaction. In MVCC systems (like PostgreSQL), A is given a snapshot of the database as it existed when the transaction began, and all reads are satisfied from that snapshot — effectively freezing A's view of any row it touches.

This eliminates dirty reads and non-repeatable reads. However, phantom reads remain possible in lock-based implementations because the locks cover specific rows that have been read, not the ranges or conditions under which they were found. If Transaction A queries SELECT * FROM orders WHERE status = 'pending' and gets ten rows, it holds locks on those ten rows. Transaction B can still insert an eleventh pending order and commit. If A re-runs the same query, it now sees eleven rows — a phantom has appeared. The original ten rows are stable, but the set has grown.

Note that PostgreSQL's implementation of Repeatable Read goes further than the SQL standard requires: because it uses a transaction-level snapshot, it naturally prevents phantom reads within that snapshot as well. However, this behavior is specific to PostgreSQL and should not be assumed in other databases.

Repeatable Read is appropriate when a transaction must perform consistent calculations across multiple reads of the same rows. A pricing engine that reads product costs, applies a formula, and then re-reads those costs to verify a total needs to know the values are stable. A financial reconciliation that sums a set of records, then reads individual records to explain the sum, needs them to match.

Serializable is the strongest isolation level and provides the most rigorous guarantee: the outcome of any set of concurrently executing transactions is identical to some sequential (serial) execution of those same transactions. All three concurrency anomalies — dirty reads, non-repeatable reads, and phantom reads — are completely prevented.

Achieving this is expensive. In lock-based systems, the database must hold locks not just on rows that have been read, but on the gaps between rows — so-called predicate or range locks — to prevent phantom insertions. In systems that use Serializable Snapshot Isolation (SSI), such as PostgreSQL since version 9.1 and recent versions of SQL Server, the engine tracks read/write dependencies between concurrent transactions and aborts any transaction whose execution would be inconsistent with a serial order. SSI reduces unnecessary blocking but increases the rate of transaction aborts that must be retried.

The practical consequence is that Serializable transactions have lower throughput, higher latency under contention, and a greater chance of being rolled back so the application must retry them. Consider two transactions that each read the same account balance and then write a debit based on what they read. At Read Committed, both might read a balance of $1,000, both decide there are sufficient funds, and both commit debits of $900 — leaving the account at -$800. At Serializable, one of the two transactions will be forced to abort and retry; when it re-runs, it reads the already-debited balance and correctly declines the second debit.

Serializable isolation is essential for financial transactions, inventory reservations with hard limits, voting systems, and any business process where the correctness of the outcome depends on no other transaction having slipped in concurrently. The cost in throughput is the price of that correctness guarantee.

The following table summarizes which anomalies each isolation level permits or prevents according to the SQL standard:

* PostgreSQL's MVCC-based Repeatable Read also prevents phantom reads in practice, but this is an implementation detail that exceeds the SQL standard's requirement for this level.

Choosing between these levels requires analyzing the concrete read and write patterns of each transaction in your application. A helpful way to think about it is to ask: if two copies of this transaction ran simultaneously on overlapping data, could any interleaving produce a wrong result? If yes, identify which anomaly category describes that wrong result and select the level that prevents it.

There are several practical considerations that go beyond the theoretical definitions:

• Deadlock risk increases with isolation level. Higher levels hold locks longer and over more resources. Two transactions that acquire locks in different orders can each wait for the other indefinitely. Applications must detect deadlock errors (typically a specific error code returned by the database) and retry the transaction from scratch.
• Transaction retry logic is mandatory at Serializable. In SSI-based systems, the database may abort a transaction that has not done anything "wrong" in the traditional sense but whose execution conflicts with another transaction's. Application code must be prepared for this and retry transparently.
• Database implementations diverge from the standard. MySQL's InnoDB engine implements Repeatable Read with gap locks that also prevent phantom reads under many conditions, but not all. Oracle does not implement Read Uncommitted at all; its lowest level is Read Committed. SQL Server offers a fifth level — Snapshot Isolation — that is not part of the SQL standard but behaves similarly to MVCC-based Read Committed. Always consult the specific database's documentation rather than assuming standard-compliant behavior.
• Isolation level can be set at different granularities. Most databases allow the default isolation level to be configured server-wide or per-session, and allow individual transactions to override it with a SET TRANSACTION ISOLATION LEVEL statement. This means a single application can use Read Committed for lightweight queries and Serializable only for the critical financial transactions that require it.
• Long-running transactions amplify the cost of higher isolation levels. A Serializable transaction that runs for thirty seconds holds its conflict-detection structures for thirty seconds, blocking or aborting other transactions that touch the same data. Keeping transactions short — reading only what is needed, writing promptly, committing as soon as the logical unit of work is complete — reduces the window during which conflicts can accumulate.

A worked example ties all of this together. Suppose an e-commerce site has a transaction that (1) reads available stock for a product, (2) applies a discount based on the current promotional rules, and (3) creates an order record and decrements the stock. At Read Committed, two simultaneous buyers could both read a stock count of 1, both decide to proceed, and both create orders — overselling by one unit. At Repeatable Read, the rows they read are locked, but a newly inserted promotional rule between steps 1 and 2 might still affect one buyer's discount inconsistently. At Serializable, one buyer's transaction will win; the other will abort and retry, at which point it reads stock of 0 and declines the order. The application must handle that retry gracefully — ideally surfacing a "sold out" message to the second buyer rather than an unexplained error.

The right isolation level is therefore never an afterthought. It is a design decision made with full awareness of which anomalies the application can tolerate, what the performance budget allows, and how the specific database engine actually implements each level. Getting this decision right — and then testing concurrent behavior explicitly — is one of the more important and underappreciated aspects of building reliable database-backed systems.