Datapages locking

In datapages locking, entire data pages are still locked, but index pages are not locked. When a row needs to be changed on a data page, that page is locked, and the lock is held until the end of the transaction. The updates to the index pages are performed using latches, which are nontransactional. Latches are held only as long as required to perform the physical changes to the page and are then released immediately. Index page entries are implicitly locked by locking the data page. No transactional locks are held on index pages. See “Latches” and “Choosing a locking scheme based on contention statistics” for more information.

Figure 1-2 shows an insert into a datapages-locked table. Only the affected data page is locked.

Figure 1-2: Locks held during datapages locking

Two images showing an insert in to the leaf-level of a datapages-locked table.