Global Locking

Global locks restrict the entire database instance to a read-only state, preventing any data modification operations (insert, update, or delete) while the lock is active. This is often used for maintenance tasks or consistent backups.

-- Acquire a global read lock
FLUSH TABLES WITH READ LOCK;

-- Release the global lock
UNLOCK TABLES;

Shared Locks (Read Locks)

Shared locks permit concurrent read operations among different transactions but block write operations. A transaction holding a shared lock can read data but cannot modify it. Multiple transactions can hold shared locks on the same resource simultaneously. However, because write operations require an exclusive lock, any pending write is blocked until all shared locks are released.

Deadlock Scenarios

Deadlocks occur when transactions create a cycle of dependency. A common situation involves two transactions reading data with shared locks (held until the transaction ends) and then attempting to update it.

-- Transaction A
BEGIN TRANSACTION t1;
SELECT * FROM ProductInventory WITH (HOLDLOCK); -- Sustains shared lock
UPDATE ProductInventory SET ItemName = 'Deadlock Test A';

-- Transaction B
BEGIN TRANSACTION t2;
SELECT * FROM ProductInventory WITH (HOLDLOCK);
UPDATE ProductInventory SET ItemName = 'Deadlock Test B';

In this scenario, both transactions successfully acquire shared locks. When they attempt to update, they require exclusive locks. Since neither can proceed until the other releases its shared lock, a deadlock occurs. Increasing the isolation level to Serializable can resolve this by enforcing strict serialization.

Lock Contention and Waiting

Resource contention can also lead to waiting without a deadlock. Consider two sessions updating the same record:

-- Session 1
UPDATE ProductInventory SET ItemName = 'Update 1' WHERE ProductID = 5;

-- Session 2
UPDATE ProductInventory SET ItemName = 'Update 2' WHERE ProductID = 5;

The behavior depends on indexing:

1. With Index: The database quickly locates the specific row and applies an exclusive lock. The second session waits for the first to complete.
2. Without Index: A full table scan is required. The scanning operation may acquire locks (shared or update) on a range of data, causing the second session to wait significantly longer if it needs to scan or lock the same range.

Update Locks

Update locks are designed to prevent the deadlock issues inherent in the shared-to-exclusive lock conversion process. They are compatible with shared locks but block other update locks and exclusive locks.

This mechanism ensures that only one transaction can hold an update lock with the intent to modify data, allowing other transactions to read the data concurrently. Since only one update lock exists at a time for a resource, it can safely convert to an exclusive lock without waiting for other shared locks to release.

-- Transaction Alpha
BEGIN TRANSACTION;
SELECT * FROM ProductInventory WITH (UPDLOCK); -- Acquire update lock
UPDATE ProductInventory SET ItemName = 'Processed';

-- Transaction Beta
BEGIN TRANSACTION;
SELECT * FROM ProductInventory; -- Acquires shared lock (compatible)
UPDATE ProductInventory SET ItemName = 'Pending';

Execution Flow:

1. If Transaction Alpha arrives first, it acquires an update lock. Transaction Beta can still read (shared lock is compatible) but will be blocked when attempting to write, waiting for Alpha to finish.
2. If Transaction Beta arrives first (without an update lock hint) and acquires a shared lock, but Transaction Alpha requests an update lock, Alpha waits. Once Beta finishes, Alpha proceeds, blocking subsequent writers.

Exclusive Locks (Write Locks)

Exclusive locks, or write locks, provide the highest level of restriction. They prevent any other transaction from acquiring read (shared) or write (exclusive) locks on the same resource.

Granularity in Index Locking

Row-level storage engines utilize specific locking strategies to ensure data integrity:

• Record Locks: Locks are applied directly to the index records (leaf nodes) to prevent modifications or deletions by other transactions.
• Gap Locks: Locks are applied to the gaps between index records. This prevents other transactions from inserting new records into the locked range, ensuring the range remains consistent during repeatable reads.
• Next-Key Locks: A combination of record locks and gap locks. This locks the specific data record and the gap preceding it, effectively preventing phantom reads and insertions.