ACID Transactions Explained: Atomicity, Consistency, Isolation & Durability

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 add commits on top of files.

What are the four ACID properties?

Assume a table already exists:

Copy code
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.

Copy code
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

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

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

Copy code
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.

What is ACID vs BASE?

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 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 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.

• 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. 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 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:

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.