Recovery of uncommitted transactions after checkpoint

Question

After reading from several resources including:

• COMMIT (Oracle Database SQL Reference)
• Checkpoints (Oracle Programmer Reference)
• Difference between commit and checkpoint (on this site)
• ORACLE CHECKPOINTS by Anju Garg

I have concluded the following:

1. The effects of any transaction are written to the log buffer in the memory.
2. Commit flushes the log buffer to the logfiles in the disk.
3. On the occurrence of a Checkpoint, DBW writes the dirty blocks to datafiles on the basis of logfile entries.
4. In the event of a crash, the Recovery Manager reads the logfiles (till the last checkpoint) and performs Undo/Redo on the transactions.

I understand that the committed transactions are redone and uncommitted transactions are undone.

What I don't understand is how are uncommitted transactions known, given that they exist only in the log buffer in the memory and any crash is supposed to wipe-out the memory and the only surviving transactions are those residing in the logfiles on the disk (after getting committed). Is something wrong in my understanding?

Answer 1

Write-ahead logging guarantees that log records describing a change (and how to undo it) are written to persistent storage before the changed data is.

Log records do not necessarily wait for a commit before being persisted. Changed data may also be written to persistent storage (e.g. by checkpoint) before transaction commit, but only if the associated log record(s) have been persisted first. Commit does guarantee that all log records for the transaction are on stable storage before the commit completes.

The ARIES protocol further requires that each database page contains the Log Sequence Number (LSN) of the most recent transaction that changed anything on that page.

Under ARIES, recovery can analyse the persisted log records to determine which transactions to undo (no transaction commit record found).

The redo recovery phase redoes all logged operations representing changes that may not have been persisted to disk. When processing a log record, redo compares the page LSN with the LSN of the current log record, and only applies the logged operation if the page LSN is lower (i.e. the current image of the page is older).

For each active transaction, undo processing scans the log backwards, starting from the last log record generated by this transaction, then undoes the operation performed by each log record.

The original paper Aries: A transaction recovery method supporting fine-granularity locking and partial rollbacks using write-ahead logging is available on CiteSeer. Section 3 provides a reasonably concise overview.