Skip to content

02 · ACID Properties — Transactional Guarantees

Foundations · Topic 2 of 4


What is ACID?

ACID defines the four guarantees that make database transactions reliable:

Property Guarantee
Atomicity All-or-nothing: the transaction fully commits or fully rolls back
Consistency Correct transaction logic preserves invariants; declared constraints reject invalid states
Isolation The selected isolation level limits which concurrency anomalies are allowed
Durability Acknowledged commits survive the failures covered by the configured persistence and replication policy

Atomicity

A transaction is a unit. If any step fails, all changes are rolled back.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- both succeed or neither does

Cloud implementations

  • PostgreSQL: native multi-statement transactions
  • Spanner: global atomic transactions across regions
  • DynamoDB: TransactWriteItems (up to 100 items, single-region)
  • MongoDB: multi-document transactions (4.0+, replica sets)
  • Cassandra: lightweight transactions (LWT) via Paxos — limited, avoid for complex workflows

Consistency

The DB enforces constraints (foreign keys, uniqueness, check constraints) across every transaction.

-- Constraint prevents invalid state
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id);

Note

Consistency in ACID is different from the C in CAP theorem.


Isolation

Isolation controls what a transaction can see from other in-flight transactions. See Isolation Levels for full coverage.


Durability

Writes are persisted before the commit is acknowledged using the Write-Ahead Log (WAL).

sequenceDiagram
    Client->>DB: COMMIT
    DB->>WAL: Append log record
    WAL->>Disk: fsync
    DB->>Client: OK (committed)

ACID vs BASE

ACID BASE
Meaning Transaction correctness properties Informal design vocabulary: Basically Available, Soft state, Eventual consistency
Scope A transaction at a specified isolation level Convergence and availability goals of a distributed design
Caveat Does not imply linearizable replica reads Not a formal opposite of ACID; one product can offer both transactional and eventual operations

Trace a Commit and Its Failure Boundaries

A transaction moves from validation through durable log flush to acknowledgment; data pages can be flushed later

Edit this diagram in draw.io.

The two UPDATE statements above illustrate atomicity, not a complete transfer implementation. Production logic must validate both account IDs, reject insufficient funds, check affected-row counts, and use a consistent locking order. A transaction cannot infer a missing business rule.

Failure What the application should do
Constraint or serialization error before commit Roll back; retry the whole transaction only when appropriate
Connection lost while committing Treat the outcome as unknown; look up an idempotency key before repeating a payment
Machine lost after a locally durable commit Recovery depends on surviving storage and the replication acknowledgment policy
Accidental deletion replicated everywhere Restore from a tested backup/PITR chain; replication is not a backup

Check Your Understanding

#

Question 1

Does ACID mean a second request can always read the newest value?

#

Question 2

Can a timeout prove that a transfer failed?

Further reading: PostgreSQL transaction isolation.