Database Recovery Techniques in DBMS: Log-Based, Checkpoint, Shadow Paging and a 10-Mark Answer

Database recovery is the last unit of the TU BSc CSIT Database Management System syllabus (CSC265) and the recovery unit of the BIT paper (BIT202), and it is one of the most reliable sources of a 10-mark question. The question is usually some version of "explain the database recovery techniques" or "differentiate deferred and immediate update with an example", sometimes with "what is a checkpoint and why is it needed" as a 5-mark follow-up.

This article explains the techniques in exam order, with the small transaction example that examiners expect you to reproduce, and ends with an outline you can write in the time a 10-mark answer allows. The full unit notes and solved past questions are on the Database Recovery Techniques notes page.

Why recovery is needed

A database system guarantees the ACID properties, and two of them depend entirely on recovery. Atomicity says a transaction either completes fully or has no effect. Durability says a committed transaction's changes survive any failure. Failures do happen: a transaction aborts because of a logical error or deadlock, the system crashes and loses main memory, or a disk fails and loses stored data.

The recovery manager is the part of the DBMS that restores the database to a consistent state after a failure. Its two basic operations are undo, which removes the effect of an uncommitted transaction, and redo, which reapplies the effect of a committed transaction whose changes may not have reached the disk.

Log-based recovery

The log is a sequential file of records that describes every change a transaction makes. The important record types are:

The rule that makes the log useful is write-ahead logging (WAL): the log record describing a change must be written to stable storage before the changed data page is written to disk. If the system fails, the log on disk is always at least as up to date as the data, so the recovery manager can trust it.

Log-based recovery comes in two forms, and the exam almost always asks you to compare them.

Deferred update (NO-UNDO/REDO)

Under deferred update, a transaction's writes are recorded in the log but not applied to the database until the transaction reaches its commit point. Because nothing uncommitted ever reaches the disk, there is never anything to undo. After a crash, the recovery manager scans the log: for every transaction with a commit record, it redoes the writes in order; transactions without a commit record are simply ignored and restarted.

The cost is that the buffer must hold all of a long transaction's updates until commit, and the redo work after a crash can be large.

Immediate update (UNDO/REDO)

Under immediate update, a transaction's writes may be applied to the database as they happen, before commit, as long as the log record is written first. This improves performance and buffer use, but now an uncommitted transaction may have changed the disk. Recovery therefore has two passes. Transactions that have a commit record in the log are redone. Transactions that started but did not commit are undone by writing back the old values recorded in the log, in reverse order.

A worked example of the kind the paper expects:

Under deferred update the same log would only require the redo of T1, since T2's write never reached the database.

Checkpoints

Scanning the entire log after every crash is slow, and it redoes committed work that is already safely on disk. A checkpoint solves this. At a checkpoint the DBMS suspends transaction execution briefly, forces all log records and all modified buffer pages to disk, and writes a [checkpoint] record to the log.

After a crash, the recovery manager only needs to look at transactions that were active at the last checkpoint or started after it. Transactions that committed before the checkpoint are already durable and can be skipped. This bounds the recovery time and is why every production DBMS takes checkpoints at regular intervals. A fuzzy checkpoint lets transactions continue while the pages are written out, recording where the checkpoint began so recovery still works.

Shadow paging

Shadow paging is the main alternative to log-based recovery and the one students most often forget. The database is divided into fixed-size pages, and a page table maps logical page numbers to physical disk pages. When a transaction starts, the current page table is copied to make a shadow page table, which is never changed during the transaction.

When the transaction writes to a page, the DBMS copies that page to a new disk location, updates the current page table to point to the copy, and leaves the original untouched. On commit, the current page table becomes the new permanent page table with a single atomic write. On failure, the current page table is discarded and the shadow page table still points at the unchanged pages, so the database is exactly as it was before the transaction began.

ARIES in one paragraph

Many papers set a 5-mark question on ARIES, the recovery algorithm used in most commercial systems. It is an immediate update scheme built on three ideas: write-ahead logging, repeating history during redo (every action in the log since the last checkpoint is redone, including those of transactions that will later be undone, so the database reaches exactly the pre-crash state), and logging the undo actions themselves as compensation log records so that a crash during recovery does not repeat work. Recovery runs in three phases: analysis, which finds the dirty pages and active transactions at the time of the crash; redo; and undo.

Transaction rollback and cascading rollback

If a transaction T reads a value written by an uncommitted transaction S and S is then aborted, T must also be rolled back, and anything that read from T as well. This is cascading rollback, and it is expensive. Recoverable schedules avoid it by ensuring a transaction commits only after every transaction it read from has committed, and strict schedules avoid it entirely by not allowing a read or write of an item until the transaction that last wrote it has committed. The link between recovery and concurrency control is a common cross-unit question, so revise the Concurrency Control Techniques notes alongside this unit.

A 10-mark model answer outline

For "Explain the database recovery techniques with suitable examples":

1. Define recovery and relate it to atomicity and durability; name the failure types (transaction, system, media). Two or three sentences.
2. Describe the log, its record types and the write-ahead logging rule. Include the log record table.
3. Deferred update: define, explain why only redo is needed, give the recovery procedure.
4. Immediate update: define, explain why undo and redo are both needed, give the small log example with the actions after a crash.
5. Checkpoints: what happens at a checkpoint and how it shortens recovery.
6. Shadow paging: the two page tables, the commit step, and the comparison with log-based recovery in three lines.
7. One closing sentence on which technique real systems use and why.

Points 3, 4 and 6 carry the bulk of the marks. If the question says "differentiate deferred and immediate update", answer with a table and the example, and keep shadow paging to a single mention.

Where this sits in the syllabus

Recovery is Unit 10 of CSC265 and closes the transaction processing sequence that begins with transaction concepts and continues through concurrency control. BIT students will find the matching unit at Database Recovery and Reliability. The important questions view for CSC265 shows how often each of these topics has appeared in past papers.