1 / 77

CS 245: Database System Principles Notes 10: More TP

CS 245: Database System Principles Notes 10: More TP. Hector Garcia-Molina. Sections to Skim:. Section 18.8 [18.8] Sections 19.2 19.4, 19.5, 19.6 [none, i.e., read all Ch 19] [In the Second Edition, skip all of Chapter 20, and Sections 21.5, 21.6, 21.7, 22.2 through 22.7].

dillan
Download Presentation

CS 245: Database System Principles Notes 10: More TP

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. CS 245: Database System PrinciplesNotes 10: More TP Hector Garcia-Molina Notes 10

  2. Sections to Skim: • Section 18.8 [18.8] • Sections 19.2 19.4, 19.5, 19.6[none, i.e., read all Ch 19] • [In the Second Edition, skip all of Chapter 20, and Sections 21.5, 21.6, 21.7, 22.2 through 22.7] Notes 10

  3. Chapter 19 [19] More on transaction processing Topics: • Cascading rollback, recoverable schedule • Deadlocks • Prevention • Detection • View serializability • Distributed transactions • Long transactions (nested, compensation) Notes 10

  4. Concurrency control & recovery … … Example: Tj Ti Wj(A) ri(A) Commit Ti Abort Tj … … … … Notes 10

  5. Concurrency control & recovery … … Example: Tj Ti Wj(A) ri(A) Commit Ti Abort Tj … … … …  Non-Persistent Commit (Bad!) Notes 10

  6. Concurrency control & recovery … … Example: Tj Ti Wj(A) ri(A) Commit Ti Abort Tj … … … … avoided by recoverable schedules  Non-Persistent Commit (Bad!) Notes 10

  7. Concurrency control & recovery … … Example: Tj Ti Wj(A) ri(A) wi(B) Abort Tj [Commit Ti] … … … … Notes 10

  8. Concurrency control & recovery … … Example: Tj Ti Wj(A) ri(A) wi(B) Abort Tj [Commit Ti] … … … …  Cascading rollback (Bad!) Notes 10

  9. Concurrency control & recovery … … Example: Tj Ti Wj(A) ri(A) wi(B) Abort Tj [Commit Ti] … … … … avoided by avoids-cascading- rollback (ACR) schedules  Cascading rollback (Bad!) Notes 10

  10. Schedule is conflict serializable • Tj Ti • But not recoverable Notes 10

  11. Need to make “final’ decision for each transaction: • commit decision - system guarantees transaction will or has completed, no matter what • abort decision - system guarantees transaction will or has been rolled back (has no effect) Notes 10

  12. To model this, two new actions: • Ci - transaction Ti commits • Ai - transaction Ti aborts Notes 10

  13. Back to example: Tj Ti Wj(A) ri(A) Cican wecommit here? ... ... ... ... Notes 10

  14. Definition Ti reads from Tj in S (Tj STi) if (1) wj(A)<S ri(A) (2) aj <S ri(A) (< : does not precede) (3) If wj(A)<Swk(A)<Sri(A) then ak <S ri(A) Notes 10

  15. Definition Schedule S is recoverable if whenever TjS Ti and j  i and Ci  S then Cj <S Ci Notes 10

  16. Note: in transactions, reads and writes precede commit or abort  If Ci  Ti, then ri(A) < Ci wi(A) < Ci  If Ai  Ti, then ri(A) < Ai wi(A) < Ai • Also, one of Ci, Ai per transaction Notes 10

  17. How to achieve recoverable schedules? Notes 10

  18.  With 2PL, hold write locks to commit (strict 2PL) Tj Ti Wj(A) Cj uj(A) ri(A) ... ... ... ... ... ... ... Notes 10

  19.  With validation, no change! Notes 10

  20. S is recoverable if each transaction commits only after all transactions from which it read have committed. Notes 10

  21. S is recoverable if each transaction commits only after all transactions from which it read have committed. • S avoids cascading rollback if each transaction may read only those values written by committed transactions. Notes 10

  22. S is recoverable if each transaction commits only after all transactions from which it read have committed. • S avoids cascading rollback if each transaction may read only those values written by committed transactions. • S is strict if each transaction may read and write only items previously written by committed transactions. Notes 10

  23. Relationship of RC, ACR, Strict RC Avoids cascading rollback ST SERIAL ACR Notes 10

  24. Examples • Recoverable: • w1(A) w1(B) w2(A) r2(B) c1 c2 • Avoids Cascading Rollback: • w1(A) w1(B) w2(A) c1 r2(B) c2 • Strict: • w1(A) w1(B) c1 w2(A) r2(B) c2 Assumes w2(A) is done without reading Notes 10

  25. Where are serializable schedules? RC Avoids cascading rollback ST SERIAL ACR Notes 10

  26. Deadlocks • Detection • Wait-for graph • Prevention • Resource ordering • Timeout • Wait-die • Wound-wait Notes 10

  27. Deadlock Detection • Build Wait-For graph • Use lock table structures • Build incrementally or periodically • When cycle found, rollback victim T5 T2 T1 T7 T4 T6 T3 Notes 10

  28. Resource Ordering • Order all elements A1, A2, …, An • A transaction T can lock Ai after Aj only if i > j Notes 10

  29. Resource Ordering • Order all elements A1, A2, …, An • A transaction T can lock Ai after Aj only if i > j Problem : Ordered lock requests not realistic in most cases Notes 10

  30. Timeout • If transaction waits more than L sec., roll it back! • Simple scheme • Hard to select L Notes 10

  31. Wait-die • Transactions given a timestamp when they arrive …. ts(Ti) • Ti can only wait for Tj if ts(Ti)< ts(Tj) ...else die Notes 10

  32. Example: T1 (ts =10) T2 (ts =20) T3 (ts =25) wait wait Notes 10

  33. wait? Example: T1 (ts =10) T2 (ts =20) T3 (ts =25) wait wait Notes 10

  34. wait? Example: T1 (ts =10) T2 (ts =20) T3 (ts =25) wait wait Notes 10

  35. Starvation with Wait-Die • When transaction dies, re-try laterwith what timestamp? • original timestamp • new timestamp (time of re-submit) Notes 10

  36. Starvation with Wait-Die • Resubmit with original timestamp • Guarantees no starvation • Transaction with oldest ts never dies • A transaction that dies will eventuallyhave oldest ts and will complete... Notes 10

  37. Second Example: requests A: wait for T2 or T3? T1 (ts =22) T2 (ts =20) T3 (ts =25) Note: ts between 20 and 25. wait(A) Notes 10

  38. Second Example (continued): One option: T1 waits just for T3, transaction holding lock. But when T2 gets lock, T1 will have to die! T1 (ts =22) T2 (ts =20) T3 (ts =25) wait(A) wait(A) Notes 10

  39. Second Example (continued): Another option: T1 only gets A lock after T2, T3 complete, so T1 waits for both T2, T3 T1 dies right away! T1 (ts =22) T2 (ts =20) T3 (ts =25) wait(A) wait(A) wait(A) Notes 10

  40. Second Example (continued): Yet another option: T1 preempts T2, so T1 only waits for T3; T2 then waits for T3 and T1...  T2 may starve? T1 (ts =22) T2 (ts =20) T3 (ts =25) wait(A) wait(A) wait(A) redundant arc Notes 10

  41. Wound-wait • Transactions given a timestamp when they arrive … ts(Ti) • Ti wounds Tj if ts(Ti)< ts(Tj) else Ti waits “Wound”: Tj rolls back and gives lock to Ti Notes 10

  42. Example: T1 (ts =25) T2 (ts =20) T3 (ts =10) wait wait Notes 10

  43. wait Example: T1 (ts =25) T2 (ts =20) T3 (ts =10) wait wait Notes 10

  44. Starvation with Wound-Wait • When transaction dies, re-try laterwith what timestamp? • original timestamp • new timestamp (time of re-submit) Notes 10

  45. Second Example: requests A: wait for T2 or T3? T1 (ts =15) T2 (ts =20) T3 (ts =10) Note: ts between 10 and 20. wait(A) Notes 10

  46. Second Example (continued): One option: T1 waits just for T3, transaction holding lock. But when T2 gets lock, T1 waits for T2 and wounds T2. T1 (ts =15) T2 (ts =20) T3 (ts =10) wait(A) wait(A) Notes 10

  47. Second Example (continued): Another option: T1 only gets A lock after T2, T3 complete, so T1 waits for both T2, T3 T2 wounded right away! T1 (ts =15) T2 (ts =20) T3 (ts =10) wait(A) wait(A) wait(A) Notes 10

  48. Second Example (continued): Yet another option: T1 preempts T2, so T1 only waits for T3; T2 then waits for T3 and T1...  T2 is spared! T1 (ts =15) T2 (ts =20) T3 (ts =10) wait(A) wait(A) wait(A) Notes 10

  49. User/Program commands Lots of variations, but in general • Begin_work • Commit_work • Abort_work Notes 10

  50. Nested transactions User program: Begin_work; If results_ok, then commit work else abort_work ... ... ... Notes 10

More Related