Savepoints Under Failure: MySQL SAVEPOINT vs PostgreSQL Subtransactions in Batch Jobs
A ported importer hit one duplicate key at row 312 of a 2-million-row batch and failed the entire run — because code that coasts on MySQL's statement-level rollback meets PostgreSQL's aborted-transaction state and dies. Savepoints look identical in the manual; the error semantics around them are not.
The importer had run on MySQL 5.7 and then 8.0 for four years: read a feed file, insert 2 million usage rows in one transaction, catch the occasional duplicate-key error, log the bad row, keep going. It survived a MySQL upgrade without a scratch. The PostgreSQL 15 port lasted until row 312. That row violated a unique constraint, the code caught the exception exactly as it always had, logged it, issued the next INSERT — and got back ERROR: current transaction is aborted, commands ignored until end of transaction block, SQLSTATE 25P02. Every one of the remaining 1,999,688 inserts failed the same way. The retry loop made it worse: three more full passes, three more failures, and by 04:00 the feed backlog had its own backlog.
The root cause is the single most-ported-and-broken assumption between these engines: on MySQL, a failed statement inside a transaction rolls back only itself and the transaction stays usable; on PostgreSQL, any error poisons the whole transaction until you roll back to a savepoint, and without a savepoint your only exit is aborting everything. SAVEPOINT exists on both engines with nearly identical syntax, which is exactly why the semantic difference ambushes people. This article is the field guide to that difference: the error model, the syntax gotchas, the performance cost, and the batch pattern that survives both.
Why did the same try/catch work on MySQL and fail on PostgreSQL?
Because MySQL implements statement-level atomicity while PostgreSQL implements transaction-level error state, and identical-looking application code relies on the difference. On MySQL with InnoDB, when a statement fails with a non-fatal error — duplicate key, data truncation under STRICT mode, a check-constraint violation — the engine rolls back that statement’s changes and leaves the transaction open and healthy. Code that catches the exception and continues is relying on documented behavior, and it works. On PostgreSQL the same failed statement puts the transaction into aborted state: every subsequent command returns 25P02 until the transaction ends or rolls back to a savepoint taken before the failure. The driver does not rescue you, the ORM does not rescue you, and the error message, while accurate, tells you nothing about which statement caused the poisoning.
The correct PostgreSQL shape wraps each recoverable unit in a savepoint and rolls back to it on failure:
-- PostgreSQL: the only way to survive a per-row failure mid-transaction
BEGIN;
SAVEPOINT before_row;
INSERT INTO usage_rows (account_id, day, units) VALUES (1042, '2025-11-03', 87);
-- on error: the transaction is now aborted; this is the ONLY way back:
ROLLBACK TO SAVEPOINT before_row;
-- transaction is healthy again; continue with the next row
COMMIT;The ported importer had the savepoints in the code but the rollback call lived in a branch the MySQL error path never exercised — on MySQL, ROLLBACK TO SAVEPOINT after a statement error is usually redundant, so a refactor had “simplified” it away years earlier. Dead code on one engine was load-bearing on the other.
How do savepoint semantics actually differ between the engines?
The syntax is nearly identical — SAVEPOINT name, ROLLBACK TO SAVEPOINT name, RELEASE SAVEPOINT name — and three behavioral differences live underneath it. First, release destroys nested savepoints on both engines, but the failure surface differs: rolling back to an already-destroyed savepoint on MySQL raises ERROR 1305 (42000): SAVEPOINT name does not exist, while PostgreSQL raises “savepoint does not exist” similarly — the trap is that on PostgreSQL, ROLLBACK TO SAVEPOINT restores the transaction to health but does not make destroyed savepoints valid again, so retry loops that re-use an outer savepoint name after an inner RELEASE fail on the second iteration. Second, MySQL’s ROLLBACK TO SAVEPOINT keeps the named savepoint alive for repeated rollbacks, and so does PostgreSQL — but MySQL also permits rolling back to savepoints created later in the stack in some legacy paths, which silently flattens nesting assumptions during ports.
Third, and the one that has burned me directly: DDL. On MySQL, DDL causes an implicit commit, which ends the transaction and destroys the entire savepoint stack without warning — a migration helper that ran ALTER TABLE mid-batch left every subsequent ROLLBACK TO SAVEPOINT raising 1305. On PostgreSQL, DDL is transactional, the savepoints survive, and you can even roll back the DDL itself. The DDL transactions comparison covers that whole family of divergence. Isolation behavior interacts too: what a concurrent transaction can see of your batch mid-flight differs per engine, per the REPEATABLE READ vs READ COMMITTED notes.
What does a savepoint-heavy loop actually cost?
On PostgreSQL a savepoint is a subtransaction, and subtransactions are not free: each one that touches data gets its own transaction ID, and the bookkeeping lives in the pg_subtrans SLRU cache, which exists to answer “which top-level transaction owns this row’s XID” for visibility checks. At low savepoint rates you never notice. At a savepoint per row across a 2-million-row transaction, you are generating subtransaction IDs at feed speed, and the known pathological case — heavy subtransaction use combined with snapshots held open by other sessions — turns pg_subtrans into a contention point that can stall unrelated queries. The deep dive is in the PostgreSQL subtransactions performance notes; the short version is that a savepoint per row is a pattern PostgreSQL tolerates but does not love.
On MySQL the per-savepoint overhead is lower — no subtransaction IDs, no equivalent SLRU — and the practical cost is undo-log growth from the long transaction itself rather than the savepoints. The design conclusion is the same on both engines regardless: chunk the batch. Committing every 10,000 rows with a savepoint per chunk bounds the blast radius of any failure to the chunk, bounds lock and undo/WAL pressure, and turns a four-hour rerun into a two-minute one. The per-row savepoint pattern exists to keep one bad row from killing a batch; chunking solves the same problem with one percent of the savepoint count.
What is the batch pattern that survives both engines?
The pattern that ports cleanly is: chunk, savepoint per chunk, ROLLBACK TO on failure, then fall into a per-row retry only inside the failed chunk. That gives you MySQL’s forgiving error handling by construction rather than by assumption, and it gives PostgreSQL the explicit rollback path it demands. Two supporting rules from the incident report. First, test the error path on both engines with real errors — a duplicate key and a constraint violation, not just the happy path — because the difference between statement rollback and transaction poisoning only appears when something fails, which is precisely the code path nobody tests. Second, prefer idempotency over error handling where you can: an upsert that treats a duplicate as an update turns the whole savepoint conversation into a non-issue for most feed importers, and the dialect differences there are their own subject, covered in the ON DUPLICATE KEY vs ON CONFLICT comparison. Error handling that never fires is the only error handling with a perfect record.
Where MonPG fits when a transaction poisons
A poisoned transaction has a distinctive signature: one statement error followed by thousands of 25P02 rejections, an application that looks up but produces nothing, and a database that shows a long-open transaction doing no useful work. MonPG’s PostgreSQL monitoring surfaces exactly that shape — long-lived transactions, idle and active session states with the offending query attached, and statement-level error history — so the four-hour importer spiral becomes a ten-minute diagnosis instead of a morning of grep.
MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL side of this article is what it will watch: long transactions growing undo, and the statement errors that MySQL forgives but that still mark data quality problems upstream. Until then, the MySQL monitoring page tracks that work. And the portable lesson costs nothing: on PostgreSQL, a savepoint you roll back to is error handling; on MySQL it is a formality; and code ported between the two will tell you which assumption it was written under the first time a row fails at 02:00.