ACID

Overview

ACID is shorthand for four guarantees that a transactional database makes about how it processes work. Together they ensure that transactions behave predictably even in the presence of concurrent access, crashes, or hardware failures.

The Four Properties

• Atomicity — a transaction's operations either all succeed or none do; there's no partial commit.
• Consistency — a transaction moves the database from one valid state to another, respecting constraints, types, and referential integrity rules.
• Isolation — concurrent transactions don't see each other's uncommitted changes; the effective isolation level determines exactly what anomalies are prevented.
• Durability — once a transaction commits, its changes survive crashes and power loss, typically because they were written to a write-ahead log before the commit was acknowledged.

Why ACID Matters

ACID guarantees are what make it safe to build applications on top of a shared database without reasoning about every possible interleaving of concurrent operations. Without them, a crash mid-write or two simultaneous updates could silently corrupt or lose data.

ACID in DuckDB

DuckDB is a fully ACID-compliant database. It implements a variant of MVCC inspired by the HyPer research system, tailored for hybrid analytical workloads: updates are applied in place and prior versions are kept in an undo buffer so concurrent readers and writers don't block each other. Durability is provided by a write-ahead log that records changes before they're applied to the main database file, and CHECKPOINT periodically merges the WAL into the database file. Because DuckDB uses optimistic concurrency control, isolation is enforced by aborting a transaction with a conflict error if it tries to commit changes that overlap with another transaction's writes, rather than by blocking with locks.