You are inserting a batch of records inside one transaction. One violates a constraint. In several databases, that error puts the entire transaction into a failed state — every subsequent statement errors, and your only option is to roll back everything.
That is correct behaviour and unhelpful when the failure was expected and you wanted to skip that record and carry on.
What changed in 2026
- ORM savepoint usage became more visible. Query logs showing savepoints developers did not write prompted better understanding of nested transaction handling.
- The overhead got documented. Performance problems from savepoints created per iteration in loops became a recognised pattern.
- Bulk load patterns matured. Batch-level rather than row-level savepoints became standard for large imports.
- Behaviour differences stayed engine-specific. Whether an error aborts the whole transaction varies, which affects how necessary savepoints are.
What a savepoint does
It marks a point within a transaction that you can return to. Roll back to a savepoint and everything after it is undone; everything before it remains, and the transaction is still open and usable.
The pattern for handling an expected failure:
Set a savepoint. Attempt the risky operation. If it succeeds, release the savepoint and continue. If it fails, roll back to the savepoint — which clears the error state — and continue with the next operation.
Without that, the first failure ends the transaction and you lose all the work that preceded it.
|
Plain transaction |
With savepoints |
| Error effect |
Whole transaction unusable |
Roll back to the savepoint |
| Partial progress kept |
No |
Yes |
| Complexity |
Low |
Moderate |
| Overhead |
None |
Per savepoint |
Why ORMs create them
If you have seen savepoints in query logs you did not write, this is why. Most databases do not support genuinely nested transactions — you cannot begin a transaction inside a transaction and commit the inner one independently.
ORMs simulate them with savepoints. An inner transaction block becomes a savepoint; committing it releases the savepoint; rolling it back rolls back to the savepoint. From the application's perspective it looks like nesting, and underneath it is one transaction with markers.
That is a reasonable abstraction with one important consequence: the inner block is not durably committed. If the outer transaction rolls back, the inner work is gone regardless of the inner commit. Code assuming an inner commit persisted the data is wrong, and the failure appears only when the outer transaction fails.
The overhead
Savepoints are not free. Each one is tracked by the transaction, and creating a large number within one transaction consumes resources and can degrade performance meaningfully.
The failure mode is a loop that creates a savepoint per iteration — importing a million rows with a savepoint each. That is a million savepoints in one transaction, and it can slow the database in ways that look unrelated to the import.
The fix is granularity. Savepoint per batch rather than per row: process a thousand rows, and on failure roll back that batch and either retry it row by row or skip it. You lose the ability to skip exactly one bad row without extra work, and you avoid a pathological savepoint count.
For bulk loading, better still is to avoid savepoints entirely by validating before insert or loading into a staging table and moving valid rows across — see COPY bulk loading.
Common mistakes
- A savepoint per row in a large loop. Serious overhead.
- Assuming an inner ORM transaction is durably committed. It is not until the outer one commits.
- Using savepoints instead of validating first. Cheaper to check than to fail and recover.
- Not releasing savepoints. They accumulate for the transaction's life.
- Long transactions with many savepoints. Compounds with the usual long-transaction problems, per MVCC explained.
- Assuming uniform engine behaviour. Whether an error aborts everything differs.
FAQ
Do all databases abort the transaction on error?
No — behaviour varies. Some allow you to continue after a statement error without a savepoint. Where the abort behaviour exists, savepoints are the only recovery mechanism.
How many savepoints are too many?
Engine-dependent, and the practical answer is that per-row savepoints in a large loop is too many. Batch granularity avoids the question.
Are nested transactions real anywhere?
Genuine independently-committable nested transactions are rare. What frameworks provide is savepoint emulation, with the durability caveat above.
Should I release savepoints I no longer need?
Yes — it frees the associated resources. Frameworks generally handle this; hand-written savepoint code frequently does not.
Where to go next
For the transaction semantics underneath, read ACID transactions explained and database isolation levels. For bulk loading without savepoint overhead, COPY bulk loading.