SQL Transaction Control — BEGIN, COMMIT, ROLLBACK & SAVEPOINT
The raw SQL vocabulary for controlling transaction boundaries directly, independent of any ORM or framework — what Spring's @Transactional is actually generating underneath, and the autocommit trap that catches almost every developer at least once.
Learning objectives
- Explain autocommit mode and why leaving it on by default is dangerous for anything beyond single, isolated statements.
- Use BEGIN, COMMIT, and ROLLBACK to explicitly control a multi-statement transaction's boundary.
- Use SAVEPOINT to roll back part of a transaction without discarding work that happened before it.
- Trace through a worked multi-statement transaction that fails partway through and rolls back correctly.
Story
A developer opens a database client, connects to production, and runs a cleanup query: DELETE FROM customers; — forgetting the WHERE clause they meant to add. In most query tools, by the time they notice the mistake, it's already too late: the entire table is gone, and it's already committed. There is no Ctrl+Z. The only path back is a backup restore, which means minutes or hours of downtime and very possibly some amount of genuinely lost data from between the last backup and the moment of the mistake.
This happens because of a setting almost nobody thinks about until it bites them: autocommit mode. Nearly every database client and command-line tool — GUI tools, CLIs, ORM connection defaults — runs with autocommit turned on out of the box. Under autocommit, every single SQL statement you execute is silently wrapped in its own implicit transaction and committed immediately, with no gap for you to inspect the result or change your mind. Run UPDATE users SET is_active = true WHERE id = 10; under autocommit, and the database behaves exactly as if you'd typed:
BEGIN;
UPDATE users SET is_active = true WHERE id = 10;
COMMIT;
...except you never typed the BEGIN or the COMMIT — they happened invisibly, instantly, and irreversibly, statement by statement.
For a single, well-tested, narrowly-scoped statement, autocommit is harmless and even convenient — you don't want to type BEGIN; ...; COMMIT; around every trivial read. The danger shows up specifically when you're running ad hoc, exploratory, or destructive statements directly against a database — exactly the situations where a typo, a missing WHERE clause, or a wrong table name is most likely, and where the cost of that mistake being instantly permanent is highest. Autocommit removes your safety net at precisely the moment you're most likely to need it: when you're typing SQL by hand, under time pressure, possibly against production.
The fix is a habit, not a setting most people remember to permanently change: before running anything destructive or exploratory directly against a database, start with an explicit BEGIN (this takes you out of autocommit for that session, for that one logical unit of work). Run your statements. Look at the results with a SELECT if you're not sure. Only then, once you're confident, run COMMIT. If anything looks wrong at any point before that final COMMIT, you still have a way out: ROLLBACK discards everything in that transaction, cleanly, as if none of it had happened — the exact same atomicity guarantee from a transaction's design, just put to use deliberately as a safety mechanism rather than only as a crash-recovery one.
This matters just as much, if not more, in application code than at an interactive terminal. An application that issues several related SQL statements against a connection running in autocommit mode is implicitly running each one as its own separate, independently-committed transaction — which means there's no atomicity across the group of statements as a whole, even if the application code visually groups them together in one function. If the second statement fails after the first one already committed, the first one's effect is permanent and uncorrected, regardless of what the application code does next. This is exactly why frameworks like Spring explicitly turn autocommit off for the duration of a @Transactional method and manage the transaction boundary themselves — the behavior you get from @Transactional is, underneath, precisely the discipline this subtopic describes, automated so a developer doesn't have to remember to apply it by hand on every method.
💻 Code example
-- Autocommit ON (the default in most clients) -- DANGEROUS -- for anything destructive or exploratory: DELETE FROM customers; -- No WHERE clause -- and because autocommit silently wrapped -- this single statement in its own instant BEGIN...COMMIT, -- the entire table is already gone and already permanent by -- the time the mistake is noticed. There is nothing to roll -- back to -- the commit already happened. -- The safe habit: make the transaction boundary explicit and -- deliberate BEFORE running anything destructive. BEGIN; DELETE FROM customers WHERE last_login < '2020-01-01'; -- Inspect the result before committing to it: SELECT COUNT(*) FROM customers; -- Only now, once the result looks right, make it permanent: COMMIT; -- If the SELECT above had shown something unexpected -- -- say, the count looked far too low because the WHERE clause -- was wrong -- the way back is still open at that point: -- ROLLBACK;
Story
An order-processing flow needs to do three things together: insert the order row, decrement the product's stock, and record a loyalty-points credit for the customer. All three have to succeed, or none of them should — inserting an order for a product that's actually out of stock, or crediting loyalty points for an order that didn't actually get inserted, are both states the business cannot tolerate. This is the textbook shape of a transaction, and tracing it step by step with the stock check failing partway through makes the mechanics of BEGIN, COMMIT, and ROLLBACK concrete in a way that's easy to lose track of in the abstract.
BEGIN (sometimes written START TRANSACTION, depending on the engine) is the statement that ends autocommit mode for the current session and opens an explicit transaction boundary. From this point forward, nothing any statement does is visible to other sessions, and nothing is permanent, until a COMMIT is explicitly issued. Any number of statements — inserts, updates, deletes, even reads — can happen between BEGIN and whatever closes the transaction; the database doesn't care how many statements are inside, only that they're all treated as one unit when the boundary closes.
Walking through the order scenario: BEGIN opens the transaction. The INSERT for the order row runs and succeeds — but note carefully, it is not yet durable or visible to anyone else; it exists only within this transaction's own view of the world so far. Next, the stock decrement runs as an UPDATE with a CHECK constraint (or an explicit application-level check) guarding against negative stock — and here, say, it turns out the product's stock is actually zero because of a race with another order that committed moments earlier. That UPDATE fails, either by violating a constraint outright or by an explicit check inside the transaction's own logic deciding the result is unacceptable.
At this exact point, the right response is ROLLBACK, not trying to "undo" the order insert with a separate DELETE. ROLLBACK discards every single change made since BEGIN — not just the failed stock update, but the order insert that succeeded just fine a moment earlier too — and returns the database to exactly the state it was in before this transaction started. This is atomicity doing its job: the transaction, as a whole, failed, so the whole thing is undone, including the parts that individually "worked." The order row that was inserted a moment ago is gone, cleanly, as if it had never been attempted. No orphaned order exists for a product that was never actually reserved.
If, instead, the stock decrement had succeeded, the flow would continue to the loyalty-points insert, and only after every step in the unit of work has succeeded would the code issue COMMIT — the point at which all three changes become durable and simultaneously visible to every other session, exactly together, exactly once. There is no window, ever, where another session can see the order row but not yet the stock decrement, or vice versa; isolation and atomicity working together are precisely what rules that out. The practical discipline this teaches for application code: decide the transaction's boundary around the business unit of work — everything that must succeed or fail together — not around any single database call, and only call commit once every step inside that boundary has actually succeeded.
💻 Code example
-- Worked example: order placement with a mid-transaction failure, -- traced step by step. BEGIN; -- Step 1: insert the order. Succeeds -- but not yet durable, -- not yet visible outside this transaction. INSERT INTO orders (id, customer_id, product_id, quantity) VALUES (7001, 42, 500, 1); -- Step 2: decrement stock for the ordered product. UPDATE product_stock SET quantity = quantity - 1 WHERE product_id = 500; -- Suppose product_stock has: CHECK (quantity >= 0) -- and another order already consumed the last unit moments ago. -- This UPDATE violates that constraint: -- ERROR: new row for relation "product_stock" violates -- check constraint "stock_never_negative" -- The transaction is now in a failed state. The correct move -- is to roll back EVERYTHING since BEGIN -- including the -- order insert from step 1, which otherwise worked fine on -- its own: ROLLBACK; -- Verify: the order from step 1 does not exist. Atomicity -- held -- a half-finished order was never left behind. SELECT * FROM orders WHERE id = 7001; -- 0 rows -- The success path, for contrast -- every step must pass -- before COMMIT is issued: BEGIN; INSERT INTO orders (id, customer_id, product_id, quantity) VALUES (7002, 42, 501, 1); UPDATE product_stock SET quantity = quantity - 1 WHERE product_id = 501; INSERT INTO loyalty_ledger (customer_id, points) VALUES (42, 10); COMMIT; -- all three changes become durable and visible together
Story
A checkout flow runs five steps inside one transaction: insert the order, calculate loyalty points, apply a coupon, save the shipping address, and finalize the total. Step three — applying the coupon — turns out to be invalid; maybe it already expired, or it conflicts with another promotion already on the order. A full ROLLBACK at this point is technically correct but needlessly destructive: it would also discard the order insert and the loyalty-points calculation from steps one and two, which were completely fine, forcing the customer to start the entire checkout over from scratch for a problem that only ever existed in one isolated step.
SAVEPOINT solves exactly this. It lets you place a named marker at any point inside an already-open transaction, and later roll back only to that marker — undoing everything that happened after it, while leaving everything that happened before it fully intact and still part of the open transaction. Think of it as a checkpoint: you're not ending the transaction and you're not discarding all progress, you're rewinding to a specific point you chose in advance, then continuing forward from there with a different attempt.
The syntax is direct: SAVEPOINT some_name; creates the marker. ROLLBACK TO SAVEPOINT some_name; rewinds to it — every statement issued after that savepoint is undone, but the transaction itself stays open, and every statement issued before the savepoint remains exactly as it was, uncommitted but intact, still waiting for the eventual COMMIT or full ROLLBACK that closes the whole transaction. After rolling back to a savepoint, you're free to issue new statements going forward from that point — in the checkout example, applying a corrected discount instead of the invalid coupon — and those new statements become the path forward from the checkpoint.
One detail worth being precise about: a savepoint is not a substitute for a nested transaction, and standard SQL doesn't actually have true nested transactions in the way the word "nested" might suggest — issuing a second BEGIN inside an already-open transaction on most engines either raises a warning, is simply ignored, or (on some engines) is reinterpreted as an implicit savepoint, but it does not create an independent inner transaction that can commit separately while the outer one is still deciding its own fate. If you need something that behaves like "this inner unit of work can fail and be cleanly contained without aborting the outer transaction, but the outer transaction's own eventual commit or rollback still governs everything," a savepoint is precisely that construct, and it's also exactly what backs Spring's PROPAGATION_NESTED behavior under the hood — that's a Spring-specific mechanism covered in this course's transaction-propagation material, but the SQL-level primitive making it possible is the plain SAVEPOINT shown here.
Savepoints can also be stacked — you can place several named savepoints at different points through a long transaction, and roll back to any one of them, discarding everything after that specific point while keeping everything before it. This is genuinely useful in transactions with several independent, speculative steps where you want fine-grained control over exactly which part failed, rather than an all-or-nothing choice between "keep everything" and "discard everything." The one thing a savepoint can never do is survive past the transaction's own boundary — once the enclosing transaction commits or fully rolls back, every savepoint inside it is gone along with it; a savepoint is a bookmark inside one transaction's lifetime, not an independent unit of durability.
💻 Code example
-- SAVEPOINT: undo only the broken step, keep the rest. BEGIN; -- Step 1: insert the order -- this must survive no matter what -- happens to the coupon step below. INSERT INTO orders (id, customer_id, total) VALUES (501, 42, 2000); -- Checkpoint right before the risky step. SAVEPOINT before_coupon_applied; -- Step 2 (risky): apply a coupon that turns out to be invalid -- -- say, a 50% discount that violates a "max 20% discount" rule. UPDATE orders SET total = total * 0.5 WHERE id = 501; -- Problem detected. Roll back ONLY to the checkpoint -- the -- order insert from step 1 is untouched and still part of -- this open transaction: ROLLBACK TO SAVEPOINT before_coupon_applied; -- Continue forward from the checkpoint with a corrected step: UPDATE orders SET total = total * 0.9 WHERE id = 501; -- valid 10% discount -- Finalize the whole transaction, including step 1 (still intact) -- and the corrected version of step 2: COMMIT; -- Result: order 501 saved once, with the correct ₹1800 total -- -- the original insert was never discarded, only the bad discount -- attempt was.
Want a visual for this concept?
Generate a diagram tailored to “SQL Transaction Control — BEGIN, COMMIT, ROLLBACK & SAVEPOINT” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.
Sign in to generate a visual →