1 / 22

CS4432: Database Systems II

CS4432: Database Systems II. Lecture #20 Concurrency Control – Chapter 18. Professor Elke A. Rundensteiner. Chapter 9 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)

preston-raymond
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 #20 Concurrency Control – Chapter 18 Professor Elke A. Rundensteiner concurrency control

  2. Chapter 9 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 What is correct outcome ? 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. Yet 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) Observation : there seems to be no save way to transform this schedule 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. Idea: Swap non-conflicting operation pairs to see if you can go to a serial schedule. concurrency control

More Related