Database Reference
In-Depth Information
Lock Operations and Modes
Because of the variety of operations that SQL Server needs to perform, an equally large and complex set of locking
mechanisms are maintained. In addition to the different types of locks, there is an escalation path to change from one
type of lock to another. The following sections describe these modes and processes, as well as their uses.
Lock Escalation
When a query is executed, SQL Server determines the required lock level for the database objects referred to in
the query, and it starts executing the query after acquiring the required locks. During the query execution, the lock
manager keeps track of the number of locks requested by the query to determine the need to escalate the lock level
from the current level to a higher level.
The lock escalation threshold is determined by SQL Server during the course of a transaction. Row locks and
page locks are automatically escalated to a table lock when a transaction exceeds its threshold. After the lock level
is escalated to a table-level lock, all the lower-level locks on the table are automatically released. This dynamic lock
escalation feature of the lock manager optimizes the locking overhead of a query.
It is possible to establish a level of control over the locking mechanisms on a given table. For example, you can
control whether lock escalation occurs. The following is the T-SQL syntax to make that change:
ALTER TABLE schema.table
SET (LOCK_ESCALATION = DISABLE)
This syntax will disable lock escalation on the table entirely (except for a few special circumstances). You can
also set it to
TABLE
, which will cause the escalation to go to a table lock every single time. You can also set lock
escalation on the table to
AUTO
, which will allow SQL Server to make the determination for the locking schema and
any escalation necessary. If that table is partitioned, you may see the escalation change to the partition level. Again,
exercise caution using these types of modifications to standard SQL Server behavior.
You also have the option to disable lock escalation on a wider basis by using trace flag 1224. This disables lock
escalation based on the number of locks but leaves intact lock escalation based on memory pressure. You can also
disable the memory pressure lock escalation as well as the number of locks by using trace flag 1211, but that's a
dangerous choice and can lead to errors on your systems. I strongly suggest thorough testing before using either of
these options.
Lock Modes
The degree of isolation required by different transactions may vary. For instance, consistency of data is not affected
if two transactions read the data simultaneously; however, the consistency is affected if two transactions are allowed
to modify the data simultaneously. Depending on the type of access requested, SQL Server uses different lock modes
while locking resources:
•
Shared (S)
•
Update (U)
•
Exclusive (X)
•
Intent:
•
Intent Shared (IS)
•
Intent Exclusive (IX)
•
Shared with Intent Exclusive (SIX)
