Press "Enter" to skip to content

Learning about Locks in SQL Server

Bob Dorr migrates over a couple of posts on locking. The first one describes lock enumeration:

SQL Server has a lock iterator class which is used by Lock Monitor (deadlock detection), DMVs such as dm_tran_locks and other workers.   The iterator is designed to remain lock free whenever possible to avoid contention with active queries.  To accomplish this the iterator uses a marker (bookmark) approach and the lock type of NL.  The NL lock does not block and provides a bookmark (reference count and location) supporting the iterator.

The other post covers lock partitioning:

Shared access blocks exclusive acquire requests. The shared access needs only to acquire on the worker’s local partition. An attempt to acquire exclusive access is blocked by the shared access holders. Conversely to acquire exclusive access the worker must acquire the exclusive access of every partition. The exclusive acquisition path is longer than the shared path because N partitions must be acquired to achieve the exclusive access. Increasing the number of partitions increases the work required to acquire exclusive access. This means that only certain protection paths should use the partitioning approach.

Both get pretty deep into internals.

One Comment

  1. George Walkey
    George Walkey2022-02-22

    anyone have Bobs email?
    tired of getting pre-login handshake timeouts
    opened a support ticket and after 18 months, no resolution

Comments are closed.