Title: CS4432: Database Systems II
1CS4432 Database Systems II
- Lecture 21
- Concurrency Control
Professor Elke A. Rundensteiner
2Chapter 9 Concurrency Control
DB (consistency constraints)
3Example
- T1 Read(A) T2 Read(A)
- A ? A100 A ? A?2
- Write(A) Write(A)
- Read(B) Read(B)
- B ? B100 B ? B?2
- Write(B) Write(B)
- Constraint AB
4A schedule
- An ordering of operations inside
- one or more transactions over time
Why interleave operations?
5Schedule A
- T1 T2
- Read(A) A ? A100
- Write(A)
- Read(B) B ? B100
- Write(B)
- Read(A)A ? A?2
- Write(A)
- Read(B)B ? B?2
- Write(B)
-
6Schedule B
- T1 T2
- Read(A)A ? A?2
- Write(A)
- Read(B)B ? B?2
- Write(B)
- Read(A) A ? A100
- Write(A)
- Read(B) B ? B100
- Write(B)
-
7Serial Schedules !
- Any serial schedule is good.
8Interleave Transactionsina Schedule?
9Schedule C
- T1 T2
- Read(A) A ? A100
- Write(A)
- Read(A)A ? A?2
- Write(A)
- Read(B) B ? B100
- Write(B)
- Read(B)B ? B?2
- Write(B)
-
10Schedule D
- T1 T2
- Read(A) A ? A100
- Write(A)
- Read(A)A ? A?2
- Write(A)
- Read(B)B ? B?2
- Write(B)
- Read(B) B ? B100
- Write(B)
-
11Schedule E
Same as Schedule D but with new T2
- T1 T2
- Read(A) A ? A100
- Write(A)
- Read(A)A ? A?1
- Write(A)
- Read(B)B ? B?1
- Write(B)
- Read(B) B ? B100
- Write(B)
-
12What is a good schedule?
13- We want
- schedules that are good regardless of
- initial state and
- transaction semantics
- Hence we consider
- Only order of read and writes
- Example
- Scr1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2(B)
14Example Scr1(A)w1(A)r2(A)w2(A)r1(B)w1(B)r2(B)w2
(B)
- Scr1(A)w1(A) r1(B)w1(B)r2(A)w2(A)r2(B)w2(B)
- T1 T2
15- Now for Sd
- Sdr1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B)
- Sdr1(A)w1(A) r1(B)w1(B)r2(A)w2(A)r2(B)w2(B)
16- Or, lets try for Sd
- Sdr1(A)w1(A)r2(A)w2(A) r2(B)w2(B)r1(B)w1(B)
- Sdr2(A)w2(A)r2(B)w2(B) r1(A)w1(A)r1(B)w1(B)
17- However, for Sd
- Sdr1(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?
-
18- We note, T2 ? T1
- Also, T1 ? T2
- T1 T2 Sd cannot be rearranged
- into a serial schedule
- Sd is not equivalent to
- any serial schedule
- Sd is bad
19Returning to Sc
- Scr1(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).
20Idea Swap non-conflicting operation pairs to see
if you can go to a serial schedule.