MS SQL Server Optimization Question:

Main lock types:

Shared: Applied to read only operations where the data is not modified. E.g.: Select statements.
Update: Applied to resources which can be updated. It resolves dead locks in case of multiple sessions are reading, locking or updating resources later.
Exclusive: Used for operations involving data modification. E.g.: Insert, Update, and Delete. This ensures that multiple updates are not made to the same data at the same time.
Intent: Establishes a lock hierarchy. E.g.: Intent shared Intent exclusive and Shared with intentexclusive.
Schema: Used when schema dependent operations are being executed. E.g.: Schema modification and Schema stability.
Bulk update: Used while bulk copying of data and Tablock is specified.