ACID Properties: Mermaid Flowchart Diagram

About Source

ACID is the set of four guarantees — Atomicity, Consistency, Isolation, and Durability — that define what it means for a database system to process transactions reliably.

This diagram maps each ACID property to the mechanism the database uses to enforce it. Atomicity means that all operations within a transaction either all succeed or all fail. The database enforces this through its COMMIT/ROLLBACK mechanism and the undo log: if any statement fails or the process crashes mid-transaction, the engine uses the undo log to reverse every change from that transaction as if it never ran.

Consistency means a transaction can only bring the database from one valid state to another, never leaving it in a state that violates defined constraints, foreign keys, or triggers. This is enforced partly by the database engine (constraint checking on write) and partly by the application's business logic.

Isolation controls how concurrent transactions see each other's in-progress changes. The SQL standard defines four isolation levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — each offering progressively stronger guarantees at the cost of more locking or more version overhead. Most databases default to Read Committed. The MVCC Workflow diagram shows how Postgres implements isolation without taking read locks.

Durability guarantees that once a transaction is committed, its changes survive crashes and restarts. This is achieved by writing to the Write-Ahead Log before the commit returns, then fsyncing to disk. The committed state can always be reconstructed by replaying the WAL. The Transaction Lifecycle diagram shows exactly when the WAL fsync occurs relative to the COMMIT acknowledgment. ACID guarantees are central to relational databases; NoSQL systems often relax them in favor of availability, as captured in the CAP Theorem Model.

Frequently asked questions

ACID stands for Atomicity, Consistency, Isolation, and Durability — the four properties that define reliable transaction processing in a relational database. Together they ensure that data is never left in a partial or corrupt state, even in the presence of failures.

Atomicity uses COMMIT/ROLLBACK and the undo log to make transactions all-or-nothing. Consistency enforces schema constraints and triggers on every write. Isolation uses locking or MVCC to prevent concurrent transactions from corrupting each other's reads. Durability writes to the Write-Ahead Log and fsyncs to disk before acknowledging a COMMIT, so committed data survives crashes.

Choose an ACID-compliant database whenever data correctness is non-negotiable — financial systems, order management, healthcare records, or any workflow where partial writes would cause business-logic errors. ACID is the baseline for any multi-step operation that must succeed or fail as a whole.

A frequent mistake is assuming ACID protects against application-level bugs — it does not. If your code commits a logically wrong value, ACID dutifully persists it. Another common error is choosing too high an isolation level (Serializable) for all queries, which adds unnecessary locking overhead, or too low a level (Read Uncommitted) where dirty reads corrupt application logic.

mermaid

flowchart TD
    ACID[ACID Properties] --> A[Atomicity]
    ACID --> C[Consistency]
    ACID --> I[Isolation]
    ACID --> D[Durability]

    A --> A1[All operations succeed\nor all are rolled back]
    A --> A2[Undo log reverses\npartial changes on failure]

    C --> C1[Constraints and rules\nalways satisfied]
    C --> C2[Foreign keys, check\nconstraints enforced on write]

    I --> I1{Isolation Level}
    I1 -->|Weakest| IL1[Read Uncommitted\nDirty reads allowed]
    I1 --> IL2[Read Committed\nOnly committed data visible]
    I1 --> IL3[Repeatable Read\nSnapshot held for transaction]
    I1 -->|Strongest| IL4[Serializable\nFull serial execution order]

    D --> D1[WAL written and fsynced\nbefore COMMIT returns]
    D --> D2[Changes survive crash\nand can be replayed]