1 / 33

CS4432: Database Systems II

CS4432: Database Systems II. Lecture #23 Transaction Management. Professor Elke A. Rundensteiner. 2 Phase Locking Protocol. # locks held by Ti Time Growing Shrinking Phase Phase. Now :. Show that rules #1,2,3  conflict- serializable

kelsie-guthrie
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 Lecture #23 Transaction Management Professor Elke A. Rundensteiner transaction management

  2. 2 Phase Locking Protocol # locks held by Ti Time Growing Shrinking Phase Phase transaction management

  3. Now : Show that rules #1,2,3  conflict- serializable schedules transaction management

  4. Conflict rules for li(A), ui(A): • li(A), lj(A) conflict • li(A), uj(A) conflict transaction management

  5. Theorem Rules #1,2,3  conflict (2PL) serializable schedule To help in proof: Definition Shrink(Ti) = SH(Ti) = first unlock action of Ti transaction management

  6. Proof of lemma: Ti  Tj means that S = … pi(A) … qj(A) …; p,q conflict By rules 1,2: S = … pi(A) … ui(A) … lj(A) ... qj(A) … Lemma Ti  Tj in S  SH(Ti) <S SH(Tj) SH(Ti)SH(Tj) By rule 3: So, SH(Ti) <S SH(Tj) transaction management

  7. Theorem Rules #1,2,3  conflict (2PL) serializable schedule Proof: (1) Assume P(S) has cycle T1 T2 …. Tn T1 (2) By lemma: SH(T1) <SH(T2) < ... <SH(T1) (3) Impossible, so P(S) acyclic (4)  S is conflict serializable transaction management

  8. Beyond this simple 2PL protocol, it is all a matter of improving performance and allowing more concurrency…. • Shared locks • Multiple granularity • Other types of C.C. mechanisms transaction management

  9. Chapter 10 More on transaction processing Topics: • Cascading rollback • Recoverable schedule • Deadlocks • Prevention • Detection transaction management

  10. Schedule H (T2 reversed) T1 T2 l1(A); Read(A) l2(B);Read(B) A A+100;Write(A) B Bx2;Write(B) l1(B) l2(A) delayed delayed transaction management

  11. Assume deadlocked transactions are rolled back • They have no effect • They do not appear in schedule E.g., Schedule H = This space intentionally left blank! transaction management

  12. Concurrency control & recovery Example: Ti To Wi(A) rj(A) Commit To Abort Ti … … … … … …  Cascading rollback (Bad!) transaction management

  13. Schedule is conflict serializable • Ti To • But not recoverable transaction management

  14. 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) transaction management

  15. To model this, two new actions: • Ci - transaction Ti commits • Ai - transaction Ti aborts transaction management

  16. Back to example: Ti Tj Wi(A) rj(A) Cjcan wecommit here? ... ... ... ... transaction management

  17. Definition Ti read from Tj in S (Tj STj) 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) transaction management

  18. Definition Schedule S is recoverable if whenever TjS Ti and j  i and Ci  S then Cj <S Ci transaction management

  19. 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 transaction management

  20. How to achieve recoverable schedules? transaction management

  21.  With 2PL, hold write locks to commit (strict 2PL) Ti Tj Wi(A) Ci ui(A) rj(A) ... ... ... ... ... ... ... transaction management

  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. transaction management

  23. S is strict if each transaction may read and write only items previously written by committed transactions. RC Avoids cascading rollback ST SERIAL ACA transaction management

  24. transaction management

  25. Schedule with Deadlock T1 T2 l1(A); Read(A) l2(B);Read(B) A A+100;Write(A) B Bx2;Write(B) l1(B) l2(A) delayed delayed transaction management

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

  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 transaction management

  28. 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 transaction management

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

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

  31. wait? Example: T1 (ts =10) T2 (ts =20) T3 (ts =25) wait wait transaction management

  32. 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 transaction management

  33. Summary • Cascading rollback Recoverable schedule • Deadlock • Prevention • Detection transaction management

More Related