Shared Lock in DBMS: Definition, Example, and Usage

A shared lock in a database management system (DBMS) allows multiple transactions to read a data item simultaneously but prevents any transaction from modifying it until all shared locks are released. It ensures data consistency by allowing concurrent reads but blocking writes during the lock period.

How It Works

A shared lock works like a "read-only" permission on a piece of data in a database. Imagine a library where several people can read the same book at the same time, but no one is allowed to write notes or change the book while others are reading. This is similar to how a shared lock allows many transactions to read data simultaneously without changing it.

When a transaction places a shared lock on data, other transactions can also place shared locks on the same data to read it. However, if any transaction wants to modify (write) that data, it must wait until all shared locks are released. This prevents conflicts and keeps the data consistent.

Example

This example shows two transactions trying to read the same data with shared locks, and a third transaction waiting to write until the reads finish.

sql

BEGIN TRANSACTION;
-- Transaction 1 places a shared lock to read data
SELECT * FROM accounts WITH (HOLDLOCK, ROWLOCK) WHERE account_id = 101;
-- Transaction 2 also places a shared lock to read the same data
SELECT * FROM accounts WITH (HOLDLOCK, ROWLOCK) WHERE account_id = 101;
-- Transaction 3 tries to update but waits because of shared locks
UPDATE accounts SET balance = balance + 100 WHERE account_id = 101;
COMMIT;

Output

Transaction 1 and 2 read the account data simultaneously.
Transaction 3 waits until both reads finish and shared locks are released before updating.

When to Use

Use a shared lock when you want to allow multiple users or processes to read the same data at the same time without letting anyone change it during the read. This is common in banking systems, inventory checks, or reporting where data accuracy is important but many users need to see the data simultaneously.

Shared locks help avoid errors like reading data that is being changed at the same time, which could cause wrong results or inconsistencies.

Key Points

• A shared lock allows multiple transactions to read data simultaneously.
• It blocks any transaction from writing to the data until all shared locks are released.
• It helps maintain data consistency during concurrent reads.
• Shared locks are released when the transaction finishes or explicitly unlocks.

Key Takeaways

A shared lock lets many transactions read data but blocks writes until all reads finish.

It ensures data stays consistent by preventing changes during concurrent reads.

Use shared locks when multiple users need safe, simultaneous access to read data.

Shared locks are released after the reading transactions complete.

They help avoid conflicts between reading and writing operations in a database.