1 / 51

Concurrency Control II

Concurrency Control II. More on Two Phase Locking Time Stamp Ordering Validation Scheme. Learning Objectives. Variations of two phase locking Dealing with Deadlock and Starvation Time Stamp Ordering Technique Validation. Schedules.

nalanie
Télécharger la présentation

Concurrency Control 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. Concurrency Control II More on Two Phase Locking Time Stamp Ordering Validation Scheme

  2. Learning Objectives • Variations of two phase locking • Dealing with Deadlock and Starvation • Time Stamp Ordering Technique • Validation Database Implementation – Concurrency Control Yan Huang

  3. Schedules • Interleaved (or non-interleaved) actions from several transactions • A schedule is good if it can be transformed into one of the serial schedules by switching two consecutive non-conflict actions • We’ve learned 2PL to achieve serializability • It is a pessimistic scheme Database Implementation – Concurrency Control Yan Huang

  4. Recall • 2PL (two phase locking) Locks of Ti growing shrinking time Each transaction has to follow, no locking anymore after the first unlocking Database Implementation – Concurrency Control Yan Huang

  5. Why 2PL serializability? • Acyclic precedence graph = serializability • Basic idea: • No cycle in precedence graph • Because • if there is a arc from Ti to Tj in precedence graph, the first unlocking of Ti precede the first unlocking of Tj (proof details in ccI notes) • If there is circle, you will have Ti < Ti Database Implementation – Concurrency Control Yan Huang

  6. Who will follow 2PL in practice? • Looks like it is DB application developers’ job. • But, they can not be trusted  and too much work • Checking conformity of every transaction is costly • In practice, CC subsystems of DBMS take over the responsibility Database Implementation – Concurrency Control Yan Huang

  7. Variations of 2PL • Basic 2PL • Conservative 2PL • Strict 2PL • Rigorous 2PL Database Implementation – Concurrency Control Yan Huang

  8. Basic 2PL • 2PL with binary locks • Covered in last class Database Implementation – Concurrency Control Yan Huang

  9. Shared locks So far: S = ...l1(A) r1(A) u1(A) … l2(A) r2(A) u2(A) … Do not conflict Instead: S=... ls1(A) r1(A) ls2(A) r2(A) …. us1(A) us2(A) Database Implementation – Concurrency Control Yan Huang

  10. Lock actions l-ti(A): lock A in t mode (t is S or X) u-ti(A): unlock t mode (t is S or X) Shorthand: ui(A): unlock whatever modes Ti has locked A Database Implementation – Concurrency Control Yan Huang

  11. Well formed transactions Ti =... l-S1(A) … r1(A) …u1 (A) … Ti =... l-X1(A) … w1(A) …u1 (A) … Database Implementation – Concurrency Control Yan Huang

  12. What about transactions that read and write same object? Option 1: Request exclusive lock Ti = ...l-X1(A) … r1(A) ... w1(A) ... u1(A) … Database Implementation – Concurrency Control Yan Huang

  13. What about transactions that read and • write same object? Option 2: Upgrade (E.g., need to read, but don’t know if will write…) Ti=... l-S1(A) … r1(A) ...l-X1(A) …w1(A) ...u1(A)… Think of - Get 2nd lock on A, or - Drop S, get X lock Database Implementation – Concurrency Control Yan Huang

  14. Compatibility matrix Comp S X S true false X false false Database Implementation – Concurrency Control Yan Huang

  15. Schedule T1 T2 l-s1(A);Read(A) A A+100;Write(A) l-x1(B); u1(A) l-s2(A);Read(A) A Ax2;Write(A); l-x2(B) Read(B);B B+100 Write(B); u1(B) l-x2(B); u2(A);Read(B) B Bx2;Write(B);u2(B); delayed Database Implementation – Concurrency Control Yan Huang

  16. Conservative 2PL • Lock all items it needs then transaction starts execution • If any locks can not be obtained, then do not lock anything • Difficult but deadlock free first action starts growing locks shrinking time Database Implementation – Concurrency Control Yan Huang

  17. Strict 2PL • T does not release any write locks until it commits or aborts • Good for recoverability • Since reads or writes on what T writes • Deadlock free? growing locks shrinking time First write unlock Database Implementation – Concurrency Control Yan Huang T commits or aborts

  18. Rigorous 2PL • T does not release any locks until it commits or aborts • Easy to implement • Deadlock free? T commits or aborts growing locks shrinking time Database Implementation – Concurrency Control Yan Huang

  19. 2PL • Does basic 2PL guarantee serializability? • Does conservative 2PL guarantee serializability? • Does strict 2PL guarantee serializability? • Does rigorous 2PL guarantee serializability? Database Implementation – Concurrency Control Yan Huang

  20. Compare variations of 2PL • Deadlock • Only conservative 2PLis deadlock free • Q: give a schedule of two transactions following 2PL but result in deadlock. Database Implementation – Concurrency Control Yan Huang

  21. Exercises: • S1: r1(y)r1(x)w1(x)w2(x)w2(y) • S2: r1(y)r3(x)w1(x)w3(x)w2(y)w2(x) • S3: r3(y)w1(x)w3(x)r1(z)w2(y)w2(x) • Assuming binary lock right before read or write; and rigorous 2PL (release all locks right after last operation), are S1, S2,and S3 possible? Database Implementation – Concurrency Control Yan Huang

  22. Deadlocks • Detection • Wait-for graph • Prevention • Resource ordering • Timeout • Wait-die • Wound-wait Database Implementation – Concurrency Control Yan Huang

  23. 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 Database Implementation – Concurrency Control Yan Huang

  24. 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 Database Implementation – Concurrency Control Yan Huang

  25. Timeout • If transaction waits more than L sec., roll it back! • Simple scheme • Hard to select L Database Implementation – Concurrency Control Yan Huang

  26. Wait-die • Transactions are given a timestamp when they arrive …. ts(Ti) • Ti can only wait for Tj if ts(Ti)< ts(Tj) ...else die Database Implementation – Concurrency Control Yan Huang

  27. wait? Example: T1 (ts =10) T2 (ts =20) T3 (ts =25) wait wait Very high level: only older ones have the privilege to wait, younger ones die if they attempt to wait for older ones Database Implementation – Concurrency Control Yan Huang

  28. Wound-wait • Transactions are 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 Database Implementation – Concurrency Control Yan Huang

  29. wait Example: T1 (ts =25) T2 (ts =20) T3 (ts =10) wait wait Very high level: younger ones wait; older ones kill (wound) younger ones who hold needed locks Database Implementation – Concurrency Control Yan Huang

  30. Who die? • Looks like it is always the younger ones • either die automatically • or killed • What is the reason? • Will the younger ones starve? • Suggestions? Database Implementation – Concurrency Control Yan Huang

  31. Timestamp Ordering • Key idea: • Transactions access variables according to an order decided by their time stamps when they enter the system • No cycles are possible in the precedence graph Database Implementation – Concurrency Control Yan Huang

  32. Timestamp • System time when transactions starts • An increasing unique number given to each stransaction • Denoted by ts(Ti) Database Implementation – Concurrency Control Yan Huang

  33. The way it works • Two time stamps associated with each variable x • RS(x): the largest time stamp of the transactions read it • WS(x): the largest time stamp of the transactions write it • Protocol: • ri(x) is allowed if ts(Ti) >= WS(x) • wi(x) is allowed if ts(Ti) >=WS(x) and ts(Ti) >=RS(x) • Disallowed ri(x) or wi(x) will kill Ti, Ti will restart Database Implementation – Concurrency Control Yan Huang

  34. x y z RS=-1 RS=-1 RS=-1 WS=-1 WS=-1 WS=-1 Example Assuming: ts(T1) = 100, ts(T2) = 200, ts(T3) = 300 T1 T2 T3 R(x); W(y); R (y); W(z); R(x); W(z); R(y); W(x); Database Implementation – Concurrency Control Yan Huang

  35. x y z RS=100 RS=-1 RS=-1 WS=-1 WS=-1 WS=-1 Example Assuming: ts(T1) = 100, ts(T2) = 200, ts(T3) = 300 T1 T2 T3 R(x); Database Implementation – Concurrency Control Yan Huang

  36. x y z RS=100 RS=-1 RS=-1 WS=-1 WS=100 WS=-1 Example Assuming: ts(T1) = 100, ts(T2) = 200, ts(T3) = 300 T1 T2 T3 R(x); W(y); Database Implementation – Concurrency Control Yan Huang

  37. x y z RS=100 RS=200 RS=-1 WS=-1 WS=100 WS=-1 Example Assuming: ts(T1) = 100, ts(T2) = 200, ts(T3) = 300 T1 T2 T3 R(x); W(y); R (y); Database Implementation – Concurrency Control Yan Huang

  38. x y z RS=100 RS=200 RS=-1 WS=-1 WS=100 WS=300 Example Assuming: ts(T1) = 100, ts(T2) = 200, ts(T3) = 300 T1 T2 T3 R(x); W(y); R (y); W(z); Database Implementation – Concurrency Control Yan Huang

  39. x y z RS=200 RS=200 RS=-1 WS=-1 WS=100 WS=300 Example Assuming: ts(T1) = 100, ts(T2) = 200, ts(T3) = 300 T1 T2 T3 R(x); W(y); R (y); W(z); R(x); Database Implementation – Concurrency Control Yan Huang

  40. x y z RS=200 RS=200 RS=-1 WS=-1 WS=100 WS=300 Example Assuming: ts(T1) = 100, ts(T2) = 200, ts(T3) = 300 T1 T2 T3 R(x); W(y); R (y); W(z); R(x); W(z); T1 is rolled back Database Implementation – Concurrency Control Yan Huang

  41. x y z RS=200 RS=200 RS=-1 WS=-1 WS=100 WS=300 Example Assuming: ts(T1) = 100, ts(T2) = 200, ts(T3) = 300 T1 T2 T3 R(x); W(y); R (y); W(z); R(x); W(z); What happen to RS and WS? T1 is rolled back Database Implementation – Concurrency Control Yan Huang

  42. Net result of TO scheduling • Conflict pairs of actions are taken in the order of their home transactions • But the basic TO does not guarantee recoverability • later Database Implementation – Concurrency Control Yan Huang

  43. Validation An optimistic scheme Transactions have 3 phases: (1) Read • all DB values read • writes to temporary storage • no locking (2) Validate • check if schedule so far is serializable (3) Write • if validate ok, write to DB Database Implementation – Concurrency Control Yan Huang

  44. Key idea • Make validation atomic • If T1, T2, T3, … is validation order, then resulting schedule will be conflict equivalent to Ss = T1 T2 T3... Database Implementation – Concurrency Control Yan Huang

  45. To implement validation, system keeps two sets: • FIN = transactions that have finished phase 3 (and are all done) • VAL = transactions that have successfully finished phase 2 (validation) Database Implementation – Concurrency Control Yan Huang

  46. =   Example of what validation must prevent: RS(T2)={B} RS(T3)={A,B} WS(T2)={B,D} WS(T3)={C} T2 validated T3 validated T2 start T3 start time Database Implementation – Concurrency Control Yan Huang

  47. =   allow Example of what validation must prevent: RS(T2)={B} RS(T3)={A,B} WS(T2)={B,D} WS(T3)={C} T2 validated T3 validated T2 start T3 start T3 start T2 finish phase 3 time Database Implementation – Concurrency Control Yan Huang

  48. BAD: w3(D) w2(D) Another thing validation must prevent: RS(T2)={A} RS(T3)={A,B} WS(T2)={D,E} WS(T3)={C,D} T2 validated T3 validated finish T2 time Database Implementation – Concurrency Control Yan Huang

  49. allow Another thing validation must prevent: RS(T2)={A} RS(T3)={A,B} WS(T2)={D,E} WS(T3)={C,D} T2 validated T3 validated finish T2 finish T2 time Database Implementation – Concurrency Control Yan Huang

  50. Validation Rule • When start validating T • Check RS(T)  WS(U) is empty for U that started but did not finish validation before T started • Check WS(T)  WS(U) is empty for any U that started but did not finish validation T start validation Database Implementation – Concurrency Control Yan Huang

More Related