Concurrency Control Techniques - Locking

Concurrency control in a Database Management System (DBMS) ensures that database transactions are performed concurrently without violating the consistency of the database. Locking is a fundamental technique for managing concurrency. It involves controlling access to data by multiple transactions to prevent conflicts.

1. Types of Locks

Locks are used to restrict access to database resources. There are various types of locks based on the level of restriction they impose:

• Shared Lock (S-lock): Allows multiple transactions to read a resource simultaneously but not modify it.
• Exclusive Lock (X-lock): Allows a single transaction to both read and modify a resource. No other transaction can access the resource while the lock is held.
• Update Lock (U-lock): Used when a transaction intends to read and potentially write a resource. It prevents deadlock by converting to an exclusive lock if an update is required.

2. Lock Granularity

Lock granularity refers to the size of the data item that a lock protects:

• Database-level Lock: Locks the entire database. It's the coarsest granularity and affects all transactions.
• Table-level Lock: Locks a specific table within the database.
• Page-level Lock: Locks a specific page of a table. A page is a fixed-length block of data.
• Row-level Lock: Locks a specific row within a table. It provides the finest granularity and allows high concurrency.
• Column-level Lock: Locks a specific column within a row.

3. Two-Phase Locking (2PL)

Two-Phase Locking is a protocol ensuring serializability by requiring that all locking operations precede the first unlock operation:

3.1 Phases of 2PL

• Growing Phase: A transaction may acquire locks but cannot release any lock.
• Shrinking Phase: A transaction may release locks but cannot acquire any new lock.

This ensures no two transactions can have conflicting locks at the same time, thereby maintaining database consistency.

4. Deadlock

Deadlock occurs when two or more transactions are waiting for each other to release locks, causing all of them to be stuck indefinitely. Techniques to handle deadlock include:

• Deadlock Prevention: Ensures that at least one of the transactions can't request a lock that would lead to a deadlock.
• Deadlock Detection: The system periodically checks for deadlocks and resolves them by aborting one or more transactions.
• Deadlock Avoidance: Uses algorithms like the Wait-Die and Wound-Wait schemes to decide whether a transaction should wait or abort.

5. Locking Implementation in SQL

SQL provides mechanisms to implement locks. Here are examples of using locks in SQL:

5.1 Shared Lock Example

-- Acquiring a shared lock on a table
SELECT * FROM table_name WITH (NOLOCK);

5.2 Exclusive Lock Example

-- Acquiring an exclusive lock on a table
BEGIN TRANSACTION;
SELECT * FROM table_name WITH (XLOCK);
-- Perform updates
COMMIT TRANSACTION;

6. Summary

Locking is crucial for maintaining database consistency in a concurrent environment. Understanding the types of locks, lock granularity, two-phase locking protocol, and handling deadlocks are essential for effective concurrency control. SQL provides explicit mechanisms to implement these locking strategies.