Implementing Locking in DBMS

Locking protocols are used in database management systems as a means of concurrency control. Multiple transactions may request a lock on a data item simultaneously. Hence, we require a mechanism to manage the locking requests made by transactions. Such a mechanism is called a Lock Manager. It relies on the process of message passing where transactions and lock manager exchange messages to handle the locking and unlocking of data items.

Data Structure in Lock Manager

The data structure required for the implementation of locking is called a Lock table.

1. It is a hash table where the names of data items are used as a hashing index.

2. Each locked data item has a linked list associated with it.

3. Every node in the linked list represents the transaction requested for lock, the mode of lock requested (mutual/exclusive), and the current status of the request (granted/waiting).

4. Every new lock request for the data item will be added to the end of the linked list as a new node.

5. Collisions in the hash table are handled by the technique of separate chaining.

Consider the following example of a lock table:

GFG Reference

Explanation: In the above figure, the locked data items present in the lock table are 5, 47, 167, and 15. The transactions which have requested for lock have been represented by a linked list shown below them using a downward arrow. Each node in the linked list has the name of the transaction which has requested the data item like T33, T1, T27, etc. The color of the node represents the status i.e. whether the lock has been granted or waiting. Note that a collision has occurred for data items 5 and 47. It has been resolved by separate chaining where each data item belongs to a linked list. The data item is acting as a header for a linked list containing the locking request.

Working as Lock Manager

• Initially, the lock table is empty as no data item is locked.

• Whenever the lock manager receives a lock request from a transaction Ti on a particular data item Qi following cases may arise:

  • If Qi is not already locked, a linked list will be created and a lock will be granted to the requesting transaction Ti.

  • If the data item is already locked, a new node will be added at the end of its linked list containing the information about the request made by Ti.

• If the lock mode requested by Ti is compatible with the lock mode of the transaction currently having the lock, Ti will acquire the lock too and the status will be changed to ‘granted’. Else, the status of Ti’s safety will be ‘waiting’.

• If a transaction Ti wants to unlock the data item it is currently holding, it will send an unlock request to the lock manager. The lock manager will delete Ti’s node from this linked list. The lock will be granted to the next transaction in the list.

• Sometimes transaction Ti may have to be aborted. In such a case all the waiting requests made by Ti will be deleted from the linked lists present in the lock table. Once abortion is complete, locks held by Ti will also be released.

Advantages of Locking:

1. Data Consistency: Locking ensures data consistency by preventing concurrent modifications to the same data, maintaining a consistent database state.

2. Isolation: Locking isolates transactions from each other, reducing the risk of data inconsistencies and conflicts between transactions.

3. Granularity: Locking can be applied at various levels of granularity, offering precise control over shared resources (e.g., row-level or table-level locking).

4. Availability: Locking prevents resource monopolization, ensuring shared resources remain available to multiple users or applications.

Disadvantages of Locking:

1. Overhead: Locking introduces overhead due to acquiring and releasing locks, potentially leading to slower performance and increased resource consumption, especially in highly concurrent systems.

2. Deadlocks: Deadlocks can occur when transactions wait for each other to release resources, causing circular dependencies and hindering progress, resulting in reduced throughput and latency.

3. Reduced Concurrency: Locking limits the number of concurrent users or applications accessing the database simultaneously, potentially leading to reduced concurrency and slower performance in highly concurrent systems.

4. Complexity: Implementing locking, especially in distributed or complex systems, can be challenging and may increase development and maintenance costs.