SQL Server Interview Question #251
What is an update lock and why does SQL Server use it?
Advanced Transactions & Concurrency Senior Advanced
Detailed Explanation
An Update (U) lock is used in certain read-before-write operations. It is compatible with shared locks but only one update lock can generally be held on a given resource in the relevant compatibility context.
Its purpose includes reducing a common conversion-deadlock pattern where multiple sessions first acquire shared locks and then both attempt to convert them to exclusive locks.
When the modification occurs, the update lock is converted to an Exclusive (X) lock.