---

Lock Based Protocols in DBMS

---

Why Do We Need Locks?

Locks are essential in a database system to ensure:

1. Consistency: Without locks, multiple transactions could modify the same data item simultaneously, resulting in an inconsistent state.
2. Isolation: Locks ensure that the operations of one transaction are isolated from other transactions, i.e., they are invisible to other transactions until the transaction is committed.
3. Concurrency: While ensuring consistency and isolation, locks also allow multiple transactions to be processed simultaneously by the system, optimizing system throughput and overall performance.
4. Avoiding Conflicts: Locks help in avoiding data conflicts that might arise due to simultaneous read and write operations by different transactions on the same data item.
5. Preventing Dirty Reads: With the help of locks, a transaction is prevented from reading data that hasn't yet been committed by another transaction.

Lock-Based Protocols

1. Simple Lock Based Protocol

The Simple lock based protocol is a mechanism in which there is exclusive use of locks on the data item for current transaction.

Types of Locks: There are two types of locks used -

Shared Lock (S-lock)

This lock allows a transaction to read a data item. Multiple transactions can hold shared locks on the same data item simultaneously. It is denoted by Lock-S. This is also called as read lock.

Exclusive Lock (X-lock):

This lock allows a transaction to read and write a data item. If a transaction holds an exclusive lock on an item, no other transaction can hold any kind of lock on the same item. It is denoted as Lock-X. This is also called as write lock.

   T1     |    T2
----------|----------
Lock-S(A) |
Read(A)   |  
Unlock(A) |  Lock-X(A)
          |  Read(A)
          |  Write(A)
          |  Unlock(A)

The difference between shared lock and exclusive lock?

2. Two-Phase Locking (2PL) Protocol

The two-phase locking protocol ensures a transaction gets all the locks it needs before it releases any. The protocol has two phases:

• Growing Phase: The transaction may obtain any number of locks but cannot release any.
• Shrinking Phase: The transaction may release but cannot obtain any new locks.

The point where the transaction releases its first lock is the end of the growing phase and the beginning of the shrinking phase.

This protocol ensures conflict-serializability and avoids many of the concurrency issues, but it doesn't guarantee the absence of deadlocks.

   T1     |    T2
----------|----------
Lock-S(A) |               
          |                
          |  Lock-S(A)     
Lock-X(B) |              
Unlock(A) |               
          |  Lock-X(C)    
Unlock(B) |               
          |  Unlock(A)
          |  Unlock(C)

Pros of Two-Phase Locking (2PL)

• Ensures Serializability: 2PL guarantees conflict-serializability, ensuring the consistency of the database.
• Concurrency: By allowing multiple transactions to acquire locks and release them, 2PL increases the concurrency level, leading to better system throughput and overall performance.
• Avoids Cascading Rollbacks: Since a transaction cannot read a value modified by another uncommitted transaction, cascading rollbacks are avoided, making recovery simpler.

Cons of Two-Phase Locking (2PL)

• Deadlocks: The main disadvantage of 2PL is that it can lead to deadlocks, where two or more transactions wait indefinitely for a resource locked by the other.
• Reduced Concurrency (in certain cases): Locking can block transactions, which can reduce concurrency. For example, if one transaction holds a lock for a long time, other transactions needing that lock will be blocked.
• Overhead: Maintaining locks, especially in systems with a large number of items and transactions, requires overhead. There's a time cost associated with acquiring and releasing locks, and memory overhead for maintaining the lock table.
• Starvation: It's possible for some transactions to get repeatedly delayed if other transactions are continually requesting and acquiring locks.

Categories of Two-Phase Locking in DBMS

1. Strict Two-Phase Locking
2. Rigorous Two-Phase Locking
3. Conservative (or Static) Two-Phase Locking:

Strict Two-Phase Locking

• Transactions are not allowed to release any locks until after they commit or abort.
• Ensures serializability and avoids the problem of cascading rollbacks.
• However, it can reduce concurrency.

Pros Strict Two-Phase Locking

• Serializability: Ensures serializable schedules, maintaining the consistency of the database.
• Avoids Cascading Rollbacks: A transaction cannot read uncommitted data, thus avoiding cascading aborts.
• Simplicity: Conceptually straightforward in that a transaction simply holds onto its locks until it's finished.

Cons Strict Two-Phase Locking

• Reduced Concurrency: Since transactions hold onto locks until they commit or abort, other transactions might be blocked for longer periods.
• Potential for Deadlocks: Holding onto locks can increase the chances of deadlocks.

Rigorous Two-Phase Locking

• A transaction can release a lock after using it, but it cannot commit until all locks have been acquired.
• Like strict 2PL, rigorous 2PL is deadlock-free and ensures serializability.

Pros Rigorous Two-Phase Locking

• Serializability: Like strict 2PL, ensures serializable schedules.
• Avoids Cascading Rollbacks: Prevents reading of uncommitted data.
• Improved Concurrency: By releasing locks before committing, it can potentially allow higher concurrency compared to strict 2PL.

Cons Rigorous Two-Phase Locking

• Deadlock Possibility: Still susceptible to deadlocks as transactions might hold some locks while releasing others.

Conservative or Static Two-Phase Locking

• A transaction must request all the locks it will ever need before it begins execution. If any of the requested locks are unavailable, the transaction is delayed until they are all available.
• This approach can avoid deadlocks since transactions only start when all their required locks are available.

Pros Conservative or Static Two-Phase Locking

• Avoids Deadlocks: By ensuring that all required locks are acquired before a transaction starts, it inherently avoids the possibility of deadlocks.
• Serializability: Ensures serializable schedules.

Cons Conservative or Static Two-Phase Locking

• Reduced Concurrency: Transactions might be delayed until all required locks are available, leading to potential inefficiencies.

---

Next Topic :Timestamp Based Protocols in DBMS