---
title: "ACID Transactions Explained: Atomicity, Consistency, Isolation & Durability"
description: "ACID stands for Atomicity, Consistency, Isolation, and Durability — four guarantees a database makes per transaction. See the SQL and how DuckDB enforces them."
canonical: "https://motherduck.com/learn/acid-transactions-sql/"
related:
  - title: "MotherDuck Tutorial | MotherDuck Docs"
    url: "https://motherduck.com/docs/getting-started/e2e-tutorial/"
  - title: "Just Enough SQL to Safely Use AI for Data Analysis"
    url: "https://motherduck.com/blog/just-enough-sql-for-ai/"
  - title: "Big Data is Dead"
    url: "https://motherduck.com/blog/big-data-is-dead/"
gated_asset:
  title: "DuckLake on MotherDuck"
  url: "https://motherduck.com/product/ducklake/"
---

# ACID Transactions Explained: Atomicity, Consistency, Isolation & Durability

> ACID stands for Atomicity, Consistency, Isolation, and Durability — four guarantees a database makes per transaction. See the SQL and how DuckDB enforces them.

ACID stands for Atomicity, Consistency, Isolation, and Durability: the four guarantees a database makes about a transaction. A transaction is a sequence of statements treated as one unit of work. If the unit commits, all four hold. If it fails, the database looks as if the unit never ran. PostgreSQL, SQL Server, Oracle, MySQL (InnoDB), and [DuckDB](/learn/what-is-duckdb/) are ACID-compliant. Many NoSQL stores default to BASE instead.

Those four words are the difference between a database and a pile of files. The dashboard that does not add up usually read a table mid-load; the pipeline that died halfway left half its rows behind; the "committed" order that vanished never reached disk. Each failure maps to one property, and each property has SQL you can run against DuckDB below.

## Key takeaways

- Atomicity is all-or-nothing. Consistency is valid state to valid state. Isolation is concurrent transactions do not see each other's unfinished work. Durability is a successful `COMMIT` survives a crash.
- DuckDB is fully ACID. It uses snapshot isolation via a bulk-optimized MVCC design and does not expose `SET TRANSACTION ISOLATION LEVEL`.
- Snapshot isolation blocks dirty reads, non-repeatable reads, and phantom reads. It is not identical to SERIALIZABLE (write skew is the known gap).
- ACID is a transaction contract. BASE is an availability-first alternative. They are different products, not degrees of the same thing.
- A Parquet file is not a transaction. Iceberg, Delta Lake, and [DuckLake](/learn/ducklake-guide/) add commits on top of files.

![ACID transactions Illustration: Atomicity, Consistency, Isolation, Durability in databases](https://motherduck-com-web-prod.s3.amazonaws.com/assets/img/ACID_Transactions_Illustration_686037dddf.png)

## What are the four ACID properties?

| Property | What it guarantees | What breaks without it | How SQL / DuckDB enforces it |
|---|---|---|---|
| Atomicity | The unit commits entirely or not at all | Debit lands, credit does not | `BEGIN` / `COMMIT` / `ROLLBACK`. A failed statement aborts the transaction |
| Consistency | The database moves from one valid state to another | Negative balances, orphan FKs, duplicate keys | Constraints: `PRIMARY KEY`, `CHECK`, `NOT NULL`, `UNIQUE`, `FOREIGN KEY` |
| Isolation | Concurrent transactions do not see unfinished work | Dirty, non-repeatable, or phantom reads in a report | Snapshot isolation via MVCC. One snapshot per `BEGIN` |
| Durability | A successful commit survives power loss | Committed rows vanish after a crash | Write-ahead log. `COMMIT` returns only after the log is stable |

Assume a table already exists:

```sql
CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    balance DECIMAL(10, 2) CHECK (balance >= 0)
);
INSERT INTO accounts VALUES (1, 500.00), (2, 300.00);
```

Two constraints do the policing: `PRIMARY KEY` refuses a duplicate `account_id`, and `CHECK (balance >= 0)` refuses an overdraft. Every snippet below runs against this table, in order. DuckDB requires those constraints on `CREATE TABLE`. It does not support `ALTER TABLE … ADD CONSTRAINT`.

### Atomicity

```sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;   -- or ROLLBACK; if anything failed
```

The two `UPDATE`s are one unit of work. `BEGIN` opens it; `COMMIT` makes both changes permanent in a single step. If the second statement fails — a constraint violation, a crash, a dropped connection — DuckDB rolls the first back with it, so the state where account 1 is debited but account 2 was never credited cannot be observed or persisted. `ROLLBACK` triggers the same undo on demand: everything since `BEGIN` is discarded.

### Consistency

```sql
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
-- fails: CHECK (balance >= 0) when the row only has 400
ROLLBACK;
```

DuckDB raises `Constraint Error: CHECK constraint failed on table accounts` and aborts the transaction. After the transfer above, account 1 holds 400.00; subtracting 500 would leave it at -100, so the engine refuses and the row stays at 400. The rule lives in the table definition, not in application code, so every writer — an app, an ad-hoc `duckdb` session, a dbt job — is held to it. A retry with a valid amount succeeds because it keeps the constraint satisfied.

### Isolation

```sql
BEGIN;
SELECT sum(balance) FROM accounts;
-- other work, other connections may be writing
SELECT sum(balance) FROM accounts;  -- same snapshot
COMMIT;
```

Both `SELECT sum(balance)` statements return the same total even if another connection commits a transfer between them. DuckDB takes a snapshot of the database at the transaction's first statement, and every read inside the transaction sees that snapshot until `COMMIT`. That is what MVCC buys: writers create new row versions instead of overwriting old ones, so readers never block and never see a half-applied transfer — the debit visible, the credit missing. A report that runs inside one transaction does all of its arithmetic against one version of the data, which is why the two sums match.

### Durability

```sql
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 2;
COMMIT;
-- After COMMIT returns, a crash must not lose the transfer.
```

`COMMIT` returns only after DuckDB has written the change to its write-ahead log and flushed it to disk. If the process crashes or the machine reboots after that point, recovery replays the log and the transfer is still there. The guarantee is tied to the database file: an in-memory DuckDB instance has no file and no WAL, so nothing survives the process ending. Run one against a file when the data has to outlive the session.

## What is ACID vs BASE?

| | ACID | BASE |
|---|---|---|
| Stands for | Atomicity, Consistency, Isolation, Durability | Basically Available, Soft state, Eventually consistent |
| Priority | Correctness of each transaction | Availability under partition |
| After a write | Readers see the committed state (within the isolation level) | Readers may see a stale replica until it converges |
| Typical engines | PostgreSQL, DuckDB, InnoDB, Oracle | Default Redis, CouchDB, many older NoSQL stores |
| Failure mode you accept | A write may wait or abort | A read may be briefly wrong |

MongoDB (multi-document transactions since 4.0), some DynamoDB modes, and Cassandra's tunable consistency sit between the poles. Check the product, not the "NoSQL" label.

The choice is workload-shaped. A checkout, a ledger, a warehouse transform: ACID, because a wrong answer costs money. A session cache or a like counter: BASE is fine, because a stale read for a few hundred milliseconds costs nothing.

## What is ACID compliance?

ACID compliance is a property of the engine: it promises all four guarantees — atomicity, consistency, isolation, durability — on every transaction, including across crashes and power failures. An ACID transaction is one unit of work that used that promise. The words are not interchangeable: compliance describes the database; a transaction is the unit of work it protects.

The label matters in analytics because it was not always the default. Legacy [data warehouses](/learn/what-is-a-data-warehouse/) often relaxed transactional guarantees to speed up bulk loads, which is where half-loaded facts and dirty reads entered the folklore. Modern engines, DuckDB and MotherDuck among them, run [analytical queries](/learn/what-is-OLAP/) under full ACID compliance: a report reads the state before a load or the state after it, never the middle.

## What are SQL isolation levels?

ANSI SQL defines four levels by which of three anomalies they allow.

| Isolation level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| READ UNCOMMITTED | yes | yes | yes |
| READ COMMITTED | no | yes | yes |
| REPEATABLE READ | no | no | yes |
| SERIALIZABLE | no | no | no |

- **Dirty read:** you see another transaction's uncommitted write.
- **Non-repeatable read:** the same row changes between two reads in your transaction.
- **Phantom read:** a `WHERE` range gains or loses rows because another transaction inserted or deleted.

**DuckDB's position:** snapshot isolation via a [bulk-optimized MVCC](https://duckdb.org/2024/09/25/changing-data-with-confidence-and-acid.html). There is no `SET TRANSACTION ISOLATION LEVEL`. Every `BEGIN` gets a consistent snapshot. That blocks the three ANSI anomalies above. Snapshot isolation is not SERIALIZABLE: write-skew histories that SERIALIZABLE would reject can still commit. For analytical DuckDB workloads that is almost never the binding constraint.

Postgres and MySQL let you pick a weaker level for throughput. DuckDB does not.

## How do ACID transactions work in the lakehouse?

A directory of [Parquet](/learn/why-choose-parquet-table-file-format/) files is not a database. Two writers can overwrite the same file. A reader can see a half-written object. That is why Databricks ranks for "ACID transactions" with a Delta Lake page, not a glossary of the acronym.

Table formats add a commit log on top of the files:

| | What is ACID | Catalog | Typical data files |
|---|---|---|---|
| Parquet (files only) | Nothing. Files are immutable blobs | None | Parquet |
| Apache Iceberg | Snapshots, schema evolution, concurrent writers via optimistic commit | File metadata + external catalog (Glue, REST, Nessie, Hive) | Parquet (also ORC/Avro) |
| Delta Lake | `_delta_log` commits, time travel, schema enforcement | Log in object storage; optional Hive/Glue | Parquet |
| DuckLake | Commits, multi-table transactions, time travel, schema evolution | SQL database (Postgres, MySQL, DuckDB, MotherDuck) | Parquet |

Iceberg and Delta keep the transaction log as files next to the data. DuckLake keeps it in a SQL database, so a commit is a SQL transaction rather than a new JSON/Avro metadata file. The data is still Parquet. MotherDuck can host that catalog and the object store.

If you need `UPDATE` / `DELETE` / concurrent writers on a lake, you need a table format. If you need a single-node analytical database with `BEGIN` / `COMMIT`, you need DuckDB. Those are complementary, not substitutes.

## Why should engineers care?

A pipeline that fails on row 4,000,001 should not leave 4,000,000 half-applied rows. A dashboard that sums sales while an ingest is mid-flight should not mix old and new grains. A `COMMIT` that returned should still be there after the node reboots.

That is atomicity, isolation, and durability. Consistency is the `CHECK` that stops the overdraft, and the foreign key that stops an order without a customer.

The four properties are also a debugging map. Committed data missing after a restart points at durability. A report showing intermediate values no single job ever wrote points at isolation. A batch that failed halfway and left the tables clean is atomicity doing its job. Knowing which guarantee covers which symptom turns "the dashboard is wrong" into a short list of suspects.

MotherDuck inherits DuckDB's ACID contract in the cloud. It does not invent a fifth letter.

## Which databases are ACID compliant?

- **Fully ACID:** PostgreSQL, SQL Server, Oracle, MySQL (InnoDB), DuckDB / MotherDuck.
- **Tunable / sometimes ACID:** MongoDB (multi-document since 4.0), Cassandra (tunable consistency), some DynamoDB modes.
- **BASE by default:** Redis, CouchDB, many caches.

The middle tier is where labels mislead. MongoDB has been ACID for multi-document transactions since 4.0, but not in every deployment topology. Cassandra trades consistency per query. DynamoDB transactions exist but cost extra read and write capacity. Check the mode you are actually running, not the product page.

For analytics, ACID means a transformation either lands or it does not. Reports do not see a half-merged dimension.