SQL Transaction Control
ACID was the theory. This chapter is hands-on-keyboard SQL — every statement you actually type to control a transaction's boundaries.
By default, most database clients run in autocommit mode: every single SQL statement is automatically wrapped in its own implicit transaction and committed immediately after it runs. This is why a lone UPDATE statement "just works" without you ever typing BEGIN or COMMIT — you're technically still using a transaction, just an invisible, single-statement one.
▲ Common mistake
Forgetting that autocommit is on by default means a beginner can run a destructive UPDATE or DELETE with a missing WHERE clause and have it committed instantly, permanently, before realizing the mistake — always explicitly BEGIN before an exploratory or risky statement so you have the option to ROLLBACK.
BEGIN; -- turns off autocommit for the statements that follow, starts an explicit transaction UPDATE wallets SET balance = balance - 500 WHERE user_id = 1; UPDATE wallets SET balance = balance + 500 WHERE user_id = 2; -- inspect the result before deciding, if you want to SELECT * FROM wallets WHERE user_id IN (1, 2); COMMIT; -- makes both updates permanent -- or: ROLLBACK; -- discards both updates entirely, as if they never ran
Note that the SELECT in the middle sees the uncommitted updates from within the same transaction — a transaction always sees its own writes, regardless of isolation level (Chapter 04). Isolation governs visibility to other transactions, not to yourself.
💻 Code example
BEGIN; -- turns off autocommit for the statements that follow, starts an explicit transaction UPDATE wallets SET balance = balance - 500 WHERE user_id = 1; UPDATE wallets SET balance = balance + 500 WHERE user_id = 2; -- inspect the result before deciding, if you want to SELECT * FROM wallets WHERE user_id IN (1, 2); COMMIT; -- makes both updates permanent -- or: ROLLBACK; -- discards both updates entirely, as if they never ran
◆ The problem
A transaction with five steps fails on step 4. A full ROLLBACK discards steps 1-3 too, even if they were fine and expensive to redo — sometimes you want to undo only the failing part.
BEGIN; INSERT INTO orders (customer_id, total) VALUES (42, 1500); -- step 1 SAVEPOINT before_discount; -- named marker UPDATE orders SET total = total * 0.5 WHERE customer_id = 42; -- step 2: a discount that turns out to be wrong ROLLBACK TO SAVEPOINT before_discount; -- undoes ONLY step 2 — the order insert from step 1 survives UPDATE orders SET total = total * 0.9 WHERE customer_id = 42; -- step 2, corrected COMMIT; -- the order exists, with the CORRECT discount applied
◆ Under the hood
A SAVEPOINT is implemented as a named position within the same undo log from Chapter 02 — ROLLBACK TO SAVEPOINT replays the undo log backward only until it reaches that named position, rather than all the way back to the transaction's start. The transaction itself is still one single transaction; a savepoint doesn't create a separate one (see §06.4's important distinction).
💻 Code example
BEGIN; INSERT INTO orders (customer_id, total) VALUES (42, 1500); -- step 1 SAVEPOINT before_discount; -- named marker UPDATE orders SET total = total * 0.5 WHERE customer_id = 42; -- step 2: a discount that turns out to be wrong ROLLBACK TO SAVEPOINT before_discount; -- undoes ONLY step 2 — the order insert from step 1 survives UPDATE orders SET total = total * 0.9 WHERE customer_id = 42; -- step 2, corrected COMMIT; -- the order exists, with the CORRECT discount applied
▲ Common mistake — and a favorite interview trap
Standard SQL does not support true nested transactions. Calling BEGIN again while already inside a transaction typically either raises a warning and is silently ignored, or (depending on the client library) throws an error — it does not create an independent inner transaction that can commit or rollback separately from the outer one. What SQL actually gives you for "partial rollback" behavior is SAVEPOINT (§06.3) — genuinely different from nesting, because a savepoint has no independent commit of its own; it only ever resolves when the single enclosing transaction eventually commits or rolls back entirely.
This distinction matters directly for Chapter 09's Spring propagation types — when Spring says PROPAGATION_NESTED, it means "use a SAVEPOINT under the hood," not "start a genuinely separate database transaction." Understanding this SQL-level truth first is what makes that Spring behavior make sense later instead of feeling like arbitrary framework magic.
Want a visual for this concept?
Generate a diagram tailored to “SQL Transaction Control” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.
Sign in to generate a visual →