Transactions and ACID, in practice

ACID has been referenced conceptually in nearly every earlier lesson in this domain — this is where the four letters get their actual hands, wrapped around real BEGIN/COMMIT/ROLLBACK statements.

Intermediate

4 min read

The problem a transaction solves: a multi-statement operation that must be all-or-nothing

UPDATE accounts SET balance = balance - 100 WHERE id = 1;   -- debit Ada
UPDATE accounts SET balance = balance + 100 WHERE id = 2;   -- credit Grace

If the first UPDATE succeeds and the second one fails — a crash, a constraint violation, a network hiccup — the database is left in a genuinely broken state: $100 vanished from Ada's account without ever reaching Grace's. Neither statement alone is wrong; the problem is that these two statements only make sense together, as a single logical operation, and nothing about running them as two separate statements guarantees they both happen or neither does.

BEGIN, COMMIT, and ROLLBACK

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

BEGIN starts a transaction — every statement after it is provisional, not yet permanent, until COMMIT makes the whole group durable at once. If anything looks wrong before committing (a check reveals Ada's balance would go negative), ROLLBACK instead undoes every statement since BEGIN, as if none of them had ever run:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- a check here reveals balance is now negative — abort
ROLLBACK;   -- both statements are undone; the database is exactly as it was before BEGIN

This is the concrete, hands-on version of the "undo a mistake before it's permanent" idea previewed in the INSERT/UPDATE/DELETE lesson.

A: Atomicity — all statements in the transaction, or none

Atomicity is precisely the guarantee demonstrated above: every statement between BEGIN and COMMIT succeeds together, or the whole group is rolled back together — there's no possible outcome where the debit happened but the credit didn't. This is the letter that most directly matches the money-transfer example, and it's exactly why the system design domain's monolith-vs-microservices lesson flagged cross-service "transactions" as a genuinely hard problem: a single database's transaction gives this guarantee for free, but two separate databases (one per microservice) have no shared transaction spanning both, which is why that lesson needed sagas and compensating actions instead of a plain COMMIT.

C: Consistency — the database's rules are never violated, even mid-transaction

Consistency means every COMMIT leaves the database satisfying all its own constraints — the NOT NULL, UNIQUE, FOREIGN KEY, and CHECK rules from the constraints lesson. A transaction that would leave a CHECK (balance >= 0) constraint violated gets rejected at COMMIT time (or earlier), rather than being allowed to commit a database into a state that breaks its own declared rules. This is the constraints lesson's guarantees, extended to also hold across a multi-statement operation, not just a single INSERT/UPDATE.

I: Isolation — concurrent transactions don't corrupt each other

Isolation is about what happens when multiple transactions run concurrently — real production databases handle many requests at once, and without isolation, two simultaneous transactions reading and writing the same row could interleave in ways that corrupt the result (both reading 500, both computing a new balance from that same stale 500, and one update silently overwriting the other). Databases offer different isolation levels — stricter isolation prevents more of these interleaving problems but costs more performance (more locking, more waiting); this is a genuine, explicit trade-off real systems have to choose, not a single fixed guarantee every database gives by default.

D: Durability — once committed, it survives a crash

Durability means once COMMIT returns successfully, that data is guaranteed to still be there even if the database process crashes, or the power fails, one second later — the database has actually written the change to persistent storage (not just held it in memory) before confirming the commit succeeded. This is the guarantee that makes COMMIT meaningfully different from just "the query appeared to work" — a committed transaction's effects are permanent, full stop, regardless of what happens to the server immediately afterward.

Why this domain kept referencing ACID before actually defining it

The constraints lesson's NOT NULL/UNIQUE/FOREIGN KEY/CHECK are Consistency's actual mechanism. The INSERT/UPDATE/DELETE lesson's ROLLBACK preview was Atomicity in miniature. The system design domain's CAP theorem and monolith-vs-microservices lessons both lean on "what a single database's transaction gives you for free" as the baseline that distributed systems have to work much harder to approximate. ACID isn't a separate, abstract theory sitting apart from everything covered so far — it's the name for the specific set of guarantees every other lesson in this domain has been quietly relying on the whole time.