Livelocks - Do they exist?

I have been involved in an interesting discussion over the last couple of days around livelocks, and whether or not they exist in SQL Server. The question arose because somebody had been asked the difference between a deadlock and a livelock in a job interview and had not been able to answer the question, and in fact, if you Google livelocks in SQL Server, 90% of the results are sites offering standard SQL Server interview questions.

So first off, what is a livelock? Basically, it is where a resource has an exclusive lock is blocked by a shared lock, but then another shared lock is granted before the exclusive lock, and then another, and another, and so on, meaning that the exclusive lock is never granted.

But do they exist? Well, my initial reaction was no, because SQL's lock manager queues lock requests and grants them in order, and there are not any scenarios where locks can "jump the queue" but I never like to state something as a fact unless I can prove it, and it is very difficult to prove a negative.

However, I though that I would do my best to replicate this behavior, and see what happened. So the first thing I did was create a table and populate it with the following script...

CREATE TABLE livelock (col1 int)
INSERT INTO livelock
VALUES(1)
GO 10000

...The next step was to try and create a live lock, so I opened three query windows and in the first, I ran the following query...

BEGIN TRANSACTION
SELECT col1
FROM livelock WITH (TABLOCK, HOLDLOCK)

...I used TABLOCK to reduce the number of locks, to make it easier to monitor and HOLDLOCK to create a queue.

Then in the second windows I ran...

BEGIN TRANSACTION
DELETE FROM livelock
COMMIT TRANSACTION

...And then in the third I ran the original query...

BEGIN TRANSACTION
SELECT col1
FROM livelock WITH (TABLOCK, HOLDLOCK)

...Now, in a separate query window, I ran a query against the DMV sys.dm_tran_locks, which looks at lock manager, and lets you see the lock waits in progress...

resource_type   resource_associated_entity_id    request_mode     request_type request_status
OBJECT           1927677915                                S                          LOCK GRANT
OBJECT           1927677915                                IX                        LOCK WAIT
OBJECT           1927677915                                S                          LOCK WAIT

...Following this, MVP Tibor Karaszi confirmed that LiveLocks had been removed back in version 6 of SQL Server, so in this context, they no longer exist.

This was where I was planning to end the post, but whilst writing this article, I stumbled across this post by Bob Ward at Microsoft, which talks about the relaxing of the strict queuing within SQL Server from 2005 onwards. However, it also mentions that the logic has been designed to avoid starvation.

So in conclusion, SQL has logic to avoid Livelocks. It may be possible to force one to happen, but fundamentally they are very unlikely to occur.

Find my book, Pro SQL Server Administration on Amazon -

America

United Kingdom