2 phase locking protocol DBMS

ppt on 2 phase locking protocol of Database Management systems useful for GTU students

1. Database management system: TWO phase locking protocol 170410107027 Dhananjaysinh Jhala SY CE-I BATCH B

2. INDEX Locks Locking protocols 2 phase locking protocol Phases Merits demerits

3. LOCKS To ensure serializability, it is required that data items should be accessed in mutual exclusive manner, i.e. if one transaction is accessing a data item, no other transaction can modify that data item. To implement this requirement locks are used. A transaction is allowed to access a data item only if it is currently holding a lock on that item. There are 2 modes in which a data item may be locked: Shared mode: if a transaction T 1 has obtained a shared mode lock on item Q, then T 1 can read, but cannot write Q. it is denoted by S. Exclusive: if a transaction T 1 has obtained a exclusive mode lock on item Q, then T 1 can read & also write Q. it is denoted by X.

4. Locking protocols When a transaction requests a lock on a data item in a particular mode and no other transaction has a lock on the same data item, the lock can be granted. Supposing a transaction T 1 requests a lock on data item Q in a particular mode M, the concurrency control manager grants the lock provided that There is no other transaction holding a lock on Q in a mode that conflicts with M There is no other transaction that is waiting for a lock on Q, and that made its lock request before T 1 .

5. 2 phase locking protocol This protocol requires that each transaction issue lock and unlock requests in two phases: Growing phase: in this phase, a transaction may obtain locks, but may not release any lock. Shrinking phase: in this phase, a transaction may release locks, but may not obtain any new locks. Initially, a transaction is in the growing phase. The transaction acquires locks as needed. Once a transaction releases a lock, it enters in the shrinking phase and it cannot issue more lock requests.

6. 2 phase locking protocol E.g. Transaction given below is a two phase transaction. T 3 : lock X(B); read(B); B:= B-50; write(B); lock X(A); read(A); A:=A+50; write(A); unlock(B); unlock(A);

7. MERITS OF 2 phase locking protocol The two phase locking protocol ensure conflict serializability. Consider any transaction, the point in the schedule where the transaction has obtained it's final lock is called the lock point of the transaction. Now, transaction can be ordered according to their lock points. This ordering is a serializability ordering for the transactions.

8. DEMERITS OF 2 phase locking protocol The two phase locking protocol Does not ensure freedom from deadlock: Consider the schedule shown in figure Transaction T3 and T4 are two phases but they are deadlocked.

9. Cascading rollback may occur under two phase locking: Consider the schedule shown in figure

10. As shown in the above figure transactions T5,T6 and T7 are two phase, but failure of T5 after the read(A) instruction of T7 leads to cascading rollback of T6 and T7. Cascading rollback can be avoided by modification of two phase locking: 1. Strict two phase locking protocol: This protocol requires that locking should be two phase and all executive mode locks taken by a transaction should be held until the transaction commits. This requirement prevents any transaction from reading the data written by any uncommitted transaction under exclusive mode until the transaction commits.

11. 2. The rigorous two phase locking protocol: This protocol requires that all locks be held until the transaction commits.