SQL Server locking behaviour on repeated reads and one write

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply