Gyaan

Transactions & ACID

intermediate transactions acid sql databases consistency

A transaction is a group of database operations that succeed or fail together, as one unit. Either all of them happen, or none of them do.

In simple language — it’s an “all or nothing” wrapper around a bunch of statements. No half-done state.

The classic example: transfer ₹1000 from Asha to Rohan. That’s two writes — subtract from Asha, add to Rohan. If the power dies between them, Asha lost money that never reached Rohan. A transaction makes sure that can never happen.

The WHY — ACID

The four guarantees a transaction gives us spell ACID. This is the interview bread and butter, so let’s nail each one in plain words, using the bank transfer throughout.

ACID — the four promises
A
Atomicity — all or nothing. Both writes land, or neither does.
C
Consistency — the DB moves from one valid state to another; rules (constraints) hold. Total money is unchanged.
I
Isolation — concurrent transactions don't step on each other. Another transfer can't see our half-done state.
D
Durability — once committed, it survives a crash. Confirmed money stays moved even if the server dies.

Let’s unpack each with the transfer in mind:

  • Atomicity — the “all or nothing” bit. Subtract-from-Asha and add-to-Rohan are one indivisible action. If step two fails, step one is undone.
  • Consistency — the total across both accounts is the same before and after. Constraints like “balance can’t go negative” are never violated by a committed transaction.
  • Isolation — if two transfers run at once, each behaves as if it’s alone. Nobody sees another transaction’s uncommitted, half-written state. (How much isolation is a dial — that’s a whole topic on its own.)
  • Durability — the moment we get “committed”, it’s written to durable storage. A crash one millisecond later can’t lose it.

The HOW — COMMIT and ROLLBACK

We open a transaction, run our statements, then either COMMIT (make it permanent) or ROLLBACK (throw it all away). Until we commit, nothing is visible to the outside world.

BEGIN;                                              -- start the transaction

UPDATE accounts SET balance = balance - 1000 WHERE id = 'asha';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'rohan';

COMMIT;   -- both writes become permanent, together
-- if anything had failed above, we'd run ROLLBACK instead and lose both

And the failure path — if a check fails, we bail and nothing sticks:

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 'asha';
-- oops, Asha's balance would go negative — abort the whole thing
ROLLBACK;   -- Asha's 1000 is restored, as if nothing happened

In application code it’s usually a try/catch: commit on success, rollback in the catch.

await client.query("BEGIN");
try {
  await client.query("UPDATE accounts SET balance = balance - 1000 WHERE id = 'asha'");
  await client.query("UPDATE accounts SET balance = balance + 1000 WHERE id = 'rohan'");
  await client.query("COMMIT");   // success — make it stick
} catch (err) {
  await client.query("ROLLBACK"); // any failure — undo everything
  throw err;
}

One gotcha — auto-commit

By default, most databases run in auto-commit mode: each lone statement is its own tiny transaction that commits immediately. That’s fine for single writes, but the moment two writes must move together, we have to wrap them in an explicit BEGIN ... COMMIT. Forgetting this is a real production bug, not just an interview one.

Interview soundbite

A transaction bundles multiple operations into one all-or-nothing unit, guaranteed by ACID: Atomicity (all or nothing), Consistency (constraints stay valid), Isolation (concurrent transactions don’t see each other’s half-done work), Durability (committed data survives crashes). We wrap the work in BEGIN, then COMMIT to persist or ROLLBACK to undo — the classic example being a bank transfer where both the debit and credit must land together or not at all.

References