Transactions & COMMIT / ROLLBACK

Advanced

⏱️ 12 mins read

Runs against the ShopCo tables themselves — employees (Topic 1) and orders (Topic 7) — plus the classic bank-transfer example, because transfers are where transactions were invented.

What You'll Learn

A transaction is a unit of work that either completes fully or not at all. Use BEGIN/START TRANSACTION to start, COMMIT to save permanently, ROLLBACK to undo all changes. SAVEPOINT creates a checkpoint for partial rollbacks. Transactions are essential for any multi-step operation (bank transfers, inventory updates, order placement).

Syntax

BEGIN TRANSACTION;
-- SQL statements
COMMIT;
-- or
ROLLBACK;

SAVEPOINT sp_name;
ROLLBACK TO sp_name;

Example

-- Bank transfer — must be atomic
BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;

-- Both succeed → save permanently
COMMIT;

-- If any step failed → undo everything
-- ROLLBACK;

-- Savepoint for partial rollback
SAVEPOINT before_update;
-- If only this part needs undoing:
ROLLBACK TO before_update;

Beyond the Basics

Data in play — reference tables for this topic
-- employees (from Topic 1)
-- id | name    | department_id | salary
-- 1  | Ada     | 1             | 145000
-- 2  | Grace   | 1             | 115000
-- 3  | Alan    | 2             | 92000
-- 4  | Edsger  | NULL          | 78000   <- the reassignment target
-- 5  | Barbara | 3             | 64000

-- orders (from Topic 7)
-- id | customer_id | status    | total
-- 1  | 1           | shipped   | 240.00
-- 2  | 2           | shipped   | 89.90
-- 3  | 1           | pending   | 430.00
-- 4  | 3           | shipped   | 59.50
-- 5  | 4           | cancelled | 120.00

COMMIT is not save — it's publish

Inside a transaction, your changes are already applied — to YOUR view of the data. Other sessions see nothing until COMMIT makes them public; ROLLBACK discards them entirely. Every statement outside an explicit transaction runs in autocommit: one statement, one invisible transaction, published instantly.

BEGIN;
UPDATE employees SET salary = salary * 1.1 WHERE id = 4;
-- In THIS session: Edsger earns 85800
-- In another session: still 78000 — the change is unpublished
COMMIT;   -- NOW other sessions see 85800
Testing inside a transaction shows your session's truth, not the database's. COMMIT is the moment the rest of the world finds out.

Two UPDATEs, one fact: the reassignment must be atomic

Promoting Edsger is really two facts: he joins department 1, and his salary rises 10%. Without a transaction, a crash (or a failed second statement) between the UPDATEs leaves a half-fact in the database — a permanent, invisible bug. Transactions make the pair indivisible: both publish, or neither does.

BEGIN;
UPDATE employees SET department_id = 1    WHERE id = 4;
UPDATE employees SET salary = salary * 1.1 WHERE id = 4;
-- Crash here? ROLLBACK (explicitly, or on reconnect) → no half-fact
COMMIT;
-- Now Edsger is dept 1 at 85800 — both, or neither, forever
Whenever two statements express one fact, wrap them. The rule of thumb: if the intermediate state would be a bug you'd have to fix by hand, it needs a transaction.

SAVEPOINT: partial undo inside one transaction

Sometimes part of the work is optional — try it, and undo just that piece if it fails, keeping the rest. SAVEPOINT names a position you can roll back TO without abandoning the whole transaction. Perfect for 'best effort' steps in an order flow.

BEGIN;
UPDATE orders SET status = 'shipped',
  shipped_at = '2024-03-12' WHERE id = 3;   -- keep this
SAVEPOINT before_bonus;
UPDATE orders SET total = total * 0.9 WHERE id = 3;  -- try a discount
-- Discount turns out to be disallowed:
ROLLBACK TO before_bonus;                    -- undo ONLY the discount
COMMIT;   -- shipped: yes; discount: never happened
SAVEPOINT gives try/catch semantics inside a transaction — commit the required steps, roll back the optional ones, publish once.

Common Mistakes

Forgetting to COMMIT — changes sit in an open transaction and lock rows, blocking other queries. Long-running transactions cause lock contention. Always handle COMMIT/ROLLBACK in application error handling.

Interview Tips

Explain isolation levels: READ UNCOMMITTED (dirty reads), READ COMMITTED (default in most DBs), REPEATABLE READ (MySQL default), SERIALIZABLE (strictest). Higher isolation = more consistent but more locking and lower throughput.

Official References

Test Yourself — 6 questions
Self-check · 0/6 answered

1. Inside BEGIN, you UPDATE Edsger's salary. Another session reads his row. What does it see?

2. Why must the reassign-to-dept-1 + 10%-raise pair run in one transaction?

3. The server crashes a microsecond BEFORE your COMMIT line. What survived?

4. SAVEPOINT before_bonus; ROLLBACK TO before_bonus; — what does this do?

5. Why are long-running transactions a problem?

6. What does autocommit mean?

Practice

Project each account holder's net flow, plus the net flow after a ₹5,000 transfer from account 1 (Riya) to account 3 (Nisha) — computed with CASE so both sides of the transfer are one atomic expression.

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Why are long-running transactions a problem?

They hold locks on the rows they touched, blocking other writers, and force the engine to keep old row versions alive for their snapshot (bloat in PostgreSQL's vacuum, long undo logs in MySQL/InnoDB). Keep transactions short: do the reading and computing in the app, then transact only the writes.

If every statement auto-commits, why do I ever see 'idle in transaction'?

Because a transaction was opened (BEGIN, or a failed statement in some drivers) and never finished — usually a connection that errored out mid-flow. Those sessions hold locks and bloat forever until killed. Always pair BEGIN with COMMIT/ROLLBACK, including on error paths.

Back to Full Roadmap