2026-09-13 · 8 min read
- sql
- transactions
- reliability
A savepoint creates a named rollback position inside an open transaction. Rolling back to it can undo later database changes while keeping earlier work available for a later commit.
Savepoints are useful for optional batches and framework-level nested transactions, but they do not provide independent commits. The outer transaction still owns visibility, locks, failure recovery, and the final outcome.
Map the engine and driver contract
Record the database engine and version, isolation level, auto-commit setting, driver behavior, ORM nesting model, implicit DDL commits, and which errors place the transaction in an aborted state.
- Use generated names when helpers can nest.
- Release savepoints according to engine requirements.
- Do not assume a released savepoint commits its work.
- Test the exact errors the application intends to recover from.
Separate recoverable statement work
Place a savepoint immediately before a truly optional database unit. On a classified recoverable failure, roll back to that point, restore application bookkeeping, and continue only when transaction state remains valid.
Deadlocks, serialization failures, connection loss, administrator termination, and uncertain commit results generally require outer recovery. Restart the complete transaction from fresh reads when the engine invalidates its snapshot or ownership.
Account for deferred behavior and side effects
A rollback may not reclaim sequence values, erase notifications already delivered outside the database, or release all locks in the way the application expects. Deferred constraints and triggers can surface failures only at statement end or outer commit.
- Keep remote API calls out of rollback-only assumptions.
- Use an outbox or idempotency key for external effects.
- Treat commit failure as a distinct outcome.
- Bound retries and classify only documented transient failures.
Prove state across nested failures
Use Flashman's SQL formatter for statements, diff for expected row transitions, JSON formatter for sanitized transaction events, timestamp converter for contention timelines, and UUID generator for disposable operation IDs.
Test unique and foreign-key errors, deferred constraints, triggers, nested savepoints, name reuse, lock waits, deadlocks, serialization failures, timeouts, cancellation, connection loss, commit failure, retry, pooled connections, and every supported engine.