After a statement fails mid-transaction, does COMMIT still persist the earlier statements?
answer
- the error may not end the transaction
- engines disagree about the aftermath
- one engine refuses every later statement
- a blind COMMIT can persist half the work
basics
~20 sIt depends on the engine. PostgreSQL puts the transaction in an aborted state where a later COMMIT behaves as ROLLBACK. MySQL and Oracle roll back only the failing statement and leave the transaction open, so COMMIT persists the earlier work.
solid answer
~50 sA statement error does not automatically end the transaction, and engines disagree about what it leaves behind. **PostgreSQL** poisons the whole transaction: after the error every further statement fails with *current transaction is aborted, commands ignored until end of transaction block*, and a `COMMIT` you send anyway is executed as a `ROLLBACK`. **MySQL/InnoDB and Oracle** do statement-level rollback: only the failing statement's effects are undone, the transaction stays open and usable, and a subsequent `COMMIT` makes everything before the error permanent. So the same client code — "send statements, ignore errors, commit at the end" — silently persists half a unit of work on one engine and nothing at all on the other. The portable rule is that the application must decide: check every statement's result, and on error issue an explicit `ROLLBACK` rather than letting control flow reach `COMMIT`.
code
sql · 6 lines-- PostgreSQL: the error poisons the whole transaction
BEGIN;
INSERT INTO orders (id, customer_id) VALUES (1, 42);
INSERT INTO orders (id, customer_id) VALUES (1, 43); -- ERROR: duplicate key
SELECT 1; -- ERROR: current transaction is aborted, commands ignored
COMMIT; -- behaves as ROLLBACK: neither row existsgo deeper
Know that a failed statement does not necessarily end the transaction, so after an error you still have to decide between COMMIT and ROLLBACK yourself.
Explain both behaviours precisely — PostgreSQL's aborted transaction where COMMIT acts as ROLLBACK, and statement-level rollback on MySQL and Oracle — and show the script whose outcome differs.
Show how you make the divergence irrelevant in production: results checked per statement, an explicit rollback on error, the boundary owned by one helper, and the failure path covered by a test.
Own the error contract across services so no team's batch job can commit half a unit of work, including how partial failure is surfaced, retried, and reconciled rather than discovered later in the data.
## The question behind the question Interviewers ask this because it separates people who have written a transaction from people who have debugged one. The naive model is "an error rolls back the transaction". The real model is "an error rolls back *something*, and which something depends on the engine". ## PostgreSQL: the transaction is poisoned ```sql BEGIN; INSERT INTO orders (id, customer_id) VALUES (1, 42); INSERT INTO orders (id, customer_id) VALUES (1, 43); -- ERROR: duplicate key SELECT 1; -- ERROR: current transaction is aborted, commands ignored -- until end of transaction block COMMIT; -- behaves as ROLLBACK: neither row exists ``` After any statement error, PostgreSQL marks the transaction as failed. Every subsequent statement — even a trivial `SELECT 1` — is refused with the same message until you end the block. When you do, `COMMIT` is treated as `ROLLBACK`. This is safe by construction: you cannot accidentally commit a transaction that has already gone wrong. It is also why a naive batch loop against PostgreSQL that keeps going after an error appears to "lose" everything — the engine is doing exactly what it promised. ## MySQL and Oracle: statement-level rollback ```sql START TRANSACTION; INSERT INTO orders (id, customer_id) VALUES (1, 42); INSERT INTO orders (id, customer_id) VALUES (1, 43); -- ERROR 1062 duplicate entry COMMIT; -- the first row IS committed ``` Here the failing statement is undone and nothing else is. The transaction remains open and perfectly usable; you may issue more statements and commit. Sending `COMMIT` after the error persists the first insert. The same script, the same errors, and the opposite database state at the end. ## Why this is a real production bug, not trivia The dangerous shape is code that treats errors as advisory: a loop that catches an exception per row, logs it, continues, and commits at the end. On MySQL that commits a partial batch that looks complete to everything downstream. On PostgreSQL it commits nothing but also processes nothing after the first bad row, while the logs happily list thousands of "processed" rows. Neither outcome matches what the author intended, and neither is the engine's fault. Other shapes worth knowing: - **Autocommit changes the question entirely.** If the driver has autocommit on, each statement already committed as it succeeded; there is no transaction left to roll back, and the errors you catch are simply the statements that did not happen. - **The connection dying mid-transaction is the easy case.** With no `COMMIT` received, the engine rolls the transaction back. Losing the connection is safer than surviving with an ignored error. - **A failed `COMMIT` is not a failed statement.** Deferred constraint checks and serialization failures surface at commit time, and when they do the transaction is over and rolled back. Handle the error from `COMMIT` itself. ## The portable discipline Write code that is correct on the strictest reading and does not depend on which engine you happen to be on: 1. **Check the result of every statement.** Never let an ignored error flow past. 2. **Decide explicitly on error.** Either issue `ROLLBACK` immediately, or deliberately scope a partial recovery with a savepoint. What must never happen is that control flow reaches `COMMIT` because nobody looked at the error. 3. **Make the error path structural.** In application code, that is a framework or helper that owns `begin/commit/rollback` so no handwritten path can forget the rollback; in scripts, it is stopping on first error rather than continuing. 4. **Test the failure path.** Force a constraint violation mid-transaction in a test and assert on the final database state. This is the only way you find out what your specific engine and driver combination really does. ## What a strong answer sounds like "An error rolls back the statement; whether it rolls back the transaction is engine-specific. PostgreSQL aborts the whole block and turns a later `COMMIT` into a `ROLLBACK`; MySQL and Oracle undo just the statement and let you commit the rest. I do not rely on either — the code checks each statement and rolls back explicitly, so the outcome is the same everywhere." That names the divergence, names both behaviours correctly, and states the habit that makes the divergence stop mattering.
- How does the driver's autocommit setting change this picture?It removes the question. With autocommit on, each statement committed as it succeeded, so after an error there is no open transaction and nothing to roll back — the failed statement simply did not happen and everything before it is already permanent. That is precisely why multi-statement work must turn autocommit off or open an explicit transaction.
- How would you structure a bulk-load loop so an error cannot commit partial work?Check every statement's result and treat any unexpected error as fatal for the batch: issue an explicit `ROLLBACK` and stop, rather than continuing and reaching `COMMIT`. If some rows are legitimately expected to fail, that is a deliberate design with a scoped recovery point, not a default — and it should be tested by forcing a violation and asserting the final state.
- Is an error raised by COMMIT itself the same situation?No. Deferred constraint checks and serialization failures surface at commit time, and when `COMMIT` fails the transaction has already ended and been rolled back — there is nothing left to retry in place. The correct response is to start a new transaction and redo the work, which is why retry logic has to wrap the whole unit rather than the single statement.
One engine treats a mistake mid-form as voiding the whole form; another lets you cross out the bad line and hand the rest in. If you never look at whether a line failed, you cannot know which you are holding.
saying these in an interview costs you the question
- Assumes any statement error automatically rolls the transaction back
- Believes COMMIT always persists whatever succeeded before the error
- Lets a loop catch errors, continue, and commit at the end
- Thinks PostgreSQL's aborted-transaction message means the connection broke
- Treats COMMIT as unable to fail once statements succeeded