1 / 55

CS4432: Database Systems II

CS4432: Database Systems II. Concurrency Control. Chapter 18 Concurrency Control. T1 T2 … Tn. DB (consistency constraints). Example:. T1: Read(A) T2: Read(A) A  A+100 A  A  2 Write(A) Write(A) Read(B) Read(B) B  B+100 B  B  2 Write(B) Write(B)

clare
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 Concurrency Control concurrency control

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

  3. Example: T1: Read(A) T2: Read(A) A  A+100 A  A2 Write(A) Write(A) Read(B) Read(B) B  B+100 B  B2 Write(B) Write(B) Constraint: A=B concurrency control

  4. A schedule An ordering of operations inside one or more transactions over time Why interleave operations? concurrency control

  5. A B 25 25 125 125 250 250 250 250 Schedule A T1 T2 Read(A); A  A+100 Write(A); Read(B); B  B+100; Write(B); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); concurrency control

  6. A B 25 25 50 50 150 150 150 150 Schedule B T1 T2 Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(A); A  A+100 Write(A); Read(B); B  B+100; Write(B); concurrency control

  7. Serial Schedules ! • Any serial schedule is “good”. concurrency control

  8. Interleave Transactionsina Schedule? concurrency control

  9. A B 25 25 125 250 125 250 250 250 Schedule C 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

  10. A B 25 25 125 250 50 150 250 150 Schedule D T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(B); B  B+100; Write(B); concurrency control

  11. A B 25 25 125 125 25 125 125 125 Same as Schedule D but with new T2’ Schedule E T1 T2’ Read(A); A  A+100 Write(A); Read(A);A  A1; Write(A); Read(B);B  B1; Write(B); Read(B); B  B+100; Write(B); concurrency control

  12. What is a ‘good’ schedule? concurrency control

  13. We want schedules that are “good” regardless of : • initial state and • transaction semantics • Hence we consider only : • order of read/writes Example: Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B) concurrency control

  14. Remember Schedule C 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

  15. Example: Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B) Sc’=r1(A)w1(A) r1(B)w1(B)r2(A)w2(A)r2(B)w2(B) T1 T2 concurrency control

  16. Now Let’s Try that for Schedule D !!! T1 T2 Read(A); A  A+100 Write(A); Read(A);A  A2; Write(A); Read(B);B  B2; Write(B); Read(B); B  B+100; Write(B); concurrency control

  17. Now for Sd: Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) Sd=r1(A)w1(A) r1(B)w1(B)r2(A)w2(A)r2(B)w2(B) concurrency control

  18. Or, let’s try for Sd: Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) Sd=r2(A)w2(A)r2(B)w2(B) r1(A)w1(A)r1(B)w1(B) concurrency control

  19. In short, Schedule D cannot be “fixed” : Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) • as a matter of fact, there seems to • be no safe way to transform this Sd • into an equivalent serial schedule !? concurrency control

  20. We note about schedule D: • T2 T1 • T1 T2 T1 T2 Sd cannot be rearranged into a serial schedule Sd is not “equivalent” to any serial schedule Sd is “bad” concurrency control

  21. Returning to Sc Sc=r1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B) T1 T2 T1 T2  no cycles  Sc is “equivalent” to a serial schedule, I.e., in this case (T1,T2). concurrency control

  22. Concepts Transaction: sequence of ri(x), wi(x) actions Conflicting actions: read/write on same resource A: r1(A) w2(A) w1(A) w2(A) r1(A) w2(A) Schedule: represents chronological order in which actions are executed Serial schedule: no interleaving of trans/actions concurrency control

  23. Anomalies with Interleaving Reading Uncommitted Data (WR Conflicts, “dirty reads”):e.g. T1: A+100, B+100, T2: A*1.06, B*1.06 T1: R(A), W(A), R(B), W(B), Abort T2: R(A), W(A), C Unrepeatable Reads (RW Conflicts): E.g., T1: R(A), check if A >0, decrement, T2: R(A), decrement T1: R(A),R(A), W(A), C T2: R(A), W(A), C • Overwriting Uncommitted Data (WW Conflicts): T1: W(A), W(B), C T2: W(A), W(B), C

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

  25. Definition A schedule is conflict serializable if it is conflict equivalent to some serial schedule. concurrency control

  26. How determine this ? Answer: A Precedence Graph ! concurrency control

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

  28. 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? concurrency control

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

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

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

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

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

  34. How to enforce serializable schedules? concurrency control

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

More Related