1 / 23

Understanding Concurrency Control in Database Systems

This lecture on Concurrency Control, presented by Professor Elke A. Rundensteiner, delves into vital concepts such as transactions and their actions, the nature of conflicting actions, and schedule representations in database systems. It distinguishes between serial schedules and non-interleaved transactions by exploring scenarios of conflicting actions. Through practical examples, it demonstrates determining conflict serializability using precedence graphs and highlights the significance of transforming schedules into conflict-equivalent structures. These foundational concepts are critical for ensuring data integrity in concurrent transaction processing.

nayda-fisher
Télécharger la présentation

Understanding Concurrency Control in Database Systems

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 #21 Concurrency Control : Theory Professor Elke A. Rundensteiner concurrency control

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

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

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

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

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

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

  8. 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) T2 T1 concurrency control

  9. 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 save way to transform this Sd • into an equivalent serial schedule !? concurrency control

  10. 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 Sd cannot be rearranged into serial schedule concurrency control

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

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

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

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

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

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

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

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

  19. Note: P(S1)=P(S2)  S1, S2 conflict equivalent? concurrency control

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

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

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

  23. Theorem holds: P(S1) acyclic  S1 conflict serializable concurrency control

More Related