Inside a long transaction you want to undo the last few statements without losing everything done so far. Explain what a savepoint gives you, what it does not undo, and how it interacts with locks the transaction already holds.

## What a savepoint is A transaction is normally all-or-nothing. A savepoint adds an intermediate marker: a named position inside the open transaction that you can later rewind to. Rolling back to a savepoint discards every data modification made after it, keeps everything before it, and — crucially — leaves the transaction open and usable. The transaction as a whole still commits or aborts atomically at the end. Mentally, the transaction becomes a stack of markers. Setting a savepoint with a name that already exists pushes a new one and hides the old; rolling back to a name rewinds to the most recent one with that name and keeps it available for reuse. Releasing a savepoint discards the marker (and any nested inside it) without undoing the work — you simply lose the ability to rewind that far. Committing releases all of them implicitly. ## What it does not do **It does not commit.** This is the most common misunderstanding. Nothing before the savepoint becomes durable or visible to other sessions. If the transaction later rolls back entirely, or the process dies, all of it disappears. A savepoint is a *checkpoint of intent* inside your own transaction, not a partial commit. **It does not release locks.** Every row lock the transaction has taken is held until the transaction ends, and rolling back to a savepoint does not hand them back in the general case. So a savepoint gives you no relief from blocking, and it does not reduce the pressure a long transaction puts on the system. Similarly, the transaction keeps its snapshot, its identifier, and its position in whatever the engine tracks as the oldest running transaction. **It does not undo the outside world.** Anything that happened in application memory, on disk, or over the network during the rolled-back section is still done. After a partial rollback, in-memory entities may hold values that no longer exist in the database — an ORM in particular will happily flush that stale state later unless you clear or reload it. Treat "what does my application think is true now?" as an explicit part of the recovery. **It is not free.** Each savepoint creates bookkeeping the engine must track, and the rows written and then rolled back still consumed space and log volume; they become garbage for the cleanup machinery. Taking a savepoint per row across a very large batch has a measurable cost, which is why per-chunk savepoints usually beat per-row ones. ## Why it exists — the two real uses **Batch error recovery.** You are processing many items in one transaction and want a single bad item to be skipped rather than destroying the whole batch. Take a savepoint before each item (or each chunk); on failure, roll back to it, record the item as rejected, and continue with the next. Without this, the failure may leave the transaction in a state where the engine refuses further work until it is unwound, so the entire batch is lost. **Nested-transaction emulation.** SQL transactions do not truly nest. When an application framework offers "a new transaction inside an existing one", it is almost always implemented as a savepoint: entering the inner scope sets one, the inner scope failing rolls back to it, and the inner scope succeeding merely releases it. The semantics follow from the mechanism and are worth stating precisely — an inner failure does not abort the outer transaction, but an inner *success* is not durable either; it can still be thrown away when the outer transaction rolls back. Anyone expecting "the inner part committed regardless" needs a genuinely separate transaction on a separate connection instead. ## Practical guidance - Use savepoints for *expected*, recoverable per-item failures, not as a substitute for validating input before the transaction starts. Validating up front is cheaper than writing and rewinding. - Prefer per-chunk savepoints over per-row for large volumes, and re-run only the failed chunk item-by-item to isolate offenders. - Remember that a savepoint does not shorten the transaction. If the batch is long, that is a lock-hold and cleanup problem that only committing chunks solves. - After rolling back to a savepoint, reconcile application state deliberately: clear cached entities, and do not assume identifiers or generated values from the rolled-back section survived.