Search code examples
sql-servert-sqldatabase-deadlocks

How is a deadlock possible on a SELECT

I was running a proc inside a cursor. After a lot of successful iterations, I got this:

Transaction (Process ID 104) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I am not posting the full details, so I don't expect a fine grained debugging answer. The facts:

  1. I am sure no one else (including myself in another session) was using the proc, as I was developing it and
  2. This transaction was "stuck" when doing a select (I saw the running query from dm exec requests)

If I am not mistaken on my 2 points, is it ever possible to have a deadlock? Wouldn't the deadlock require all of the involved users of a resource to be doing write operations on them, which would create a cycle in the resource request graph? I understand a timeout error in a select, but cannot understand a deadlock. What am I missing?

An update:

I abandoned further debugging because I noticed that an index I thought existed didn't. When it was created, the performance was OK.

However, in hopes to keep this useful and hopefully come up with an answer, here is some more things I investigated, some facts, and thoughts on comments:

First, the SQL Server version is 2008. I understand this is not supported. I am in no position to make recommendations, much less update the server though.

I found Jeroen Mostert's comment interesting. How much is "the past"? I noticed in sys.dm_os_waiting_tasks the session being blocked by itself multiple times with wait type CXPACKET. I did some searching around, but option(maxdop 1) did not solve the problem. However, remember the index that did not exist which would cause abysmal performance. Could it be that there was correct parallelism appended, but the operations were too many? Still, I also witnessed a huge dm_exec_requests.wait_time. So, even though the query was bad, I am led to believe that there were strange (dead)locks around.

If an answer/comment comes up with specific queries/steps to do to trace the problem, I'll be happy to recreate it.

Solution

  • It is possible for a SELECT to cause a deadlock if someone else is using the table.

    This example is ripped almost 100% from Brent Ozar's video on deadlocks, but changed one command to a SELECT.

    To start with, create two tables

    CREATE TABLE Lefty (ID int PRIMARY KEY)
CREATE TABLE Righty (ID int PRIMARY KEY)
INSERT INTO Lefty (ID)  VALUES (1)
INSERT INTO Righty (ID) VALUES (2)

    Then open two windows in SSMS. In the first put this code (don't run it yet)

    BEGIN TRAN
UPDATE Lefty SET ID = 5

SELECT * FROM Righty
COMMIT TRAN

    In the second window put in this code (also don't run it yet).

    BEGIN TRAN
UPDATE Righty SET ID = 5
UPDATE Lefty SET ID = 5
COMMIT TRAN

    Now, in the first window, run the first two commands (BEGIN TRAN AND UPDATE LEFTY). That starts.

    In the second window, run the whole transaction. It sits there waiting for your first window, and will wait forever.

    In the first window, go back and run the SELECT * FROM Righty and COMMIT TRAN. 5, 4, 3, 2, 1 Boom deadlock - because the second window already had a lock on the table and therefore the SELECT in the first window couldn't run (and the second window couldn't run because the first had a lock on a table it needed).

    (I'd like to reiterate - this is Brent Ozar's demo not mine! I'm just passing it on. Indeed, I recommend them).