Difference between shared lock and update lock

A Shared Lock allows multiple sessions to read the same data simultaneously, whereas an Update Lock is a special lock used when SQL Server intends to modify data later; only one Update Lock is allowed on a resource, and it helps prevent deadlocks by converting to an Exclusive Lock when the update occurs.

Shared Lock (S) vs Update Lock (U) in SQL Server

Feature	Shared Lock (S)	Update Lock (U)
Purpose	Read data	Read data with intention to update later
Used By	SELECT statements	UPDATE statements (internally), UPDLOCK hint
Operation Type	Read-only	Read-then-update
Multiple Locks Allowed on Same Row	Yes	No
Compatible with Shared Lock (S)	Yes	Yes
Compatible with Update Lock (U)	No	No
Compatible with Exclusive Lock (X)	No	No
Blocks Readers	No	No
Blocks Writers	Yes	Yes
Can Convert to X Lock	No	Yes
Deadlock Prevention	No	Yes
Lock Duration (READ COMMITTED)	Released after read	Held until update/conversion
Common DMV Output	S	U
Concurrency	High	Medium
Main Goal	Allow multiple readers	Prevent update conversion deadlocks

Compatibility Matrix

Existing Lock	New Shared (S)	New Update (U)	New Exclusive (X)
Shared (S)	✅ Allowed	✅ Allowed	❌ Blocked
Update (U)	✅ Allowed	❌ Blocked	❌ Blocked
Exclusive (X)	❌ Blocked	❌ Blocked	❌ Blocked

Internal Workflow Comparison

Step	Shared Lock (S)	Update Lock (U)
Read Row	✅	✅
Acquire Lock	S	U
Allow Other Readers	✅	✅
Prepare for Update	❌	✅
Convert to X Lock	❌	✅
Modify Data	❌	✅