Shivnath Babu

1
CS216 Data-Intensive Computing Systems
Concurrency Control (II)

• Shivnath Babu

2
How to enforce serializable schedules?

• Option 1 run system, recording P(S) at end
  of day, check for P(S) cycles and declare if
  execution was good

3
How to enforce serializable schedules?

• Option 2 prevent P(S) cycles from occurring
  
• T1 T2 .. Tn

Scheduler
DB
4
A locking protocol

• Two new actions
• lock (exclusive) li (A)
• unlock ui (A)

T1 T2
lock table
scheduler
5
Rule 1 Well-formed transactions

• Ti li(A) pi(A) ui(A) ...

6
Rule 2 Legal scheduler

• S .. li(A) ... ui(A) ...

no lj(A)
7
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)

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

9
Schedule F
T1 T2 l1(A)Read(A) A
A100Write(A)u1(A) l2(A)Read(A) A
Ax2Write(A)u2(A) l2(B)Read(B) B
Bx2Write(B)u2(B) l1(B)Read(B) B
B100Write(B)u1(B)
10
Schedule F
A B
T1 T2 25 25 l1(A)Read(A) A
A100Write(A)u1(A) 125 l2(A)Read
(A) A Ax2Write(A)u2(A)
250 l2(B)Read(B) B
Bx2Write(B)u2(B) 50 l1(B)Read(B) B
B100Write(B)u1(B) 150 250
150
11
Rule 3 Two phase locking (2PL) for
transactions

• Ti . li(A) ... ui(A) ...

no unlocks no locks
12

• locks
• held by
• Ti
• Time
• Growing Shrinking
• Phase Phase

13
Schedule G

• T1 T2
• l1(A)Read(A)
• A A100Write(A)
• l1(B) u1(A)
• l2(A)Read(A)
• A Ax2Write(A)l2(B)

delayed
14
Schedule G
T1 T2 l1(A)Read(A) A A100Write(A) l1(B)
u1(A) l2(A)Read(A)
A Ax2Write(A)l2(B) Read(B)B
B100 Write(B) u1(B)
delayed
15
Schedule G
T1 T2 l1(A)Read(A) A A100Write(A) l1(B)
u1(A) l2(A)Read(A)
A Ax2Write(A)l2(B) Read(B)B
B100 Write(B) u1(B) l2(B)
u2(A)Read(B) B Bx2Write(B)u2(B)
delayed
16
Schedule H (T2 reversed)

• T1 T2
• l1(A) Read(A) l2(B)Read(B)
• A A100Write(A) B Bx2Write(B)
• l1(B) l2(A)

delayed
delayed
17

• Assume deadlocked transactions are rolled back
• They have no effect
• They do not appear in schedule
• E.g., Schedule H
• This space intentionally
• left blank!

18
Next step

• Show that rules 1,2,3 ? conflict-
• serializable
• schedules

19

• Conflict rules for li(A), ui(A)
• li(A), lj(A) conflict
• li(A), uj(A) conflict
• Note no conflict lt ui(A), uj(A)gt, lt li(A),
  rj(A)gt,...

20

• Theorem Rules 1,2,3 ? conflict
• (2PL) serializable
• schedule

To help in proof Definition Shrink(Ti)
SH(Ti) first unlock action of Ti
21

• Lemma
• Ti ? Tj in S ? SH(Ti) ltS SH(Tj)

Proof of lemma Ti ? Tj means that S pi(A)
qj(A) p,q conflict By rules 1,2 S
pi(A) ui(A) lj(A) ... qj(A)
22
Theorem Rules 1,2,3 ? conflict (2PL)
serializable schedule

• Proof
• (1) Assume P(S) has cycle
• T1 ? T2 ?. Tn ? T1
• (2) By lemma SH(T1) lt SH(T2) lt ... lt SH(T1)
• (3) Impossible, so P(S) acyclic
• (4) ? S is conflict serializable

23

• Beyond this simple 2PL protocol, it is all a
  matter of improving performance and allowing more
  concurrency.
• Shared locks
• Multiple granularity
• Inserts, deletes, and phantoms
• Other types of C.C. mechanisms