What is Shared locks (S) in SQL Server 2005/2008

Shared locks are held on data being read under the pessimistic concurrency model.

While a shared lock is being held other transactions can read but can't modify locked data.

 In the below example you can't see the shared locks because they're taken for the duration of the select statement and are already released when we would select data from sys.dm_tran_locks. That is why an addition of WITH (HOLDLOCK) is needed to see the locks

BEGIN TRAN

USE Master

SELECT * FROM Person.Name WITH (HOLDLOCK)
WHERE UserId = 2
	
ROLLBACK
Ebook Download
View all
Learn
View all