After a crash, we find the following log:
0 | BEGIN CHECKPOINT |
5 | END CHECKPOINT (EMPTY XACT TABLE AND DPT) |
10 | T1: UPDATE P1 (OLD: YYY NEW: ZZZ) |
15 | T1: UPDATE P2 (OLD: WWW NEW: XXX) |
20 | T1: COMMIT |
Analysis phase:
Scan forward through the log starting at LSN 0.
LSN 5: Initialize XACT table and DPT to empty.
LSN 10: Add (T1, LSN 10) to XACT table. Add (P1, LSN 10) to DPT.
LSN 15: Set LastLSN=15 for T1 in XACT table. Add (P2, LSN 15) to DPT.
LSN 20: Change T1 status to "Commit" in XACT table
Redo phase:
Scan forward through the log starting at LSN 10.
LSN 10: Read page P1, check PageLSN stored in the page. If PageLSN<10,
redo LSN 10 (set value to ZZZ) and set the page's PageLSN=10.
LSN 15: Read page P2, check PageLSN stored in the page. If PageLSN<15,
redo LSN 15 (set value to XXX) and set the page's PageLSN=15.
Undo phase:
Do nothing; no transactions to undo.
After a crash, we find the following log:
0 | BEGIN CHECKPOINT |
5 | END CHECKPOINT (EMPTY XACT TABLE AND DPT) |
10 | T1: UPDATE P1 (OLD: YYY NEW: ZZZ) |
15 | T1: UPDATE P2 (OLD: WWW NEW: XXX) |
20 | T2: UPDATE P3 (OLD: UUU NEW: VVV) |
25 | T1: COMMIT |
30 | T2: UPDATE P1 (OLD: ZZZ NEW: TTT) |
Analysis phase:
Scan forward through the log starting at LSN 0.
LSN 5: Initialize XACT table and DPT to empty.
LSN 10: Add (T1, LSN 10) to XACT table. Add (P1, LSN 10) to DPT.
LSN 15: Set LastLSN=15 for T1 in XACT table. Add (P2, LSN 15) to
DPT.
LSN 20: Add (T2, LSN 20) to XACT table. Add (P3, LSN 20) to DPT.
LSN 25: Change T1 status to "Commit" in XACT table
LSN 30: Set LastLSN=30 for T2 in XACT table.
Redo phase:
Scan forward through the log starting at LSN 10.
LSN 10: Read page P1, check PageLSN stored in the page. If PageLSN<10,
redo LSN 10 (set value to ZZZ) and set the page's PageLSN=10.
LSN 15: Read page P2, check PageLSN stored in the page. If PageLSN<15,
redo LSN 15 (set value to XXX) and set the page's PageLSN=15.
LSN 20: Read page P3, check PageLSN stored in the page. If PageLSN<20,
redo LSN 20 (set value to VVV) and set the page's PageLSN=20.
LSN 30: Read page P1 if it has been flushed, check PageLSN stored in
the page. It will be 10. Redo LSN 30 (set value to TTT) and set the
page's PageLSN=30.
Undo phase:
T2 must be undone. Put LSN 30 in ToUndo.
Write Abort record to log for T2
LSN 30: Undo LSN 30 - write a CLR for P1 with "set P1=ZZZ" and undonextLSN=20. Write ZZZ
into P1. Put LSN 20 in ToUndo.
LSN 20: Undo LSN 20 - write a CLR for P3 with "set P3=UUU" and
undonextLSN=NULL. Write UUU into P3.
After a crash, we find the following log:
10 | T1: UPDATE P1 (OLD: YYY NEW: ZZZ) |
15 | T2: UPDATE P3 (OLD: UUU NEW: VVV) |
20 | BEGIN CHECKPOINT |
25 | END CHECKPOINT (XACT TABLE=[[T1,10],[T2,20]]; DPT=[[P1,10],[P2,15]]) |
30 | T1: UPDATE P2 (OLD: WWW NEW: XXX) |
35 | T1: COMMIT |
40 | T2: UPDATE P1 (OLD: ZZZ NEW: TTT) |
45 | T2: ABORT |
50 | T2: CLR P1(ZZZ), undonextLSN=15 |
Analysis phase:
Scan forward through the log starting at LSN 20.
LSN 25: Initialize XACT table with T1 (LastLSN 10) and T2 (LastLSN
20). Initialize DPT to P1 (RecLSN 10) and P3 (RecLSN 15).
LSN 30: Add (T1, LSN 30) to XACT table. Add (P2, LSN 30) to DPT.
LSN 35: Change T1 status to "Commit" in XACT table
LSN 40: Set LastLSN=40 for T2 in XACT table.
LSN 45: Change T2 status to "Abort" in XACT table
LSN 50: Set LastLSN=45 for T2 in XACT table.
Redo phase:
Scan forward through the log starting at LSN 10.
LSN 10: Read page P1, check PageLSN stored in the page. If PageLSN<10,
redo LSN 10 (set value to ZZZ) and set the page's PageLSN=10.
LSN 15: Read page P3, check PageLSN stored in the page. If PageLSN<15,
redo LSN 15 (set value to VVV) and set the page's PageLSN=15.
LSN 30: Read page P2, check PageLSN stored in the page. If PageLSN<30,
redo LSN 30 (set value to XXX) and set the page's PageLSN=30.
LSN 40: Read page P1 if it has been flushed, check PageLSN stored in
the page. It will be 10. Redo LSN 40 (set value to TTT) and set the
page's PageLSN=40.
LSN 50: Read page P1 if it has been flushed, check PageLSN stored in
the page. It will be 40. Redo LSN 45 (set value to ZZZ) and set the
page's PageLSN=50.
Undo phase:
T2 must be undone. Put LSN 50 in ToUndo.
LSN 50: Put LSN 15 in ToUndo
LSN 15: Undo LSN 15 - write a CLR for P3 with "set P3=UUU" and
undonextLSN=NULL. Write UUU into P3.