Lock-Based Protocol in DBMS

Lock-Based Protocol

In database systems, concurrency control plays an important role in maintaining consistency and isolation when multiple transactions execute at the same time. One of the most widely used techniques for concurrency control is the Lock-Based Protocol.

Under a lock-based protocol, a transaction must acquire the appropriate lock before it can read or write a data item. Locks help prevent concurrency problems such as lost updates, dirty reads, and inconsistent data.

What is a Lock?

A lock is a mechanism associated with a data item that controls how transactions can access that item. By managing locks, the database system prevents conflicting operations from being performed simultaneously.

When a transaction acquires a lock on a data item, other transactions may have to wait before accessing the item, depending on the type of lock already held.

Types of Locks

Locks can be classified into different types depending on the level of access they provide.

1. Binary Locks

A binary lock is the simplest type of lock. It has only two possible states:

Locked (1)
Unlocked (0)

Each data item has an associated lock. If a transaction locks a data item, another transaction cannot access that item until the lock is released.

Two basic operations are associated with binary locking of a data item A:

Lock(A): Acquires a lock on data item A before performing an operation.
Unlock(A): Releases the lock after the transaction has finished using A.

Rules for Binary Locking

A transaction must acquire a lock before reading or writing a data item.
A transaction should release the lock after it has finished using the data item.
A lock can only be released by the transaction that currently holds it.
A transaction cannot acquire the same binary lock again while already holding it.

Example: Solving the Lost Update Problem

Suppose two transactions attempt to update the same bank account at the same time. Without proper locking, one transaction may overwrite the changes made by the other, resulting in a lost update.

With binary locks, the first transaction acquires the lock and performs its update. The second transaction must wait until the first transaction releases the lock. This prevents conflicting updates and helps maintain consistency.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

2. Shared and Exclusive Locks

Binary locks are simple, but practical database systems generally use shared locks and exclusive locks, commonly referred to as read/write locks.

Shared Lock (S-Lock)

Also known as a read lock.
Multiple transactions can hold a shared lock on the same data item.
Allows a transaction to read the data item.
Does not allow the transaction to modify the data item.

Exclusive Lock (X-Lock)

Also known as a write lock.
Allows the transaction to read and modify the data item.
Only one transaction can hold an exclusive lock on a data item at a time.
Other transactions cannot acquire a shared or exclusive lock on that item while the exclusive lock is held.

Lock Compatibility Matrix

Current Lock	Shared Requested	Exclusive Requested
Shared	Yes	No
Exclusive	No	No

The compatibility matrix shows that multiple transactions can hold shared locks simultaneously, while an exclusive lock is incompatible with both shared and exclusive locks.

Example: Inconsistent Read Problem

Consider two bank accounts:

Account A = Rs. 1,000
Account B = Rs. 900

Suppose transaction T1 transfers Rs. 200 from A to B, while transaction T2 calculates the total balance of both accounts.

If T2 reads account A before T1 updates it but reads account B after T1 updates it, T2 may observe an inconsistent state.

For example:

T2 reads A = Rs. 1,000.
T1 transfers Rs. 200 from A to B.
T1 changes A to Rs. 800 and B to Rs. 1,100.
T2 reads B = Rs. 1,100.

T2 calculates a total of Rs. 2,100 instead of the original total of Rs. 1,900. Appropriate locking can prevent transactions from observing such an inconsistent intermediate state.

Lock-Based Protocols

Several protocols have been developed to manage locks and control concurrent transactions.

1. Simplistic Lock Protocol

In a simplistic locking approach:

A transaction acquires a lock before performing a read or write operation.
Locks are generally held until the transaction has completed its required operations.
It is easy to understand and implement.
Depending on the locking strategy, transactions may experience deadlocks.

2. Pre-Claiming Lock Protocol

In the pre-claiming lock protocol, a transaction requests all the locks it will need before it begins execution.

The transaction identifies all required data items.
It requests the required locks before starting execution.
If all locks are available, execution begins.
If one or more locks are unavailable, the transaction waits or may be rolled back.

This approach can reduce the possibility of deadlocks because a transaction does not acquire additional locks while holding others. However, it can increase waiting time and may require a transaction to know all of its required data items in advance.

3. Two-Phase Locking Protocol (2PL)

The Two-Phase Locking Protocol (2PL) is one of the most important lock-based protocols used for concurrency control. It divides transaction execution into two phases:

Growing Phase

The transaction can acquire new locks.
The transaction cannot release any lock.

Shrinking Phase

The transaction can release locks.
The transaction cannot acquire any new locks.

The basic idea can be represented as:

Growing Phase → Lock Acquisition
        ↓
    Lock Point
        ↓
Shrinking Phase → Lock Release

The lock point is the point at which a transaction has acquired its final lock. After reaching this point, the transaction enters its shrinking phase.

2PL guarantees conflict serializability, although it can still result in deadlocks.

Lock Conversion in 2PL

Two important lock-conversion operations can occur:

Upgrade: A transaction changes a shared lock (S) into an exclusive lock (X). This must occur during the growing phase.
Downgrade: A transaction changes an exclusive lock (X) into a shared lock (S). This occurs during the shrinking phase.

Example of Two-Phase Locking

Suppose transaction T1 needs to access data items A and B.

T1 acquires a lock on A.
T1 acquires a lock on B.
T1 reaches its lock point after obtaining all required locks.
T1 performs the required operations.
T1 releases the lock on A.
T1 releases the lock on B.

Once T1 starts releasing locks, it cannot acquire any new locks under the basic 2PL protocol.

4. Strict Two-Phase Locking Protocol (Strict 2PL)

Strict Two-Phase Locking (Strict 2PL) is a stricter version of the Two-Phase Locking protocol.

Under Strict 2PL, a transaction keeps its exclusive locks until it commits or aborts. This prevents other transactions from reading or overwriting data written by an uncommitted transaction.

Important advantages include:

Prevents dirty reads of uncommitted updates.
Prevents cascading rollbacks caused by reading uncommitted data.
Produces schedules that are easier to recover.
Provides strong consistency guarantees.

The main disadvantage is that transactions may have to wait longer because locks remain held until commit or abort.

Complete Python Course with Advanced Topics:
SQL Tutorial:
Download New Real-Time Projects:

Advantages of Lock-Based Protocol

Helps maintain database consistency.
Controls concurrent access to shared data.
Prevents problems such as lost updates and dirty reads.
Supports serializable transaction schedules.
Provides different locking strategies for different application requirements.

Disadvantages of Lock-Based Protocol

Locks can cause transactions to wait.
Deadlocks may occur when transactions wait for each other.
Managing a large number of locks can introduce overhead.
Strict locking can reduce concurrency because locks may be held for longer periods.

Lock-Based Protocol vs 2PL vs Strict 2PL

Feature	Lock-Based Protocol	2PL	Strict 2PL
Uses locks	Yes	Yes	Yes
Growing and shrinking phases	Not necessarily	Yes	Yes
Conflict serializability	Depends on protocol	Yes	Yes
Exclusive locks held until commit	Not necessarily	Not required	Yes
Cascading rollback prevention	Depends on implementation	Not guaranteed by basic 2PL	Yes
Deadlock possible	Yes	Yes	Yes

YT:- DecodeIT

Conclusion

The Lock-Based Protocol is a fundamental technique for concurrency control in database management systems. It regulates how transactions access shared data and helps prevent problems such as lost updates, dirty reads, and inconsistent states.

Binary locks provide a simple locking mechanism, while shared and exclusive locks offer more flexible access control. The Two-Phase Locking (2PL) protocol provides conflict serializability, and Strict 2PL provides stronger recovery properties by retaining exclusive locks until transaction completion.

Although locking improves consistency, it can introduce deadlocks, blocking, and additional overhead. Therefore, the appropriate locking protocol should be selected based on the application’s requirements for concurrency, consistency, performance, and recovery.

Keywords

Lock-Based Protocol, DBMS Lock-Based Protocol, Lock-Based Protocol in DBMS, Concurrency Control in DBMS, Binary Locks, Shared Lock, Exclusive Lock, Two Phase Locking, 2PL Protocol, Strict 2PL, Lock Compatibility, Database Transactions, Serializability, Deadlock in DBMS

Lock-Based Protocol in DBMS

Lock-Based Protocol

Table of Contents

What is a Lock?