Monday, February 21, 2022

How It Works: SQL Server Lock Iteration / Enumeration

Moved from:


When executing a query to enumerate the locks, such as select * from sys.dm_tran_locks, how does SQL Server scan the locks and avoid impacting the overall concurrency?

I recently posted on aspects of the SQL Server Lock Manager and found the iteration of the locks interesting as I was stepping in the code.

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.


Lock compatibility matrix:

  • The lock manager is made up of a hash table where each bucket holds zero or more Locks.
  • Each Lock can have one or more Lock Owners associated with the queues. 
  • The iterator is a stop-and-go iterator completing the scan by:

Looping over each bucket
    —- For each lock found in the bucket lopping over the lock’s queues
          —-  For each queue looping over the lock owners




  • A Lock object is made up of a set of queues (grant, waiting, conversion, …)
  • To obtain a lock a transaction places a LockOwner structure in the appropriate queue.   If granted the LockOwner goes into a grant queue, if blocked the LockOwner is placed the waiting queue and so forth.
  • Iteration key: It is more complicated than this (background gc capabilities) but for this discussion focus on the fact that the Lock object is kept alive in the hash table as long as one lock owner is present.

The following three sessions are used to illustrate the lock iteration behavior.   Each of the sessions has performed a ‘use test’ and acquired the same shared database lock.




The hash bucket contains a Lock, which holds the three granted lock owners.



Let’s assume that lock iterator has already located #53 and returned the information to the caller.  The Lock now includes the NL marker from this iterator.  (Multiple iterators == each iterator has its own NL marker.)




The iterator always goes through WakeUp, Gather Data and GoDormant states To make sure the iterator does not hold a spinlock or other synchronization primitive while the data is consumed (perhaps by a slow fetching TDS client from sys.dm_tran_locks) the iterator always achieves the dormant state before returning the lock data/information to the caller. 

Now that you have an idea of the basics behind the iterator let’s walk through the gathering of a row from the iterator.



The iterator first performs Wakeup using the NL marker as the restart position.  The covering hash bucket spinlock is acquired (this prevents changes to the Lock and the iterator only holds the spinlock to walk from the current marker to the next Lock Owner.)  The next Lock Owner in the example will be Lock Owner #83.   As soon as the iterator walks from the #100 marker to #83 the spinlock is released.   By keeping the marker present in the Lock the Lock remains (reference counted) so it cannot be deleted.  Lock Owner #83 is also marked as in-use so it can’t be deleted until the temporary, iterator reference is released.


Gather Data

Data about Lock Owner #83 is gathered into local structures (lock type, wait time, owner session, etc…)  This data becomes what is returned to the caller, describing the lock request.   During the gather data phase other workers are free to use the hash bucket spinlock and perform other locking activities.  The Lock Owner #83 remains referenced by the iterator while the data is gathered so other activity can continue.



Once the data is gathered the marker is moved to the position after #83 (positioning the marker for the next Wakeup call.)  GoDormant acquires the spinlock on the bucket moves the marker, releases the reference to Lock Owner #83, and releases the spinlock.

The lock iterator repeats Wakeup, Gather Data and GoDormant to produce the lock information.  The marker maintains the stability of the lock and the restart position for the iterator while allowing other workers to continue working, limiting the impact on concurrency.

Posted at