1 / 33

CS4432: Database Systems II

CS4432: Database Systems II. Lecture #23 Concurrency Control. Professor Elke A. Rundensteiner. Chapter 9 Concurrency Control. T1 T2 … Tn. DB (consistency constraints). Concepts. Transaction : sequence of r i (x), w i (x) actions

elizabeth-garner
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 Concurrency Control Professor Elke A. Rundensteiner concurrency control

  2. Chapter 9 Concurrency Control T1 T2 … Tn DB (consistency constraints) concurrency control

  3. Concepts Transaction: sequence of ri(x), wi(x) actions Conflicting actions: read/write on same resource A. Schedule: represents chronological order in which actions are executed Serial schedule: no interleaving of trans/actions S1, S2 are conflict equivalentschedules if S1 transform into S2 by swaps on non-conflicting actions. A schedule is conflict serializable if it is conflict equivalent to some serial schedule. concurrency control

  4. For a given schedule: Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) T1 T2 T2 T1 Record dependencies in Precedence Graph: T1 T2 If graph has cycles, Then schedule is not conflict serializable. concurrency control

  5. Theorem P(S1) acyclic  S1 conflict serializable concurrency control

  6. How to enforce serializable schedules? concurrency control

  7. 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’. concurrency control

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

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

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

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

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

  13. 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) concurrency control

  14. 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) concurrency control

  15. Schedule F : Adding locking ??? 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) concurrency control

  16. Schedule F A B T1 T2 25 25 l1(A);Read(A) A A+100;Write(A);u1(A) 125 l2(A);Read(A) A Ax2;Write(A);u2(A) 250 l2(B);Read(B) B Bx2;Write(B);u2(B) 50 l1(B);Read(B) B B+100;Write(B);u1(B) 150 250 150 concurrency control

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

  18. # locks held by Ti Time Growing Shrinking Phase Phase concurrency control

  19. Schedule F : Does it follow 2PL ? 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) concurrency control

  20. 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); concurrency control

  21. 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 concurrency control

  22. 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 concurrency control

  23. 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 concurrency control

  24. 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); concurrency control

  25. Schedule H (T2 reversed) T1 T2 l1(A); Read(A) l2(B);Read(B) A A+100;Write(A) B Bx2;Write(B) … … concurrency control

  26. 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 concurrency control

  27. 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! concurrency control

  28. We need to show 2PL Protocol “works”: Show that rules #1,2,3  conflict- serializable schedules concurrency control

  29. Conflict rules for li(A), ui(A): • li(A), lj(A) conflict • li(A), uj(A) conflict concurrency control

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

  31. By rule 3: SH(Ti) SH(Tj) So, SH(Ti) <S SH(Tj) 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) concurrency control

  32. 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 concurrency control

  33. 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 concurrency control

More Related