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.
- Locking is also performed on disk units, called extents, which are 64K in size. When a table or index grows and additional disk space is needed, Extent locks locks are automatically set.
- Lock granularity affects concurrency. In general, the more granular the lock, the more concurrency is reduced.
- This means that row-level locking maximizes concurrency as it leaves all but one row on the page unlocked.
- On the other hand, the system overhead increases because each lock row requires one lock.
Page-level locking and table-level locking restricts the availability of data, but reduces system overhead.