Reference · Diagram Design

ACID and Where It Actually Holds

What each of the four letters guarantees, which engines honour them and with what caveats, and what BASE offers instead when a system gives ACID up.

ACID properties and which database engines guarantee them A reference in three layers. The first explains the four ACID properties — Atomicity, Consistency, Isolation and Durability — each with a concrete bank-transfer example. The second is a matrix of nine database engines against the four properties, marking full support, conditional support, partial support or none, with Cassandra's isolation cell flagged as the clearest example of a system that gives ACID up. The third contrasts ACID with the BASE model used by eventually consistent distributed stores. THE FOUR GUARANTEES A Atomicity All of it happens, or none of it does. No half-finished transactions. Bank transfer: If the deposit fails, the withdrawal is undone too. C Consistency The database never lands in a state that breaks its own rules. Bank transfer: Constraints, foreign keys and checks still hold afterwards. I Isolation Concurrent transactions do not see each other's half-done work. Bank transfer: Two simultaneous transfers cannot both spend the same balance. D Durability Once committed, it survives a crash, a restart or a power cut. Bank transfer: Written to a log on disk before the commit is acknowledged. WHERE EACH GUARANTEE HOLDS Engine vs. ACID property Atomicity all or nothing Consistency rules always hold Isolation no dirty reads Durability survives a crash PostgreSQL relational Yes Yes Yes Yes MySQL (InnoDB) relational Yes Yes Yes Yes SQLite · Cloudflare D1 relational Yes Yes Yes Yes Neo4j graph Yes Yes Yes Yes · write-ahead log MongoDB document Yes, since v4.0 Yes, in a transaction Snapshot isolation Yes DynamoDB key-value Yes, up to 100 items Yes, same region only Yes, at 2× the cost Yes Redis key-value Partial — no rollback No such concept Partial — no interleaving Depends on AOF config Cassandra column-family Per-partition only Eventual, not immediate No — BASE, not ACID LWT via Paxos only, ~1/3 the speed Yes · commit log ClickHouse OLAP Per insert only Eventual across replicas Experimental only Yes WHAT YOU GET INSTEAD THE STRICT MODEL ACID Correctness first. The write waits until every replica agrees it is safe. Cost: coordination, and lower availability when the network splits. THE RELAXED MODEL BASE Basically Available · Soft state · Eventually consistent. Accept the write now. Gain: availability and scale. Cost: readers may briefly see stale data. FULL CONDITIONAL PARTIAL NOT GUARANTEED FOCAL — THE CLEAREST CASE OF GIVING ACID UP