A single UPDATE statement modifies 1,000 rows and hits a constraint violation on row 700. What happens to the 699 rows it already changed, and does the answer depend on whether an explicit transaction is open?
answer
- statement = its own atomic unit
- implicit savepoint before each statement
- autocommit: statement is the transaction
- some engines poison the whole transaction on error
- explicit savepoint = portable partial continuation
basics
~20 sThe statement is atomic too: all 699 changes are reversed, so the statement leaves no partial effect. Whether the surrounding transaction survives differs by engine — some abort the whole transaction, others undo only the failed statement and let you continue.
solid answer
~60 sA statement is itself an atomic unit, so a failure at row 700 rolls back the 699 rows it had already modified. You never see a partially applied statement. Engines implement this with an internal savepoint taken before the statement: on error, they roll back to that mark rather than reversing the whole transaction. What differs is the fate of the **surrounding transaction**: - With no explicit transaction (autocommit), each statement is its own transaction, so the failure simply means nothing happened. - Inside an explicit transaction, some engines abort the entire transaction and refuse further work until it is ended — the failed statement poisons it. Others undo only that statement and leave the transaction usable, so the application may retry or take a different path and still commit the earlier statements. Because this varies, portable application code should not rely on continuing after an error: treat any statement error as a signal to roll back the transaction, or use an explicit savepoint if partial continuation is genuinely required.
code
sql · 10 linesBEGIN;
INSERT INTO orders (id, customer_id) VALUES (1, 10);
SAVEPOINT try_optional;
INSERT INTO order_notes (order_id, note) VALUES (1, NULL); -- may violate NOT NULL
-- on error:
ROLLBACK TO SAVEPOINT try_optional;
INSERT INTO order_lines (order_id, sku, qty) VALUES (1, 'X', 2);
COMMIT;go deeper
Know that a failed statement leaves no partial changes behind, and that without an explicit transaction each statement stands alone.
Explain the implicit savepoint mechanism and that whether the surrounding transaction remains usable is engine-dependent.
Add the operational angle: locks held to transaction end, wasted work from partial application plus reversal, deferred constraints failing at commit, and when explicit savepoints are the right tool.
Set the convention for the codebase — errors abort the unit of work unless a savepoint makes continuation explicit — and prefer designs that avoid raising errors in bulk paths altogether.
## Two nested units of atomicity Atomicity is usually taught at the transaction level, but engines actually provide two nested units: 1. **The statement.** One statement either applies fully or not at all, even when it touches millions of rows. 2. **The transaction.** One or more statements either all apply or none do. The first is what the question is about, and it is not optional or best-effort: no engine leaves 699 of 1,000 rows updated after the statement errors. ## How statement-level atomicity is implemented The standard trick is an implicit savepoint. Before executing a statement inside a transaction, the engine marks the current position in the transaction's undo chain (or version history). If the statement raises an error, the engine rolls back to that mark: everything the statement did is reversed, everything earlier in the transaction is untouched. This is the same mechanism an application-level savepoint uses, applied automatically. It is cheap, but not free — which is why some engines let you disable per-statement marks in specific bulk paths, and why very large statements still cost real work to reverse. ## What kind of failures it covers - Constraint violations — unique, foreign key, check, not-null — raised mid-statement. - Data errors such as a value that does not fit the column type or a division by zero encountered on a particular row. - Being chosen as a deadlock victim, or hitting a lock timeout. - Cancellation of the statement by the client or an administrator. One subtlety: **deferred constraint checks** are evaluated at commit rather than per row, so a violation surfaces as a commit failure and aborts the transaction rather than a single statement. And triggers fire within the statement's atomic unit, so their effects are reversed with it. ## The part that varies: the surrounding transaction This is where candidates most often over-generalise from the one engine they know. **Autocommit (no explicit transaction).** Each statement is implicitly its own transaction. A failure means the statement's changes are gone and there is nothing else to decide. **Inside an explicit transaction, strict behaviour.** Some engines put the transaction into a failed state on any statement error. Every subsequent statement is rejected until the transaction is ended, and only a rollback is possible. The rationale is safety: continuing after an unexpected error is usually a bug, so the engine refuses to let you commit a transaction that contains a failure you might not have noticed. **Inside an explicit transaction, permissive behaviour.** Other engines undo only the failed statement and leave the transaction open and usable. The application may inspect the error, do something else, and still commit the work done before the failure. This is convenient but dangerous with careless code: a swallowed error can lead to committing a half-built unit of work that the developer believed was complete. Because behaviour differs, and because most applications go through a framework or ORM that manages transaction boundaries for them, the portable discipline is: **treat a statement error as fatal to the transaction unless you deliberately wrapped that statement in an explicit savepoint.** ## Explicit savepoints as the portable tool When partial continuation is genuinely required — importing a batch where individual bad rows should be skipped, for instance — the intent should be explicit: mark a savepoint, attempt the risky work, and on error roll back to the mark and carry on. This makes the recovery point visible in the code rather than depending on engine defaults. The alternative, often better, design is to avoid the error instead of recovering from it: validate first, or use a conflict-handling form of the statement so no error is raised at all, which avoids the cost of repeated partial rollbacks in a hot loop. ## Operational notes An error at row 700 of 1,000 still cost the work of modifying 699 rows plus the work of reversing them, and the locks taken on those rows are held until the transaction ends, not until the statement fails. In a batch job that retries statement-by-statement, this can quietly double the write volume and extend lock hold times, so it is worth measuring rather than assuming errors are cheap. ## Interview framing \"The statement is atomic — all 699 rows are reversed via an implicit savepoint. Whether the transaction survives depends on the engine, so I write code that rolls back on error, or uses an explicit savepoint when I truly need to continue.\"
- How does an engine implement statement-level atomicity without rolling back the whole transaction?It takes an implicit savepoint before the statement runs — a mark in the transaction's undo chain or version history — and on error rolls back only to that mark. Everything the failed statement did is reversed while earlier statements in the transaction remain intact. It is the same mechanism as an application-issued savepoint, applied automatically.
- Your batch job needs to insert 100,000 rows and skip individual rows that violate a unique constraint. Why is relying on statement errors a poor approach?Each failing statement still performs and then reverses work, and repeated error handling adds round trips and, in strict engines, forces savepoint management for every row. It is far cheaper to avoid the error — use a conflict-handling insert form or pre-filter the duplicates — so the engine never raises and reverses anything. Reserve savepoint-based recovery for genuinely exceptional cases.
saying these in an interview costs you the question
- Believing the 699 already-modified rows stay changed
- Assuming every engine keeps the transaction usable after a statement error
- Confusing autocommit behaviour with explicit-transaction behaviour
- Thinking a savepoint is only an application-level feature, not something the engine uses internally
- Assuming a failed statement releases the locks it took (they are held until the transaction ends)