SQL Server locking mechanism

There are several levels of granularity that commercial RDBMS products can use to lock the physical data including row-level locking and page-level locking. With row-level locking, the smallest unit of data that can be locked is the row. Any row within a database can be locked. In some cases, rows, even within the same table, can have a variable length.

With page-level locking, the smallest unit of data that can be locked is the page. A page is typically fixed in size and generally includes more than one row of data. The RDBMS can manage page-level locks more efficiently than row-level locks because page size is fixed and because page-level locking usually requires fewer locks than row-level locking. The disadvantage of page-level locking is that a page lock can affect more than the single row of data that is being modified. This can have an adverse impact on concurrency.

SQL Server offers the advantages of both row-level locking and page-level locking by introducing a dynamic locking strategy. This strategy allows the server to use page-level locking when there is no lock contention, but to de-escalate the page lock to a row-level lock if there is contention for the locked page.