After ROLLBACK TO SAVEPOINT s1, which savepoints in the transaction are still usable?

## Savepoints nest like a stack Within one transaction, savepoints form a last-in-first-out structure. Each `SAVEPOINT` pushes a mark; `ROLLBACK TO SAVEPOINT` and `RELEASE SAVEPOINT` pop back to a named mark, discarding everything pushed above it. ```sql BEGIN; INSERT INTO t VALUES (1); SAVEPOINT s1; INSERT INTO t VALUES (2); SAVEPOINT s2; INSERT INTO t VALUES (3); ROLLBACK TO SAVEPOINT s1; -- rows 2 and 3 gone; s2 destroyed; s1 still live ROLLBACK TO SAVEPOINT s1; -- legal: s1 survived its own rollback COMMIT; -- only row 1 is committed ``` ## Why savepoints above the target die The reason is not a special rule, it is the ordinary meaning of the rewind. `SAVEPOINT s2` was executed *after* `s1` was established. A rollback to `s1` undoes everything the transaction did after that point, and establishing `s2` was part of that. There is nothing left for the name `s2` to refer to, so it is discarded. ## Why the target survives The standard specifies that a rollback to a savepoint leaves that savepoint established, and the major engines document the same: after `ROLLBACK TO SAVEPOINT s1`, `s1` is still available to roll back to again. This matters for the common retry shape — attempt a step, rewind, adjust, attempt again — where the same mark is used several times without being re-established between attempts. If you want the mark gone, say so explicitly with `RELEASE SAVEPOINT s1`. ## RELEASE cascades identically `RELEASE SAVEPOINT s1` destroys `s1` **and** every savepoint established after it — but it undoes no data. So the two statements differ in what they do to rows and agree in what they do to the marks above the target. A useful way to remember it: both pop the stack down to (and, for `RELEASE`, including) the named entry; only `ROLLBACK TO` also throws the work away. ## Naming a savepoint that no longer exists This is an error, not a no-op. The standard puts it in SQLSTATE class `3B` — savepoint exception, with invalid savepoint specification as the specific condition. Engines surface it as something like "no such savepoint". Treating it as an error is the right design: a code path that rolls back to a savepoint an earlier path already consumed is broken, and you want to hear about it rather than have the statement quietly do nothing while your error handling believes the rewind happened. ## Reusing a savepoint name This is the one corner where dialects genuinely diverge, and it is worth stating carefully rather than confidently. The SQL standard says establishing a savepoint with a name that already exists destroys the earlier savepoint of that name and creates a new one. Some engines instead keep the older savepoint hidden behind the newer one, so that releasing the newer makes the older reachable again. Because the observable behaviour differs, portable code should not reuse names within a transaction: give each mark a distinct name, or release the old one before re-establishing it. In generated code (a driver or framework emitting savepoints for nested logical units), distinct generated names are the norm for exactly this reason. ## How deep should the stack go? Nesting savepoints is legal to arbitrary depth, but a transaction carrying a deep stack of live marks is usually a sign that the unit of work is doing too much. Each live savepoint is state the engine has to track for the duration, and — more practically — a deep stack makes the code hard to reason about, because the effect of a rewind depends on which marks are still live at that moment. Releasing marks as soon as their step has succeeded keeps the stack shallow and the reasoning local. ## What a strong answer covers Name the stack model; state that the target survives and everything above it is destroyed; note that `RELEASE` cascades the same way; and mention that a missing savepoint name is an error rather than a silent skip. Adding the name-reuse caveat — "engines differ, so I use distinct names" — signals someone who has written this code rather than only read about it.