Database Transactions

A transaction is a group of one or more SQL statements that are executed as a single, indivisible unit of work. Either every statement in the transaction succeeds and its changes are saved permanently, or — if anything goes wrong — none of the statements take effect and the database is left exactly as it was before the transaction started.

This matters whenever an operation involves multiple related changes that must happen together. If only some of the changes were applied and the rest failed silently, the database would be left in an inconsistent state that doesn't correspond to any valid real-world outcome.

Why it matters

Moving money between two accounts takes two updates, and a crash between them would destroy money. A transaction makes the two updates a single unit: both happen or neither does. Any operation that changes more than one row in a way that must stay consistent needs one.

BEGIN, COMMIT, and ROLLBACK

A transaction is controlled with three commands. BEGIN (or START TRANSACTION) marks the start of a transaction block. COMMIT saves all changes made since BEGIN permanently to the database. ROLLBACK discards all changes made since BEGIN, as if none of the statements ever ran. Many databases also run every individual statement in its own implicit transaction by default (called "autocommit"), and BEGIN/COMMIT/ROLLBACK are how you opt out of that to group several statements together.

• BEGIN / START TRANSACTION — opens a transaction block.
• COMMIT — makes all changes in the transaction permanent and visible to other connections.
• ROLLBACK — undoes all changes made since BEGIN, restoring the prior state.

Why Multi-Statement Operations Need Atomicity

Consider transferring $100 from Account A to Account B. This requires two separate statements: subtract $100 from A, and add $100 to B. If the subtraction succeeds but the server crashes, the network drops, or a constraint fails before the addition runs, $100 has simply vanished from the system — it left A but never reached B. Wrapping both statements in a transaction guarantees that either both changes happen together or neither does, so the money is never lost or duplicated.

A Concrete Bank Transfer Example

The pattern below is the classic illustration of why transactions exist: two UPDATE statements that must rise or fall together, with an explicit ROLLBACK if a business rule (insufficient funds) is violated partway through.

Examples

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 'A' AND balance >= 100;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 'B';

COMMIT;

UPDATE 1
UPDATE 1
COMMIT
(Account A is now $100 lower, Account B is $100 higher — both changes are visible together, or neither is.)

BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
-- Application checks the new balance and finds it went negative,
-- which violates the "no overdraft" business rule.

ROLLBACK;

-- Account A's balance is exactly what it was before BEGIN.
SELECT balance FROM accounts WHERE account_id = 'A';

ROLLBACK
balance: 250.00   -- unchanged, as if the UPDATE never ran

Exercise

Transfer 30 from account A to account B inside a transaction and commit it. Then start a second transaction that moves 500 from A to B, and roll it back because A cannot afford it. Finally list the balances.

Expected output: name | balance, A | 70, B | 80 (one per line)

CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER);
INSERT INTO accounts (name, balance) VALUES ('A', 100), ('B', 50);

-- TODO: a transaction that moves 30 from A to B, then commits

-- TODO: a transaction that moves 500 from A to B, then rolls back

SELECT name, balance FROM accounts ORDER BY name;

CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER);
INSERT INTO accounts (name, balance) VALUES ('A', 100), ('B', 50);

BEGIN;
UPDATE accounts SET balance = balance - 30 WHERE name = 'A';
UPDATE accounts SET balance = balance + 30 WHERE name = 'B';
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE name = 'A';
UPDATE accounts SET balance = balance + 500 WHERE name = 'B';
ROLLBACK;

SELECT name, balance FROM accounts ORDER BY name;

Quick quiz

Interview questions