2026-09-13 · 6 min read · Rahul Chitturi
- sql
- transactions
- debugging
A savepoint marks a position inside a transaction so selected work can be rolled back without necessarily discarding the entire transaction. That does not make every database error recoverable or release every resource acquired before the rollback.
Behavior differs across engines and drivers, especially after constraint errors, deadlocks, serialization failures, and implicit transaction boundaries.
Trace the exact transaction state
Record BEGIN, each savepoint name, statements, errors, rollback or release operations, and the final commit outcome. Include driver auto-commit settings and framework transaction wrappers.
- Use unique savepoint names in nested helpers.
- Do not assume RELEASE commits any data.
- Check whether one error aborts the whole transaction.
- Keep external side effects outside rollback assumptions.
Account for locks and deferred checks
Rolling back to a savepoint reverses database changes made after it according to engine semantics, but locks and sequence allocations may not behave like application memory. Deferred constraints can still fail at commit after every inner step appeared successful.
A deadlock victim or lost connection usually requires restarting the complete transaction from fresh reads rather than returning to a savepoint.
Build a controlled reproduction
Use the SQL formatter for statements, diff for expected row states, JSON formatter for sanitized transaction events, and timestamp converter for lock and error timing.
Test duplicate keys, foreign keys, deferred constraints, triggers, nested savepoints, deadlocks, timeouts, cancellation, connection loss, commit failure, retries, and each supported database engine.