Concurrency Control Techniques

Lock-Based Protocols, Two-Phase Locking (2PL), and Timestamp Ordering

In a multi-user database system, concurrent access to shared data is inevitable. Without careful management, concurrent transactions can lead to issues such as dirty reads, lost updates, or inconsistent data. To prevent these problems and ensure the isolation property of transactions, concurrency control techniques are used. This section explains three major approaches: Lock-Based Protocols, Two-Phase Locking (2PL), and Timestamp Ordering.

1. Lock-Based Protocols

Lock-based protocols use locks to control access to data items. When a transaction wants to read or write a data item, it must first obtain a lock on it. The lock ensures that no other transaction can perform conflicting operations on the same data at the same time.

There are two common types of locks:

• Shared Lock (S-lock): Allows multiple transactions to read a data item but not write.
• Exclusive Lock (X-lock): Allows a transaction to both read and write the data item, and prevents other transactions from accessing it until the lock is released.

This technique prevents race conditions, but it can lead to deadlocks if not handled properly.

2. Two-Phase Locking (2PL)

Two-Phase Locking is a protocol that organizes how locks are acquired and released during a transaction. It guarantees conflict serializability, one of the most desirable properties in concurrency control.

2PL divides a transaction into two distinct phases:

• Growing Phase: The transaction can acquire locks but cannot release any.
• Shrinking Phase: The transaction can release locks but cannot acquire any new ones.

Once the transaction begins releasing locks, it cannot request new ones. This discipline avoids cycles in the wait-for graph, helping maintain consistency.

Variants of 2PL:

• Strict 2PL: All exclusive locks are held until the transaction commits or aborts.
• Rigorous 2PL: Both shared and exclusive locks are held until the end.

2PL is widely used because it ensures that the schedule is serializable and helps manage deadlocks with additional strategies like wait-die or wound-wait schemes.

3. Timestamp Ordering

This protocol uses timestamps to determine the order of transaction operations. Each transaction is assigned a unique timestamp when it begins, and the protocol ensures that operations are executed in timestamp order.

For each data item, the system tracks:

• The read timestamp: the largest timestamp of any transaction that has read the item.
• The write timestamp: the largest timestamp of any transaction that has written the item.

When a transaction tries to read or write a data item, the system compares its timestamp with these values. If the operation violates the timestamp order, it is rolled back to prevent inconsistency.

This method is non-locking and helps avoid deadlocks, but frequent rollbacks can occur if many transactions conflict.

Final Thoughts

Each technique has its own trade-offs:

• Lock-based protocols are intuitive and practical but need care to prevent deadlocks.
• 2PL enforces strict order for safety, often used in commercial systems.
• Timestamp ordering is non-blocking and avoids deadlocks but may lead to higher transaction aborts.