Lock Granularity and Hierarchies
The Microsoft SQL Server Database Engine has multigranular locking that allows different types of resources to be locked by a transaction. To minimize the cost of locking, the Database Engine locks resources automatically at a level appropriate to the task. Locking at a smaller granularity, such as rows, increases concurrency but has a higher overhead because more locks must be held if many rows are locked. Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions. However, it has a lower overhead because fewer locks are being maintained.
Applies to: SQL Server 2008 R2 and higher versions.
The Database Engine often has to acquire locks at multiple levels of granularity to fully protect a resource. This group of locks at multiple levels of granularity is called a lock hierarchy. For example, to fully protect a read of an index, an instance of the Database Engine may have to acquire share locks on rows and intent share locks on the pages and table.
The following table shows the resources that the Database Engine can lock.
Resource |
Description |
|---|---|
RID |
A row identifier used to lock a single row within a heap. |
KEY |
A row lock within an index used to protect key ranges in serializable transactions. |
PAGE |
An 8-kilobyte (KB) page in a database, such as data or index pages. |
EXTENT |
A contiguous group of eight pages, such as data or index pages. |
HoBT |
A heap or B-tree. A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index. |
TABLE |
The entire table, including all data and indexes. |
FILE |
A database file. |
APPLICATION |
An application-specified resource. |
METADATA |
Metadata locks. |
ALLOCATION_UNIT |
An allocation unit. |
DATABASE |
The entire database. |
Note
HoBT and TABLE locks can be affected by the LOCK_ESCALATION option of ALTER TABLE.