What is Lock-Based Concurrency Control?

Lock-based concurrency control is a mechanism where the database restricts access to data by allowing transactions to acquire locks on the data they access. Locks ensure that no two transactions can read or modify the same piece of data simultaneously, preventing conflicts and maintaining isolation.

Why Use Locks?

Locks are used to:

1. Prevent Conflicts: Ensure that multiple transactions don’t interfere with each other.
2. Maintain Consistency: Preserve the integrity of data even with concurrent transactions.
3. Enable Isolation: Ensure that a transaction’s changes are not visible to others until it is committed.

Types of Locks

Locks can be classified based on their purpose and scope:

1. Shared Lock (S-Lock)

It allows multiple transactions to read the same data simultaneously but prevents any transaction from modifying it.

• Use Case: Used for read-only operations.
• Example: If Transaction A reads a row with a shared lock, Transaction B can also read the same row but cannot modify it.

2. Exclusive Lock (X-Lock)

It allows a transaction to modify data while preventing other transactions from reading or writing to the same data.

• Use Case: Used for write operations.
• Example: If Transaction A acquires an exclusive lock on a row, Transaction B must wait until the lock is released to access the row.

3. Intention Locks

It is used to indicate a transaction’s intent to acquire a shared or exclusive lock on a specific part of the data.

• Types:
  • Intention Shared (IS): Indicates a transaction intends to acquire a shared lock.
  • Intention Exclusive (IX): Indicates a transaction intends to acquire an exclusive lock.
• Use Case: Ensures compatibility between table-level and row-level locks.

Lock Granularity

Locks can be applied at different levels of granularity to balance performance and concurrency:

1. Row-Level Lock

• Restricts access to a specific row in a table.
• Advantage: Maximizes concurrency by allowing other rows to be accessed.
• Disadvantage: Higher overhead due to managing many locks.
• Example: A transaction locks a specific row in a customer table for updating.

2. Page-Level Lock

• Restricts access to a group of rows (page) stored in memory or disk.
• Advantage: Balances concurrency and overhead.
• Disadvantage: May lock unrelated rows, reducing concurrency.
• Example: A transaction locks a page in a storage system.

3. Table-Level Lock

• Restricts access to an entire table.
• Advantage: Simple to implement with low overhead.
• Disadvantage: Reduces concurrency as other transactions are blocked from accessing the table.
• Example: A transaction locks the entire order table for a batch update.

Locking Protocols

In this section, we explore the most common locking protocols used in database systems to enforce concurrency control, ensure isolation, and prevent conflicts during transaction execution. These include Two-Phase Locking, Predicate Locking, and Index-Range Locking.

1. Two-Phase Locking (2PL)

Two-Phase Locking (2PL) is a concurrency control protocol that ensures serializability by dividing the locking process into two distinct phases: Expanding Phase and Shrinking Phase.

How Two-Phase Locking Works

1. Expanding Phase:
   • Locks are acquired as needed.
   • No locks are released during this phase.
2. Shrinking Phase:
   • Once the transaction starts releasing locks, no new locks can be acquired.

Lock Behavior in Two-Phase Locking

The table explains the compatibility of lock types in Two-Phase Locking. A Shared Lock allows multiple transactions to read the same data simultaneously, but no writes are allowed. An Exclusive Lock prevents any other transaction from reading or writing the data, ensuring isolation for write operations.

Advantages

• Guarantees serializability.
• Prevents conflicts between concurrent transactions.

Disadvantages

• Deadlocks: Transactions can block each other, requiring deadlock detection and resolution.
• Reduced Throughput: High contention may result in significant waiting times and lower performance.

Use Case

• Suitable for systems requiring strong consistency and isolation, such as financial applications.

2. Predicate Locking

Predicate locking extends 2PL by locking all objects that match a specified condition or predicate, including objects that do not currently exist but could potentially match the condition.

How Predicate Locking Works

• A shared or exclusive lock is applied to all rows matching the predicate condition.
• Even rows that do not currently exist but fall within the predicate range are locked to prevent conflicts (e.g., insertion conflicts).

Example

Explanation

• A query like UPDATE table SET name = 'XYZ' WHERE id > 1 locks records 1, 2, and 3.
• Even though record 2 does not exist, it is locked to prevent its insertion during the transaction.

Advantages

• Prevents anomalies like write skew by locking potential conflicts, even for non-existent rows.

Disadvantages

• Overhead: Predicate locking can lead to over-locking, reducing concurrency.
• Complexity: Managing predicate locks requires significant computational resources.

Use Case

• Ideal for preventing conflicts in systems with complex query conditions.

3. Index-Range Locking

Index-Range Locking is a simplified extension of predicate locking. It locks only the existing keys that match the predicate condition, leaving gaps between keys unlocked.

How Index-Range Locking Works

• A shared or exclusive lock is applied to rows matching the predicate condition.
• Gaps between existing rows are left unlocked, allowing concurrent inserts.

Example

Explanation

• A query like UPDATE table SET name = 'XYZ' WHERE id > 1 locks records 1 and 3.
• Record 2 is not locked, allowing concurrent insertion of a new row with key 2.

Advantages

• Improves concurrency by avoiding over-locking.
• Efficient for queries with sparse or non-contiguous data.

Disadvantages

• Does not prevent conflicts in the gaps (e.g., insertion anomalies).

Use Case

• Best suited for queries on indexed data where gaps between rows exist.

Comparison of Locking Strategies