Concurrency Control I

1
Concurrency Control I
2
Concurrency Control

• T1 T2 Tn

DB (consistency constraints)
3
Enforce Conflict Serializable Schedules

• Prevent cycles in precedence graph from occurring
  
• T1 T2
  .. Tn

Scheduler
DB
4
A locking protocol

• For transaction i
• Use li to lock an item
• Use ui to unlock the lock enforced by transaction
  i

T1 T2
lock table
scheduler
5
Well behaved transactions

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

6
Example of a transaction performing locking

• T1 l1(A)
• read (A)
• u1(A)
• l1(B)
• read (B)
• u1(B)
• display(AB)
• Sufficient to guarantee serializability ?

7
Example

• T2 Read(A) T3 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

8
Schedule A
A B
T2 T3
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
9
Two-Phase Locking Protocol

• Phase 1 Growing Phase
• transaction may obtain locks
• transaction may not release locks
• Phase 2 Shrinking Phase
• transaction may release locks
• transaction may not obtain locks

10

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

no unlocks no locks
11

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

12

• What happens to a transaction which tries to lock
  an item but failed?

13
Schedule B

• T2 T3
• 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
14

• 2PL ? conflict-serializable schedules?

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

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

Proof Ti ? Tj means that S pi(A) ui(A)
lj(A) ... qj(A)
16

• Then
• (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

17
Deadlock

• To handle a deadlock one of T4 or T5 must be
  rolled back and its locks released.

T4 T5 l3(B) read(B) write(B)
l4(A) read(A) l4(B) l3(A)
18
Starvation

• A transaction does not get its turn for a long
  time
• Example
• A transaction may be waiting for a lock on an
  item, while a sequence of other transactions
  request and are granted an lock on the same item.
  
• The same transaction is repeatedly rolled back
  due to deadlocks.
• Concurrency control manager can be designed to
  prevent starvation.

19
2PL and Deadlock

• Are schedules from 2PL transactions deadlock free?

20
2PL and Possible Schedules

• Does 2PL allow all possible conflict serializable
  schedules?

21

• 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