Implementation of Locking

Shivani Kumari
Last Updated: May 13, 2022

Introduction

Locking

In DBMS, Lock Based Protocol 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.

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.

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.

Frequently Asked Questions

  1. Why is locking needed in DBMS?
    In database management systems, locking protocols 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.
     
  2. What are two problems with lock-based protocols?
    Most locking protocols have the possibility of deadlock. Deadlocks are unavoidable. If the concurrency control manager is poorly built, starvation is also possible.
     
  3. What is the locking method?
    A lock is associated with each data item in the locking technique. Before executing a read or write operation on a data item, it is expected that each transaction must lock it and that each transaction must release all locks it holds before completing its tasks.

Key Takeaways

In this article, we learned about locking and locking managers. Then we discussed how the lock works. What data structure is used, and the steps involved in locking and unlocking techniques.

Ready to solve problems asked in the interviews? 

Click here. Enroll today, practice problems asked in technical interview rounds, and learn tricks to solve them faster and more confidently. Also, check out the top 100 SQL problems.

Apart from that, you can use CodeStudio to practice a wide range of DSA questions asked in lots of interviews. It will assist you in mastering efficient coding techniques, and you will also get interview experiences from people working in big companies.

Was this article helpful ?
1 upvote

Comments

No comments yet

Be the first to share what you think