Shared (S) Lock in SQL Server

A Shared Lock (S Lock) is acquired by SQL Server when reading data. It allows multiple sessions to read the same data simultaneously but prevents other sessions from modifying that data while the Shared Lock is active. Shared Locks are typically short-lived under READ COMMITTED isolation but can be retained longer under REPEATABLE READ and SERIALIZABLE isolation levels. Many readers can share it due to that it is called Shared Lock.

We can run the select query on different session it is not blocking

Session 1

Session 2

Transaction is not yet committed.

Read + Read = OK

Read + Write = Wait

Write + Write = Wait

In SQL server default isolation level is READ COMMITTED.

We can use the below query to get the shared lock sessions

SELECT     request_session_id,     resource_type,     request_mode,     request_status FROM sys.dm_tran_locks WHERE request_mode = 'S';

Shared Locks can participate in deadlocks.

Shared Lock vs Exclusive Lock

Feature	Shared Lock (S)	Exclusive Lock (X)
Used For	Reading	Writing
Multiple Sessions Allowed	Yes	No
Blocks Readers	No	Yes
Blocks Writers	Yes	Yes
Compatibility with S	Yes	No
Compatibility with X	No	No

Shared Locks held until COMMIT; It Depends on isolation level.

Isolation Level	Shared Lock Duration
READ COMMITTED	Until row read
REPEATABLE READ	Until COMMIT
SERIALIZABLE	Until COMMIT
RCSI	Generally, not required for data reads
SNAPSHOT	Generally, not required for data reads

Shared Lock is a read lock. Multiple users can read the same data simultaneously, but no user can modify that data until the Shared Lock is released. In short, Readers can share, Writers must wait.