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.

Step through the transfer below, first with no transaction: each UPDATE commits on its own, and the server crashes between them. Predict the balances after the restart, then flip the switch to run the same transfer inside a transaction.

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. Ideally 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 ever see committed data), repeatable read (rows you've read won't change under you) and serializable (the strictest: the outcome is as if the transactions ran one after another). Looser levels allow anomalies. A dirty read sees another transaction's uncommitted change, which might later be rolled back; read committed already prevents that. A lost update is subtler, and read committed does not prevent it.

Below, two ATM withdrawals hit the same account at once. Each reads the balance, subtracts $30 and writes the result back. Predict the final balance under read committed (the default in PostgreSQL, SQL Server and Oracle), then switch the isolation level to serializable.

Engines differ in how they enforce serializable. Some reject the late write with a serialization error; others make one transaction wait for the other's locks, or abort one of them as a deadlock. Either way, your code must be ready to retry the whole transaction, starting from its reads. If you'd rather stay at read committed, there are two common fixes for this exact pattern: lock the row you're about to change with SELECT … FOR UPDATE, so the second ATM waits for the first; or let the database do the arithmetic in one statement, UPDATE accounts SET balance = balance - 30, so there's no stale number in your code to write 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.

How can a database undo a change after the power fails, or keep a change it never finished writing to the table? Almost every database relies on one structure: an append-only write-ahead log. Every change goes into the log before it touches the table, and COMMIT is just one more line in that log. A transaction counts as committed the moment its COMMIT line is safely on disk, and only then does your app hear "OK".

The table files themselves are written later, whenever convenient, so after a crash they can be half-updated in either direction: holding changes that never committed, or missing changes that did. On restart, recovery reads the log and repairs them. Changes from transactions with a COMMIT line are replayed; changes from transactions without one are undone. Step through the transfer one more time: you choose when the power fails, then predict what recovery does.

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.