skip to content

Does RELEASE SAVEPOINT commit the work done since that savepoint was set?

level: middleimportance: should knowfreq 34%

answer

  1. removes a marker, not data
  2. commits nothing, undoes nothing
  3. the transaction stays open either way
  4. also invalidates savepoints set after it
  5. only COMMIT makes anything permanent

basics

~10 s

No. RELEASE SAVEPOINT only discards the savepoint marker, together with any savepoints set after it. The statements executed since remain part of the still-open transaction and are still undone by a later ROLLBACK.

solid answer

~40 s

`RELEASE SAVEPOINT s1` destroys the mark named `s1` — nothing more. It commits nothing, undoes nothing, and does not end the transaction; the rows changed since `s1` stay exactly where they were, pending the transaction's eventual `COMMIT` or `ROLLBACK`. The only observable effect is that `s1` (and every savepoint established after it) can no longer be named: a later `ROLLBACK TO SAVEPOINT s1` raises an invalid-savepoint error. You release a savepoint when the step it guarded has succeeded and you no longer want the option of rewinding to it — typically inside a loop, so the transaction does not accumulate one live savepoint per iteration. The name is unfortunate: "release" means *release the marker*, not *release the data*.

code

sql · 5 lines
sql
BEGIN;
SAVEPOINT s1;
UPDATE accounts SET balance = 0 WHERE id = 7;
RELEASE SAVEPOINT s1;   -- drops the marker only
ROLLBACK;               -- account 7 is unchanged: nothing was ever committed

go deeper

for a junior

Recall that RELEASE SAVEPOINT is bookkeeping only: it drops the marker and leaves every row exactly as it was. If you remember nothing else, remember that only COMMIT makes changes permanent.

for a middle

Explain the three savepoint statements and which of them touches data. Be precise that releasing a savepoint also invalidates savepoints established after it, and that naming a gone savepoint is an error.

for a senior

Show the loop shape you actually write: savepoint before the risky step, release on success, rewind on failure — and be able to say why unreleased savepoints accumulating over a long batch is worth avoiding.

for a principal

Be ready to discuss how savepoint use interacts with your team's transaction-boundary conventions, and when the clearer design is a shorter transaction rather than a deep stack of rewind points.

## The three statements, and where RELEASE sits Savepoints are manipulated by exactly three statements inside an open transaction: - `SAVEPOINT s1` — establish a named mark. - `ROLLBACK TO SAVEPOINT s1` — undo everything done after the mark, keeping the transaction open. - `RELEASE SAVEPOINT s1` — forget the mark, keeping *everything* that was done. Only the middle one touches data. `RELEASE` is pure bookkeeping. ## What RELEASE actually does ```sql BEGIN; SAVEPOINT s1; UPDATE accounts SET balance = 0 WHERE id = 7; RELEASE SAVEPOINT s1; ROLLBACK; -- account 7 is unchanged: RELEASE committed nothing ``` After `RELEASE SAVEPOINT s1`, the `UPDATE` is still an uncommitted change belonging to the open transaction. The final `ROLLBACK` therefore discards it, exactly as it would have without the savepoint. Releasing a savepoint gives up the *ability to rewind* to that point; it does not give the work any new status. The symmetric statement is just as important: `RELEASE` also does not undo anything. Candidates sometimes guess that it is a tidier `ROLLBACK TO SAVEPOINT`. It is not — the two are opposites in effect on data (one discards a suffix of the work, the other discards nothing) and identical in effect on the marker's future usability in only one respect: after either, savepoints established after the named one are gone. ## Why the misconception is so common The verb "release" reads like "release the changes" — publish them, let them go through. In SQL it means the opposite of holding a resource: the engine was holding a rewind point for you, and you are telling it that it no longer needs to. The mental model that keeps this straight: a savepoint is a bookmark in a book you are still writing. `ROLLBACK TO` tears out the pages after the bookmark; `RELEASE` removes the bookmark and leaves the pages. Publishing the book is `COMMIT`. ## Why you would release at all Since releasing changes no data, why bother? 1. **Loops.** A batch that sets a savepoint per iteration and never releases accumulates one live savepoint per row. Releasing after a successful iteration keeps the count at one and keeps the engine's per-savepoint bookkeeping bounded. 2. **Name hygiene.** Once released, a name cannot be rolled back to by accident from a later code path — an error is far better than silently rewinding to a stale mark. 3. **Intent.** In hand-written scripts, `RELEASE` documents "this step is settled; no further rewind past here is intended." ## The cascade rule Releasing a savepoint also destroys every savepoint established after it. If the transaction set `s1` then `s2`, then `RELEASE SAVEPOINT s1` leaves neither name usable — `s2` was nested inside the scope `s1` opened. Naming a destroyed savepoint in a later `ROLLBACK TO SAVEPOINT` or `RELEASE SAVEPOINT` raises an error in the standard's savepoint-exception class (SQLSTATE class `3B`, invalid savepoint specification) rather than being silently ignored, which is what you want: a typo in a savepoint name is a bug, not a no-op. ## Portability `SAVEPOINT` and `ROLLBACK TO SAVEPOINT` are the widely implemented core; `RELEASE SAVEPOINT` is supported by several major engines but not universally, and some dialects express savepoints with entirely different keywords (T-SQL uses `SAVE TRANSACTION` and has no release statement — its savepoints simply cease to exist when the transaction ends). Engines also differ on what happens when you reuse a savepoint name: the SQL standard destroys the earlier savepoint of that name, while some engines keep it hidden behind the newer one. Do not rely on reuse — pick distinct names, or release before re-establishing. ## What a strong answer sounds like "`RELEASE SAVEPOINT` drops the marker and nothing else. The work since the savepoint is still uncommitted and still hangs on the transaction's final `COMMIT` or `ROLLBACK`. I release inside batch loops so savepoints do not pile up, and I know that releasing a savepoint also invalidates the ones set after it." That answer shows the candidate has actually written the statement rather than read the keyword and guessed.

  • If RELEASE SAVEPOINT changes no data, why use it inside a batch loop?
    So the transaction does not accumulate one live savepoint per iteration. Releasing after a successful step keeps the active savepoint count at one, bounds the engine's per-savepoint bookkeeping, and makes a later rewind to a stale mark impossible — the name simply no longer exists.
  • What happens if you RELEASE SAVEPOINT s2 after already rolling back to an earlier savepoint s1?
    It fails. Rolling back to s1 destroyed every savepoint established after it, s2 included, so naming s2 raises an invalid-savepoint-specification error (SQLSTATE class 3B) rather than being ignored. The same applies to ROLLBACK TO SAVEPOINT s2.
  • Does releasing a savepoint make the work since that savepoint visible to other sessions?
    No. Visibility is decided by COMMIT, not by savepoint bookkeeping. Until the transaction commits, every change it has made — before or after any savepoint, released or not — remains invisible to sessions that do not read uncommitted data.

A savepoint is a bookmark in a manuscript you are still writing. ROLLBACK TO tears out the pages after the bookmark; RELEASE just removes the bookmark. Publishing the manuscript is COMMIT.

saying these in an interview costs you the question

  • Says RELEASE SAVEPOINT commits that part of the transaction
  • Thinks RELEASE undoes the statements since the savepoint
  • Believes a released savepoint can still be rolled back to
  • Assumes releasing s1 leaves a later s2 still usable
  • Uses RELEASE as a way to partially commit a long batch

context

resolving links…