Isolation level
An isolation level defines how much one transaction's in-progress changes are visible to other concurrently running transactions, trading off consistency against concurrency.
Overview
Isolation is one of the four ACID properties, but in practice it isn't all-or-nothing — SQL databases expose a spectrum of isolation levels that trade strict consistency for higher concurrency. The SQL standard defines four levels, each preventing a different set of anomalies.
Standard Isolation Levels
- Read Uncommitted — transactions can see other transactions' uncommitted changes ("dirty reads"). Rarely used in practice.
- Read Committed — a transaction only ever sees data that has been committed, but a value it reads twice may change between reads ("non-repeatable read").
- Repeatable Read — a transaction sees a consistent snapshot for values it has already read, but new rows matching a filter may appear on a re-run ("phantom read").
- Serializable — transactions behave as if they ran one at a time in some serial order; the strongest and most expensive level.
Stricter isolation levels prevent more anomalies but generally require more locking or more conflict aborts, reducing throughput under contention.
DuckDB's Isolation Level
DuckDB provides snapshot isolation: every transaction sees a consistent snapshot of the database as of the moment it started, and concurrent writers don't block each other. DuckDB doesn't expose a SET TRANSACTION ISOLATION LEVEL knob — snapshot isolation is the fixed behavior. Because DuckDB uses optimistic concurrency control rather than locking, a write-write conflict (two transactions modifying the same rows) doesn't wait; instead, whichever transaction commits second fails with a conflict error and must be retried.
Related terms
ACID is an acronym describing four properties — Atomicity, Consistency, Isolation, and Durability — that guarantee database transactions are processed reliably.
Concurrency control →Concurrency control is the set of techniques a database uses to let multiple transactions execute at the same time without violating data consistency or isolation guarantees.
Transactions →A database transaction is a sequence of one or more operations that are executed as a single logical unit of work, either fully applied or fully rolled back.
OLTP →OLTP (Online Transaction Processing) refers to systems designed for fast, high-concurrency, small read/write transactions — like inserting an order or updating an account balance — typically backed by normalized relational schemas.
Apache Iceberg →Apache Iceberg is an open table format that adds ACID transactions, schema evolution, and time travel to large analytic tables stored as files in a data lake.
Delta Lake →Delta Lake is an open table format, originally developed by Databricks, that adds ACID transactions, schema enforcement, and time travel to Parquet data stored in a data lake.
FAQS
DuckDB implements snapshot isolation for all transactions and does not support configuring a different isolation level per transaction.
A phantom read happens when a transaction re-runs a query and sees new rows that another committed transaction inserted in the meantime. Repeatable Read isolation doesn't prevent phantom reads; Serializable does.
