Understanding SQL Server Lock Granularity

Lock granularity specifies which resource is locked by a single lock attempt. The SQL Server Database Engine can lock the following resources:

•     Row
•     Page
•     Index key or range of index keys
•     Table
•     Extent
•     Database itself

NOTE

The following discussion concerns the pessimistic concurrency model. The optimistic concurrency model is handled using row versioning. 

NOTE

The system automatically chooses the appropriate lock granularity.

A row is the smallest resource that can be locked. The support of row-level locking includes both data rows and index entries. Row-level locking means that only the row that is accessed by an application will be locked. Hence, all other rows that belong to the same page are free and can be used by other applications. The Database Engine can also lock the page on which the row that has to be locked is stored.

NOTE

For clustered tables, the data pages are stored at the leaf level of the (clustered) index structure and are therefore locked with index key locks instead of row locks.

Locking is also done on disk units, called extents, that are 64K in size (see Chapter 15). Extent locks are set automatically when a table (or index) grows and the additional disk space is needed.

Lock granularity affects concurrency. In general, the more granular the lock, the more concurrency is reduced. This means that row-level locking maximizes concurrency because it leaves all but one row on the page unlocked. On the other hand, system overhead is increased because each locked row requires one lock. Page-level locking (and table-level locking) restricts the availability of data but decreases the system overhead.