Transactions and ACID

ACID is the set of guarantees a database transaction provides. Each letter is best understood through what breaks in a real application if that specific guarantee were missing — this page walks through all four with a concrete example each, using the transfer of money between two accounts as the running scenario (the same example the MySQL transactions page uses for its syntax; this page focuses on why each property matters).

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

Atomicity#

Atomicity guarantees that a transaction's statements all succeed together, or none of them take effect at all — there's no partial, half-applied state.

What breaks without it: imagine the application crashes (or the database connection drops) exactly between the two UPDATE statements above. Without atomicity, account 1 has already been debited $100, but account 2 was never credited — $100 has simply vanished from the system. With atomicity, that crash instead leaves the entire transaction unapplied (or the database's crash recovery replays it to completion) — either both updates happened, or neither did, but never just one.

Consistency#

Consistency guarantees that a transaction moves the database from one valid state to another, respecting every constraint (foreign keys, NOT NULL, CHECK) defined on the schema — it's a property enforced by the schema design covered elsewhere in this section, that transactions are required to uphold.

What breaks without it: suppose accounts has a CHECK (balance >= 0) constraint, forbidding a negative balance. Without consistency enforcement, a transaction that debits an account below zero would simply commit an invalid state into the database — a bug (or a race condition) silently corrupts data that every other part of the application assumed could never happen, because the database was supposed to be the last line of defense against exactly that.

Isolation#

Isolation guarantees that concurrent transactions don't observe each other's uncommitted, in-progress changes — each transaction behaves (to some configurable degree, per the isolation level in play) as if it were the only one running.

What breaks without it: two transfers happen concurrently — one reads account 1's balance to compute a new value, and before it writes that value back, a second transaction reads the same stale balance and computes its own update from it. Without isolation, both transactions can silently overwrite each other's changes (a classic "lost update"), leaving the account's final balance wrong even though each individual transaction looked correct on its own. This is exactly what isolation levels (covered in depth on the MySQL and PostgreSQL transaction pages) are designed to prevent, at varying strictness and performance cost.

Durability#

Durability guarantees that once a transaction commits, its changes survive — even a crash, a power loss, or a server restart immediately afterward.

What breaks without it: a customer completes a purchase, the application shows a "payment successful" confirmation, and the server crashes one second later. Without durability, that committed transaction could simply disappear on restart — the customer was charged (or believes they were), but the order no longer exists anywhere. Durability is what makes "the database said COMMIT succeeded" an actual, trustworthy guarantee rather than an optimistic one; it's typically implemented via a write-ahead log flushed to disk before the commit is acknowledged to the caller.

Tying ACID to real transaction boundaries#

None of this is abstract — it directly determines where START TRANSACTION/COMMIT boundaries belong in application code. The money-transfer example above is a transaction boundary precisely because "debit one account, credit another" is a single logical operation from the business's point of view — it must not be observable, or leave the database, in a half-done state. A transaction boundary should wrap exactly the set of writes that only make sense together: creating an order and decrementing the corresponding product's stock, for instance, belongs in one transaction, because "an order exists but stock was never reserved" (or the reverse) is exactly the kind of inconsistent state ACID exists to rule out.

A common design mistake is drawing transaction boundaries too narrowly (each UPDATE in its own auto-committed transaction, as MySQL does by default without an explicit START TRANSACTION) for an operation that's logically one unit — reintroducing the exact partial-failure risk atomicity is supposed to eliminate. The opposite mistake — wrapping far more work than necessary in one long-running transaction (an external API call, user input, unrelated writes) — holds locks longer than needed and hurts concurrency, as covered in the MySQL locking page.

Common mistakes#

• Performing a multi-step business operation as several independent, auto-committed statements instead of one explicit transaction, leaving a real window for partial failure.
• Assuming isolation eliminates all forms of concurrent-access bugs — it prevents specific, well-defined anomalies (dirty reads, non-repeatable reads, and more depending on the level chosen), but application-level race conditions (like two requests both checking "is there stock available?" before either decrements it) still need explicit handling, such as row locking (SELECT ... FOR UPDATE) or an atomic conditional update.
• Wrapping unrelated operations into one oversized transaction, holding locks far longer than the actual logical unit of work requires.
• Treating "durability" as "instant replication to every replica" — durability guarantees a committed transaction survives that server's crash (via its write-ahead log), not that it has already propagated everywhere; replication lag (covered in this app's System Design track) is a separate concern.