ACID Properties¶
What ACID is, precisely¶
A transaction is a group of reads and writes that the database treats as one unit. ACID names four guarantees a transactional database makes about transactions. They are easy to recite and easy to misunderstand, because each letter is implemented by a different mechanism, has a different cost, and has exceptions that matter in production. The measurements on this page were made on PostgreSQL 16.2 running locally on an Apple Silicon Mac.
Atomicity: all or nothing¶
If a transaction commits, all its changes take effect; if it aborts — explicitly, because of an error, or because the server crashed before commit — none of them do.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- crash here
COMMIT;
After a crash between the two updates, the debit must not survive. How engines guarantee it:
| Engine | Mechanism |
|---|---|
| PostgreSQL | Every row version records its creating transaction ID (xmin). A transaction's changes become visible only once its commit record is in the WAL and its status in the commit log (pg_xact) says committed. An aborted or crashed transaction's versions are simply never visible and are later removed by VACUUM. No undo is needed. See MVCC in PostgreSQL |
| InnoDB, Oracle | Rows are updated in place after the old values are written to an undo log. Abort or crash recovery applies the undo records in reverse. See MVCC in Storage Engines |
| LSM engines (RocksDB) | The whole WriteBatch is one WAL record with a checksum; recovery replays it entirely or not at all |
In every case, the commit record is the atomic point: one small write that flips the whole transaction from "not happened" to "happened."
Atomicity is about failure, not concurrency: it says nothing about what other transactions see while this one runs. That is isolation.
Consistency: invariants hold — partly the database's job¶
"Consistency" means the database moves from one valid state to another. The database enforces the invariants it can express:
| Mechanism | Example |
|---|---|
Types and NOT NULL |
Balance is an integer and present |
CHECK |
CHECK (balance >= 0) |
UNIQUE, primary keys |
No two users with the same email |
| Foreign keys | Every order references an existing customer |
| Exclusion constraints (PostgreSQL) | No two bookings for the same room overlap in time |
| Triggers | Arbitrary checks, at a cost |
Invariants that span rows or tables in ways a constraint can't express — "an order's total equals the sum of its line items," "at least one doctor is on call" — are the application's responsibility. The database helps only by providing the other three letters: if every transaction individually preserves the invariant, atomicity and (sufficient) isolation make sure the combination does too. The "at least one doctor on call" invariant was violated in the measured experiments under Repeatable Read even though each transaction was individually correct — see Isolation Levels and Anomalies.
Note that the C in ACID is unrelated to the C in the CAP theorem, which means linearizability.
Isolation: concurrent transactions don't interfere — to a degree¶
Ideal isolation is serializability: the outcome of running transactions concurrently equals the outcome of running them one at a time in some order. Because that is expensive, databases offer weaker isolation levels and default to one of them — PostgreSQL, Oracle, and SQL Server default to Read Committed; MySQL/InnoDB to Repeatable Read. Under those defaults, real anomalies happen.
Measured in PostgreSQL with two interleaved sessions (details and explanations in Isolation Levels and Anomalies):
| Scenario | Read Committed (default) | Repeatable Read | Serializable |
|---|---|---|---|
| Re-read a row another txn changed | Sees new value (100 → 50) | Sees 100 both times | Sees 100 both times |
| Read-modify-write in the application, two txns | Lost update: final 110, expected 120 | Second txn fails, 40001 |
Second txn fails |
| Two doctors go off call (write skew) | Both commit: 0 on call | Both commit: 0 on call | Second fails: 1 on call |
The isolation level is a correctness setting, not a performance knob to leave at its default without thought.
Durability: committed means it survives¶
Once COMMIT returns, the changes must survive a process crash, an OS
crash, and a power loss. The mechanism is the write-ahead log: the
commit record (and everything before it) is flushed to stable storage
before the client is told "committed"; data pages are written later.
After a crash, recovery replays the WAL. See
WAL and
Durability and fsync.
What durability costs: measured¶
Single-row INSERTs, each its own transaction, one client:
| Configuration | Commits/s | Latency per commit |
|---|---|---|
wal_sync_method = fsync_writethrough (macOS F_FULLFSYNC: really on the SSD's stable media) |
207 | 4.83 ms |
Default on macOS (open_datasync, which does not flush the drive's cache on macOS) |
20,424 | 0.049 ms |
synchronous_commit = off |
47,097 | 0.021 ms |
| 2,000 inserts in one transaction | — | 40 ms total (0.02 ms per row) |
Three conclusions:
- True durability costs one device flush per commit, here ~5 ms — 100x the cost of everything else in the commit. That number, not CPU, bounds single-client commit throughput.
- The default configuration was not durable on this OS — it was
100x faster because it skipped the drive cache flush. Whether a
"durable" commit is really durable depends on the OS, file system,
drive cache, and settings; verify, don't assume (PostgreSQL's
pg_test_fsynctool measures each method). - Batching is the main lever: many rows per transaction share one flush; many concurrent clients share flushes through group commit.
Weakening durability on purpose¶
| Setting | What can be lost | When reasonable |
|---|---|---|
synchronous_commit = off (PostgreSQL), WriteOptions::sync = false (RocksDB) |
The last few hundred ms of acknowledged commits on a crash; the database stays consistent | Analytics events, caches, logs where losing a moment of data is acceptable |
innodb_flush_log_at_trx_commit = 2 |
Up to ~1 s on an OS crash or power loss | Similar |
fsync = off (PostgreSQL) |
Database corruption on power loss | Never in production; only for throwaway test databases |
Durability beyond one machine¶
A single disk can fail outright. Real durability usually also means replication: a commit is acknowledged only after it reaches one or more replicas (synchronous replication), so it survives the loss of the whole primary machine. See Replication.
ACID vs. BASE¶
Many distributed NoSQL systems advertised BASE — Basically Available, Soft state, Eventually consistent — trading isolation and immediate consistency for availability and scale (see Eventual Consistency). The line has blurred: modern distributed SQL systems (Spanner, CockroachDB, TiDB, YugabyteDB) provide serializable ACID transactions across machines, paying for it with consensus round trips; many NoSQL systems added single-key or limited multi-key transactions.
Common pitfall¶
Treating "the database is ACID" as a complete correctness argument. Each letter has a default that weakens it: isolation defaults to Read Committed, which allowed the lost update and write skew measured above; durability can be quietly weakened by OS and drive behavior (the default macOS configuration measured here acknowledged commits 100x faster than a real flush allows); and consistency covers only what constraints can express. Know the isolation level each critical transaction runs at, verify the fsync behavior of the deployment platform, and encode invariants as constraints wherever possible rather than in application code alone.