Does ROLLBACK TO SAVEPOINT end the transaction, and what happens to earlier work?
answer
- undoes a suffix, not everything
- the transaction is still open afterwards
- nothing is durable until COMMIT
- plain ROLLBACK is the one that ends it
basics
~10 sROLLBACK TO SAVEPOINT undoes only the statements executed after that savepoint. The transaction stays open, everything done before the savepoint is still pending, and you must still issue COMMIT or ROLLBACK to finish.
solid answer
~40 s`SAVEPOINT s1` marks a point inside an already-open transaction. `ROLLBACK TO SAVEPOINT s1` rewinds the transaction to that mark: changes made after it disappear, changes made before it remain — still uncommitted, still invisible to other sessions. Crucially it is *not* a transaction-ending statement: the transaction continues, you can run more statements, and nothing becomes permanent until a later `COMMIT`. A plain `ROLLBACK` with no savepoint name is the opposite — it discards the whole transaction and ends it. So the usual shape is `BEGIN … SAVEPOINT s1 … ROLLBACK TO SAVEPOINT s1 … COMMIT`, where the final `COMMIT` still decides the fate of everything that survived the partial rollback.
code
sql · 9 linesBEGIN;
INSERT INTO orders (id, customer_id) VALUES (1001, 42);
SAVEPOINT before_items;
INSERT INTO order_items (order_id, sku, qty) VALUES (1001, 'BAD-SKU', 1);
ROLLBACK TO SAVEPOINT before_items; -- only this INSERT is undone
INSERT INTO order_items (order_id, sku, qty) VALUES (1001, 'GOOD-SKU', 1);
COMMIT; -- orders row + GOOD-SKU row become permanentgo deeper
Recall the three statements and the one-line meaning: ROLLBACK TO SAVEPOINT undoes only what came after the mark and leaves the transaction open. Be able to write BEGIN, SAVEPOINT, ROLLBACK TO SAVEPOINT, COMMIT in order.
Explain that a partial rollback is not a partial commit: pre-savepoint work is still pending and still lost by a later full ROLLBACK. Contrast ROLLBACK TO SAVEPOINT with plain ROLLBACK precisely.
Show where you scope savepoints in real write paths so a failing step does not discard a whole unit of work, and be clear about what still hangs on the final COMMIT.
Be ready to argue when partial rollback is the right tool versus splitting the work into separate transactions, and what each choice implies for retry semantics and for how long a transaction stays open.
## What a savepoint is A savepoint is a named mark placed inside a transaction that is already open. It creates no data and changes no rows; it simply records "this is where the transaction stood at this instant" so that the transaction can later be rewound to that point instead of being thrown away entirely. The three statements that make up the feature are `SAVEPOINT <name>` (establish the mark), `ROLLBACK TO SAVEPOINT <name>` (rewind to it), and `RELEASE SAVEPOINT <name>` (discard the mark without rewinding). ## What ROLLBACK TO SAVEPOINT undoes ```sql BEGIN; INSERT INTO orders (id, customer_id) VALUES (1001, 42); SAVEPOINT before_items; INSERT INTO order_items (order_id, sku, qty) VALUES (1001, 'BAD-SKU', 1); ROLLBACK TO SAVEPOINT before_items; INSERT INTO order_items (order_id, sku, qty) VALUES (1001, 'GOOD-SKU', 1); COMMIT; ``` After the `COMMIT`, two rows exist: the `orders` row inserted before the savepoint, and the second `order_items` row inserted after the partial rollback. The `BAD-SKU` row is gone — no other session ever saw it, because it was never committed in the first place. The rule is positional, not statement-counting: everything the transaction changed *after* the savepoint was established is undone, however many statements that was. ## The transaction does not end This is the single point interviewers are checking. `ROLLBACK TO SAVEPOINT` is not a transaction-terminating statement. After it runs: - the transaction is still open and still holds its identity; - the work done before the savepoint is still pending, not committed; - you may run further statements, set further savepoints, and roll back to the same savepoint again; - nothing is durable until `COMMIT`, and a later plain `ROLLBACK` still throws away *everything*, including the pre-savepoint work. So a partial rollback is not a partial commit. Candidates who say "the earlier rows are saved" are wrong in a way that matters: if the connection drops or the transaction later rolls back, those earlier rows vanish too. ## Contrast with plain ROLLBACK `ROLLBACK` (no savepoint name) discards the entire transaction and ends it. `ROLLBACK TO SAVEPOINT s1` discards a suffix of the transaction and continues it. The two share a keyword and nothing else; conflating them is the classic error. ## After the rewind, the savepoint is still there The standard, and the major engines that implement it, leave the named savepoint established after you roll back to it — you can roll back to `s1` repeatedly. Any savepoints established *after* `s1`, by contrast, are destroyed by the rewind, because the statements that created them have been undone. ## Syntax and spelling The standard form is `ROLLBACK [WORK] TO SAVEPOINT <name>`; several dialects let you drop `WORK`, `SAVEPOINT`, or both, so `ROLLBACK TO s1` is common in practice. Savepoint names are ordinary identifiers, not string literals — `SAVEPOINT 's1'` is not valid. Portability caveats worth knowing: T-SQL spells the feature `SAVE TRANSACTION <name>` and rewinds with `ROLLBACK TRANSACTION <name>` rather than the `SAVEPOINT` keywords, and not every engine offers `RELEASE SAVEPOINT`. SQLite's `SAVEPOINT` will open a transaction if none is active; elsewhere you are expected to be inside an explicit transaction already, and issuing `SAVEPOINT` in autocommit mode is an error or a no-op depending on the engine. Write the `BEGIN`/`START TRANSACTION` explicitly and the question does not arise. ## Where it is actually used The pattern appears wherever one step of a multi-step unit of work is allowed to fail without discarding the rest: a batch loop that must skip a bad row, an optional enrichment step, a "try the fast path, fall back to the slow path" sequence. The savepoint scopes the blast radius of the failure to the statements between the mark and the rewind. ## What interviewers listen for A good answer names the three statements, states plainly that the transaction remains open, and stresses that nothing is committed by a partial rollback. A weak answer describes it as "saving" work — the word *savepoint* invites exactly that misreading, and it is the misreading the question is designed to surface.
- After ROLLBACK TO SAVEPOINT s1, is the pre-savepoint work visible to another session?No. The transaction is still open, so all of its changes — before and after the savepoint — remain uncommitted and invisible to other sessions under any isolation level that hides uncommitted data. A partial rollback publishes nothing; only COMMIT does.
- Can you roll back to the same savepoint more than once in one transaction?Yes. Rolling back to a savepoint leaves that savepoint established, so you can run more statements and rewind to the same mark again. What the rewind destroys is any savepoint created after it, not the target savepoint itself.
- What does a plain ROLLBACK do after you have set several savepoints?It discards the whole transaction and ends it, savepoints included. Work done before the first savepoint is undone along with everything else — savepoints never protect earlier statements from a full rollback.
It is a checkpoint in a video-game level: dying sends you back to the checkpoint rather than the start screen — but reaching a checkpoint has not finished the level, and quitting still loses the run.
saying these in an interview costs you the question
- Says ROLLBACK TO SAVEPOINT commits the earlier statements
- Thinks it ends the transaction like a plain ROLLBACK
- Claims other sessions can now see the pre-savepoint rows
- Treats SAVEPOINT as saving or flushing data to disk
- Uses SAVEPOINT without opening an explicit transaction first