Savepoints and Nested Transactions
Savepoints mark intermediate positions inside a transaction that partial rollback can return to, allowing one failed step to be undone without aborting the whole transaction.
Definition
A savepoint is a named marker within a transaction established by SAVEPOINT and addressed by ROLLBACK TO SAVEPOINT. Work after the savepoint is undone; everything before it remains, and the transaction continues. Savepoints implement partial failure recovery inside one database transaction, distinct from autonomous transactions.
Context and scope
Used for batch processing where individual item failures should skip, multi-step business operations with per-step compensation, and test isolation wrappers that roll back setup.
Common misconceptions
- Savepoints create separate transactions — they are positions inside one transaction; commit semantics remain transaction-wide
- Rollback to a savepoint releases it — it stays valid and reusable until released or the transaction ends
- Savepoints undo side effects on other connections — they only affect work in the connection that created them
Anti-patterns
- Catching errors and rolling back to a savepoint while the failed statement already invalidated the transaction in strict modes
- Nesting thousands of savepoints without releasing old ones and bloating transaction state
- Using savepoints to simulate retries of a failed statement without re-establishing a valid transaction state first
Related concepts
Related examples
Authoritative source
What it is
A transaction is all-or-nothing at the top level. Savepoints carve interior checkpoints: a failed sub-step rewinds to the checkpoint while the surrounding work survives. This is how a loop importing ten thousand rows can skip one bad row without losing nine thousand nine hundred ninety-nine.
How it works
SAVEPOINT name establishes a marker; ROLLBACK TO SAVEPOINT name undoes work after it, keeping locks acquired before it. RELEASE SAVEPOINT frees the name. PostgreSQL aborts only the current statement on error, making catch-and-continue natural; Oracle and SQL Server offer similar mechanics with transaction-scoped caveats.
When to use it
Batch loaders with per-record error tolerance, composite operations where later steps may be skippable, and test frameworks wrapping each case in rollback. Combine with numbered savepoints per item and periodic release to bound state.
Pitfalls
Some drivers and modes abort the whole transaction on error, leaving nothing to roll back to — verify behavior per stack. Locks taken after the savepoint release only when the outer transaction ends, so rollback-to-savepoint can still hold locks longer than expected. Deep nesting without release degrades performance and memory.