1 / 41

CS542

CS542. Concurrency Control: Theory and Protocol. Professor Elke A. Rundensteiner. For Schedule D: Sd=r 1 (A)w 1 (A)r 2 (A)w 2 (A) r 2 (B)w 2 (B)r 1 (B)w 1 (B). T 1  T 2 T 2  T 1. T 1 T 2 . D cannot be rearranged into serial schedule.

jensen
Download Presentation

CS542

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. CS542 Concurrency Control: Theory and Protocol Professor Elke A. Rundensteiner

  2. For Schedule D: Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) T1 T2 T2 T1 T1 T2 D cannot be rearranged into serial schedule

  3. Idea: Swap non-conflicting operation pairs to see if you can go to a serial schedule.

  4. Definition S1, S2 are conflict equivalentschedules if S1 can be transformed into S2 by a series of swaps on non-conflicting actions.

  5. Definition A schedule is conflict serializable if it is conflict equivalent to some (any) serial schedule.

  6. How determine this ? One Answer: A Precedence Graph !

  7. Precedence graph P(S) (S is schedule) Nodes: transactions in S Arcs: Ti  Tj whenever - pi(A), qj(A) are actions in S - pi(A) <S qj(A) - at least one of pi, qj is a write

  8. Exercise: • What is P(S) forS = w3(A) w2(C) r1(A) w1(B) r1(C) w2(A) r4(A) w4(D) • Is S conflict serializable?

  9. Another Exercise: • What is P(S) forS = w1(A) r2(A) r3(A) w4(A) ? • Is S conflict serializable?

  10. Proof: Assume P(S1)  P(S2)  Ti: Ti  Tj in S1 and not in S2  S1 = …pi(A)... qj(A)… pi, qj S2 = …qj(A)…pi(A)... conflict  S1, S2 not conflict equivalent Lemma S1, S2 conflict equivalent  P(S1)=P(S2)

  11. Note: P(S1)=P(S2)  S1, S2 conflict equivalent Counter example: S1=w1(A) r2(A) w2(B) r1(B) S2=r2(A) w1(A) r1(B) w2(B) P(S1)=P(S2)  S1, S2 conflict equivalent ??

  12. Theorem P(S1) acyclic  S1 conflict serializable () Assume S1 is conflict serializable  Ss: Ss, S1 conflict equivalent  P(Ss)=P(S1)  P(S1) acyclic since P(Ss) is acyclic

  13. Theorem P(S1) acyclic  S1 conflict serializable T1 T2 T3 T4 () Assume P(S1) is acyclic Transform S1 as follows: (1) Take T1 to be transaction with no incident arcs (2) Move all T1 actions to the front S1 = ……. qj(A)…….p1(A)….. (3) we now have S1 = < T1 actions ><... rest ...> (4) repeat above steps to serialize rest!

  14. How to enforce serializable schedules?

  15. How to enforce serializable schedules? Option 1 : try all possible swaps of non-conflicting operation pairs to determine if the schedule can be turned into a serial one, i.e., if the schedule is ‘good’.

  16. How to enforce serializable schedules? Option 2: a) run system, recording P(S); b) at end of day, check for P(S) cycles c) and declare if execution was good

  17. How to enforce serializable schedules? Option 3: prevent P(S) cycles from occurring T1 T2 ….. Tn Scheduler DB

  18. A locking protocol Two new actions: lock (exclusive): li (A) unlock: ui (A) T1 T2 lock table scheduler

  19. Rule #1: Well-formed transactions Ti: … li(A) … pi(A) … ui(A) ...

  20. Rule #2 Legal scheduler S = …….. li(A) ………... ui(A) ……... no lj(A)

  21. Exercise: • What schedules are legal?What transactions are well-formed? S1 = l1(A)l1(B)r1(A)w1(B)l2(B)u1(A)u1(B) r2(B)w2(B)u2(B)l3(B)r3(B)u3(B) S2 = l1(A)r1(A)w1(B)u1(A)u1(B) l2(B)r2(B)w2(B)l3(B)r3(B)u3(B) S3 = l1(A)r1(A)u1(A)l1(B)w1(B)u1(B) l2(B)r2(B)w2(B)u2(B)l3(B)r3(B)u3(B)

  22. Exercise: • What schedules are legal?What transactions are well-formed? S1 = l1(A)l1(B)r1(A)w1(B)l2(B)u1(A)u1(B) r2(B)w2(B)u2(B)l3(B)r3(B)u3(B) S2 = l1(A)r1(A)w1(B)u1(A)u1(B) l2(B)r2(B)w2(B)l3(B)r3(B)u3(B) S3 = l1(A)r1(A)u1(A)l1(B)w1(B)u1(B) l2(B)r2(B)w2(B)u2(B)l3(B)r3(B)u3(B)

  23. Schedule F : Adding locking ! How? T1 T2 l1(A);Read(A) A A+100;Write(A);u1(A) l2(A);Read(A) A Ax2;Write(A);u2(A) l2(B);Read(B) B Bx2;Write(B);u2(B) l1(B);Read(B) B B+100;Write(B);u1(B) Legal ? Well-formed ?

  24. A B 25 25 125 250 50 150 250 150 Schedule F : Adding locking ! How? T1 T2 l1(A);Read(A) A A+100;Write(A);u1(A) l2(A);Read(A) A Ax2;Write(A);u2(A) l2(B);Read(B) B Bx2;Write(B);u2(B) l1(B);Read(B) B B+100;Write(B);u1(B)

  25. Rule #3 Two phase locking (2PL)for transactions Ti = ……. li(A) ………... ui(A) ……... no unlocks no locks

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

  27. Schedule F : Can 2PL fix it ? T1 T2 l1(A);Read(A) A A+100;Write(A);u1(A) l2(A);Read(A) A Ax2;Write(A);u2(A) l2(B);Read(B) B Bx2;Write(B);u2(B) l1(B);Read(B) B B+100;Write(B);u1(B)

  28. Schedule G T1 T2 l1(A);Read(A) A A+100;Write(A) l1(B); u1(A) l2(A);Read(A) A Ax2;Write(A);

  29. Schedule G T1 T2 l1(A);Read(A) A A+100;Write(A) l1(B); u1(A) l2(A);Read(A) A Ax2;Write(A);l2(B) delayed

  30. Schedule G T1 T2 l1(A);Read(A) A A+100;Write(A) l1(B); u1(A) l2(A);Read(A) A Ax2;Write(A);l2(B) Read(B);B B+100 Write(B); u1(B) delayed

  31. Schedule G T1 T2 l1(A);Read(A) A A+100;Write(A) l1(B); u1(A) l2(A);Read(A) A Ax2;Write(A);l2(B) Read(B);B B+100 Write(B); u1(B) l2(B); u2(A);Read(B) B Bx2;Write(B);u2(B); delayed

  32. A B 25 25 125 250 125 250 250 250 We Got the GOOD Schedule !!!! T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B); B  B+100; Write(B); Read(B);B  B2; Write(B);

  33. Schedule H (T2 reversed) T1 T2 l1(A); Read(A) l2(B);Read(B) A A+100;Write(A) B Bx2;Write(B) . . . . . . What’s happening ?

  34. 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

  35. Deadlocked transactions can arise in ‘good’ schedules (generated due to 2PL locking). • We assume that when they are rolled back: • They have no effect • They do not appear in schedule E.g., Schedule H = This space intentionally left blank!

  36. Correctness of 2PC Protocol : Show that rules #1,2,3 • Conflict-serializable schedules

  37. Conflict rules for li(A), ui(A): • li(A), lj(A) conflict • li(A), uj(A) conflict

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

  39. 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)

  40. 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

  41. Beyond this simple 2PL protocol, it is all a matter of improving performance and allowing yet more concurrency…. • Shared locks • Multiple granularity • Inserts, deletes and phantoms • Other types of C.C. mechanisms

More Related