We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).
In short
ACID is a set of four guarantees that database transactions provide so your data stays correct even when things go wrong: Atomicity (all the steps in a transaction happen or none do), Consistency (every committed transaction leaves the database in a valid state), Isolation (concurrent transactions don't see each other's half-finished work), and Durability (once a transaction commits, its changes survive crashes and power loss).
In short
ACID is a set of four guarantees that database transactions provide so your data stays correct even when things go wrong: Atomicity (all the steps in a transaction happen or none do), Consistency (every committed transaction leaves the database in a valid state), Isolation (concurrent transactions don't see each other's half-finished work), and Durability (once a transaction commits, its changes survive crashes and power loss).
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers ACID across a run of lessons, not one page. These 4 alone are about 190 minutes of step-by-step reading, 2 of them with code you run in the browser, all with a quiz.
Database transaction guarantees that keep your data correct.
Two writers update the same Delta Lake table at once: who wins, and what does the loser see? I ran real writer processes against 3,475,226 NYC taxi trips, 5 runs per case. Two appends both committed every time. Two updates of different rows clashed every time, because they shared the same 9 files: one committed, the other got CommitFailedError. Splitting the table by the filter column let both commit. A check-then-append job started twice stored 317,150 trips for a day of 158,575 in 5 of 5 runs. In delta-rs 1.6.6, a WriteSerializable setting took effect only when spelled writeSerializable.
Design a payment processing system - ACID transactions, idempotency, reconciliation, retry strategies, and the saga pattern for distributed payments.
Distributed SQL databases that promise the scalability of NoSQL with the ACID guarantees of traditional SQL. CockroachDB, Google Spanner, and the NewSQL movement.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with ACID Propertiespay once,
yours for life
ACID describes four properties a database promises about a transaction, which is a group of reads and writes treated as one unit. The classic example is a bank transfer: subtract 100 from account A and add 100 to account B. Those two writes must succeed or fail together. If the system credits B but crashes before debiting A, you just printed money.
Atomicity means a transaction is all-or-nothing. If any step fails, the database rolls back every change so it looks like the transaction never started. There is no partial result left behind.
Consistency means the transaction moves the database from one valid state to another. Rules like foreign keys, unique constraints, and check constraints are never violated by a committed transaction. Part of this is on the database and part is on the application writing correct logic.
Isolation means transactions running at the same time do not corrupt each other. Even though the database may run thousands of them concurrently, each one behaves as if it had the database to itself. Durability means once you get the commit confirmation, the data is permanently stored and will survive a crash, a restart, or a power cut a millisecond later.
Atomicity and durability are usually handled by a write-ahead log (WAL). Before changing the actual data pages, the database appends a record of the change to a log file and flushes it to disk. PostgreSQL, MySQL with InnoDB, and SQLite in WAL mode all do this. If the server dies mid-transaction, on restart it replays committed log records and discards uncommitted ones, so the database recovers to a clean state.
Durability specifically depends on that flush hitting stable storage. This is why the famous fsync setting matters: if you let writes sit in an OS buffer for speed, a power cut can lose committed data. Strict durability waits for disk confirmation, which is slower but honest.
Isolation is enforced with locking or with multi-version concurrency control (MVCC). PostgreSQL and Oracle use MVCC, where each transaction reads a consistent snapshot of the data and writers create new row versions instead of blocking readers. The SQL standard defines four isolation levels (read uncommitted, read committed, repeatable read, serializable) that trade strictness for concurrency. Read committed is the common default; serializable is the strongest and prevents anomalies like phantom reads at the cost of throughput.
Reach for full ACID whenever correctness of the data matters more than raw speed: money movement, inventory counts, order processing, ticket booking, anything where a double-spend or a lost write is a real bug with real cost. Relational databases give you these guarantees by default, which is a big reason banks and ledgers run on them.
The cost is performance and scaling. Strong isolation requires coordination, and that coordination gets expensive across many machines. A single Postgres node enforces ACID cheaply, but spreading one transaction across servers in different regions means network round trips and locking that can stall under load.
This is the tension behind NoSQL and the BASE model (Basically Available, Soft state, Eventually consistent). Systems like early DynamoDB and Cassandra relaxed isolation and immediate consistency to get higher availability and horizontal scale. The industry has since swung back: many distributed databases now offer ACID transactions again, just with more engineering behind them. Pick based on your workload, not fashion.
Imagine a checkout. You wrap three statements in one transaction: insert an order row, decrement the product stock count, and insert a payment record. Atomicity guarantees that if the payment insert fails, the order and the stock change both roll back, so you never ship a product you never charged for.
Isolation matters here when two shoppers buy the last unit at the same time. Without it, both transactions could read stock as 1, both decrement to 0, and you oversell. With proper isolation (a row lock or a serializable transaction), the second buyer is forced to wait and then sees stock as 0, so they get an out-of-stock error instead of a phantom order.
Durability is the final promise: after the database returns COMMIT, your service can safely tell the customer the order is placed. Even if the server loses power one millisecond later, the order is on disk in the WAL and will be there when the machine comes back.
PostgreSQL
Fully ACID-compliant by default; uses a write-ahead log for atomicity and durability and MVCC for isolation across concurrent transactions.
MySQL (InnoDB)
The InnoDB storage engine provides ACID transactions with row-level locking and a redo log; powering ledgers and order systems at companies like Booking.com.
Amazon DynamoDB
Started as a BASE eventually-consistent store, then added ACID transactions in 2018 so apps can update multiple items atomically without giving up its scale.
Google Spanner
A globally distributed SQL database that keeps full ACID across data centers using synchronized TrueTime clocks; backs Google Ads and other money-critical systems.
Atomicity, Consistency, Isolation, and Durability. Together they are the four guarantees a database makes about a transaction so your data stays correct even during crashes or concurrent access.
ACID prioritizes correctness with strong consistency and isolation, common in relational databases. BASE (Basically Available, Soft state, Eventually consistent) relaxes those guarantees to get higher availability and easier horizontal scaling, common in NoSQL systems. ACID gives you immediate correctness; BASE gives you scale and accepts that data converges to consistency over a short delay.
Many now do, at least partially. MongoDB added multi-document ACID transactions in version 4.0, and DynamoDB added transactions in 2018. Older NoSQL designs guaranteed atomicity only for a single record, so check the specific database and version before relying on multi-record transactions.
Isolation, and to a degree consistency, because they require coordination between machines. Enforcing that concurrent transactions across many nodes behave as if they ran one at a time needs locking or consensus, which adds network round trips and limits throughput. That coordination cost is exactly why distributed databases like Spanner are hard to build.
It is a setting that controls how strictly the database keeps concurrent transactions from interfering. The SQL standard defines four: read uncommitted, read committed, repeatable read, and serializable. Higher levels prevent more anomalies like dirty reads and phantom reads but reduce concurrency. Read committed is a frequent default; serializable is the safest and slowest.
ACID on a Lakehouse: Two Writers, One Table, Who Wins
Two writers update the same Delta Lake table at once: who wins, and what does the loser see? I ran real writer processes against 3,475,226 NYC taxi trips, 5 runs per case. Two appends both committed every time. Two updates of different rows clashed every time, because they shared the same 9 files: one committed, the other got CommitFailedError. Splitting the table by the filter column let both commit. A check-then-append job started twice stored 317,150 trips for a day of 158,575 in 5 of 5 runs. In delta-rs 1.6.6, a WriteSerializable setting took effect only when spelled writeSerializable.
Design a Payment System
Design a payment processing system - ACID transactions, idempotency, reconciliation, retry strategies, and the saga pattern for distributed payments
NewSQL Databases
Distributed SQL databases that promise the scalability of NoSQL with the ACID guarantees of traditional SQL. CockroachDB, Google Spanner, and the NewSQL movement
Lakehouse Architecture: What a Folder of Parquet Files Gets Wrong
What is a lakehouse, and what breaks if a table is just a folder of Parquet files? Warehouse, lake and lakehouse in plain words first. Then a lab on 7,052,769 real NYC taxi trips: while a writer added February as 8 files, a reader that listed the folder saw every half-written state, 9 different row counts; a Delta Lake reader saw only 3,475,226 rows or 7,052,769, never anything between. During an overwrite, the folder reader counted 6,806,334 trips for one month that held 3,331,108. In a real race, 5 runs per case, the folder reader saw a half-written table 7 to 9 times per run and failed 955 to 5,003 reads; the Delta reader saw 0 half-written tables and 0 failures.
BASE
An alternative to ACID for distributed systems: Basically Available, Soft state, Eventually consistent. Trades strong consistency for availability.
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
Two-Phase Commit
A protocol ensuring all nodes in a distributed transaction either commit or abort together. Phase 1: prepare (vote). Phase 2: commit or rollback.
Write-Ahead Log
A technique where changes are written to a log before being applied to the database. Ensures durability and crash recovery.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
This page explains the idea. The "ACID Properties" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the ACID Properties lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about ACID in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
