Implementation of Locking

Introduction

Locking

In DBMS,  Locked based protocol in DBMS is a method that prevents a transaction from reading or writing data unless it obtains an appropriate lock. By locking or isolating a particular transaction to a single user, lock-based protocols eliminate the concurrency problem in DBMS for simultaneous transactions.

A lock is a data variable linked to a specific data item. This lock indicates those activities that are permitted on the data item. Locks in DBMS assist synchronized concurrent transactions access to database elements.

Also Read - Specialization and Generalization in DBMS, Multiple Granularity in DBMS

Lock Manager

In database management systems, locking techniques are used to control concurrency. Many transactions can request a lock on a data item simultaneously. As a result, we'll need a way to handle transaction locking requests. 

Lock Manager is the name for such a mechanism. To handle the locking, unlocking of data items, it relies on the message passing mechanism, in which transactions and the lock manager exchange messages.

Data Structures used

1. It maintains a linked list of records for each locked data item, one for each request, in the order, they were received.
2. Each node of the linked list represents the transaction requested for lock, the requested lock mode (mutual/exclusive), and the request's current status (granted/waiting).
3. A hash table indexed on the name of the data item. It is used to find the linked list (if any) for a data item.
4. Separate chaining is used to handle collisions in hash tables.

Example

Explanation of the above example:

1. The status of a node is represented by its color, which indicates whether the lock has been granted or waiting.
2. A linked list of requested lock transactions is displayed below them, with a descending arrow ↓. Each node in the linked list contains the name of the transaction that requested that data item.
3. The data item acts as a header for the linked list containing the locking request.
4. The above table includes locks for four different data items, 5, 14, 18, 17. There is also a list of transactions that have been granted locks or are waiting for locks for each data item.
5. Separate chaining handles the collision between data items 5 and 14.
   
   You can also read about the Multiple Granularity Locking, Recursive Relationship in DBMS and  Checkpoint in DBMS.

Working of the Lock Manager 

1. The lock table is initially empty because no data items are locked.
2. When a lock request is received from a transaction Ti on a specific data item Qi, the following cases may occur:
   1. A linked list will be created if Qi is not already locked, and a lock will be granted to the requesting transaction Ti.
   2. If the data item is already locked, a new node with information about Ti's request will be appended to the end of its linked list.
3. Ti will acquire the lock, and the status will be changed to 'granted' if the lock mode requested by Ti is compatible with the lock mode of the transaction that currently has the lock. Otherwise, Ti's lock will be marked as 'waiting.' From the above example, It can be seen that T1 has been granted locks on 14 and is waiting for a lock on data item 18.
4. A transaction Ti will send an unlock request to the lock manager if it wants to unlock the data item it is currently holding. The lock manager will remove Ti’s node from this linked list. The next transaction in the queue will be given the lock.
5. Transaction Ti may need to be canceled sometimes. In this situation, all of Ti's pending requests will be removed from the lock table's linked lists. Once abortion is complete, locks held by Ti will also be released.

This algorithm guarantees freedom from starvation for lock requests since a request can never be granted while a request received earlier is waiting to be granted.

You can also read about the Log based recovery.

Must Recommended Topic, Schema in DBMS