ACID & Transactions

Imagine moving $100 from your checking account to your savings account. Behind the scenes that's two separate steps: debit $100 from checking, then credit $100 to savings. Now suppose the first step succeeds but the database crashes before the second one runs. The $100 has left checking but never arrived in savings — it has simply vanished. That's the nightmare scenario databases are built to prevent.

The fix is to treat both steps as a single, inseparable action. Either both happen or neither does. This bundle is called a transaction, and the guarantees that make it trustworthy are summed up by the acronym ACID.

A transaction is a group of one or more operations that the database treats as a single indivisible unit of work. You mark the beginning, run your reads and writes, and then either commit (make every change permanent) or rollback (throw every change away and return to how things were before you started).

The key promise is that the outside world never sees a half-finished transaction. From any other user's point of view, the whole bundle either happened completely or didn't happen at all — there is no in-between state where the money has left one account but not reached the other.

Picture the money transfer wrapped inside a transaction. The database opens the transaction, performs the debit, then performs the credit, and only then commits — at which point both changes become permanent together. The operations are staged, not finalized, until that final commit.

But if something goes wrong partway through — the second account is locked, a constraint is violated, the server crashes — the database performs a rollback. Every change made so far inside the transaction is undone, and the database snaps back to exactly the state it was in before the transaction began. The animation below shows a transaction wrapping several operations that either all commit together or, on failure, all roll back as one.

Atomicity is the all-or-nothing rule. Every operation in the transaction succeeds together, or the entire transaction is rolled back as if it never ran. This is what saves our money transfer: if the credit step fails, the debit is automatically undone, so the $100 can never disappear.

Consistency means a transaction always moves the database from one valid state to another valid one. Any rules you've defined — a balance can't go negative, an email must be unique, a foreign key must point to a real row — are checked, and if the transaction would break them, it's rejected instead of leaving the data in a broken state.

Isolation governs what happens when many transactions run at the same time. It ensures concurrent transactions don't step on each other — each one behaves as though it's running alone, even when dozens are interleaved. Databases offer different isolation levels that trade strictness for speed: read-committed (you only see committed data), repeatable-read (rows you've read won't change under you), and serializable (the strictest, as if transactions ran one after another). Looser levels allow anomalies like a dirty read, where one transaction sees another's uncommitted changes that might later be rolled back.

Durability promises that once a transaction commits, its changes are permanent — they survive crashes, power loss, and restarts. The database writes the change to durable storage (typically a write-ahead log) before reporting success, so a committed transfer stays committed no matter what happens next.

ACID is a natural fit for a single database server, where one machine controls all the data and can coordinate commits cleanly. But once data is spread across many machines — see replication — enforcing strict ACID across the whole cluster gets expensive and slow, because every node has to agree before anything commits.

Many large distributed systems therefore relax the guarantees in favor of a model nicknamed BASE: Basically Available, Soft state, Eventually consistent. Instead of insisting every copy of the data is identical at all times, BASE accepts that replicas may briefly disagree and will converge to the same value shortly after a write. This is eventual consistency: the data becomes correct everywhere, just not instantly.

Why give up the comforting strictness of ACID? Because of a fundamental trade-off captured by the CAP theorem: when the network between nodes breaks, a distributed system must choose between staying consistent and staying available. BASE systems lean toward availability, answering requests with possibly-stale data rather than refusing to respond.

The right choice depends on the data. A bank ledger or an inventory count wants ACID, because a wrong answer causes real harm. A social feed's like count or a product recommendation can happily use BASE, because a few seconds of staleness hurts no one. Many real systems even mix both — strict transactions for the money, eventual consistency for everything else.