Loading…
Loading…
SQL tutorial · Advanced
⏱️ 12 mins read
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).
BEGIN TRANSACTION;
-- SQL statements
COMMIT;
-- or
ROLLBACK;
SAVEPOINT sp_name;
ROLLBACK TO sp_name;-- 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;| 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 |
| 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 |
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 85800Promoting 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, foreverSometimes 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 happenedForgetting 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.
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.
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?
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 →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.
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.