Lock Based Concurrency Control Protocol in DBMS

First things first, I hope you are familiar to some of the concepts relating to Transactions.

Now, we all know the four properties a transaction must follow. Yes, you got that right, I mean the ACID properties. Concurrency control techniques are used to ensure that the Isolation (or non-interference) property of concurrently executing transactions is maintained.

A trivial question I would like to pose in front of you, (I know you must know this but still) why do you think that we should have interleaving execution of transactions if it may lead to problems such as Irrecoverable Schedule, Inconsistency and many more threats.
Why not just let it be Serial schedules and we may live peacefully, no complications at all.

Yes, the performance effects the efficiency too much which is not acceptable.
Hence a Database may provide a mechanism that ensures that the schedules are either conflict or view serializable and recoverable (also preferably cascadeless). Testing for a schedule for Serializability after it has executed is obviously too late!
So we need Concurrency Control Protocols that ensures Serializability .

Concurrency-control protocols : allow concurrent schedules, but ensure that the schedules are conflict/view serializable, and are recoverable and maybe even cascadeless.
These protocols do not examine the precedence graph as it is being created, instead a protocol imposes a discipline that avoids non-seralizable schedules.
Different concurrency control protocols provide different advantages between the amount of concurrency they allow and the amount of overhead that they impose.
We’ll be learning some protocols which are important for GATE CS. Questions from this topic is frequently asked and it’s recommended to learn this concept. (At the end of this series of articles I’ll try to list all theoretical aspects of this concept for students to revise quickly and they may find the material in one place.) Now, let’s get going:

Different categories of protocols:

  • Lock Based Protocol
    • Basic 2-PL
    • Conservative 2-PL
    • Strict 2-PL
    • Rigorous 2-PL
  • Graph Based Protocol
  • Time-Stamp Ordering Protocol
  • Multiple Granularity Protocol
  • Multi-version Protocol

For GATE we’ll be focusing on the First three protocols.

Lock Based Protocols –
A lock is a variable associated with a data item that describes a status of data item with respect to possible operation that can be applied to it. They synchronize the access by concurrent transactions to the database items. It is required in this protocol that all the data items must be accessed in a mutually exclusive manner. Let me introduce you to two common locks which are used and some terminology followed in this protocol.

  1. Shared Lock (S): also known as Read-only lock. As the name suggests it can be shared between transactions because while holding this lock the transaction does not have the permission to update data on the data item. S-lock is requested using lock-S instruction.
  2. Exclusive Lock (X): Data item can be both read as well as written.This is Exclusive and cannot be held simultaneously on the same data item. X-lock is requested using lock-X instruction.

Lock Compatibility Matrix –