Locking Protocols

In a Database Management System (DBMS), multiple transactions often run at the same time to improve performance and resource utilization. However, when two or more transactions access the same data concurrently, problems like lost updates, temporary inconsistency, or uncommitted data being read can occur. To avoid such issues, DBMS uses Concurrency Control techniques, and one of the most widely used methods is Locking Protocols.

Locking protocols define rules on how and when transactions can lock and unlock data items to ensure consistency, isolation, and correctness of transactions.

What is a Lock?

A lock is a mechanism that restricts access to a data item by multiple transactions. When one transaction locks a data item, others may have to wait until the lock is released.

There are mainly two types of locks in DBMS:

1. Shared Lock (S-lock)
   • Allows a transaction to read a data item.
   • Multiple transactions can hold a shared lock on the same item simultaneously.
   • Example: If Transaction T1 is reading a record, T2 can also read it.
2. Exclusive Lock (X-lock)
   • Allows a transaction to read and write a data item.
   • Only one transaction can hold an exclusive lock on an item at a time.
   • Example: If T1 is updating a record, no other transaction can read or write it until T1 releases the lock.

Why Do We Need Locking Protocols?

Without proper control, concurrent transactions may lead to problems such as:

• Lost Update: Two transactions overwrite each other’s updates.
• Dirty Read: A transaction reads uncommitted data from another transaction.
• Unrepeatable Read: A transaction reads the same item twice and gets different values due to updates by another transaction.
• Deadlock: Two transactions wait indefinitely for each other’s locks.

Locking protocols provide a systematic set of rules to avoid these problems and ensure serializability (transactions execute as if run one after another).

Types of Locking Protocols

1. Simplistic Locking Protocol

• A transaction must acquire a lock before using a data item.
• It must release the lock after use.
• This protocol is simple but does not guarantee serializability.

2. Pre-claiming Locking Protocol

• All locks required by a transaction are obtained before the transaction starts.
• If all locks are available, the transaction proceeds. Otherwise, it waits.
• Advantage: Avoids deadlocks.
• Disadvantage: Difficult to know all required locks in advance.

3. Two-Phase Locking Protocol (2PL)

This is the most commonly used locking protocol. It ensures conflict serializability.

• A transaction has two phases:
  1. Growing Phase – It may acquire locks but cannot release any.
  2. Shrinking Phase – It may release locks but cannot acquire new ones.
• Once a transaction releases a lock, it cannot obtain any more locks.
• Guarantees serializability but may lead to deadlocks.

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

• A special version of 2PL.
• A transaction holds all exclusive locks until it commits or aborts.
• Prevents cascading rollbacks (when one transaction’s failure causes others to roll back).
• Widely used in practice.

5. Rigorous Two-Phase Locking

• Even stricter than strict 2PL.
• A transaction holds both shared and exclusive locks until commit.
• Ensures serializability and avoids cascading rollbacks.
• Provides the highest degree of isolation.

6. Graph-Based Locking Protocol

• Uses a partial ordering of data items (like a graph) to avoid deadlocks.
• A transaction can only request locks in a specific predefined order.
• Ensures no cyclic dependencies (hence no deadlocks).
• Example: If data items are A → B → C in order, a transaction that locks A must lock B before locking C.

Deadlock in Locking Protocols

Even with locking protocols, deadlocks can occur when two or more transactions wait for each other indefinitely. For example:

• T1 locks A, needs B.
• T2 locks B, needs A.
• Both wait forever.

Solutions to deadlock:

• Deadlock prevention (follow lock order, pre-claim locks).
• Deadlock detection (use wait-for graph).
• Deadlock avoidance (like Banker’s algorithm).

Advantages of Locking Protocols

• Maintains data consistency.
• Ensures isolation of transactions.
• Supports serializability in concurrent execution.
• Prevents issues like lost updates or dirty reads.

Disadvantages of Locking Protocols

• May cause deadlocks.
• Can lead to starvation (some transactions keep waiting indefinitely).
• Performance overhead due to lock management.

Conclusion

Locking protocols in DBMS are essential for handling concurrent transactions safely. By controlling how transactions acquire and release locks, these protocols ensure data consistency and system reliability. Among the various protocols, Two-Phase Locking (2PL) and its variations are the most widely used in real-world systems because they guarantee serializability.

In short, while locking protocols may introduce challenges like deadlocks, they form the backbone of concurrency control in databases, ensuring smooth and reliable multi-user operations.