Transaction log

Anatomy of a general database log

• Log Sequence Number : A unique ID for a log record. With LSNs, logs can be recovered in constant time. Most LSNs are assigned in monotonically increasing order, which is useful in recovery algorithms, like ARIES.
• Prev LSN: A link to their last log record. This implies database logs are constructed in linked list form.
• Transaction ID number: A reference to the database transaction generating the log record.
• Type: Describes the type of database log record.
• Information about the actual changes that triggered the log record to be written.

  Types of database log records

• Update Log Record notes an update to the database. It includes this extra information:
• *PageID: A reference to the Page ID of the modified page.
• *Length and Offset: Length in bytes and offset of the page are usually included.
• *Before and After Images: Includes the value of the bytes of page before and after the page change. Some databases may have logs which include one or both images.
• Compensation Log Record notes the rollback of a particular change to the database. Each corresponds with exactly one other Update Log Record. It includes this extra information:
• *undoNextLSN: This field contains the LSN of the next log record that is to be undone for transaction that wrote the last Update Log.
• Commit Record notes a decision to commit a transaction.
• Abort Record notes a decision to abort and hence roll back a transaction.
• Checkpoint Record notes that a checkpoint has been made. These are used to speed up recovery. They record information that eliminates the need to read a long way into the log's past. This varies according to checkpoint algorithm. If all dirty pages are flushed while creating the checkpoint, it might contain:
• *redoLSN: This is a reference to the first log record that corresponds to a dirty page. i.e. the first update that wasn't flushed at checkpoint time. This is where redo must begin on recovery.
• *undoLSN: This is a reference to the oldest log record of the oldest in-progress transaction. This is the oldest log record needed to undo all in-progress transactions.
• Completion Record notes that all work has been done for this particular transaction.