SQL-01-016-P · Primer — Transactions and ACID Basics

The idea in one sentence

A transaction wraps several separate writes — subtract from one account, add to another — inside a single BEGIN ... COMMIT fence so the database treats them as one indivisible unit: either every change lands together, or if anything goes wrong, ROLLBACK erases the whole batch as if you never touched the data.

Why it exists

Real operations almost never fit in a single UPDATE. Transferring money is two writes — take from Alice, give to Bob. Placing an order is three — insert the order, decrement inventory, charge the card. The danger is what happens between those writes. Imagine the first UPDATE succeeds, and then the power blips, the connection drops, or the second statement hits an error. Now Alice's money is gone and Bob never got it. The database is in a broken, half-finished state that no correct business rule allows, and no amount of careful SQL in a single statement can fix a gap that lives between two statements.

Transactions exist to close that gap. They let you tell the database, "these writes belong together — don't let the outside world ever see them half-done." If all the steps succeed, you COMMIT and they become permanent in one atomic flip. If anything fails partway through, you ROLLBACK and the database rewinds to exactly how it looked before you began, with no trace of the partial work. This all-or-nothing guarantee is what lets banks, stores, and booking systems trust a database with data that must never tear in half.

How it works

You open a transaction with BEGIN, run as many INSERT/UPDATE/DELETE statements as your operation needs, and then close it with exactly one of two words. COMMIT says "I'm done and everything worked — make it all permanent at once." ROLLBACK says "abandon everything since BEGIN — pretend none of it happened." Until you commit, your changes are provisional: your own session can see them, but other connections still see the old data, and a crash before the commit throws the whole batch away. That last point is the heart of atomicity — the "A" in ACID — which promises the transaction is indivisible: all its writes take effect or none do, never some. Here's the whole idea in one runnable script — a money transfer, then a transfer we deliberately abandon:

CREATE TABLE accounts (name TEXT, balance INTEGER);
INSERT INTO accounts VALUES ('Alice', 100), ('Bob', 50);

-- Transfer 40 from Alice to Bob, as ONE atomic unit
BEGIN;
UPDATE accounts SET balance = balance - 40 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 40 WHERE name = 'Bob';
COMMIT;                       -- both writes land together

SELECT name, balance FROM accounts ORDER BY name;
-- Alice | 60
-- Bob   | 90

-- Start another transfer, then change our mind
BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE name = 'Alice';
ROLLBACK;                     -- the UPDATE is erased

SELECT name, balance FROM accounts ORDER BY name;
-- Alice | 60   (unchanged — the rolled-back write never happened)
-- Bob   | 90

After COMMIT the transfer is permanent; after ROLLBACK the balances are exactly what they were before that second BEGIN — the failed write left no fingerprint.

The one gotcha

Nothing you do inside a transaction is real until you COMMIT — and forgetting to commit is a silent, invisible bug. In many database clients, if you type BEGIN, run your writes, and then close the connection or your program crashes without ever calling COMMIT, the database quietly rolls everything back. Your code ran with no errors, the UPDATE reported "1 row affected," you saw the new values in your own session — and then it all vanished, because an uncommitted transaction is discarded when the connection ends. The reverse trap bites people using tools with "autocommit" turned on, where every statement commits instantly and secretly, so their carefully grouped writes are not actually atomic — a crash between two statements leaves the first one permanently applied. The fix is to be deliberate about the boundary: know whether your connection is in autocommit mode or not, always pair every BEGIN with a definite COMMIT or ROLLBACK, and in application code wrap the transaction in a construct that commits on success and rolls back on any exception (a try/except or a with block) so the fence can never be left dangling.

See it — two writes commit together, or the whole batch rewinds

flowchart TD
    A["BEGIN<br/>open transaction"] --> B["UPDATE Alice<br/>balance - 40"]
    B --> C["UPDATE Bob<br/>balance + 40"]
    C --> D{"All steps<br/>succeeded?"}
    D -->|"yes"| E["COMMIT<br/>both writes permanent"]
    D -->|"no / crash"| F["ROLLBACK<br/>erase everything"]
    E --> G["DB consistent:<br/>money conserved"]
    F --> H["DB unchanged:<br/>as if never begun"]

The one-line takeaway

Wrap writes that must succeed or fail together in BEGIN ... COMMIT, and let ROLLBACK guarantee atomicity — the "A" in ACID — so the database is never left in a half-finished state.

Where you'll meet it

You'll meet transactions the first time you write any operation that touches more than one row or more than one table and must not tear in half: money transfers, order checkout, "create the user and their default settings," inventory that decrements as a sale is recorded. You'll meet them baked into every serious application framework — Django's atomic(), Rails' transaction do, raw connection objects with .commit() and .rollback() — because ORMs wrap your web request in a transaction so a crash mid-request rolls back cleanly instead of leaving corrupt half-data. You'll meet the gotcha the first time a script "worked" but the data never changed (you forgot to commit), or the first time autocommit silently broke your all-or-nothing guarantee. Interviewers reach for this constantly: "what does ACID stand for?", "walk me through a bank transfer and what atomicity guarantees," and the classic "the second UPDATE fails — what happens to the first one?" Answer "it rolls back, because the whole transaction is atomic — all or nothing," and you've shown you understand the single most important safety net in databases. The other three ACID letters — Consistency, Isolation, Durability — build on this foundation, but atomicity and the BEGIN/COMMIT/ROLLBACK trio are where every learner starts.

Named concepts: BEGIN/COMMIT/ROLLBACK, atomicity, ACID properties, all-or-nothing changes