What are the different lock modes in SQL Server?

Hello and welcome back to my blog!  Today one of the junior DBAs came to me and asked when he views the locks in SQL Server by executing sp_lock or sys.dm_tran_locks what does Mode (in sp_lock) or request_mode (in sys.dm_tran_locks) mean and what are the values.  It occurred to me that perhaps not all DBAs (juniors as well as seniors) may not understand the modes for locks.  So I thought today I would cover the different lock modes.

When executing sp_lock or sys.dm_tran_locks in SQL Server you will see mode or request_mode in the result set that is generated.  Request_mode is simply the lock mode requested when a session is executing on SQL Server.  The values and the definitions for Mode\request_mode are:

NULL = No access is granted to the resource. Serves as a placeholder.
Sch-S = Schema stability. Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.
Sch-M = Schema modification. Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.
S = Shared. The holding session is granted shared access to the resource.
U = Update. Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.
X = Exclusive. The holding session is granted exclusive access to the resource.
IS = Intent Shared. Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.
IU = Intent Update. Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.
IX = Intent Exclusive. Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.
SIU = Shared Intent Update. Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.
SIX = Shared Intent Exclusive. Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
UIX = Update Intent Exclusive. Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
BU = Bulk Update. Used by bulk operations.
RangeS_S = Shared Key-Range and Shared Resource lock. Indicates serializable range scan.
RangeS_U = Shared Key-Range and Update Resource lock. Indicates serializable update scan.
RangeI_N = Insert Key-Range and Null Resource lock. Used to test ranges before inserting a new key into an index.
RangeI_S = Key-Range Conversion lock. Created by an overlap of RangeI_N and S locks.
RangeI_U = Key-Range Conversion lock created by an overlap of RangeI_N and U locks.
RangeI_X = Key-Range Conversion lock created by an overlap of RangeI_N and X locks.
RangeX_S = Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_S. locks.
RangeX_U = Key-Range Conversion lock created by an overlap of RangeI_N and RangeS_U locks.
RangeX_X = Exclusive Key-Range and Exclusive Resource lock. This is a conversion lock used when updating a key in a range.

Understanding the different locks in SQL Server will help you to quickly identify and determine what mode the specific session is executing.  My next blog post I will talk about locks in further detail and also give more insight in the term “lock escalation”.