Partial Rollback
A normal rollback throws away an entire transaction. A savepoint is a named marker inside a transaction that you can roll back to, undoing only the work done after it while keeping everything before it.
How They Work
- SAVEPOINT name marks a point in the open transaction.
- ROLLBACK TO SAVEPOINT name undoes work after the marker but leaves the transaction open and earlier work intact.
- RELEASE SAVEPOINT name discards the marker once you no longer need it.
This gives a form of nested transaction behavior: an inner block can fail and be retried or skipped without aborting the outer transaction.
A Common Use
A batch loop inserts many records inside one transaction. Before each record it sets a savepoint. If one insert violates a constraint, it rolls back to that savepoint, logs the bad record, and continues, so one bad row does not discard the whole batch.
Caveats
- Savepoints still live inside one real transaction, so all the data stays locked until the final commit.
- Rolling back to a savepoint releases locks acquired after it in most engines, but the outer transaction continues.
Key idea
A savepoint marks a point inside a transaction so you can roll back partial work and continue, giving nested transaction behavior within one real transaction.