A Guide to SQL Isolation Levels

When multiple transactions run against the same database at the same time, SQL isolation levels determine how much each transaction can see of what the others are doing. 

Getting isolation right is one of the most consequential decisions in database design. Choose too little, and you risk data corruption; choose too much, and you pay a steep performance penalty.

What Are SQL Isolation Levels?

Isolation is the “I” in ACID. It defines the degree to which an in-progress transaction is shielded from the effects of other concurrent transactions before either one commits. A fully isolated transaction behaves as if it were the only one running, but full isolation comes at a cost, which is why SQL gives you a spectrum of levels to choose from.

In practice, isolation is a trade-off between data consistency and concurrency. Stricter isolation prevents more anomalies but typically reduces throughput and can increase contention. The right level is the weakest one that still satisfies your application’s correctness requirements.

What Problems Do Isolation Levels Solve?

Without proper isolation, concurrent transactions can produce three classes of anomalies:

• Dirty reads: A transaction reads data written by another transaction that hasn’t committed yet. If that other transaction rolls back, the first one acted on data that never existed.
• Non-repeatable reads: A transaction reads the same row twice and gets different values because another transaction modified and committed it in between.
• Phantom reads: A transaction re-executes a range query and gets a different set of rows because another transaction inserted or deleted rows that fall within that range.

Each isolation level is defined by which of these anomalies it prevents.

What Are the Four Standard SQL Isolation Levels?

The ANSI/SQL-92 standard defines four isolation levels, ordered from least to most strict:

Read Uncommitted offers the weakest guarantees, and transactions can read uncommitted changes from other transactions. Very few production systems use this level, and some databases, including YugabyteDB, simply treat it as Read Committed.

Read Committed prevents dirty reads. Each statement sees only data committed before it began. This is the default in many databases and a reasonable baseline for workloads where stale reads between statements are acceptable.

Repeatable Read adds a guarantee that if you read a row, re-reading it later in the same transaction returns the same value. It still allows phantoms. New rows matching your query’s range can appear between reads.

Serializable is the strictest level. Transactions are guaranteed to produce results equivalent to some serial (one-at-a-time) execution order. It prevents all three anomaly classes, including phantoms.

How Do You Choose the Right Isolation Level?

Start from correctness requirements, not performance. If a dirty read or phantom would produce incorrect application behavior, you need a level that prevents it. 

For most OLTP workloads, Read Committed is sufficient and offers the best throughput. If your application reads and re-uses values within a single transaction (pricing calculations, inventory checks, financial aggregations), Repeatable Read or Serializable is safer. 

Reserve Serializable for workloads where overlapping reads and writes could otherwise produce incorrect results.

How Does Isolation Work in Distributed SQL Databases?

In a single-node database, isolation is enforced locally. In a distributed SQL database, reads and writes can span multiple nodes and regions, so isolation must be enforced globally. 

Distributed databases typically use multi-version concurrency control (MVCC) and distributed consensus protocols rather than traditional locking, ensuring that commits are durable across nodes before becoming visible.

What Is Snapshot Isolation and How Does It Differ From Repeatable Read?

Snapshot isolation is an MVCC-based approach in which all reads within a transaction see a consistent snapshot taken at the start of the transaction. It prevents dirty reads, non-repeatable reads, and phantoms, which puts it in the same anomaly-prevention class as the SQL standard’s Repeatable Read. 

The difference is implementation: snapshot isolation also blocks some write anomalies that standard Repeatable Read technically allows, though it still falls short of full Serializable guarantees.

PostgreSQL implements its Repeatable Read level using snapshot isolation, making it stronger than the SQL standard requires. YugabyteDB follows the same convention: its Snapshot isolation level maps directly to PostgreSQL’s Repeatable Read.

What Isolation Levels Does YugabyteDB Support?

YugabyteDB supports three of the four ANSI SQL isolation levels, specifically the three strictest ones. Read Uncommitted is accepted syntactically in YSQL but behaves as Read Committed. The three active levels are:

Read Committed: Each statement sees all data committed before that statement was issued. YugabyteDB handles read restart errors and conflict resolution internally at this level, so application-level retry logic is not required. Read Committed is the default for new deployments on YugabyteDB v2025.2 or later when deploying via YugabyteDB Anywhere or YugabyteDB Aeon.

Snapshot: All reads within a transaction see a consistent snapshot taken at transaction start. No dirty reads, no non-repeatable reads, no phantoms. Maps to PostgreSQL’s Repeatable Read level and was the default for the YSQL API in earlier YugabyteDB versions.

Serializable: Transactions are guaranteed to execute as if serialized. No overlapping read/write sets can produce anomalous results. The strongest consistency guarantee, and the right choice when correctness cannot tolerate any concurrency anomaly.

For a full technical breakdown of how each level is implemented, see the YugabyteDB isolation levels architecture documentation.

The right isolation level is the one that satisfies your application’s consistency requirements without paying for guarantees you don’t need.

For most workloads, Read Committed is a practical starting point, and YugabyteDB’s implementation removes the retry burden that other databases place on the application. To go deeper, the YugabyteDB transactions documentation covers behavior and configuration across all three supported levels.