Con Currency Control

CONCURRENCY CONTROL

Lecture Series By :

Er. Kanwalvir Singh Dhindsa Deptt. of CSE & IT www.dhindsa.info DBMS by Er. K.S.Dhindsa © 2006

What is Lock ?  A variable associated with a data item that describes the status of the item with respect to possible operations that can be applied to it  There is one Lock for each data item in the database  Means of synchrozisation of the access by concurrent transactions to the database items

Types of Locks  Binary Locks

 Share/Exclusive Locks (for Read/Write)

RULES OF BINARY LOCKS For binary locking system ,every transaction must obey the following rules: 1. A transaction T must issue the operation lock_item(A) before any read_item(A) or write_item(A) operations are performed in T 2. A transaction T must issue the operation unlock_item(A) after all read_item(A) and write_item(A) operation are completed in T 3. A transaction T will not issue a lock_item(A) operation if it already holds the lock on item A 4. A transaction T will not issue an unlock_item(A) operation unless it already holds the lock on item A 5. The lock manager module of the DBMS can enforce these rules. At most one transaction can hold the lock on a particular item. Thus no two transaction can access the same item concurrently

Share/Exclusive Locks  Allow several transactions to access the same item A if they all access A for reading purposes only  If T is to write an item A,it must have exclusive access to A 

Disadvantages of Binary Locks

At most one transaction can hold a lock on a given item

COMPATIBILITY OF LOCKS If a transaction T1 request a lock of mode A on item Q on which transactionT2 currently hold a lock of mode B If transaction T1 – can be granted lock, in spite of the presence of the mode B lock, then we say mode A is compatible with mode B

COMPATIBILTY GRAPH

S

X

S

True

False

X

False

true

Concurrency Control Algorithms



Two-Phase Locking Protocol



Graph-Based protocol (Non-Two-Phase Protocol)

DBMS by Er. K.S.Dhindsa © 2006

Two-Phase Locking Protocol When all the Locking operations precede the first unlock operation in the transaction GROWING PHASE : During which all locks are requested SHRINKING PHASE : During which all locks are released DBMS by Er. K.S.Dhindsa © 2006

Two-Phase Locking Protocol Transaction,T3 Lock-X(B); Read(B,b); b:=b-50; Write(B,b); Lock-X(A); Read(A,a); a:=a+50; write(A,a); unlock(B); Unlock(A);

Transaction,T4 Lock-S(A); read(A); lock-S(B); read(B); display(A+B); unlock(A); unlock(B); 1) Transaction T3 & T4 are two-Phase 2) Lock-point of the Transaction (Final-Lock)

DBMS by Er. K.S.Dhindsa © 2006

GRAPH-BASED PROTOCOL (Non-Two Phase )

A0 A2

A1

A4

A6

A5

A3

A9 A7

A8

T1 NEEDS EXCLUSIVE LOCK on T2 NEEDS EXCLUSIVE LOCK on

A1 0

A0,A1,A4,A9,A2 and A5 A1,A4,A10,A3 and A7 DBMS by Er. K.S.Dhindsa © 2006

GRAPH-BASED PROTOCOL (Non-Two Phase ) Transaction,T1 Lock(A0) Lock(A1) Lock(A4) Unlock(A1) Lock(A9) Unlock(A4) Unlock(A9) Lock(A2) Unlock(A2)

Transaction,T2

lock(A1) lock(A4) lock(A10)

DBMS by Er. K.S.Dhindsa © 2006

CONCURRENCY CONTROL

Lecture Series By :

Er. Kanwalvir Singh Dhindsa Deptt. of CSE & IT www.dhindsa.info DBMS by Er. K.S.Dhindsa © 2006