550 likes | 651 Views
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)
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