DBMS Notes

Lock Based Concurrency Control Protocol

In this type of protocol, any transaction cannot read or write data until it acquires an appropriate lock on it. There are two types of lock-Based Protocols which are explained below

1. Shared Lock

  • It is also known as a Read-only lock. In a shared lock, if any transaction wants to perform Read operation on data then it must acquire the shared lock on data first.

  • If a transaction (say T1) holds an shared lock on Data( say A) and some other transaction (say T2) want to perform read operation on same data (A), then T2 can also acquire the shared lock without waiting the unlocking of T1. It is because Read-Read is not conflict.

2. Exclusive Lock

In an exclusive lock, if any transaction want to perform Read and Write both operation on same data then it must acquire the exclusive lock first.

  • In exclusive lock, multiple transactions do not perform the Write operations on same data simultaneously.
  •  if a transaction (say T1) holds an exclusive lock on Data( say A) and some other transaction (say T2) want to access the same data (A), then it T2 has to wait until T1 unlock the exclusive lock.

Note: Shared and exclusive lock on different data items can be applied any times without any problem

Compatibility Lock Table

Following compatibly table is used when multiple transaction wants to perform read or write operation on same data items.

Suppose T1 and T2 are parallel transaction and both wants to perform read and write operation on same data say “A”. Shared lock is denoted by “S” and Exclusive lock is denoted by “X”.

Case 01:  (Shared to shared):  If T1 has a Shared lock on data (A) then we allow to T2 to shared-lock on same data (A) without unlocking data of T1.

Explanation: When a transaction T1 holds a shared lock for read data and T2 wants the shared lock for read operation on same data “A” then shared lock is granted because Read-Read is not a conflict.

Case 02: Case (Shared to  Exclusive ) If T1 has an Shared- lock on data (A) then we cannot allow to T2 to exclusive-lock on same data (A) until unless T1 unlock the data (A)

Explanation: When a transaction T1 holds a shared lock for read data (“A”) and T2 wants the Exclusive lock for read and write operation on same data “A” then shared lock is not granted because Read-Write is a conflict.

Case 03:  Case (Exclusive to shared) If T1 has an exclusive lock on data (A) then we cannot allow to T2 to lock either shared or Exclusive on same data(A) until unless T1 unlock the data(A)

Explanation: When a transaction T1 holds a Exclusive lock for read and write data (“A”) and T2 wants the Exclusive lock for read and write operation on same data “A” then Exclusive lock is not granted because Read-Write and Write-Write is a conflict.

Case 04: (Exclusive to Exclusive ) If T1 has an exclusive lock on data (A) then we cannot allow to T2 to lock Exclusive on same data(A) until unless T1 unlock the data(A)

Explanation: When a transaction T1 holds a Exclusive lock for read and write data (“A”) and T2 wants the Exclusive lock for read and write operation on same data “A” then Exclusive lock is not granted because Read-Write and Write-Write is a conflict there.

Keep in Mind: all above conditions are required when multiple transaction are using the same data. If T1 has exclusive-lock on data A then T2 can exclusive lock on B without waiting the unlocking of T1 on data A concurrently.

Problems in Shared-Exclusive Locking 

When the Shared-Exclusive locking is given properly then there may still exist the following problem

Problem-1: Produced schedule through Shared-Exclusive locking is not a serial always.

Explanation: See the above example where read/write locking is given properly but still a loop present in the schedule of T1 and T2. We know if a loop is there then it may or may not be a serial

Problem-2: Produced schedule through Shared-Exclusive locking may be irrecoverable

Problem-3: Produced schedule through Shared-Exclusive locking may contains a deadlock problem

Problem-4: Produced schedule through Shared-Exclusive locking may contains still starvation


Explanation

  • T2 request for shared-lock on data “A” which is granted directly , Now let suppose T1 request for exclusive lock on data “A” which will not be granted until T2 unlock the data A.
  • Suppose as T2 was unlocking data A, T3 also acquire Shared-lock on same data A. It is possible shared lock and shared lock at a time on same data. So, T1 has to wait until T3 unlock the A.
  • Suppose as T3 was unlocking data A, T4 also get the Shared-lock on same data A. Now, T1 has to wait until T4 unlock the A.
  • So, Transaction T1 wait from time 2 to time 8 for getting exclusive lock on data “A”. Therefore, It is a starvation case.

Problem-5: Produced schedule through Shared-Exclusive locking may contains still cascading Rollback problem

Explanation: If the rollback of one transaction cause the rollback of other dependent transactions called cascading rollback.

To remove all said problems we use 2-PL. We will see 2-PL in next lecture.

Help Other’s By Sharing…

Contact Us

Burewala, Vehari, Punjab, Pakistan

cstaleem1@gmail.com

Website: CStaleem.com

Pin It on Pinterest