Transactions & ACID

Grouping several database operations into one all-or-nothing unit, so a failure partway through never leaves data half-changed.

What is it?

Imagine transferring money between two bank accounts: subtract $100 from Account A, then add $100 to Account B. Those are two separate operations. If the database crashes, or the connection drops, right after the subtraction but before the addition, Account A just lost $100 that vanished into nowhere.

A transaction groups multiple operations into a single all-or-nothing unit: either every operation in it succeeds and is saved permanently, or if anything fails partway through, everything in the transaction is rolled back as if none of it had ever happened. There's no in-between, half-applied state.

The guarantees transactions provide are usually summarized by the acronym ACID: Atomicity (all-or-nothing), Consistency (the data always ends up following the rules you've defined, like foreign keys), Isolation (transactions running at the same time don't see each other's half-finished work), and Durability (once a transaction is confirmed, it survives even a crash right afterward).

Explain like I'm 10

A transaction is like sealing several steps of a task inside an envelope: either the whole envelope gets delivered exactly as sealed, or it never gets sent at all. Nobody ever receives half an envelope.

Examples

A money transfer as a transaction

BEGIN;

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

COMMIT;

Both updates are wrapped between BEGIN and COMMIT. If anything fails after the first UPDATE but before COMMIT, the database can roll the whole thing back, and Account 1's balance is restored as if the transfer never started.

Rolling back on purpose

BEGIN;

UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 42;
-- Suppose application code checks the new quantity and finds it went negative:
ROLLBACK;

ROLLBACK explicitly undoes every change made since BEGIN, as if the transaction never happened — useful when application logic detects a problem partway through.

How it works

When a transaction begins, the database tracks every change made within it but doesn't make those changes visible or permanent to anyone else yet. COMMIT tells the database to make all of those changes permanent at once. ROLLBACK tells it to discard all of them, restoring the data to how it was before BEGIN.

To achieve isolation, the database controls how much (and when) one transaction's in-progress changes are visible to other transactions running at the same time, so two transfers happening simultaneously don't corrupt each other's math.

Why does it exist?

Real-world operations are frequently made of multiple steps that only make sense together. Without transactions, a crash, a network drop, or a bug partway through a multi-step operation could leave the database in a state that no correct sequence of operations would ever produce — money debited but never credited, an order marked paid with no payment recorded. Transactions exist to make "partly done" impossible.

When to use it

Wrap operations in a transaction whenever multiple statements need to succeed or fail together to keep the data correct — financial transfers, placing an order that also decrements inventory, creating a user account alongside its related settings row.

When not to use it

A single, standalone statement doesn't need an explicit transaction — most databases already treat one statement as its own transaction by default. Also avoid holding a transaction open for a long time (e.g. across a slow external API call), since it can block other transactions from proceeding.

Common mistakes

  • Forgetting to COMMIT, leaving changes uncommitted and eventually rolled back or lost.

  • Doing slow, unrelated work (like calling an external API) inside an open transaction, which can block other operations waiting on the same rows.

  • Assuming a crash mid-transaction leaves partial changes in place — properly used transactions guarantee it does not.

Practice exercises

  1. Easy:

    Explain, in your own words, what would go wrong with a money transfer if it were not wrapped in a transaction and the app crashed halfway through.

  2. Medium:

    Write a transaction that inserts a new order row and decrements a product's stock quantity, so both happen together or not at all.

  3. Hard:

    Explain each of the four ACID properties in your own words, using the money transfer example to illustrate each one.

Interview questions

What does a database transaction guarantee?

That a group of operations either all succeed and become permanent, or if any part fails, none of them take effect — there's no partially applied state.

What does the 'A' in ACID stand for, and what does it mean?

Atomicity — all operations in a transaction are treated as a single indivisible unit; either all of them apply or none do.

What is the difference between COMMIT and ROLLBACK?

COMMIT makes every change in the current transaction permanent; ROLLBACK discards every change made since the transaction began, as if it never happened.