What are Shared, Exclusive, and Update locks?

A Shared (S) lock is generally used when reading data under locking-based isolation and can coexist with other compatible shared locks. An Exclusive (X) lock is used when modifying data and conflicts with many other lock modes on the same resource.

An Update (U) lock is used during certain read-before-update operations and helps reduce a class of conversion deadlocks by allowing only one update lock on a compatible resource at a time before conversion to an exclusive lock.

Actual lock behavior depends on the statement, access path, isolation level, and SQL Server's lock manager.