1 / 24

Understanding Concurrency Control in Database Systems: Concepts and Schedules

This lecture covers essential concepts of concurrency control in database systems, including transaction actions, conflicting actions, and schedules. We explore serial schedules and conflict serialization, illustrating examples with various transaction sequences. Detailed explanations of precedence graphs and conflict equivalences help determine if a schedule can be transformed into a serial one. Exercises challenge learners to analyze schedules for serializability, reinforcing knowledge. Join us as we delve into the intricacies of maintaining database consistency through effective concurrency control mechanisms.

carina
Télécharger la présentation

Understanding Concurrency Control in Database Systems: Concepts and Schedules

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 #22 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: 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 actions or transactions concurrency control

  4. A ReCap • Examples of Main Concepts are given next. concurrency control

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

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

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

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

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

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

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

  12. For Schedule D: Sd=r1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B) • there seems to be no save way to transform this S-D into an equivalent serial schedule? concurrency control

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

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

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

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

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

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

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

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

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

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

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

  24. How to enforce serializable schedules? Next Time … concurrency control

More Related