Introduction
Your program keeps opening transactions. A signup writes a new user row. A checkout debits one account and credits another. A form submission updates three related tables at once. You wrap it all in BEGIN and COMMIT and move on, trusting that the database will handle whatever happens in between.
Most of the time it does. But what is it actually promising you when it handles that? And what does it have to do behind the scenes to keep that promise?
Say a user transfers ₹1,000 from Account A to Account B. The application runs two updates: subtract 1,000 from A, add 1,000 to B. Now say the server crashes right after the first update runs but before the second one does.
Account A: -₹1,000
Account B: +₹0
That money didn't move. It vanished. No error message fixes that, and no user accepts "the server restarted" as an explanation for their missing balance.
This is the exact problem a set of guarantees called ACID was built to solve. Most developers can recite the acronym, Atomicity, Consistency, Isolation, Durability, without being able to explain what any of the four words actually promise, or what the database has to do internally to keep those promises. This article tries to fix that.
--
1. What Is a Transaction?
Before ACID makes sense, you need to understand what a transaction actually is.
A transaction is a group of one or more database operations treated as a single logical unit of work. Either the whole group succeeds, or none of it does. The bank transfer above is a textbook transaction: two updates that only make sense together.
In SQL, a transaction usually looks like this:
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
COMMIT;
BEGIN tells the database "everything from here on is one unit." COMMIT tells it "we're done, make it permanent." If something goes wrong in between, a constraint violation, a crash, the application deciding to cancel, the database can issue a ROLLBACK instead, undoing any changes the transaction made so far. From the outside, it's as if the transaction never ran at all.
That single idea, a transaction either fully happens or fully doesn't, is the seed ACID grows out of.
--
2. Atomicity: All or Nothing
Atomicity is the guarantee that a transaction's operations are treated as one indivisible unit. If any part fails, the transaction's own effects are rolled back, so it does not leave behind a partial result.
This is what prevents the "money disappeared" scenario. If the second UPDATE never runs, the database rolls back the first one too, so Account A never actually loses its money in any state a user or another transaction can observe.
Worth being precise about what atomicity does and doesn't cover. It guarantees the transaction as a whole is all-or-nothing. It doesn't mean every individual statement behaves identically across every database engine, and it says nothing about what other concurrent transactions can see while this one is running.
That's a different guarantee, and it's the interesting one.
--
3. Consistency: Preserving the Rules of the Database
This is the word people get wrong most often, usually by simplifying it down to "the database has correct data." That's too vague to be useful.
In ACID, consistency means a successful transaction takes the database from one valid state to another valid state, where "valid" is defined by the constraints and rules you've told the database to enforce. Consistency isn't something the database magically guesses. You define it:
A CHECK constraint that says a balance can't go negative
A foreign key that requires an order's customer_id to reference an actual row in the customers table
A UNIQUE constraint that says two users can't share the same email address
If a transaction would violate any of these, the database refuses to commit it and rolls it back instead. Consistency is really the database enforcing your rules, not the database independently deciding what's correct.
It's also worth separating ACID consistency from a completely different idea that shares the same word: consistency in distributed systems, as in "eventual consistency" or "strong consistency" in something like the CAP theorem. That flavor of consistency is about whether different nodes in a distributed system agree on the current value of a piece of data. ACID consistency is about whether a single transaction preserves your integrity rules. Related in spirit, not the same concept. Conflating the two is one of the more common mistakes developers make when they first hear both terms.
--
4. Isolation: What Happens When Transactions Overlap
Atomicity and consistency mostly deal with a single transaction. Isolation deals with what happens when multiple transactions run at the same time and touch the same data.
Picture two users trying to buy the last seat on a flight at the exact same moment.
-- Transaction A (User 1)
SELECT seats_available FROM flights WHERE id = 42; -- returns 1
-- Transaction B (User 2), running concurrently
SELECT seats_available FROM flights WHERE id = 42; -- also returns 1
Both transactions see one seat available. Both proceed to book it and decrement the count. If isolation is weak and nothing else steps in, both bookings can go through, and the airline has just sold the same seat twice. Isolation is the guarantee that controls how much concurrent transactions can see and interfere with each other's in-progress work, but it isn't the only thing standing between you and this bug. How the transaction is written, whether the update is conditional on the seat still being available, what constraints exist on the table, and which isolation level you've chosen all play a part. Isolation shapes what a transaction can observe. Whether that's enough to prevent a specific race condition still depends on how you've built the transaction around it.
To understand why isolation is hard, it helps to name the specific ways things go wrong when it's weak.
Dirty Read: Transaction A updates a row but hasn't committed yet. Transaction B reads that uncommitted value. Then Transaction A rolls back. Transaction B now has a value in hand that never actually existed as far as the database's committed history is concerned.
Non-Repeatable Read: Transaction A reads a row. While Transaction A is still running, Transaction B updates that same row and commits. Transaction A reads the row again, in the same transaction, and gets a different value than before, even though it never asked to change anything.
Phantom Read: Transaction A runs a query like SELECT * FROM orders WHERE status = 'pending' and gets a set of rows. Transaction B inserts a new row that matches that condition and commits. Transaction A runs the exact same query again and now sees a row that wasn't there a moment ago.
Anomaly
What changes between two reads
Dirty Read
You read a value that was never committed
Non-Repeatable Read
An existing row's value changes underneath you
Phantom Read
The set of rows matching your query changes underneath you
--
5. Isolation Levels
Databases don't force you into one fixed behavior. The SQL standard defines isolation levels, essentially different trade-offs between correctness and concurrency.
Read Uncommitted, in the standard, lets transactions see uncommitted changes from other transactions, allowing dirty reads. In practice not every database actually implements it that way. PostgreSQL, for instance, accepts Read Uncommitted as a setting but treats it the same as Read Committed internally, so dirty reads never actually happen there.
Read Committed prevents a transaction from reading another transaction's uncommitted changes, but a value can still change between two reads in the same transaction. Prevents dirty reads, not non-repeatable or phantom reads.
Repeatable Read ensures that repeated reads within the same transaction see a consistent version of the data, so the same row won't appear to change value partway through. Some databases achieve this with locks, others with MVCC snapshots, so the exact mechanism differs, but the guarantee the reader experiences is the same. Prevents dirty and non-repeatable reads.
Serializable is the strongest level. Transactions behave as if they ran one after another, even though they may actually execute concurrently under the hood.
A useful mental model: as you move down this list, the database promises a more stable, more predictable view of the world, and pays for that promise with more locking, more version tracking, or more transactions forced to wait or retry.
One thing worth flagging clearly. These levels are not implemented identically everywhere. PostgreSQL's Repeatable Read is stricter about preventing phantoms than the SQL standard technically requires, because of how its underlying mechanism works. MySQL's InnoDB has its own specific behavior at each level too. If you're building something where these guarantees actually matter, financial systems, inventory management, check your specific database's documentation instead of assuming the textbook definition applies exactly as written.
--
6. How Databases Actually Provide These Guarantees
So far this has all been about what the guarantees are. Now, briefly, how a database actually delivers on them.
Locks. The simplest mechanism. Before a transaction reads or writes a row, it can acquire a lock on that row. Other transactions wanting conflicting access wait until the lock is released. Straightforward, but it can hurt concurrency, since transactions end up queued behind each other.
MVCC (Multi-Version Concurrency Control). A lot of modern databases, including PostgreSQL and MySQL's InnoDB, lean heavily on MVCC instead of pure locking for reads. Rather than making readers wait for writers, the database keeps multiple versions of a row around, and each transaction sees a consistent snapshot of the data as it looked at some point in time. Reads and writes happen concurrently without blocking each other in many cases, which is a big part of why MVCC databases tend to handle read-heavy workloads well.
Write-Ahead Logging (WAL). Before the database modifies its actual data files, it first writes a record of the intended change to a log on disk. If the database crashes right after a commit but before the change is fully reflected in the data files, it can replay the log on restart and recover the committed change. WAL is one important mechanism many databases use to support durable recovery, not a guarantee of durability all by itself.
Commit and rollback. The mechanisms that actually finalize or undo a transaction, working together with the log and whatever locks or version data the transaction was using.
None of these exist in isolation. A modern relational database may use MVCC to provide consistent views of data, locks to handle specific write conflicts, and WAL or a similar recovery mechanism to support durability.
--
7. Durability: Surviving the Crash
Durability is the promise that once a transaction has committed, its effects are permanent, even if the server crashes a moment later.
The database tells your application "payment successful." One second later, the server loses power. When it comes back online, that payment needs to still be there. If it isn't, durability has failed, and the consequences, a customer charged with no record of the charge, are exactly the kind of thing that makes people distrust software.
This is where WAL earns its keep. With the appropriate durability settings, the database writes the necessary recovery information to durable storage before acknowledging the commit, so it can replay that log during recovery and restore the committed state, even though the crash happened before the main data files were fully updated. The actual guarantee you get depends on how durability is configured, what storage the log itself sits on, and what kind of failures the database is designed to tolerate. WAL is an important mechanism many databases use to support this, not a guarantee that durability holds on its own.
Worth being honest about the limits here too. Durability is provided within the failure assumptions the database system is designed around. If the physical disk itself is destroyed and there's no replica or backup, no amount of WAL saves you. Durability is a strong guarantee against the kinds of failures the system is built to survive, not an absolute guarantee against every conceivable disaster.
--
8. Why ACID Gets Harder in Distributed Systems
Everything above gets meaningfully harder once your transaction stops living on a single machine.
A single-node database can coordinate a transaction within one database instance, without having to coordinate the transaction protocol across independent machines and networks. Coordinating a transaction within a single database instance is generally simpler than coordinating the same transaction across independent machines, even though the machinery involved, locking, logging, concurrency control, is already fairly sophisticated on its own.
Now imagine the same transaction needs to touch data spread across three separate database nodes: Node A, Node B, Node C. Each node can fail independently. A network partition can cut Node B off from the other two mid-transaction. A message telling Node C to commit can get delayed or lost. A node can crash after agreeing to commit but before actually doing it. Replication between nodes can lag, so a read on one node doesn't reflect a write that already committed on another.
To be clear: this doesn't mean distributed databases abandon ACID. That claim is simply false. Plenty of distributed and distributed-adjacent databases, think systems like Google Spanner or CockroachDB, do provide real transactional guarantees across machines. What changes is the cost of providing them. Coordinating a commit across multiple independent nodes generally requires mechanisms such as two-phase commit, consensus protocols, or other coordination techniques depending on the architecture, along with extra network round trips and careful handling of partial failure. That coordination adds latency and complexity that simply doesn't exist on a single machine.
This is also the point where distributed systems start forcing explicit trade-offs between consistency, availability, and latency. That's a large topic on its own, the kind of thing the CAP theorem tries to formalize, and it deserves a dedicated article rather than a rushed paragraph here. The point to take away is simpler: distributed ACID is achievable, but it is never free.
--
9. The Real Cost of ACID
None of these four letters are free.
Atomicity requires the database to track everything a transaction touches, so it can undo all of it if needed. Consistency requires constraints to be checked and enforced on every write. Isolation requires locks, versioning, or both, to control what concurrent transactions can see of each other. Durability requires writing to persistent storage in a careful, ordered way before a commit is acknowledged.
These guarantees interact with each other too. Push isolation higher, toward Serializable, and you generally reduce how much concurrency the database can offer, because more transactions end up waiting on each other or getting rolled back and retried. Spread a transaction across multiple nodes, and you add coordination overhead on top of everything else.
ACID isn't four independent boxes to check. It's a set of promises the database has to actively work to keep, and the effort required scales with how strict you ask it to be.
--
Conclusion
Think back to that ₹1,000 transfer from the start of this article. You should now be able to trace exactly what protects it at every step.
If the transfer fails halfway through, atomicity rolls it back completely. If two transactions try to touch the same account at the same time, isolation controls what each one is allowed to see and change. Once the transfer commits, durability guarantees it survives a crash a second later. Underneath all of it, consistency makes sure the transaction can't leave the accounts table in a state that violates the rules you defined, like a balance going negative.
If the accounts happened to live on different database nodes entirely, you now also know why that transfer would need more coordination, and more care, to get the same guarantees.
Next time you write BEGIN and COMMIT, remember you're not just grouping a couple of statements together. You're asking the database to keep four separate, genuinely difficult promises, all at once, no matter what fails in the middle.
Top comments (0)