Transaction Isolation Levels and the Data Inconsistencies They Prevent

An overview of database transaction isolation levels from READ UNCOMMITTED to SERIALIZABLE, verified by running two concurrent sessions against a real PostgreSQL instance. Covers write skew and the cost of raising isolation levels.

15 min read

A transaction in databases is a mechanism that treats a series of operations as a single logical unit, guaranteeing that either all succeed or all fail. In environments where multiple transactions execute concurrently, Isolation Levels become important for maintaining data consistency.

This article precisely defines what the four standard isolation levels prevent, then actually runs two concurrent sessions against PostgreSQL 16 to measure whether dirty reads, non-repeatable reads, and phantom reads really do (or don’t) occur. It also measures write skew, a subtler anomaly that even SERIALIZABLE can miss depending on the implementation. Because isolation levels are interpreted and implemented differently across database products, every measured result in this article is explicitly PostgreSQL 16.14 output — behavior of other products (e.g. MySQL) is cited from official documentation and clearly labeled as “documented, not measured.”

Data Inconsistencies in Database Transaction Processing

These are the typical data-inconsistency phenomena that can occur when multiple transactions run concurrently. In addition to the three classic anomalies organized by the ANSI/ISO SQL standard (and the classic critique by Berenson et al., “A Critique of ANSI SQL Isolation Levels,” 1995), this article treats write skew as a fourth phenomenon that the standard’s three-anomaly framework does not fully capture.

1. Dirty Read

  • Phenomenon: A transaction A reads data that has been modified but not yet committed by another transaction B.
  • Problem: If transaction B is later rolled back, the data A read becomes invalid data that never actually existed, undermining data reliability.

2. Non-repeatable Read

  • Phenomenon: When a transaction A reads the same row multiple times, another transaction B updates (or deletes) that row between the reads, resulting in different results.
  • Problem: Inconsistent data is provided within transaction A, reducing the accuracy of the processing result.

3. Phantom Read

  • Phenomenon: After executing a query with specific conditions and obtaining a result set, re-executing the same query returns a different result set because another transaction has inserted new rows matching the conditions or deleted existing ones in between.
  • Problem: This can cause unexpected results in aggregation or condition-based operations. Unlike a non-repeatable read — which is about the value of an existing row changing — a phantom read is about the set of rows itself changing.

4. Write Skew

  • Phenomenon: Two transactions A and B each write to a different row, but both base their decision on reading a shared invariant, without accounting for what the other transaction is about to write. Because A and B write to different rows, row-level locking or MVCC conflict detection does not catch the conflict, and both transactions commit successfully even though the invariant is now violated.
  • Concrete example: A hospital on-call schedule has the invariant “at least one doctor must be on call at all times.” Doctors Alice and Bob are both currently on call. If both, in independent transactions and at nearly the same time, decide “the other one is still on call, so I can go off call,” both transactions see the other doctor as on-call, both commit successfully, and the result is that nobody is on call.
  • Problem: Implementations that only prevent the three classic anomalies — in particular snapshot-isolation-based REPEATABLE READ, and even some implementations that call themselves SERIALIZABLE while internally using snapshot isolation — cannot catch this. It requires an implementation that provides true serializability. We reproduce and measure this on real PostgreSQL later in this article.

Transaction Isolation Levels

The ANSI/ISO SQL standard defines four isolation levels to prevent these inconsistencies. Higher isolation levels prevent more anomalies but reduce concurrency and increase the performance impact. What is shown here is the “minimum guarantee” required by the SQL standard — actual database products may implement stricter (or, in edge cases, looser) behavior (see footnotes *1, *2, verified in the measurement sections below).

Isolation LevelDirty ReadNon-repeatable ReadPhantom Read
READ UNCOMMITTEDCan occurCan occurCan occur
READ COMMITTEDPrevented *1Can occurCan occur
REPEATABLE READPreventedPreventedCan occur *2
SERIALIZABLEPreventedPreventedPrevented

Which isolation level prevents which anomaly (SQL standard minimum guarantee): a 4x3 matrix with green checkmarks for prevented and red crosses for anomalies that can still occur, with footnotes on PostgreSQL-specific deviations

1. READ UNCOMMITTED

The lowest isolation level. Per the standard, dirty reads, non-repeatable reads, and phantom reads can all occur. Performance is the highest, but data consistency is barely guaranteed.

2. READ COMMITTED

Reads only data that has been committed by other transactions. This prevents dirty reads from uncommitted data. However, because each statement re-fetches the latest committed snapshot, non-repeatable reads and phantom reads can still occur. This is the default isolation level in many database systems (PostgreSQL, Oracle, SQL Server).

3. REPEATABLE READ

Data read at the start of a transaction is protected from changes by other transactions until the transaction ends, so re-reading the same row is consistent (preventing non-repeatable reads). The SQL standard permits phantom reads at this level, but as shown below, PostgreSQL’s and MySQL(InnoDB)’s implementations also prevent phantom reads in many cases — this is implementation-specific behavior, so applications that need portability should design against the standard’s minimum guarantee rather than any one vendor’s extra protection.

4. SERIALIZABLE

The strictest isolation level. Guarantees transactions behave as if executed completely independently (serially), preventing all three anomalies above. However, “calling itself SERIALIZABLE” and “being truly serializable” are not the same thing. Oracle, and many earlier implementations (including PostgreSQL before 9.1), implement SERIALIZABLE internally via Snapshot Isolation, which does not prevent write skew. Since the 2011 release of PostgreSQL 9.1, PostgreSQL implements an algorithm called Serializable Snapshot Isolation (SSI), which provides true serializability including write-skew prevention — measured later in this article.

SNAPSHOT Isolation (Provided by Some DB Systems)

Some database systems (e.g., SQL Server, Oracle) offer SNAPSHOT as a distinct isolation level to achieve high concurrency comparable to SERIALIZABLE. Transactions operate against a snapshot taken at transaction start and are unaffected by other transactions’ commits. As noted above, snapshot isolation alone does not prevent write skew.

Test Environment

Everything measured below was run against PostgreSQL 16.14, installed via Homebrew on macOS (aarch64), locally. Two psql sessions were launched concurrently through named pipes, and their statements were explicitly interleaved by executing a command in one session, confirming its completion, then executing the next command in the other session. Every SQL statement and result shown is actual measured output from this environment — not a “typical” or reconstructed result. MySQL and Oracle were not available in this sandbox, so their behavior is cited explicitly from official documentation and clearly distinguished from the measured PostgreSQL results.

Measurement 1: Does a Dirty Read Really Happen (READ UNCOMMITTED)?

With an accounts(id, balance) table seeded as (1, 1000), Session A explicitly requests READ UNCOMMITTED, then attempts to re-read the row while Session B has an uncommitted update in flight.

TimeSession ASession B
1BEGIN;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2SELECT id, balance FROM accounts WHERE id = 1; → 1000
3BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; (uncommitted)
4SELECT id, balance FROM accounts WHERE id = 1; (re-read while B is uncommitted)
5ROLLBACK;
6COMMIT;

Actual output (from step 4):

 transaction_isolation
------------------------
 read uncommitted
(1 row)

 id | balance
----+---------
  1 |    1000
(1 row)                    -- step 2: first read

 id | balance
----+---------
  1 |    1000               -- step 4: still 1000 even while B has an uncommitted change
(1 row)

Even though SHOW transaction_isolation; reports read uncommitted, Session A never sees B’s uncommitted balance = 900. This matches PostgreSQL’s official documentation exactly: because PostgreSQL’s MVCC never exposes uncommitted row versions to other transactions, SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is silently treated internally as READ COMMITTED, and dirty reads are structurally impossible ( PostgreSQL: Documentation 13.2. Transaction Isolation ). This is the measured confirmation of footnote *1 in the table above.

Measurement 2: Non-repeatable Read (READ COMMITTED vs REPEATABLE READ)

Reading the same row twice within one transaction, with another transaction committing an update in between. balance was reset to 1000 before each run.

READ COMMITTED (measured)

TimeSession ASession B
1BEGIN;
SELECT balance FROM accounts WHERE id=1; → 1000
2BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id=1; COMMIT; (updates to 800 and commits)
3SELECT balance FROM accounts WHERE id=1; (re-read, same transaction)
4COMMIT;
 balance
---------
    1000     -- step 1: first read
(1 row)

 balance
---------
     800     -- step 3: re-read in the same transaction; the value changed
(1 row)

The second SELECT inside the same transaction A reflects B’s committed change (800). This is a non-repeatable read. Because READ COMMITTED re-fetches the latest committed snapshot for each statement, this is expected behavior.

REPEATABLE READ (measured, balance reset to 1000 and re-run)

 balance
---------
    1000     -- step 1: first read
(1 row)

 balance
---------
    1000     -- step 3: re-read in the same transaction. B has committed, but the value is unchanged
(1 row)

Running exactly the same steps under REPEATABLE READ, A’s second read still returns 1000 even though B has committed its update. Because REPEATABLE READ keeps the snapshot from transaction start, the non-repeatable read is measurably prevented.

Measurement 3: Phantom Read (READ COMMITTED vs REPEATABLE READ)

Running COUNT(*) WHERE balance > 400 twice inside one transaction, with a new matching row inserted by another transaction in between.

READ COMMITTED (measured)

TimeSession ASession B
1BEGIN;
SELECT count(*) FROM accounts WHERE balance>400; → 2
2BEGIN; INSERT INTO accounts VALUES (3, 900); COMMIT;
3SELECT count(*) FROM accounts WHERE balance>400; (re-run)
4COMMIT;
 count
-------
     2      -- step 1: first count
(1 row)

 count
-------
     3      -- step 3: B's committed insert is now reflected; the count went up
(1 row)

The second count increased from 2 to 3 — a phantom read actually occurred.

REPEATABLE READ (measured, inserted row deleted and re-run)

 count
-------
     2      -- step 1: first count
(1 row)

 count
-------
     2      -- step 3: B inserts and commits the same row, but the count doesn't increase
(1 row)

Running the identical steps under REPEATABLE READ, the count seen by A stays at 2 even after B commits the new row. As noted in footnote *2 above, this measurement confirms that PostgreSQL’s REPEATABLE READ exceeds the SQL standard’s minimum guarantee and effectively prevents phantom reads too. PostgreSQL’s own documentation confirms this: “PostgreSQL’s Repeatable Read is implemented as snapshot isolation, which additionally prevents phantom reads and other phenomena the standard permits at this level” ( PostgreSQL: Documentation 13.2. Transaction Isolation ).

Measurement 4: Write Skew (REPEATABLE READ vs SERIALIZABLE)

With a doctors(id, name, on_call) table seeded with Alice and Bob, both on_call = true, each doctor independently tries to honor the invariant “at least one doctor must be on call.”

Not Prevented Under REPEATABLE READ (measured)

TimeSession A (Alice’s transaction)Session B (Bob’s transaction)
1BEGIN; SET ... REPEATABLE READ;
SELECT count(*) ... on_call; → 2
2BEGIN; SET ... REPEATABLE READ;
SELECT count(*) ... on_call; → 2
3UPDATE doctors SET on_call=false WHERE name='Alice';
COMMIT; (succeeds)
4UPDATE doctors SET on_call=false WHERE name='Bob';
COMMIT; (succeeds)

Both transactions see the snapshot “2 doctors on call (including myself)” and each decides “the other one will still be on call, so I can go off call.” Both actually succeed in committing. The final state (measured):

 id | name  | on_call
----+-------+---------
  1 | Alice | f
  2 | Bob   | f
(2 rows)

Both doctors ended up off call. Because A and B each write to a different row, REPEATABLE READ (snapshot isolation in PostgreSQL) cannot detect a row-level conflict, and the invariant “at least one doctor on call” silently slips through. This is write skew.

SERIALIZABLE (PostgreSQL’s SSI) Detects It and Aborts One Transaction (measured)

on_call was reset to true for both, and the identical scenario was run under SERIALIZABLE.

TimeSession ASession B
1BEGIN; SET ... SERIALIZABLE;
SELECT count(*) ... on_call; → 2
2BEGIN; SET ... SERIALIZABLE;
SELECT count(*) ... on_call; → 2
3UPDATE ... WHERE name='Alice'; (held, not committed)
4UPDATE ... WHERE name='Bob'; (held, not committed)
5COMMIT;
6COMMIT;

Actual result:

-- Session A's COMMIT
COMMIT

-- Session B's COMMIT
ERROR:  could not serialize access due to read/write dependencies among transactions
DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
HINT:  The transaction might succeed if retried.

Session A’s COMMIT succeeds, but Session B’s fails with SQLSTATE 40001 (serialization_failure). Final state:

 id | name  | on_call
----+-------+---------
  1 | Alice | f
  2 | Bob   | t
(2 rows)

This time Bob remains on call and the invariant holds. Since PostgreSQL 9.1 (2011), SERIALIZABLE uses Serializable Snapshot Isolation (SSI), which layers detection of “dangerous structures” (chained rw-antidependencies) on top of snapshot isolation to achieve true serializability without additional locking ( Ports & Grittner, “Serializable Snapshot Isolation in PostgreSQL”, VLDB 2012 / PostgreSQL Wiki: SSI ).

An important caveat: not every implementation that “calls itself SERIALIZABLE” provides this guarantee. Oracle’s SERIALIZABLE is internally snapshot isolation and does not perform SSI-style dependency detection, so it does not prevent write skew in the scenario above. Don’t assume safety just because a setting is named SERIALIZABLE — check the vendor’s documentation for the actual implementation strategy.

MySQL’s REPEATABLE READ (Documentation-Based, Not Measured)

MySQL was not installed in this sandbox, so the following is based on official documentation, not a measured result like the rest of this article.

For plain (non-locking) SELECT statements, MySQL(InnoDB)’s REPEATABLE READ relies purely on snapshot-based MVCC. But for locking reads — SELECT ... FOR UPDATE, UPDATE, DELETE — InnoDB additionally uses next-key locks (record lock + gap lock) that lock an entire index range. Gap locks block INSERTs into the “gaps” between existing records, which prevents phantom reads for locking scans in many cases ( MySQL 8.4 Reference Manual: 17.7.2.1 Transaction Isolation Levels / MySQL 8.4 Reference Manual: 17.7.4 Phantom Rows ).

As the official documentation notes, however, this behavior is specific to locking reads. Plain SELECTs follow pure MVCC snapshots, so — for a different reason than PostgreSQL — phantom reads are also unlikely to be observed there, but the underlying mechanism (snapshot isolation vs. locking) is different from PostgreSQL’s. Applications that need portability should not rely on this implementation detail; instead, enforce required guarantees with explicit locking (SELECT ... FOR UPDATE) or application-level re-verification.

The Cost of Raising Isolation Levels

Higher isolation levels are safer, but “just crank it up to the strictest level” is not a free lunch.

  • Increased lock contention and blocking: REPEATABLE READ and SERIALIZABLE need to guarantee the consistency of data they’ve read, which (for locking-based implementations) means longer-held locks, or (for SSI) a broader scope of conflict detection. Under high-concurrency workloads, this increases blocking and deadlock frequency and reduces throughput.
  • SERIALIZABLE effectively requires retry logic: as the measurements above show, PostgreSQL’s SSI aborts a transaction with a 40001 error when it detects a dangerous conflict. This is expected behavior, not a bug — applications using SERIALIZABLE need to detect serialization failures and automatically retry the transaction. Skip this and the error leaks straight to the user.
  • Performance impact is implementation-specific: the paper that introduced PostgreSQL’s SSI (Ports & Grittner, VLDB 2012) reports SSI’s overhead over plain snapshot isolation at under 7% for most workloads, and significantly faster than traditional two-phase-locking SERIALIZABLE implementations in many cases. This figure is specific to PostgreSQL’s implementation and does not necessarily generalize to other products.
  • Practical guidance: most applications work fine on the default READ COMMITTED (or REPEATABLE READ). Reserve SERIALIZABLE plus a retry loop — or explicit locking (SELECT ... FOR UPDATE) and application-level constraints (unique constraints, CHECK constraints, exclusion constraints) — for the specific operations where an invariant genuinely must hold (e.g., preventing double-spending or negative inventory).

Recent Developments

PostgreSQL’s SERIALIZABLE (SSI) has not changed its core algorithm since its introduction in 9.1 in 2011. The PostgreSQL 16 instance used for this article’s measurements — and the latest PostgreSQL 18, released in 2025 — still document the same isolation-level definitions and implementation strategy (READ UNCOMMITTED silently treated as READ COMMITTED; REPEATABLE READ/SERIALIZABLE implemented via snapshot isolation) in the transaction-iso documentation. Write skew itself is not a new concept — it became widely recognized through academic discussion around the SSI paper and related work in the late 2000s to early 2010s, and is now a standard topic in texts like Martin Kleppmann’s Designing Data-Intensive Applications. Products such as Oracle, however, continue to implement SERIALIZABLE via plain snapshot isolation, so it remains important to check the vendor’s documentation for whether a given engine implements SSI-style dependency detection, rather than trusting the name SERIALIZABLE alone.

Summary

  • The SQL standard defines three anomalies — dirty read, non-repeatable read, phantom read — and the standard’s four isolation levels are defined by how many of these each prevents
  • Measured: PostgreSQL has no true READ UNCOMMITTED — it always behaves like READ COMMITTED, so dirty reads never actually occur regardless of the requested isolation level
  • Measured: PostgreSQL’s REPEATABLE READ exceeds the SQL standard’s minimum and effectively prevents phantom reads too, because it is snapshot isolation
  • Write skew occurs when two transactions writing to different rows independently check a shared invariant — it falls outside the classic three-anomaly framework, and REPEATABLE READ (snapshot isolation) cannot prevent it, as measured with the on-call doctors example
  • PostgreSQL’s SERIALIZABLE (SSI, since 2011) detects write skew and aborts one transaction with a 40001 error — also confirmed by measurement
  • Not every SERIALIZABLE implementation provides the same guarantee: Oracle, for example, remains internally snapshot-isolation-based
  • Raising the isolation level is not free — it brings increased blocking and, at SERIALIZABLE, a hard requirement for retry logic — so match the level to how critical the invariant actually is
  • MySQL(InnoDB)’s REPEATABLE READ uses gap locks to prevent phantom reads for locking reads, a different mechanism from PostgreSQL’s — this section is documentation-based, not measured, in this article

References

関連記事