CS4432: Database Systems II

1
CS4432 Database Systems II

• Lecture 21
• Concurrency Control

Professor Elke A. Rundensteiner
2
Chapter 9 Concurrency Control

• T1 T2 Tn

DB (consistency constraints)
3
Example

• 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

4
A schedule

• An ordering of operations inside
• one or more transactions over time

Why interleave operations?
5
Schedule 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)

6
Schedule 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)

7
Serial Schedules !

• Any serial schedule is good.

8
Interleave Transactionsina Schedule?
9
Schedule 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)

10
Schedule 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)

11
Schedule 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)

12
What 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)

14
Example 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

19
Returning 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).
20
Idea Swap non-conflicting operation pairs to see
if you can go to a serial schedule.