Lock Based Protocol and 2PL in DBMS

 Concurrency Control techniques in DBMS

Multiple transactions executing at the same time on the same data, it may affect the result of the transaction. 

In order to maintain the concurrent access of transactions, different protocols are introduced.

• Lock Based Protocol

• Time-Stamp Based Protocol

• Validation Based Protocol

 Lock Based Protocol

Lock means access.

These locks are two types 1. Binary locks and 2. shared / exclusive locks.

 

In binary lock use only two states: - 1. Locked 2.  Unlocked.

For this, it locked for retrieve or insert or update or delete the data or unlocked for not using the data.

 

In shared / exclusive lock technique:-

Exclusively locked (x) can insert / update /delete, no other transaction can read or write the data. Use by only one transaction at a time. used while modifying the data in DB.

Shared lock:(s) - data can be read-only but it cannot be changed. It can be used for more than one transaction at a time.

 

Lock based protocols are of 4 types :

 

There are four types of lock protocols available:

 

1. Simplistic lock protocol

all the transactions to get the lock on the data before insert or delete or update on it. It will unlock the data item after completing the transaction.

 

2. Pre-claiming Lock Protocol

• Pre-claiming Lock Protocols evaluate the transaction to list all the data items on which they need locks.
• Before initiating execution of the transaction, it requests DBMS for all the lock on all those data items.
• If all the locks are granted then this protocol allows the transaction to begin. When the transaction is completed then it releases all the locks.
• If all the locks are not granted then this protocol allows the transaction to rolls back and waits until all the locks are granted.

Two-phase locking (2PL)

• Divides the execution into two phases.
• In the first phase, the transaction acquires all the locks.
• The second phase is started the transaction releases locks cannot demand any new locks.
  
  

There are two phases of 2PL:

 

Growing phase (first phase): In the growing phase, a new lock on the data item may be acquired by the transaction, but none can be released.

 

Shrinking phase (second phase): In the shrinking phase, existing lock held by the transaction may be released, but no new locks can be acquired.

 

Example:

The following way shows how unlocking and locking work with 2-PL.

Transaction T1:

• Growing phase: from step 0-2
• Shrinking phase: from step 4-6
• Lock point: at 3

Transaction T2:

• Growing phase: from step 1-5
• Shrinking phase: from step 7-8
• Lock point: at 6

 

4. Strict Two-phase locking (Strict-2PL)

• The first phase of Strict-2PL is similar to 2PL and after acquiring all the locks, the transaction continues to execute normally.
• The only difference between 2PL and strict 2PL is that Strict-2PL does not release a lock after using it.
• Strict-2PL waits until the whole transaction to commit, and then it releases all the locks at a time.
• Strict-2PL the protocol does not have the shrinking phase of lock release.

It does not have cascading abort as 2PL does.

 

Drawbacks of Lock based Protocol

• Deadlock: It is a situation where two or more transactions are waiting for each other to release the lock, which will end in waiting for each other forever. This will never end and the system will hang forever. This is due to poor locking management.

• Starvation: This similar to deadlock. Here one transaction would be waiting for exclusive lock on particular data, while shared locks are repeatedly requested on the same data by other set of transactions. Then shared lock will be assigned to those transactions and the first transaction will be kept waiting for its exclusive lock. it will be repeatedly aborted to avoid deadlock. This type of situation is called starvation. ===========================================