Lecture 15 ARIES Recovery 1112017 Log Based Recovery

Lecture 15 – ARIES Recovery 11/1/2017

Log Based Recovery • Recall: database state is memory + disk pages • Add a log managed via write ahead logging – Write what we plan to do, before we do it, so that: • We can UNDO dirty data written by uncommitted/aborting transactions • We can REDO committed actions not flushed to disk at commit time • Q: do you always need to write UNDO/REDO state?

ARIES Example WA, B WC 1 WD 2 CRASH WA WB 3 CP LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 WE Flush Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN Data 1 2 A B 3 C 7 6 5 8 11 10 D B A E

ARIES Data Structures xaction. Table dirty. Pg. Table Checkpoint last. LSN TID pg. No rec. LSN xaction. Table 3 - 1 13 321 31 A D 82 5 3 B 10 3 A C 11 6 E 13 dirty. Pg. Table A - 2, B - 3 Disk 1 2 3 WA, B WC WD WB CP Flush CRASH WA WE Page page. LSN A 2? B 3? C 6? D ? E ?

Crash Recovery • 3 Phases – Analysis • Rebuild data structures • Determine winners & losers – Redo • “Repeat history” • Why? – Undo • Undo Losers

Analysis LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN Data 2 3 A B 3 C 7 6 5 8 11 10 D B A E

Analysis LSN 5 6 7 8 9 10 11 12 13 Type SOT UP EOT UP xaction. Table Tid 3 1 2 2 1 3 2 2 3 Prev. LSN Data 3 C 7 6 5 8 11 10 D dirty. Pg. Table last. LSN TID pg. No rec. LSN 3 1 A 2 B 3 B A E Disk Page page. LSN A 2 B 3 C 6 D ? E ?

Analysis LSN 5 6 7 8 9 10 11 12 13 Type SOT UP EOT UP xaction. Table Tid 3 1 2 2 1 3 2 2 3 Prev. LSN Data 3 C 7 6 5 8 11 10 D dirty. Pg. Table last. LSN TID pg. No rec. LSN 3 1 A 2 5 3 B A E Disk Page page. LSN A 2 B 3 C 6 D ? E ?

Analysis LSN 5 6 7 8 9 10 11 12 13 Type SOT UP EOT UP xaction. Table Tid 3 1 2 2 1 3 2 2 3 Prev. LSN Data 3 C 7 6 5 8 11 10 D dirty. Pg. Table last. LSN TID pg. No rec. LSN 3 1 A 2 5 3 B 3 C 6 B A E Disk Page page. LSN A 2 B 3 C 6 D ? E ?

Analysis LSN 5 6 7 8 9 10 11 12 13 Type SOT UP EOT UP xaction. Table Tid 3 1 2 2 1 3 2 2 3 Prev. LSN Data 3 C 7 6 5 8 11 10 D dirty. Pg. Table last. LSN TID pg. No rec. LSN 3 1 A 2 5 3 B 3 7 2 C 6 B A E Disk Page page. LSN A 2 B 3 C 6 D ? E ?

Analysis LSN 5 6 7 8 9 10 11 12 13 Type SOT UP EOT UP xaction. Table Tid 3 1 2 2 1 3 2 2 3 Prev. LSN Data 3 C 7 6 5 8 11 10 D dirty. Pg. Table last. LSN TID pg. No rec. LSN 3 1 A 2 5 3 B 3 8 2 C 6 D 8 B A E Disk Page page. LSN A 2 B 3 C 6 D ? E ?

Analysis LSN 5 6 7 8 9 10 11 12 13 Type SOT UP EOT UP xaction. Table Tid 3 1 2 2 1 3 2 2 3 Prev. LSN Data 3 C 7 D 6 5 Dirty page table. Bdoesn’t 8 reflect true state. Aon disk. 11 Conservative: at least all previous 10 are on disk E LSNs dirty. Pg. Table Disk pg. No rec. LSN Page page. LSN last. LSN TID A 2 13 3 A 2 B 3 C 6 D 8 D ? E 13 E ?

Redo dirty. Pg. Table • Where to begin? – Checkpoint? – Min(rec. LSN)! • What to REDO – Everything? • Slow pg. No rec. LSN A 2 B 3 C 6 D 8 E 13 • Problematic if using operational (escrow) logging – Redo an update UNLESS: • Page is not in dirty. Pg. Table – Page flushed prior to checkpoint, didn’t redirty • LSN < rec. LSN – Page flushed & redirtied prior to checkpoint • LSN <= page. LSN – Page flushed after checkpoint Disk Page page. LSN A 2 B 3 C 6 D ? E ?

Redo Example Dirty. Pg. Table pg. No rec. LSN A 2 B 3 C 6 D 8 E 13 Redo UNLESS • Page is not in dirty. Pg. Table • LSN < rec. LSN • LSN <= page. LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN 2 3 Disk Data A B 3 C 7 6 5 8 11 10 D B A E Page page. LSN A 2 B 3 C 6 D ? E ?

Redo Example Dirty. Pg. Table pg. No rec. LSN B 3 C 6 D 8 E 13 Redo UNLESS • Page is not in dirty. Pg. Table • LSN < rec. LSN • LSN <= page. LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN 2 3 Disk Data A B 3 C 7 6 5 8 11 10 D B A E Page page. LSN A 2 B 3 C 6 D ? E ?

Redo Example Dirty. Pg. Table pg. No rec. LSN C 6 D 8 E 13 Redo UNLESS • Page is not in dirty. Pg. Table • LSN < rec. LSN • LSN <= page. LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN 2 3 Disk Data A B 3 C 7 6 5 8 11 10 D B A E Page page. LSN A 2 B 3 C 6 D ? E ?

Redo Example Dirty. Pg. Table pg. No rec. LSN D 8 E 13 Redo UNLESS • Page is not in dirty. Pg. Table • LSN < rec. LSN • LSN <= page. LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN 2 3 Disk Data A B 3 C 7 6 5 8 11 10 D B A E Page page. LSN A 2 B 3 C 6 D ? E ?

Redo Example Dirty. Pg. Table pg. No rec. LSN B 10 D 8 E 13 Redo UNLESS • Page is not in dirty. Pg. Table • LSN < rec. LSN • LSN <= page. LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN 2 3 Disk Data A B 3 C 7 6 5 8 11 10 D B A E Page page. LSN A 2 B 3 C 6 D ? E ?

Redo Example Dirty. Pg. Table pg. No rec. LSN A 11 B 10 D 8 E 13 Redo UNLESS • Page is not in dirty. Pg. Table • LSN < rec. LSN • LSN <= page. LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN 2 3 Disk Data A B 3 C 7 6 5 8 11 10 D B A E Page page. LSN A 2 B 3 C 6 D ? E ?

Redo Example Dirty. Pg. Table pg. No rec. LSN A 11 B 10 D 8 E 13 Redo UNLESS • Page is not in dirty. Pg. Table • LSN < rec. LSN • LSN <= page. LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN 2 3 Disk Data A B 3 C 7 6 5 8 11 10 D B A E Page page. LSN A 2 B 3 C 6 D ? E ? State identical to pre-crash state

Undo • Walk backwards, following prev. LSNs to UNDO losers LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN Data 2 3 A B 3 C 7 6 5 8 11 10 D B A E xaction. Table last. LSN TID 13 3

Undo • Walk backwards, following prev. LSNs to UNDO losers LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN Data 2 3 A B 3 C 7 6 5 8 11 10 D B A E xaction. Table last. LSN TID 13 3

Undo • Walk backwards, following prev. LSNs to UNDO losers LSN 1 2 3 4 5 6 7 8 9 10 11 12 13 Type SOT UP UP CP SOT UP EOT UP Tid 1 1 1 3 1 2 2 1 3 2 2 3 Prev. LSN Data 2 3 A B 3 C 7 6 5 8 11 10 D B A E • Why can we just blindly apply UNDOs? xaction. Table last. LSN TID 13 3

UNDO Wrinkles • Logical UNDO – Why? • “Compensation Log Records” -- CLRs – Avoid repeating UNDO work – Why?

UNDO with CLR LSN 5 6 7 Type SOT UP SOT Tid 3 1 2 Prev. LSN Data 3 C 8 9 10 11 12 13 UP EOT UP 2 1 3 2 2 3 7 6 5 8 11 10 D Losers: 3 B A E

UNDO with CLR LSN 5 6 7 Type SOT UP SOT Tid 3 1 2 Prev. LSN Data 3 C 8 9 10 11 12 13 UP EOT UP 2 1 3 2 2 3 7 6 5 8 11 10 D B A E

UNDO with CLR LSN 5 6 7 Type SOT UP SOT Tid 3 1 2 Prev. LSN Data 3 C 8 9 10 11 12 13 14 UP EOT UP CLR 2 1 3 2 2 3 3 7 6 5 8 11 10 13 D B A E 10

UNDO with CLR LSN 5 6 7 Type SOT UP SOT Tid 3 1 2 Prev. LSN Data 3 C 8 9 10 11 12 13 14 UP EOT UP CLR 2 1 3 2 2 3 3 7 6 5 8 11 10 13 D B A E E, 10

UNDO with CLR LSN 5 6 7 Type SOT UP SOT Tid 3 1 2 Prev. LSN Data 3 C 8 9 10 11 12 13 14 15 UP EOT UP CLR 2 1 3 2 2 3 3 3 7 6 5 8 11 10 13 14 D B A E E, 10 B, 5

UNDO with CLR LSN 5 6 7 Type SOT UP SOT Tid 3 1 2 Prev. LSN Data 3 C 8 9 10 11 12 13 14 15 UP EOT UP CLR 2 1 3 2 2 3 3 3 7 6 5 8 11 10 13 14 D B A E E, 10 B, 5

UNDO with CLR LSN 5 6 7 Type SOT UP SOT Tid 3 1 2 Prev. LSN Data 3 C 8 9 10 11 12 13 14 15 UP EOT UP CLR 2 1 3 2 2 3 3 3 7 6 5 8 11 10 13 14 D B A E E, 10 B, 5

UNDO with CLR LSN 5 6 7 Type SOT UP SOT Tid 3 1 2 Prev. LSN Data 3 C 8 9 10 11 12 13 14 15 16 UP EOT UP CLR EOT 2 1 3 2 2 3 3 7 6 5 8 11 10 13 14 15 D B A E E, 10 B, 5

REDO with CLR • REDO CLRs on crash recovery – Use REDO rules to check if updates in CLRs have already been done • Avoids repeating operational (escrow) operations – After processing CLR, update last. LSN field in dirty. Pg. Table • Allows UNDO to start from the right place