When a transaction is rolled back, changes it already made to rows and indexes have to disappear. Mechanically, how does a relational engine achieve that?
answer
- undo record = before-image or inverse op
- per-transaction chain walked backwards
- undo durable before the page is written
- MVCC: abort = mark status, old version stays current
- savepoint = partial rollback to a mark
basics
~20 sThe engine durably records how to reverse each change before making it — before-images or inverse operations — and rollback replays those backwards. Multi-version engines instead leave the old row version in place and simply never make the new one visible.
solid answer
~50 sTwo designs, same guarantee. **Undo-based rollback.** As each change is made, the engine writes an undo record — a before-image of the row or an inverse operation — into the log or a dedicated undo/rollback segment. Records for one transaction are chained together, so rollback walks that chain backwards and restores prior state, including index entries. The write-ahead rule applies: the undo information must be durable before the modified page can reach disk, otherwise an evicted uncommitted page would be unrecoverable. **Version-based rollback.** In engines that keep old row versions in the table, an update writes a new version and leaves the old one in place. Rollback means the aborting transaction's versions are simply never made visible — visibility is decided by consulting the transaction's status, which is now 'aborted'. Rollback is close to constant time; the dead versions are reclaimed later by background cleanup. Crash recovery uses exactly the same mechanism: any transaction lacking a durable commit record is rolled back during startup.
code
text · 6 linesT9 undo chain (newest first):
#3 insert into idx_email -> reverse: remove entry 'bob@x'
#2 update users id=7 -> reverse: restore ('bob', 'old@x')
#1 insert users id=42 -> reverse: delete row at page 12 slot 4
ROLLBACK walks #3 -> #2 -> #1, applying each reversalgo deeper
Know that the engine saves the previous state before changing it and restores it on rollback.
Describe undo records and the per-transaction chain, and know that multi-version engines instead keep old row versions and mark the transaction aborted.
Discuss ordering guarantees (undo durable before the page write), restartable rollback, savepoints, lock release timing, and the operational cost of aborting large transactions.
Compare the two designs as a cost-placement decision — abort-time work versus deferred reclamation — and reason about which failure modes each creates under long transactions and heavy churn.
## The problem By the time a transaction aborts, its effects are not confined anywhere tidy. Rows have been modified in shared buffer pages, index entries have been inserted, pages may already have been written to disk. Rollback must remove all of that — and must work even if the abort is caused by the server dying. Two families of solution exist. Knowing both, and that engines differ, is what separates a memorised answer from a real one. ## Design 1: undo records As the engine makes each change it also records how to reverse it: - for an update, the before-image of the row (or of the changed columns) - for an insert, \"delete the row at this location\" - for a delete, the full prior row These records live either in the write-ahead log alongside redo information, or in a dedicated undo structure (rollback segments, an undo tablespace). Each record carries the transaction id and a pointer to that transaction's previous record, forming a per-transaction chain. Rollback simply follows the chain from newest to oldest and applies each reversal, including undoing index maintenance. Two ordering rules make it crash-safe. The undo information must be durable **before** the corresponding data page may be written out — the write-ahead rule — so an uncommitted page that reached disk is always reversible. And the undo work is itself logged, so that a crash *during* rollback does not lose progress: recovery resumes from where it stopped rather than starting over or, worse, double-undoing. A useful side effect: those before-images can also serve consistent reads for other sessions, which is how some engines implement read consistency without keeping old versions in the table. ## Design 2: in-table versions Multi-version engines take a different route. An update does not overwrite the row; it writes a **new version** of it and marks the old one as superseded by this transaction. Both versions physically coexist in the table, and index entries may point at both. Visibility is then a function of the reader's snapshot and the *status* of the transaction that created each version — in progress, committed, or aborted. Aborting therefore requires almost no work: the engine marks the transaction aborted, and from that moment every reader treats its new versions as invisible and the old ones as current. Rollback of a transaction that modified a million rows costs about the same as rollback of one that modified one row. The cost has not vanished; it has moved. The invisible versions still occupy space in the table and its indexes, and a background cleanup process must reclaim them later. Aborting huge transactions repeatedly produces bloat rather than a slow rollback. ## What both designs share **Commit is the decision point.** Nothing is \"committed and then unwound\". Until the commit record is durable, the transaction's effects are provisional; rollback discards provisional work. **Crash recovery reuses the same path.** On restart, the engine determines which transactions lacked a durable commit record and rolls them back with exactly the machinery an explicit abort would use. Undo-based engines may do this eagerly at startup or lazily as pages are touched; version-based engines effectively get it for free by consulting transaction status. **Savepoints are partial rollback.** A savepoint marks a position in the transaction's undo chain (or in its version history); rolling back to it reverses only the work after that mark, leaving the transaction alive. Engines use this internally to implement statement-level atomicity. **Locks and resources are released at the end.** Rollback ends the transaction, so row locks are dropped and other waiters proceed — which is why a long rollback can keep blocking other sessions until it finishes. ## Practical consequences - In undo-based engines, aborting a large transaction can take as long as the transaction took, and the work must complete before its locks are released. Killing the session does not skip it. - In version-based engines, abort is instant but leaves garbage, so the pressure shows up as table and index bloat and as cleanup load. - Either way, rollback is not free and not instantaneous in every engine, which is a good argument for bounded write transactions. ## Interview framing \"Either the engine logged how to reverse each change and replays that backwards, or it kept the old row versions and just never makes the aborted ones visible. Same guarantee, cost paid at different times — at abort, or later during cleanup.\"
- Why must undo information be durable before the modified data page is written to disk?Because the buffer manager may evict a page containing uncommitted changes, so those changes can reach the data files at any moment. If the machine crashed after that page write but before the undo information was durable, recovery would find a change it could not reverse. Ordering the undo write first closes that window; this is the same write-ahead rule that governs redo.
- Why can rolling back a large transaction take a long time in some engines but be nearly instant in others?Undo-based engines must physically reverse every change, so abort cost is proportional to the work done — and the transaction's locks are held until it finishes. Engines that keep old row versions in the table only need to mark the transaction aborted, since visibility rules then hide its versions, making abort roughly constant time. The latter defers the cost to a background cleanup process that must reclaim the dead versions.
saying these in an interview costs you the question
- Saying rollback 'reverses the commit'
- Claiming the engine replays the transaction's statements in reverse as SQL
- Forgetting that index entries must be undone too
- Assuming every engine keeps a separate undo log
- Believing rollback is always instantaneous and free