3

I am curious: suppose that SQL Server (or any other database management system) receives a lot of read traffic on one table, such that at any point in time, at least one read lock is requested (and possibly granted). Now suppose that a write lock request comes along.

Is the write lock ever issued? Or will read locks always be granted as long as no write lock is granted, such that the write lock will never be granted? Are lock requests served in the order in which they arrive? Or is the write lock issued at some point in time that is harder to determine, via a more complicated priority scheme?

0
6

The details have changed a bit over the years, and continue to be tweaked from time to time. A reasonable summary that answers the broad point in the question can be found in SQL Server, Lock Manager, and “relaxed” FIFO by Microsoft's Bob Ward.

Locks are granted in a relaxed first-in, first-out (FIFO) fashion. Although the order is not strict FIFO, it preserves desirable properties such as avoiding starvation and works to reduce unnecessary deadlocks and blocking. New lock requests where the requestor does not yet own a lock on the resource become blocked if the requested mode is incompatible with the union of granted requests and the modes of pending requests. A conversion request becomes blocked only if the requested mode is incompatible with the union of all granted modes, excluding the mode in which the conversion request itself was originally granted.

The FIFO grant algorithm was significantly relaxed in SQL Server 2005 compared to SQL Server 2000. This relaxation affected requests that are compatible with all held modes and all pending modes. In these cases, the new lock could be granted immediately by passing any pending requests. Because it is compatible with all pending requests, the newly requested mode would not result in starvation. In SQL Server 2000, the new request would not be granted, because, under its stricter FIFO implementation, new requests could not be granted until all previously made requests had been granted.

Related reading (all by Bob Dorr – Principal Software Engineer SQL Server):


The strict FIFO was mostly re-instated for Sch-M/Sch-S locks. In SQL Server 2012, Sch-M WAIT blocks Sch-S in SQL Server 2014 but not SQL Server 2008 R2?, to prevent pending Sch-M locks from being starved, which was further modified by the introduction of low priority waits in SQL Server 2014.

2
  • Oooh, right. So under newer implementations, subsequent read requests will all be granted simultaneously, as long as no write request is still pending? Thanks! – Tempestas Ludi Jun 5 at 8:55
  • And the strict FIFO was mostly re-instated for Sch-M/Sch-S locks. In SQL Server 2012, dba.stackexchange.com/questions/73833/…, to prevent pending Sch-M locks from being starved, which was further modified by the introduction of low priority waits in SQL Server 2014. – David Browne - Microsoft Jun 6 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.