Database logs are often described as containing redo information and undo information. What is the difference between the two, and what is each one needed for?
answer
- redo = re-apply committed, undo = reverse uncommitted
- no-force → need redo
- steal → need undo
- one record can carry before+after images
- rollback = undo without a crash
basics
~20 sRedo information lets the engine re-apply a committed change whose data page never reached disk. Undo information lets it reverse a change made by a transaction that aborted or was still open at crash time. Redo moves forward, undo backward.
solid answer
~50 sA **redo** record carries enough information to re-apply a change to a page: after this change, the page should look like this. It exists because commit does not force dirty pages to disk, so a committed change may live only in memory — recovery replays redo to put it back. An **undo** record carries enough information to reverse a change: the prior value, or a logical inverse operation. It exists because the engine may evict a dirty page belonging to a still-open transaction, so uncommitted changes can reach the data files — and because a live transaction can issue a rollback at any moment. The two are complementary: redo covers committed work missing from the data files, undo covers uncommitted work present in them. Where undo lives varies. Some engines put both kinds of record in the same log; others keep undo in a dedicated structure (rollback/undo segments) or, in multi-version engines, keep the old row version itself in place and let it serve as the undo image.
code
text · 7 linesLSN 1042 <T7 BEGIN>
LSN 1043 <T7 page=42 slot=5 before=('bob', 100) after=('bob', 40)>
LSN 1044 <T7 page=97 slot=1 before=('amy', 10) after=('amy', 70)>
LSN 1045 <T7 COMMIT>
read forward -> apply 'after' = redo
read backward -> apply 'before' = undogo deeper
Know the one-line distinction: redo re-applies committed work, undo reverses uncommitted work.
Explain why each is needed — lazy page writes create missing committed changes, eviction of dirty pages creates present uncommitted changes — and note that a rollback uses the same undo path.
Connect it to operations: redo volume and checkpoint interval drive recovery time; undo volume drives abort cost and, in some engines, the visibility of old versions to concurrent readers.
Reason about the design tradeoff between separate undo segments and in-table versioning — where the cost lands (abort time vs background reclamation), and what that implies for long transactions and storage growth.
## Two directions of repair After a crash, the data files are in an arbitrary middle state. Two independent things can be wrong with them, and each needs its own kind of information. **Committed changes may be missing.** Commit only guarantees that log records are durable, not that the modified pages were written. So a page in the data file may be older than the last committed transaction that touched it. Fixing that requires *redo*: reapply the change. **Uncommitted changes may be present.** Under memory pressure the buffer pool may evict a dirty page even though the transaction that dirtied it has not committed. So a page in the data file may contain writes from a transaction that later aborted or never finished. Fixing that requires *undo*: reverse the change. A rollback issued by a running transaction is the same operation as crash-time undo, just without the crash: the engine walks that transaction's changes backwards and restores prior state. ## What each record contains A redo record identifies the page and describes the resulting state — either the new bytes at an offset (physical logging), or an operation plus arguments to be re-executed against the page (logical/physiological logging, e.g. \"insert this tuple into page 42's free space\"). Physiological logging is the common compromise: physical to a page, logical within it. An undo record identifies the change and describes how to reverse it — typically the before-image of the row, or an inverse operation (\"delete the tuple you inserted at slot 5\"). Many designs put both in one record: `<T7, page42, slot5, before=(…), after=(…)>`. That single record serves redo when read forward and undo when read backward. ## Why both are needed — the buffer-manager policies The requirement for each falls straight out of two buffer-pool policies. *Steal* means the engine is allowed to write out (steal a frame from) a page containing uncommitted changes. Stealing is what lets a transaction be larger than memory and keeps eviction unconstrained — and it is exactly what makes **undo** mandatory. *No-force* means the engine does not force a transaction's dirty pages to disk at commit. No-force is what makes commit cheap — and it is exactly what makes **redo** mandatory. A no-steal/force engine would need neither, but would pay for it with pinned memory and random page writes on every commit. Practically every production engine is steal/no-force, so practically every one logs both. ## Where undo actually lives This differs by engine family and is a good discriminator in interviews. - Some engines keep undo in **dedicated segments** (undo tablespaces or rollback segments). Rollback reads the undo chain for the transaction; those same before-images can also serve consistent reads for other sessions. - Multi-version engines that keep old row versions **in the table itself** effectively store undo in the heap: aborting means the new version is simply never made visible, and the old one stays valid. Their write-ahead log is then dominated by redo. The cost moves elsewhere — dead versions must be reclaimed later by a cleanup process. - Either way the write-ahead rule still applies: undo information must be durable before the affected page can be written out. ## Consequences worth knowing Because redo is replayed forward from a checkpoint, redo work after a crash is bounded by checkpoint frequency. Because undo is per-transaction and proportional to the work that transaction did, aborting a huge transaction can cost as much as running it. And because redo must be idempotent (recovery may crash and restart mid-replay), records are stamped with sequence numbers so the engine can tell whether a page already reflects a given change. ## Interview framing \"Redo repairs missing committed work; undo removes present uncommitted work. You need redo because commit doesn't force pages, and undo because eviction doesn't wait for commit.\"
- Which buffer-pool policies force an engine to keep undo information, and which force it to keep redo information?A steal policy — allowing a dirty page from an uncommitted transaction to be written to disk — forces undo, because uncommitted changes can reach the data files and must be reversible. A no-force policy — not writing a transaction's dirty pages at commit — forces redo, because committed changes may exist only in memory. Steal/no-force is the high-performance combination, which is why real engines log both.
- A multi-version engine keeps old row versions in the table rather than in undo segments. What does that change about rollback, and what does it cost?Rollback becomes cheap and nearly constant-time: the aborted transaction's new row versions are simply never made visible, and the pre-existing versions remain the current ones. The cost is deferred — dead versions accumulate in the table and its indexes and must be reclaimed by a background cleanup process, so the space and I/O bill arrives later rather than at abort time.
saying these in an interview costs you the question
- Saying redo is used for rollback and undo for crash recovery (they are mixed up)
- Believing only committed transactions are ever written to the log
- Assuming uncommitted changes can never reach the data files
- Claiming undo is only about supporting explicit rollback, not crash recovery
- Thinking every engine keeps a separate undo log — some store old versions in the table itself