--- PDF PAGE 666 ---
659
© Peter A. Carter 2019 
P. A. Carter, Pro SQL Server 2019 Administration, https://doi.org/10.1007/978-1-4842-5089-1_18
CHAPTER 18
Locking and Blocking
Locking is an essential aspect of any RDBMS, because it allows concurrent users to 
access the same data, without the risk of their updates conflicting and causing data 
integrity issues. This chapter discusses how locking, deadlocks, and transactions work in 
SQL Server; it then moves on to discuss how transactions impact In-Memory transaction 
functionality and how the DBA can observe lock metadata regarding transactions and 
contention.
Understanding Locking
The following sections discuss how processes can take locks at various levels of 
granularity, which types of locks are compatible with others, and features for controlling 
lock behavior during online maintenance operations and lock partitioning, which can 
improve performance on large systems.
Lock Granularity
Processes can take out locks at many different levels of granularity, depending on the 
nature of the operation requesting the lock. To reduce the impact of operations blocking 
each other, it is sensible to take out a lock at the lowest possible level of granularity. 
The trade-off, however, is that taking out locks uses system resources, so if an operation 
requires acquiring millions of locks at the lowest level of granularity, then this is highly 
inefficient, and locking at a higher level is a more suitable choice. Table 18-1 describes 
the levels of granularity at which locks can be taken out.

--- PDF PAGE 667 ---
660
When SQL Server locks a resource within a table, it takes out what is known as an 
intent lock on the resource directly above it in the hierarchy. For example, if SQL Server 
needs to lock a RID or KEY, it also takes out an intent lock on the page containing the 
row. If the Lock Manager decides that it is more efficient to lock at a higher level of the 
hierarchy, then it escalates the lock to a higher level. It is worth noting, however, that row 
locks are not escalated to page locks; they are escalated directly to table locks. If the table 
is partitioned, then SQL Server can lock the partition as opposed to the whole table. The 
thresholds that SQL Server uses for lock escalation are as follows:
•	
An operation requires more than 5000 locks on a table, or a partition, 
if the table is partitioned.
•	
The number of locks acquired within the instance causes memory 
thresholds to be exceeded.
You can change this behavior for specific tables, however, by using the LOCK_
ESCALATION option of a table. This option has three possible values, as described in 
Table 18-2.
Table 18-1.  Locking Granularity
Level
Description
RID/KEY
A row identifier on a heap or an index key. Use locks on index keys 
in serializable transactions to lock ranges of rows. Serializable 
transactions are discussed later in this chapter.
PAGE
A data or index page.
EXTENT
Eight continuous pages.
HoBT (Heap or B-Tree)
A heap of a single index (B-tree).
TABLE
An entire table, including all indexes.
FILE
A file within a database.
METADATA
A metadata resource.
ALLOCATION_UNIT
Tables are split into three allocation units: row data, row overflow 
data, and LOB (Large Object Block) data. A lock on an allocation unit 
locks one of the three allocation units of a table.
DATABASE
The entire database.
Chapter 18  Locking and Blocking

--- PDF PAGE 667 ---
660
When SQL Server locks a resource within a table, it takes out what is known as an 
intent lock on the resource directly above it in the hierarchy. For example, if SQL Server 
needs to lock a RID or KEY, it also takes out an intent lock on the page containing the 
row. If the Lock Manager decides that it is more efficient to lock at a higher level of the 
hierarchy, then it escalates the lock to a higher level. It is worth noting, however, that row 
locks are not escalated to page locks; they are escalated directly to table locks. If the table 
is partitioned, then SQL Server can lock the partition as opposed to the whole table. The 
thresholds that SQL Server uses for lock escalation are as follows:
•	
An operation requires more than 5000 locks on a table, or a partition, 
if the table is partitioned.
•	
The number of locks acquired within the instance causes memory 
thresholds to be exceeded.
You can change this behavior for specific tables, however, by using the LOCK_
ESCALATION option of a table. This option has three possible values, as described in 
Table 18-2.
Table 18-1.  Locking Granularity
Level
Description
RID/KEY
A row identifier on a heap or an index key. Use locks on index keys 
in serializable transactions to lock ranges of rows. Serializable 
transactions are discussed later in this chapter.
PAGE
A data or index page.
EXTENT
Eight continuous pages.
HoBT (Heap or B-Tree)
A heap of a single index (B-tree).
TABLE
An entire table, including all indexes.
FILE
A file within a database.
METADATA
A metadata resource.
ALLOCATION_UNIT
Tables are split into three allocation units: row data, row overflow 
data, and LOB (Large Object Block) data. A lock on an allocation unit 
locks one of the three allocation units of a table.
DATABASE
The entire database.
Chapter 18  Locking and Blocking

--- PDF PAGE 668 ---
661
Locking Behaviors for Online Maintenance
In SQL Server, you can also control the behavior of locking for online index rebuilds and 
partition SWITCH operations. The available options are described in Table 18-3.
Table 18-2.  LOCK_ESCALATION Values
Value
Description
TABLE
Locks escalate to the table level, even when you are using partitioned tables.
AUTO
This value allows locks to escalate to a partition, rather than the table, on partitioned 
tables.
DISABLE
The value disables locks being escalated to the table level except when a table lock is 
required to protect data integrity.
Table 18-3.  Blocking Behaviors
Option
Description
MAX_DURATION
The duration, specified in minutes, that an online index rebuild or 
SWITCH operation waits before the ABORT_AFTER_WAIT action is 
triggered.
ABORT_AFTER_WAIT
These are the available actions:
•  NONE specifies that the operation will continue to wait, with 
normal priority.
•  SELF means that the operation will be terminated.
•  BLOCKERS means that all user transactions that are currently 
blocking the operation will be killed.
WAIT_AT_LOW_PRIORITY
Functionally equivalent to MAX_DURATION = 0, ABORT_AFTER_
WAIT = NONE.
The script in Listing 18-1 creates the Chapter18 database, which includes a table 
called Customers that is populated with data. The script then demonstrates configuring 
LOCK_ESCALATION before rebuilding the nonclustered index on dbo.customers, 
specifying that any operations should be killed if they are blocking the rebuild for more 
than 1 minute.
Chapter 18  Locking and Blocking

--- PDF PAGE 668 ---
661
Locking Behaviors for Online Maintenance
In SQL Server, you can also control the behavior of locking for online index rebuilds and 
partition SWITCH operations. The available options are described in Table 18-3.
Table 18-2.  LOCK_ESCALATION Values
Value
Description
TABLE
Locks escalate to the table level, even when you are using partitioned tables.
AUTO
This value allows locks to escalate to a partition, rather than the table, on partitioned 
tables.
DISABLE
The value disables locks being escalated to the table level except when a table lock is 
required to protect data integrity.
Table 18-3.  Blocking Behaviors
Option
Description
MAX_DURATION
The duration, specified in minutes, that an online index rebuild or 
SWITCH operation waits before the ABORT_AFTER_WAIT action is 
triggered.
ABORT_AFTER_WAIT
These are the available actions:
•  NONE specifies that the operation will continue to wait, with 
normal priority.
•  SELF means that the operation will be terminated.
•  BLOCKERS means that all user transactions that are currently 
blocking the operation will be killed.
WAIT_AT_LOW_PRIORITY
Functionally equivalent to MAX_DURATION = 0, ABORT_AFTER_
WAIT = NONE.
The script in Listing 18-1 creates the Chapter18 database, which includes a table 
called Customers that is populated with data. The script then demonstrates configuring 
LOCK_ESCALATION before rebuilding the nonclustered index on dbo.customers, 
specifying that any operations should be killed if they are blocking the rebuild for more 
than 1 minute.
Chapter 18  Locking and Blocking

--- PDF PAGE 673 ---
666
--Set WAIT_AT_LOW_PRIORITY
ALTER INDEX idx_LastName ON dbo.Customers REBUILD
WITH
(ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_
WAIT = BLOCKERS))) ;
Lock Compatibility
A process can acquire different types of locks. These lock types are described in Table 18-­4.
Table 18-4.  Lock Types
Type
Description
Shared (S)
Used for read operations.
Update (U)
Taken out on resources that may be updated.
Exclusive (X)
Used when data is modified.
Schema Modification 
(Sch-M) / Schema 
Stability (Sch-S)
Schema modification locks are taken out when DDL statements are 
being run against a table. Schema stability locks are taken out while 
queries are being compiled and executed. Stability locks only block 
operations that require a schema modification lock, whereas schema 
modification locks block all access to a table.
Bulk Update (BU)
Bulk update locks are used during bulk load operations to allow 
multiple threads to parallel load data to a table while blocking other 
processes.
Key-range
Key-range locks are taken on a range of rows when using pessimistic 
isolation levels. Isolation levels are discussed later in this chapter.
Intent
Intent locks are used to protect resources lower in the lock hierarchy by 
signaling their intent to acquire a shared or exclusive lock.
Intent locks improve performance, because they are only examined at the table level, 
which negates the need to examine every row or page before another operation acquires 
a lock. The types of intent lock that can be acquired are described in Table 18-5.
Chapter 18  Locking and Blocking

--- PDF PAGE 673 ---
666
--Set WAIT_AT_LOW_PRIORITY
ALTER INDEX idx_LastName ON dbo.Customers REBUILD
WITH
(ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_
WAIT = BLOCKERS))) ;
Lock Compatibility
A process can acquire different types of locks. These lock types are described in Table 18-­4.
Table 18-4.  Lock Types
Type
Description
Shared (S)
Used for read operations.
Update (U)
Taken out on resources that may be updated.
Exclusive (X)
Used when data is modified.
Schema Modification 
(Sch-M) / Schema 
Stability (Sch-S)
Schema modification locks are taken out when DDL statements are 
being run against a table. Schema stability locks are taken out while 
queries are being compiled and executed. Stability locks only block 
operations that require a schema modification lock, whereas schema 
modification locks block all access to a table.
Bulk Update (BU)
Bulk update locks are used during bulk load operations to allow 
multiple threads to parallel load data to a table while blocking other 
processes.
Key-range
Key-range locks are taken on a range of rows when using pessimistic 
isolation levels. Isolation levels are discussed later in this chapter.
Intent
Intent locks are used to protect resources lower in the lock hierarchy by 
signaling their intent to acquire a shared or exclusive lock.
Intent locks improve performance, because they are only examined at the table level, 
which negates the need to examine every row or page before another operation acquires 
a lock. The types of intent lock that can be acquired are described in Table 18-5.
Chapter 18  Locking and Blocking

--- PDF PAGE 674 ---
667
The matrix in Figure 18-1 shows basic lock compatibility. You can find a complete 
matrix of lock compatibility at http://msdn.microsoft.com.
Table 18-5.  Intent Lock Types
Type
Description
Intent shared (IS)
Protects shared locks on some resources at the lower level of the 
hierarchy
Intent exclusive (IX)
Protects shared and exclusive locks on some resources at the 
lower level of the hierarchy
Shared with intent exclusive 
(SIX)
Protects shared locks on all resources and exclusive locks on 
some resources at the lower level of the hierarchy
Intent update (IU)
Protects update locks on all resources at the lower level of the 
hierarchy
Shared intent update (SIU)
The resultant set of S and IU locks
Update intent exclusive (UIX)
The resultant set of X and IU locks
Figure 18-1.  Lock compatibility matrix
Lock Partitioning
It is possible for locks on frequently accessed resources to become a bottleneck. For 
this reason, SQL Server automatically applies a feature called lock partitioning for any 
instance that has affinity with more than 16 cores. Lock partitioning reduces contention 
by dividing a single lock resource into multiple resources. This means that contention is 
reduced on shared resources such as the memory used by the lock resource structure.
Chapter 18  Locking and Blocking

--- PDF PAGE 675 ---
668
Understanding Deadlocks
Because of the very nature of locking, operations need to wait until a lock has been 
released before they can acquire their own lock on a resource. A problem can occur, 
however, if two separate processes have taken out locks on different resources, but both 
are blocked, waiting for the other to complete. This is known as a deadlock.
How Deadlocks Occur
To see how this issue can arise, examine Table 18-6.
Table 18-6.  Deadlock Chronology
Process A
Process B
Acquires an exclusive lock on Row1 in Table1
Acquires an exclusive lock on Row2 in Table2
Attempts to acquire a lock on Row2 in Table2 
but is blocked by Process B
Attempts to acquire a lock on Row1 in Table1 
but is blocked by Process A
In the sequence described here, neither Process A nor Process B can continue, which 
means a deadlock has occurred. SQL Server detects deadlocks via an internal process 
called the deadlock monitor. When the deadlock monitor encounters a deadlock, it 
checks to see if the processes have been assigned a transaction priority. If the processes 
have different transaction priorities, it kills the process with the lowest priority. If they 
have the same priority, then it kills the least expensive process in terms of resource 
utilization. If both processes have the same cost, it picks a process at random and kills it.
The script in Listing 18-2 generates a deadlock. You must run the first and third parts 
of the script in a different query window than the second and fourth parts. You must run 
each section of the script in sequence.
Chapter 18  Locking and Blocking