Understanding Lock Granularity in SQL Server

SQL Server Lock granularity describe which resource is locked by a single lock attempt.

The SQL Server Database Engine can lock the below resources:-

Table

Row

Page

Extent

Index key

Range of index keys

Database itself

A row is the smallest resource that can be locked. Support for row-level locking includes both data rows and index entries.

Row-level locking means that only one row that has been accessed by an application will be locked.

Therefore, all other rows that belong to the same page are independent and can be used by other applications.The database engine can also lock the page on which the locked row 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.