Lock Based Protocol in DBMS

Amisha Purswani
Last Updated: May 13, 2022

Introduction

A lock is an intuitive software plus hardware functionality provided and implemented by the operating system. A lock is a data variable linked to a specific data item. This lock indicates that activities on the data item are permitted. Locks in DBMS synchronize concurrent transactions' access to database elements. The concurrency-control manager receives all lock requests.

Types of Locks: 

Shared Lock (S)

A shared lock is also known as a Read-only lock. The data item can be shared between transactions via the shared lock. We will not be able to update data on the lock.

Example: Consider the situation where two transactions are reading a person's account balance. By establishing a shared lock on the database, they will read it. However, a shared lock prevents another transaction from updating that account's balance until the reading procedure is completed.

Exclusive Lock (X)

The data item can be read and written via Exclusive Lock. This is an exclusive mode that cannot be used on the same data item simultaneously. The lock-x instruction is used to request X-lock. Transactions may unlock the data item after completing the 'write' process.

When a transaction needs to update a person's account balance, for example, by putting an X lock on this transaction, you can allow it to proceed. As a result, the exclusive lock prevents the second transaction from reading or writing.

Shared Lock 

Exclusive Lock 

  • A transaction T having been shared can only read the data. 
  • More than one transaction can acquire a shared lock on the same variable.
  • Represented by S  
  • A transaction T having an exclusive lock can both read as well as write 
  • At any given time, only one Transaction can have an exclusive lock on a variable.
  • Represented by X

Two-Phase Locking Protocol      

The Two-Phase Locking Protocol, often known as the 2PL protocol, is a method of concurrency control in DBMS that maintains serializability by securing transaction data with a lock that prevents subsequent transactions from accessing the same data at the same time. The Two-Phase Locking strategy aids in the elimination of the DBMS concurrency problem. It guarantees conflict serializability in turn guarantees serializability.

Conflict serializability => Serializability 

This locking protocol has 2 phases. 

Phase 1:- Growing phase / expanding phase: acquires Lock.

Phase 2:- Shrinking phase: release locks 

Rules are given in the form of 2 phases where rules are as follows : 

  1.  Ti cannot acquire a lock after a lock has been released. 
  2.  On commit/ rollback, all locks are released by default.
     

Strict 2PL

Strict-Two phase locking is nearly identical to 2PL. Strict-2PL differs only in that it never releases a lock after it has been used. It keeps all the locks until the commit point and then releases them all at the same time when the procedure is finished—strict 2PL guarantees both serializability and recoverability (strict recoverable).

It follows all rules of 2PL, plus all exclusive locks must be held till commit/rollback, but there is no such restriction with shared Lock.

Strict 2PL=>CSS+Strict recoverable 

Strong strict 2PL/ Rigorous 2PL

Rigorous 2-PL necessitates the release of any Exclusive(X) and Shared(S) locks held by the transaction until after the Transaction Commits, in addition to the Lock being 2-Phase. Our schedule will be Recoverable and Cascadeless if we follow Rigorous 2-PL.

As a result, it frees us from Cascading Abort, which was still present in Basic 2-PL, and also guarantees Strict Schedules, though Deadlocks are still possible!

2PL + all locks (shared or exclusive) should be held till transaction commit or rollback.

In the above example, S2 has an exclusive lock and it is released after commit because S2 is a strict 2PL. But it is not rigorous 2PL  

FAQs

  1. What is a lock point?
    When all locks are retained throughout the duration of the transaction, this is known as the "Lock Point." All of the data in a transaction is bound together. As a result, it prohibits a transaction from being divided into pieces.
     
  2. Which lock is also called a read-only lock?
    A shared lock, also known as a read lock, is solely used to read data objects. Read integrity is supported via shared locks. They ensure that a record is not being modified while a read-only request is being made. Shared locks can also be used to prevent any records from being updated.
     
  3. What is the difference between two-phase locking and strict two-phase locking in transactions?
    Before a transaction can start executing in 2-PL, it must first obtain locks on all the data elements it needs but a transaction can gain locks on data items whenever it needs them (just during the growing phase) in Strict 2-PL.

Key Takeaways

In this blog, we have learned that A lock is a data variable that is linked to a specific data item. This lock indicates that activities on the data item are permitted. With the Exclusive Lock, a data item can be read as well as written but with the Shared Lock item can be read-only. The Two-Phase Locking Protocol aids in the elimination of the concurrency problem. It guarantees conflict serializability in turn guarantees serializability. Strict 2PL follows all rules of 2PL + all exclusive locks must be held till commit/rollback but there is no such restriction with shared lock. Rigorous 2-PL necessitates the release of any Exclusive(X) and Shared(S) locks held by the transaction until after the Transaction Commits, in addition to the lock being 2-Phase.

Visit here to learn more about different topics related to database management systems. Ninja, don't stop here; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job. Also, try CodeStudio to practice programming problems for your complete interview preparation.

Was this article helpful ?
3 upvotes

Comments

No comments yet

Be the first to share what you think