Lock Based Concurrency Control Protocol in DBMS

In a database management system (DBMS), lock-based concurrency control (BCC) is used to control the access of multiple transactions to the same data item. This protocol helps to maintain data consistency and integrity across multiple users.

In the protocol, transactions gain locks on data items to control their access and prevent conflicts between concurrent transactions. This article will look deep into the Lock Based Protocol in detail.

A Lock is a variable assigned to any data item to keep track of the status of that data item so that isolation and non-interference are ensured during concurrent transactions.

A lock is a variable associated with a data item that describes the status of the data item to possible operations 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 that are used and some terminology followed in this protocol.

1. Shared Lock (S): Shared Lock is 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.

A transaction may be granted a lock on an item if the requested lock is compatible with locks already held on the item by other transactions. Any number of transactions can hold shared locks on an item, but if any transaction holds an exclusive(X) on the item no other transaction may hold any lock on the item. If a lock cannot be granted, the requesting transaction is made to wait till all incompatible locks held by other transactions have been released. Then the lock is granted.

Concurrency Control Protocol 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-serializable schedules. Different concurrency control protocols provide different advantages between the amount of concurrency they allow and the amount of overhead that they impose.

• 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

1. Simplistic Lock Protocol

It is the simplest method for locking data during a transaction. Simple lock-based protocols enable all transactions to obtain a lock on the data before inserting, deleting, or updating it. It will unlock the data item once the transaction is completed.

2. Pre-Claiming Lock Protocol

Pre-claiming Lock Protocols assess transactions to determine which data elements require locks. Before executing the transaction, it asks the DBMS for a lock on all of the data elements. If all locks are given, this protocol will allow the transaction to start. When the transaction is finished, it releases all locks. If all of the locks are not provided, this protocol allows the transaction to be reversed and waits until all of the locks are granted.

3. Two-phase locking (2PL)

A transaction is said to follow the Two-Phase Locking protocol if Locking and Unlocking can be done in two phases

• Growing Phase: New locks on data items may be acquired but none can be released.
• Shrinking Phase: Existing locks may be released but no new locks can be acquired.

For more detail refer the published article Two-phase locking (2PL).

4. Strict Two-Phase Locking Protocol

Strict Two-Phase Locking requires that in addition to the 2-PL all Exclusive(X) locks held by the transaction be released until after the Transaction Commits.  For more details refer the published article Strict Two-Phase Locking Protocol.

A transaction that holds a lock on an item Ais allowed under certain condition to change the lock state from one state to another. Upgrade: A S(A) can be upgraded to X(A) if T_i is the only transaction holding the S-lock on element A. Downgrade: We may downgrade X(A) to S(A) when we feel that we no longer want to write on data-item A. As we were holding X-lock on A, we need not check any conditions.

So, by now we are introduced with the types of locks and how to apply them. But wait, just by applying locks if our problems could’ve been avoided then life would’ve been so simple! If you have done Process Synchronization under OS you must be familiar with one consistent problem, starvation and Deadlock! We’ll be discussing them shortly, but just so you know we have to apply Locks but they must follow a set of protocols to avoid such undesirable problems. Shortly we’ll use 2-Phase Locking (2-PL) which will use the concept of Locks to avoid deadlock. So, applying simple locking, we may not always produce Serializable results, it may lead to Deadlock Inconsistency.

Consider the Partial Schedule:

1. Deadlock

In deadlock consider the above execution phase. Now, T₁ holds an Exclusive lock over B, and T₂ holds a Shared lock over A. Consider Statement 7, T₂ requests for lock on B, while in Statement 8 T₁ requests lock on A. This as you may notice imposes a deadlock as none can proceed with their execution.

2. Starvation

Starvation is also possible if concurrency control manager is badly designed. For example: A transaction may be waiting for an X-lock on an item, while a sequence of other transactions request and are granted an S-lock on the same item. This may be avoided if the concurrency control manager is properly designed.

In conclusion, lock-based concurrency control in a database management system (DBMS) uses locks to control access, avoid conflicts, and preserve the integrity of the database across multiple users. The protocol seeks to achieve a balance between concurrency and integrity by carefully controlling the acquisition and deletion of locks by operations.

Why do we use a lock-based protocol?

This helps to eliminate the concurrency problem by locking a certain transaction to a particular user.

Why locking is used in transaction?

Transactions use locks to control concurrent access to data.

What are the problems of lock based protocols?

• Deadlock
• Starvation

What is the time stamp protocol?

The main idea for this protocol is to order the transactions based on their Timestamps. A schedule in which the transactions participate is then serializable and the only equivalent serial schedule permitted has the transactions in the order of their Timestamp Values