Intro to DBMS

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 a Read operation on data, then it must acquire the shared lock on data first.

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

2. Exclusive Lock

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

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

Note: Shared and exclusive locks on different data items can be applied at any time without any problem

Compatibility Lock Table

The following compatible table is used when multiple transactions want to perform read or write operations on the same data items.

Suppose T1 and T2 are parallel transactions, and both want to perform read and write operations on the same data, say “A”. A shared lock is denoted by “S,” and an Exclusive lock is denoted by “X”.

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

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

Case 02: Case (Shared to  Exclusive ) If T1 has a Shared- lock on data (A) then we cannot allow to T2 to exclusive-lock on the same data (A) until T1 unlocks 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 the same data “A”, then the 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 T2 to lock either shared or Exclusive on the same data(A) until T1 unlocks the data(A)

Explanation: When a transaction T1 holds an Exclusive lock for read and write data (“A”), and T2 wants the Exclusive lock for read and write operation on the same data “A” then the 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 T2 to lock Exclusive on the same data(A) until T1 unlocks the data(A)

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

Keep in Mind that all the above conditions are required when multiple transactions are using the same data. If T1 has an exclusive-lock on data A then T2 can exclusive lock on B without waiting for 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: The produced schedule through shared-exclusive locking is not always a serial.

Explanation: See the above example where read/write locking is given properly, but still, a loop is 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: The produced schedule through Shared-Exclusive locking may contain a deadlock problem

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


Explanation

  • T2 requests for a shared lock on data “A,” which is granted directly. Now, let’s suppose T1 requests an exclusive lock on data “A,” which will not be granted until T2 unlocks data A.
  • Suppose T2 was unlocking data A, and T3 also acquired Shared-lock on the same data A. It is possible to share lock and shared lock at a time on the same data. So, T1 has to wait until T3 unlocks the A.
  • Suppose T3 was unlocking data A, and T4 also gets the Shared-lock on the same data A. Now, T1 has to wait until T4 unlocks the A.
  • So, Transaction T1 waits from time 2 to time 8 to get an exclusive lock on data “A”. Therefore, It is a starvation case.

Problem-5: Produced schedule through Shared-Exclusive locking may cause a cascading Rollback problem

Explanation: If the rollback of one transaction causes the rollback of other dependent transactions, it is called cascading rollback.

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