Lock (1) -- lock granularity and hierarchy

The Microsoft SQL Server database engine has multi-granularity locking, allowing a transaction to lock different types of resources. To minimize the lock overhead, the database engine automatically locks resources at the appropriate task level. Locking at a smaller granularity (such as rows) can increase the concurrency, but the overhead is high, because if many rows are locked, more locks need to be held. Locking in a large granularity (such as a table) reduces the concurrency, because locking the entire table limits access to any part of other transactions in the table. However, the overhead is low because there are fewer locks to be maintained.

Generally, database engines must obtain multi-granularity locks to fully protect resources. These multi-granularity locks are called lock hierarchies. For example, in order to completely protect the reading of indexes, Database Engine instances may have to obtain the row-based shared locks and the intention-shared locks on pages and tables.

The following table lists the resources that can be locked by the database engine.

Resources	Description
RID	The row identifier used to lock a single row in the heap.
Key	The index is used to protect the row locks for key ranges in serializable transactions.
Page	8 KB pages in the database, such as data pages or index pages.
Extent	A group of eight consecutive pages, such as data pages or index pages.
HOBT	Heap or B-tree.
Table	The entire table that includes all data and indexes.
File	Database files.
Application	Resources dedicated to applications.
Metadata	Metadata lock.
Allocation_unit	Allocation unit.
Database	The entire database.

 

 

 

 

 

 

 

 

Relationship between lock granularity and concurrency