Today, I learnt about various locking mechanism to prevent double update. In this blog, i make notes on Shared Lock and Exclusive Lock for my future self.

What Are Locks in Databases?

Locks are mechanisms used by a DBMS to control access to data. They ensure that transactions are executed in a way that maintains the ACID (Atomicity, Consistency, Isolation, Durability) properties of the database. Locks can be classified into several types, including

• Shared Locks (S Locks): Allow multiple transactions to read a resource simultaneously but prevent any transaction from writing to it.
• Exclusive Locks (X Locks): Allow a single transaction to modify a resource, preventing both reading and writing by other transactions.
• Intent Locks: Used to signal the type of lock a transaction intends to acquire at a lower level.
• Deadlock Prevention Locks: Special locks aimed at preventing deadlock scenarios.

Shared Lock

A shared lock is used when a transaction needs to read a resource (e.g., a database row or table) without altering it. Multiple transactions can acquire a shared lock on the same resource simultaneously. However, as long as one or more shared locks exist on a resource, no transaction can acquire an exclusive lock on that resource.

-- Transaction A: Acquire a shared lock on a row
BEGIN;
SELECT * FROM employees WHERE id = 1 FOR SHARE;
-- Transaction B: Acquire a shared lock on the same row
BEGIN;
SELECT * FROM employees WHERE id = 1 FOR SHARE;
-- Both transactions can read the row concurrently
-- Transaction C: Attempt to update the same row
BEGIN;
UPDATE employees SET salary = salary + 1000 WHERE id = 1;
-- Transaction C will be blocked until Transactions A and B release their locks

Key Characteristics of Shared Locks

1. Concurrent Reads

• Shared locks allow multiple transactions to read the same resource at the same time.
• This is ideal for operations like SELECT queries that do not modify data.

2. Write Blocking

• While a shared lock is active, no transaction can modify the locked resource.
• Prevents dirty writes and ensures read consistency.

3. Compatibility

• Shared locks are compatible with other shared locks but not with exclusive locks.

When Are Shared Locks Used?

Shared locks are typically employed in read operations under certain isolation levels. For instance,

1. Read Committed Isolation Level:

• Shared locks are held for the duration of the read operation.
• Prevents dirty reads by ensuring the data being read is not modified by other transactions during the read.

2. Repeatable Read Isolation Level:

• Shared locks are held until the transaction completes.
• Ensures that the data read during a transaction remains consistent and unmodified.

3. Snapshot Isolation:

• Shared locks may not be explicitly used, as the DBMS creates a consistent snapshot of the data for the transaction.

Exclusive Locks

An exclusive lock is used when a transaction needs to modify a resource. Only one transaction can hold an exclusive lock on a resource at a time, ensuring no other transactions can read or write to the locked resource.

-- Transaction X: Acquire an exclusive lock to update a row
BEGIN;
UPDATE employees SET salary = salary + 1000 WHERE id = 2;
-- Transaction Y: Attempt to read the same row
BEGIN;
SELECT * FROM employees WHERE id = 2;
-- Transaction Y will be blocked until Transaction X completes
-- Transaction Z: Attempt to update the same row
BEGIN;
UPDATE employees SET salary = salary + 500 WHERE id = 2;
-- Transaction Z will also be blocked until Transaction X completes

Key Characteristics of Exclusive Locks

1. Write Operations: Exclusive locks are essential for operations like INSERT, UPDATE, and DELETE.

2. Blocking Reads and Writes: While an exclusive lock is active, no other transaction can read or write to the resource.

3. Isolation: Ensures that changes made by one transaction are not visible to others until the transaction is complete.

When Are Exclusive Locks Used?

Exclusive locks are typically employed in write operations or any operation that modifies the database. For instance:

1. Transactional Updates – A transaction that updates a row acquires an exclusive lock to ensure no other transaction can access or modify the row during the update.

2. Table Modifications – When altering a table structure, the DBMS may place an exclusive lock on the entire table.

Benefits of Shared and Exclusive Locks

Benefits of Shared Locks

1. Consistency in Multi-User Environments – Ensure that data being read is not altered by other transactions, preserving consistency.
2. Concurrency Support – Allow multiple transactions to read data simultaneously, improving system performance.
3. Data Integrity – Prevent dirty reads and writes, ensuring that operations yield reliable results.

Benefits of Exclusive Locks

1. Data Integrity During Modifications – Prevents other transactions from accessing data being modified, ensuring changes are applied safely.
2. Isolation of Transactions – Ensures that modifications by one transaction are not visible to others until committed.

Limitations and Challenges

Shared Locks

1. Potential for Deadlocks – Deadlocks can occur if two transactions simultaneously hold shared locks and attempt to upgrade to exclusive locks.
2. Blocking Writes – Shared locks can delay write operations, potentially impacting performance in write-heavy systems.
3. Lock Escalation – In systems with high concurrency, shared locks may escalate to table-level locks, reducing granularity and concurrency.

Exclusive Locks

1. Reduced Concurrency – Exclusive locks prevent other transactions from accessing the locked resource, which can lead to bottlenecks in highly concurrent systems.
2. Risk of Deadlocks – Deadlocks can occur if two transactions attempt to acquire exclusive locks on resources held by each other.

Lock Compatibility