1

I was running a proc inside a cursor. After a lot of succesful 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 grain debugging answer. The facts:

  1. I am sure noone 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.

George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • 3
    A `SELECT` still requires a lock on a table, and for the table to *not* be locked so that it returns the correct data to the application/presentation layer. When you ignore locks (and do silly things like spam the `NOLOCK` hint) then you start getting all kinds of erroneous data. – Thom A Sep 29 '20 at 12:33
  • The lock is shared, though, right? So either my proc will wait because another is writing the table, or the other will wait before writing because my proc is reading. One or the other will wait for the common resource, but there are no cycles in that. I am not using nolock btw. – George Menoutis Sep 29 '20 at 12:57
  • 1
    *"The lock is shared, though, right"* Not if it's a DML statement no, and lock sharing, unless I recall incorrectly, is an enterprise feature. – Thom A Sep 29 '20 at 12:59
  • But it is not a DML statement. It's a `select`. That's what I wonder about. – George Menoutis Sep 29 '20 at 13:03
  • 1
    A `SELECT` can't share a lock with a DML statement is what I am saying. – Thom A Sep 29 '20 at 13:06
  • 2
    You say nobody else was using the proc - but was anyone else using the same table(s)? – seanb Sep 29 '20 at 13:07
  • Note that in the past, there have been cases of intra-query deadlocks -- a query deadlocking on its own execution due to the engine incorrectly taking locks (still with the message "deadlocked with another process"), typically when parallelism is involved. These are genuine bugs, though, not the norm. – Jeroen Mostert Sep 29 '20 at 13:33

1 Answers1

6

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).

seanb
  • 6,272
  • 2
  • 4
  • 22
  • More precision on the occurrence of deadlocks : 1) can occur with a read, 2) must have almost one write or DDL query (ALTER, DROP...), 3) the probability increases with parallelism, 4) the probability decreases with a good index plan 5) the probability increases with duration of the transaction – SQLpro Sep 29 '20 at 14:36
  • and finally is avoided when SELECT occurs in optimistic mode, but can results in a loss of the transaction updates – SQLpro Sep 29 '20 at 14:42
  • 1
    I cannot accept this answer as, although the running "lefty" query is a `select`, it is enclosed within a non-resolved transaction that includes an update, which is distinctly not my case. – George Menoutis Sep 29 '20 at 15:06
  • SQL operations including `SELECT` are normally done in a "transaction," even when this is being done behind-the-scenes. A deadlock is possible in any situation. – Mike Robinson Sep 29 '20 at 15:19
  • If you don't do the implicit transactions thing, the transaction includes only the `select` itself. No dml will "creep" in there. – George Menoutis Sep 29 '20 at 15:20
  • Well, it IS inserting on a single # table through an insert exec(@txt). However, I get the same plan (didnt wait for the error as it takes 50min) if I run just the `select` as i grab it from printing the dynamic query. – George Menoutis Sep 29 '20 at 15:52
  • Actually - I was thinking that the deadlock message isn't the normal one. If you search for the message you get info that it is related to the exchange operators in parallelism e.g., https://stackoverflow.com/questions/18996157/what-does-lock-communication-buffer-resources-mean – seanb Sep 29 '20 at 15:53