Review Consider the following sequence of lock requests: l 1 (B); l - PDF document

Review • Consider the following sequence of lock requests: l 1 (B); l 2 (A); l 3 (C); l 1 (C); l 2 (B); l 3 (A) l 1 (B); l 2 (A); l 3 (C); l 1 (C); l 2 (B); l 3 (A) • Assume that upon start, transactions T 1 , T 2 , T 3 were assigned timestamps 10, 20, 30, respectively. What is the order in which transactions commit in a wait-die scheme? • What is the order in which transactions commit in a wound-wait scheme? CS5208 – Crash Recovery 1 Review l 1 (B); l 2 (A); l 3 (C); l 1 (C); l 2 (B); l 3 (A) • wait-die scheme T 3, T 1 , T 2 • wound-wait scheme d it h T 1 , T 2 , T 3 CS5208 – Crash Recovery 2 1

Review • Four transactions T1, T2, T3, T4 used 2PL for concurrency control. Since 2PL ensures conflict-serializability, the schedule (say S) of actions of the four transactions has to be conflict equivalent to some serial schedule. We do not have access to the entire schedule S but only a part of it, which looks as follows: S = :::; u 4 (A); l 1 (B); :::; u 1 (B); l3(A); l 2 (B); :::; u 3 (A); l 2 (A); ::: • • Which of the following schedules are possible serial schedules that are g p conflict-equivalent to S. (a) T 1 , T 3 , T 2 , T 4 (b) T 1 , T 4 , T 3 , T 2 (c) T 4 , T 1 , T 3 , T 2 (Correct) CS5208 – Crash Recovery 3 Log-Based Recovery Schemes If you are going to be in the logging business, one of the things that you , g y have to do is to learn about heavy equipment. Robert VanNatta, Logging History of Columbia County CS5208 – Crash Recovery 4 2

Integrity or consistency constraints • Predicates data must satisfy, e.g. • x is key of relation R • x is key of relation R • x  y holds in R • Domain(x) = {Red, Blue, Green} • no employee should make more than twice the average salary • Definitions • Consistent state: satisfies all constraints • Consistent DB: DB in consistent state CS5208 – Crash Recovery 5 Observation: DB cannot always be consistent! CS5208 – Crash Recovery 6 3

Observation: DB cannot always be consistent! Example: Transfer 100 from a2 to a10 Example: Transfer 100 from a2 to a10 a2  a2 - 100 a10  a10 + 100 . . . . a2 500 400 . . . . a10 1000 1000 CS5208 – Crash Recovery 7 Observation: DB cannot always be consistent! Example: Transfer 100 from a2 to a10 Example: Transfer 100 from a2 to a10 a2  a2 - 100 a10  a10 + 100 . . . . . . a2 500 400 400 . . . . . . a10 1000 1000 1100 CS5208 – Crash Recovery 8 4

Transaction: collection of actions that preserve consistency preserve consistency Consistent DB T Consistent DB’ If T starts with consistent state + T executes in If T starts with consistent state T executes in isolation (and absence of errors)  T leaves consistent state CS5208 – Crash Recovery 9 Reasons for crashes • Transaction failures • Logical errors deadlocks Logical errors, deadlocks • System crash • Power failures, operating system bugs etc • Disk failure • Head crashes • Head crashes • STABLE STORAGE: Data never lost. Can approximate by using RAID and maintaining geographically distant copies of the data CS5208 – Crash Recovery 10 5

Review: The ACID properties • A tomicity – All actions in a transaction are carried out, or none are, i.e., no incomplete transactions • C onsistency – Each transaction preserves DB consistency C onsistency Each transaction preserves DB consistency User’s responsibility, e.g., Funds transfer between bank accounts • • I solation – A transaction isolated or protected from the effects of other transactions • D urability – When a transaction commits, its effects persist CS5208 – Crash Recovery 11 Review: The ACID properties • A tomicity – All actions in a transaction are carried out, or none are, i.e., no incomplete transactions • C onsistency – Each transaction preserves DB consistency C onsistency Each transaction preserves DB consistency User’s responsibility, e.g., Funds transfer between bank accounts • • I solation – A transaction isolated or protected from the effects of other transactions • D urability – When a transaction commits, its effects persist • Question: which ones do the Recovery Manager help with? Atomicity & Durability CS5208 – Crash Recovery 12 6

Actions of Transaction: • Read • Read • Write • Commit • Abort CS5208 – Crash Recovery 13 Key problem: Unfinished transaction Example Example Transfer fund from A to B Transfer fund from A to B T 1 : A  A - 100 B  B + 100 CS5208 – Crash Recovery 14 7

T 1 : Read (A); A  A-100 Write (A); R Read (B); d (B) B  B+100 Write (B); A: 800 A 800 A: 800 B: 800 B: 800 disk memory CS5208 – Crash Recovery 15 T 1 : Read (A); A  A-100 Write (A); R Read (B); d (B) B  B+100 Write (B); A 800 700 700 A: 800 A: 800 B: 800 B: 800 disk memory CS5208 – Crash Recovery 16 8

T 1 : Read (A); A  A-100 Write (A); R Read (B); d (B) B  B+100 Updated A value is written to disk. Write (B); This may be triggered “ANYTIME” by explicit command or DBMS or OS. A 800 700 700 A: 800 700 A: 800 B: 800 B: 800 disk memory CS5208 – Crash Recovery 17 T 1 : Read (A); A  A-100 Write (A); R Read (B); d (B) B  B+100 Write (B); A 800 700 700 A: 800 700 A: 800 B: 800 900 B: 800 disk memory CS5208 – Crash Recovery 18 9

T 1 : Read (A); A  A-100 Write (A); R Read (B); d (B) B  B+100 Write (B); failure! A 800 700 700 A: 800 700 A: 800 B: 800 900 B: 800 disk memory CS5208 – Crash Recovery 19 T 1 : Read (A); Need atomicity: execute all A  A-100 actions of a transaction or Write (A); none at all R Read (B); d (B) B  B+100 Write (B); failure! A 800 700 700 A: 800 700 A: 800 B: 800 900 B: 800 disk memory CS5208 – Crash Recovery 20 10

One Solution: Undo logging (Immediate modification) Read (A); A  A-100 T1: Write (A); Write (A); Read (B); B  B+100 Write (B); A:800 A 800 A:800 A 800 B:800 B:800 disk memory Log (Stable) CS5208 – Crash Recovery 21 One Solution: Undo logging (Immediate modification) Read (A); A  A-100 T1: Write (A); Write (A); Read (B); B  B+100 Write (B); < T1, start> A 800 A:800 A 800 A:800 B:800 B:800 disk memory log CS5208 – Crash Recovery 22 11

One Solution: Undo logging (Immediate modification) Read (A); A  A-100 T1: Write (A); Write (A); Read (B); B  B+100 Write (B); < T1, start> A 800 A:800 700 700 A 800 A:800 900 B:800 B:800 disk memory log CS5208 – Crash Recovery 23 One Solution: Undo logging (Immediate modification) Read (A); A  A-100 T1: Write (A); Write (A); Read (B); B  B+100 Write (B); < T1, start> < T1, A, 800> 700 700 A:800 A 800 A 800 A:800 900 B:800 B:800 disk memory log CS5208 – Crash Recovery 24 12

One Solution: Undo logging (Immediate modification) Read (A); A  A-100 T1: Write (A); Write (A); Read (B); B  B+100 Write (B); < T1, start> < T1, A, 800> A:800 A 800 700 700 A 800 A:800 700 700 900 B:800 B:800 disk memory log CS5208 – Crash Recovery 25 One Solution: Undo logging (Immediate modification) Read (A); A  A-100 T1: Write (A); Write (A); Read (B); B  B+100 Write (B); < T1, start> < T1, A, 800> 700 700 A:800 A 800 A 800 A:800 700 700 < T1, B, 800> T1 B 800 900 B:800 B:800 disk memory log CS5208 – Crash Recovery 26 13

One Solution: Undo logging (Immediate modification) Read (A); A  A-100 T1: Write (A); Write (A); Read (B); B  B+100 Write (B); < T1, start> < T1, A, 800> A 800 A:800 700 700 A 800 A:800 700 700 < T1, B, 800> T1 B 800 900 900 B:800 B:800 disk memory log CS5208 – Crash Recovery 27 One Solution: Undo logging (Immediate modification) Read (A); A  A-100 T1: Write (A); Write (A); Read (B); B  B+100 Write (B); < T1, start> < T1, A, 800> 700 700 A 800 A:800 A:800 A 800 700 00 < T1, B, 800> T1 B 800 900 900 < T1, commit> B:800 B:800 disk memory log CS5208 – Crash Recovery 28 14

Complication • Log is first written in memory memory A: 800 B: 800 DB A: 800 700 B: 800 900 Log: Log < T 1 ,start> T t t < T 1 , A, 800> < T 1 , B, 800> CS5208 – Crash Recovery 29 Complication • Log is first written in memory memory A: 800 700 BAD STATE B: 800 DB A: 800 700 # 1 B: 800 900 Log: Log Failure occurs after < T 1 ,start> T t t partial updates on disk but before log < T 1 , A, 800> is written to disk < T 1 , B, 800> CS5208 – Crash Recovery 30 15

Complication • Log is first written in memory memory A: 800 700 BAD STATE B: 800 DB A: 800 700 # 1 B: 800 900 Log: Log Failure occurs after < T 1 ,start> T t t partial updates on < T 1 , A, 800> disk but before log is written to disk < T 1 , B, 800> This means log record for A must be on log disk before A can be updated on data disk (DB) CS5208 – Crash Recovery 31 Complication • Log is first written in memory • Updates are not written to disk on every action Updates are not written to disk on every action memory A: 800 DB B: 800 A: 800 700 B: 800 900 Log Log: < T 1 ,start> T t t < T1, B, 800> < T 1 , A, 800> < T1, commit> < T 1 , B, 800> CS5208 – Crash Recovery 32 16