Review Review Consider the following sequence of lock requests: l - PDF document

Review 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 • wait-die scheme were assigned timestamps 10, 20, 30, respectively. g p , , , p y T T T 3, T 1 , T 2 T What is the order in which transactions commit in a wait-die scheme? • wound-wait scheme • What is the order in which transactions commit in a T 1 , T 2 , T 3 wound-wait scheme? CS5208 – Crash Recovery 1 CS5208 – Crash Recovery 2 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 Log-Based Recovery Schemes looks as follows: S = :::; u 4 (A); l 1 (B); :::; u 1 (B); l3(A); l 2 (B); :::; u 3 (A); l 2 (A); ::: • If you are going to be in the logging business, one of the things that you • Which of the following schedules are possible serial schedules that are have to do is to learn about heavy conflict-equivalent to S. equipment. (a) T 1 , T 3 , T 2 , T 4 Robert VanNatta, (b) T 1 , T 4 , T 3 , T 2 Logging History of (c) T 4 , T 1 , T 3 , T 2 (Correct) Columbia County CS5208 – Crash Recovery 3 CS5208 – Crash Recovery 4 Observation: Integrity or consistency constraints DB cannot always be consistent! • Predicates data must satisfy, e.g. • 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 CS5208 – Crash Recovery 6 1

Observation: Observation: DB cannot always be consistent! DB cannot always be consistent! Example: Transfer 100 from a2 to a10 Example: Transfer 100 from a2 to a10 a2  a2 - 100 a2  a2 - 100 a10  a10 + 100 a10  a10 + 100 a10  a10 + 100 a10  a10 + 100 . . . . . . . . . . a2 500 400 a2 500 400 400 . . . . . . . . . . a10 a10 1000 1000 1000 1000 1100 CS5208 – Crash Recovery 7 CS5208 – Crash Recovery 8 Reasons for crashes Transaction: collection of actions that • Transaction failures preserve consistency • Logical errors, deadlocks • System crash Consistent DB T Consistent DB’ • Power failures, operating system bugs etc • Power failures operating system bugs etc • Disk failure If T starts with consistent state + T executes in • Head crashes isolation (and absence of errors) • STABLE STORAGE: Data never lost. Can approximate by using RAID and maintaining  T leaves consistent state geographically distant copies of the data CS5208 – Crash Recovery 9 CS5208 – Crash Recovery 10 Review: The ACID properties Review: The ACID properties • A tomicity – All actions in a transaction are carried out, or • A tomicity – All actions in a transaction are carried out, or none are, i.e., no incomplete transactions 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 User’s responsibility, e.g., Funds transfer between bank accounts • • • I solation – A transaction isolated or protected from the • I solation – A transaction isolated or protected from the effects of other transactions effects of other transactions effects of other transactions effects of other transactions • D urability – When a transaction commits, its effects persist • D urability – When a transaction commits, its effects persist • Question: which ones do the Recovery Manager help with? Atomicity & Durability CS5208 – Crash Recovery 11 CS5208 – Crash Recovery 12 2

Actions of Transaction: Key problem: Unfinished transaction Example Transfer fund from A to B • Read T 1 : A  A - 100 • Write • Commit B  B + 100 B  B + 100 • Abort CS5208 – Crash Recovery 13 CS5208 – Crash Recovery 14 T 1 : Read (A); T 1 : Read (A); A  A-100 A  A-100 Write (A); Write (A); Read (B); Read (B); B  B+100 B  B+100 Write (B); Write (B); Write (B); Write (B); 700 A: 800 A: 800 A: 800 A: 800 B: 800 B: 800 B: 800 B: 800 disk disk memory memory CS5208 – Crash Recovery 15 CS5208 – Crash Recovery 16 T 1 : Read (A); T 1 : Read (A); A  A-100 A  A-100 Write (A); Write (A); Read (B); Read (B); B  B+100 B  B+100 Updated A value is written to disk. Write (B); Write (B); Write (B); Write (B); Thi This may be triggered “ANYTIME” b t i d “ANYTIME” by explicit command or DBMS or OS. 700 700 A: 800 A: 800 700 700 A: 800 A: 800 B: 800 B: 800 900 B: 800 B: 800 memory disk memory disk CS5208 – Crash Recovery 17 CS5208 – Crash Recovery 18 3

T 1 : Read (A); T 1 : Read (A); Need atomicity: execute all A  A-100 A  A-100 actions of a transaction or Write (A); Write (A); none at all Read (B); Read (B); B  B+100 B  B+100 Write (B); Write (B); Write (B); Write (B); failure! failure! 700 700 A: 800 A: 800 700 700 A: 800 A: 800 B: 800 B: 800 900 900 B: 800 B: 800 disk disk memory memory CS5208 – Crash Recovery 19 CS5208 – Crash Recovery 20 One Solution: Undo logging One Solution: Undo logging (Immediate modification) (Immediate modification) Read (A); A  A-100 Read (A); A  A-100 T1: T1: Write (A); Write (A); Read (B); B  B+100 Read (B); B  B+100 Write (B); Write (B); < T1, start> A:800 A:800 A:800 A:800 B:800 B:800 B:800 B:800 disk disk memory Log (Stable) memory log CS5208 – Crash Recovery 21 CS5208 – Crash Recovery 22 One Solution: Undo logging One Solution: Undo logging (Immediate modification) (Immediate modification) Read (A); A  A-100 Read (A); A  A-100 T1: T1: Write (A); Write (A); Read (B); B  B+100 Read (B); B  B+100 Write (B); Write (B); < T1, start> < T1, start> < T1, A, 800> A:800 700 A:800 700 A:800 A:800 900 900 B:800 B:800 B:800 B:800 disk disk memory log memory log CS5208 – Crash Recovery 23 CS5208 – Crash Recovery 24 4

One Solution: Undo logging One Solution: Undo logging (Immediate modification) (Immediate modification) Read (A); A  A-100 Read (A); A  A-100 T1: T1: Write (A); Write (A); Read (B); B  B+100 Read (B); B  B+100 Write (B); Write (B); < T1, start> < T1, start> < T1, A, 800> < T1, A, 800> 700 700 A:800 A:800 A:800 A:800 < T1, B, 800> 700 700 B:800 900 B:800 900 B:800 B:800 disk disk memory log memory log CS5208 – Crash Recovery 25 CS5208 – Crash Recovery 26 One Solution: Undo logging One Solution: Undo logging (Immediate modification) (Immediate modification) Read (A); A  A-100 Read (A); A  A-100 T1: T1: Write (A); Write (A); Read (B); B  B+100 Read (B); B  B+100 Write (B); Write (B); < T1, start> < T1, start> < T1, A, 800> < T1, A, 800> 700 700 A:800 A:800 A:800 A:800 700 < T1, B, 800> 700 < T1, B, 800> B:800 900 900 B:800 900 900 < T1, commit> B:800 B:800 disk disk memory log memory log CS5208 – Crash Recovery 27 CS5208 – Crash Recovery 28 Complication Complication • Log is first written in memory • Log is first written in memory memory memory A: 800 A: 800 700 BAD STATE S B: 800 B: 800 DB DB DB DB A: 800 700 A 800 700 A: 800 700 A 800 700 # 1 B: 800 900 B: 800 900 Log: Log: Log Log Failure occurs after < T 1 ,start> < T 1 ,start> partial updates on disk but before log < T 1 , A, 800> < T 1 , A, 800> is written to disk < T 1 , B, 800> < T 1 , B, 800> CS5208 – Crash Recovery 29 CS5208 – Crash Recovery 30 5