First published on MSDN on Jun 02, 2009
An interesting question came in from a customer a few weeks ago. Did SQL Server change its traditional FIFO method for granting locks in SQL Server 2005 and 2008?
Although there is no official documentation (not yet, I’ve put in a request to get this changed), the answer to the question is yes. SQL Server 2005 and future versions use a “relaxed” FIFO method.
You can find a few bits of information about this topic on the web:
http://blogs.msdn.com/weix/archive/2005/11/22/496000.aspx
– This is a post from from weix, a former developer in the engine team
http://www.informit.com/articles/article.aspx?p=686168&seqNum=5
– This is an excerpt from the book SQL Server 2005 Practical Troubleshooting. The chapter on Locking was written by Santeri Voutilainen (he goes by Santtu), a developer in the engine team who owned the Lock Manager in SQL Server 2005. In fact, if you look at the previous blog post, Wei is simply quoting Santtu on this behavior.
Rather than me trying to paraphrase what Santtu said, let me just quote him as I think it is an excellent description of the change in behavior from SQL Server 2000 to SQL Server 2005 and 2008.
In the book Santtu states the following:
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. A couple exceptions apply to these rules; these exceptions involve internal transactions that are marked as compatible with some other transaction. Requests by transactions that are compatible with another transaction exclude the modes held by the transactions with which they are compatible from the unions just described. The exclusion for compatible transactions means that it is possible to see what look like conflicting locks on the same resource (for example, two X locks held by different transactions).
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. In the following example, connections 1 and 3 would be granted when run against SQL Server 2005 in the specified order. In SQL Server 2000, only connection 1 would be granted:
/* Conn 1 */
begin tran
exec sp_getapplock 'amalgam-demo', 'IntentExclusive'
/* Conn 2 */
begin tran
exec sp_getapplock 'amalgam-demo', 'Shared'
/* Conn 3 */
begin tran
exec sp_getapplock 'amalgam-demo', 'IntentShare
So I’ve requested that we add information into our product documentation to show this example and talk about the change in this behavior for SQL Server 2005, 2008, and future releases
Bob Ward
Microsoft
