550 likes | 659 Vues
This chapter delves into concurrency control in database systems, exploring the importance of interleaving operations and adherence to consistency constraints for effective transaction management. Learn about serial schedules and conflict serializability while evaluating different schedules for optimality.
E N D
CS4432: Database Systems II Concurrency Control concurrency control
Chapter 18 Concurrency Control T1 T2 … Tn DB (consistency constraints) concurrency control
Example: T1: Read(A) T2: Read(A) A A+100 A A2 Write(A) Write(A) Read(B) Read(B) B B+100 B B2 Write(B) Write(B) Constraint: A=B concurrency control
A schedule An ordering of operations inside one or more transactions over time Why interleave operations? concurrency control
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 A2; Write(A); Read(B);B B2; Write(B); concurrency control
A B 25 25 50 50 150 150 150 150 Schedule B T1 T2 Read(A);A A2; Write(A); Read(B);B B2; Write(B); Read(A); A A+100 Write(A); Read(B); B B+100; Write(B); concurrency control
Serial Schedules ! • Any serial schedule is “good”. concurrency control
Interleave Transactionsina Schedule? concurrency control
A B 25 25 125 250 125 250 250 250 Schedule C T1 T2 Read(A); A A+100 Write(A); Read(A);A A2; Write(A); Read(B); B B+100; Write(B); Read(B);B B2; Write(B); concurrency control
A B 25 25 125 250 50 150 250 150 Schedule D T1 T2 Read(A); A A+100 Write(A); Read(A);A A2; Write(A); Read(B);B B2; Write(B); Read(B); B B+100; Write(B); concurrency control
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 A1; Write(A); Read(B);B B1; Write(B); Read(B); B B+100; Write(B); concurrency control
What is a ‘good’ schedule? concurrency control
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
Remember Schedule C T1 T2 Read(A); A A+100 Write(A); Read(A);A A2; Write(A); Read(B); B B+100; Write(B); Read(B);B B2; Write(B); concurrency control
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
Now Let’s Try that for Schedule D !!! T1 T2 Read(A); A A+100 Write(A); Read(A);A A2; Write(A); Read(B);B B2; Write(B); Read(B); B B+100; Write(B); concurrency control
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
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
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
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
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
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
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
Definition S1, S2 are conflict equivalentschedules if S1 can be transformed into S2 by a series of swaps on non-conflicting actions. concurrency control
Definition A schedule is conflict serializable if it is conflict equivalent to some serial schedule. concurrency control
How determine this ? Answer: A Precedence Graph ! concurrency control
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
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
Another Exercise: • What is P(S) forS = w1(A) r2(A) r3(A) w4(A) ? • Is S conflict-serializable? concurrency control
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
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
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
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
How to enforce serializable schedules? concurrency control
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
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
How to enforce serializable schedules? Option 3: prevent P(S) cycles from occurring T1 T2 ….. Tn Scheduler DB concurrency control
A locking protocol Two new actions: lock (exclusive): li (A) unlock: ui (A) T1 T2 lock table scheduler concurrency control
Rule #1: Well-formed transactions Ti: … li(A) … pi(A) … ui(A) ... concurrency control
Rule #2 Legal scheduler S = …….. li(A) ………... ui(A) ……... no lj(A) concurrency control
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
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
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
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
Rule #3 Two phase locking (2PL)for transactions Ti = ……. li(A) ………... ui(A) ……... no unlocks no locks concurrency control
# locks held by Ti Time Growing Shrinking Phase Phase concurrency control
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
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
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
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