1 / 46

CS4432: Database Systems II

CS4432: Database Systems II. Database Consistency and Violations?. Transactions, etc. Crash recovery Chapter 17 Concurrency control Chapter 18 Transaction processing Chapter 19. Name. Age. White Green Gray. 52 3421 1. Integrity or correctness of data ?.

santos
Download Presentation

CS4432: Database Systems II

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS4432: Database Systems II Database Consistency and Violations? recovery

  2. Transactions, etc. • Crash recovery Chapter 17 • Concurrency control Chapter 18 • Transaction processing Chapter 19 recovery

  3. Name Age White Green Gray 52 3421 1 Integrity or correctness of data ? • We would like data in our database to be “accurate” ( “correct” ) at all times. EMP • How DBMS decides if data is consistent? recovery

  4. Integrity or consistency constraints • Utilize predicates data must satisfy • Examples: - 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 recovery

  5. Definitions: • Consistent state: satisfies all constraints • Consistent DB: DB in consistent state recovery

  6. Such constraints may not capture “full correctness” Example 1 :Transaction constraints • When salary is updated, new salary > old salary • When account record is deleted, balance = 0 recovery

  7. Constraints (as we use here) may not capture “full correctness” Example 2 Database should reflect real world Reality DB recovery

  8. in any case, continue with constraints ... Observation: DB cannot be consistent always Example: Constraint : a1 + a2 +…. an = TOT Action: Deposit $100 in a2: a2 a2 + 100 TOT  TOT + 100 recovery

  9. Example: a1 + a2 +…. an = TOT (constraint) Deposit $100 in a2: a2 a2 + 100 TOT  TOT + 100 a2 TOT . . . . . . 50 150 150 . . . . . . 1000 1000 1100 recovery

  10. Transaction: a collection of actions that preserve consistency Consistent DB Consistent DB’ T recovery

  11. Big assumption: If T starts with consistent state AND T executes in isolation  T leaves consistent state recovery

  12. Correctness(informally) • If we stop running transaction(s), DB left consistent • Each transaction sees a consistent DB recovery

  13. How can constraints be violated? • Transaction bug • DBMS bug • Hardware failure e.g., disk crash alters balance of account • Data sharing e.g.: T1: give 10% raise to programmers T2: change programmers  systems analysts recovery

  14. Will not consider: • How to write correct transactions • How to write correct DBMS system • Constraint checking & repair recovery

  15. How can we prevent/fix violations? • Chapter 17: due to failures only • Chapter 18: due to data sharing only • Chapter 19: due to failures and sharing recovery

  16. Chapter 17: Recovery • First : Failure Model recovery

  17. Events Desired Undesired Expected Unexpected recovery

  18. Our failure model processor memory disk CPU D M recovery

  19. that’s it!! Undesired Unexpected: Everything else! Desired events: see product manuals…. Undesired expected events: System crash - memory lost - cpu halts, resets recovery

  20. Undesired Unexpected: Everything else! Examples: • Disk data is lost • Memory lost without CPU halt • CPU implodes wiping out universe…. • You name it … recovery

  21. x x Memory Disk Operations reStorage Hierarchy: • Input (x): block containing x  memory • Output (x): block containing x  disk • Read (x,t): do input(x) if necessary t  value of x in block • Write (x,t): do input(x) if necessary value of x in block  t recovery

  22. Key problem : Unfinished transaction Example: Constraint: A=B T1: A  A  2 B  B  2 recovery

  23. failure! 16 16 16 T1: Read (A,t); t  t2 Write (A,t); Read (B,t); t  t2 Write (B,t); Output-to-Disk (A); Output-to-Disk (B); A: 8 B: 8 A: 8 B: 8 memory disk recovery

  24. Need atomicity: • execute all actions of a transaction or none at all recovery

  25. One solution: undo logging (immediate modification) A la Hansel and Gretel recording their navigation through forest via bread crumbs … Must havedurableundo logging !!! recovery

  26. 16 16 16 16 Undo logging (Immediate modification) T1: Read (A,t); t  t2 A=B Write (A,t); Read (B,t); t  t2 Write (B,t); Output-to-disk (A); Output-to-disk (B); <T1, start> <T1, A, 8> A:8 B:8 A:8 B:8 <T1, B, 8> <T1, commit> disk memory log recovery

  27. 16 BAD STATE # 1 One “complication” : First disk, then log • Log is first written in memory • Not written to disk on every action memory DB Log A: 8 B: 8 A: 8 16 B: 8 16 Log: <T1,start> <T1, A, 8> <T1, B, 8> recovery

  28. 16 BAD STATE # 2 One “complication” : first log, then disk. • Log is first written in memory • Not written to disk on every action memory DB Log A: 8 B: 8 A: 8 16 B: 8 16 Log: <T1,start> <T1, A, 8> <T1, B, 8> <T1, commit> ... <T1, B, 8> <T1, commit> recovery

  29. Undo logging rules • For every action generate undo log record (containing old value) (2) Before x is modified on disk, log records pertaining to x must be on disk (write ahead logging) (3) Before commit is written to log on disk, all writes of transaction must be reflected on disk recovery

  30. Recovery rules: Undo logging (1) Let S = set of transactions with <Ti, start> in log, but no <Ti, commit> (or <Ti, abort>) record in log (2) For each <Ti, X, v> in log, in reverse order (latest  earliest) do: - write old value from log back to disk: - if Ti  S then - write (X, v) - output (X) (3) For each Ti  S do - write <Ti, abort> to log recovery

  31. What if failure during recovery? No problem!  Undo idempotent recovery

  32. To discuss next : • Redo logging • Undo/redo logging, why both? • Checkpoints recovery

  33. <T1, start> <T1, A, 16> <T1, B, 16> <T1, commit> output 16 16 16 Redo logging (deferred modification) T1: Read(A,t); t t2; write (A,t); Read(B,t); t t2; write (B,t); Output(A); Output(B) A: 8 B: 8 A: 8 B: 8 DB memory LOG recovery

  34. Redo logging rules (1) For every action, generate redo log record (containing new value) (2) Before X is modified on disk (DB), all log records for transaction that modified X (including commit) must be on disk (3) Flush log at commit recovery

  35. Recovery rules: Redo logging (1) Let S = set of transactions with <Ti, commit> in log (2) For each <Ti, X, v> in log, in forward order (earliest  latest) do: - if Ti  S then Write(X, v) Output(X) optional recovery

  36. Recovery is very, very SLOW ! Redo log: First T1 wrote A,B Last Record Committed a year ago Record (1 year ago) --> STILL, Need to redo after crash!! ... ... ... Crash recovery

  37. Solution: Checkpoint (simple version) Periodically: (1) Do not accept new transactions (2) Wait until all transactions finish (3) Flush all log records to disk (log) (4) Flush all buffers to disk (DB) (do not discard buffers) (5) Write “checkpoint” record on disk (log) (6) Resume transaction processing recovery

  38. <T1,A,16> <T2,B,17> <T1,commit> <T2,commit> Checkpoint <T3,C,21> Example: what to do at recovery? Redo log (disk): Crash ... ... ... ... ... ... recovery

  39. Key drawbacks: • Undo logging: Too many disk IOs • Redo logging: need to keep all modified blocks in memory until commit recovery

  40. Solution: undo/redo logging! Combine Undo/Redo Logging, namely: Update  <Ti, Xid, New X val, Old X val> page X recovery

  41. Rules • Page X can be flushed before or after Ti commit • Log record flushed before corresponding updated page (WAL) • Flush at commit (log only) recovery

  42. Non-quiescent checkpoint Start-ckpt active TR: Ti,T2,... end ckpt L O G for undo dirty buffer pool pages flushed ... ... ... ... recovery

  43. Exampleswhat to do at recovery time? no T1 commit L O G ... T1,- a ... Ckpt T1 ... Ckpt end ... T1- b  Undo T1 (undo a,b) recovery

  44. Example L O G ... T1 a ... ckpt-s T1 ... T1 b ... ckpt- end ... T1 c ... T1 cmt ...  Redo T1: (redo b,c) recovery

  45. Recovery process: • Backwards pass (end of log  latest checkpoint start) • construct set S of committed transactions • undo actions of transactions not in S • Undo pending transactions • follow undo chains for transactions in (checkpoint active list) - S • Forward pass (latest checkpoint start  end of log) • redo actions of S transactions backward pass start check- point forward pass recovery

  46. Summary • Consistency of data • One source of problems: failures - Logging - Redundancy • Another source of problems: Data Sharing..... next recovery

More Related