2

I have SQL Server database that keeps getting deadlocked when two queries attempt to acquire locks they already own to the same table. deadlock graph The above deadlock graph get's me very confused. Both PageLock are identical, which leads me to believe they are the same page. It seems to show that both processes already own locks to that page. The one on the left owns an Update (U) lock, while the one on the right owns and Intent Exclusive (IX) lock.

How is that even possible?

And why would they get blocked on acquiring locks they already own?

The query for the process on the left is: left query

The query for process on the right is: right query

2
  • 4
    Were any other statements in the same transaction of either statement? Please show the table and indexes definitions, and the XDL deadlock graph (as text), and please share the query plans for both queries (the whole SQL batch) using pastetheplan.com. We cannot diagnose this deadlock otherwise Jul 29, 2022 at 19:11
  • Is sellerId nvarchar(6) or varchar(50)? I notice the update uses the former, while the insert uses the latter. Even if you already have the index Charlieface mentioned, if the update was using the wrong datatype it could have made pretty "interesting" decisions on what to do to locate the row(s) to modify.
    – GMassDBA
    Aug 1, 2022 at 5:00

1 Answer 1

3

Ideally we need to see the full XDL graph as well as the query plan to know for sure. Note that the Page Locks are not identical, they have different Page IDs.


But most likely what is going on here is as follows:

  • The INSERT query is unlikely to be the culprit, because it's a simple single-row insert with no joins (although it is theoretically possible that an indexed view or a foreign key Assert could cause the deadlock)
  • The UPDATE query probably does not have a supporting index. This would cause it to scan the whole table, unnecessarily locking a large number of rows, and creating more opportunities for deadlocks.
  • The actual cause of the deadlock would then be that the UPDATE scans the table in one order, but the INSERT is going in a different order.

To solve this, you need to add an index to support the UPDATE. The most likely index is probably the following, but note that I don't know your table structure nor what other queries are going on, so a different index may prove better.

CREATE INDEX IX ON InventoryTotals
  (SellerID, GlobalOfferID) INCLUDE (OfferStatus);

The reason for this particular index is that the update looks at a single SellerID value, but GlobalOfferID seems to be an IN so it's probably multiple values. OfferStatus also needs to be there to avoid a key lookup, but it doesn't have to be a key column, it can be an INCLUDE.

Now the UPDATE will access the exact key it needs, and place a U-lock and then an X-lock only on that, so you should now avoid a deadlock as the access will always be in the correct order.


I note one final thing: it appears that the two statements have differently sized varchar parameters. This indicates you are relying on your driver to get the right data type and size, such as with SqlClient's AddWithValue. Never do this: always specify the data type and length/precision for each parameter.

1
  • This was so interesting: To solve this, you need to add an index to support the UPDATE. In my special case, I was updating table in a transaction and parallelism caused deadlock for me. My specific business model and table design allowed me to select rows to be updated before transaction execution so I selected ID of rows to be updated before transaction and then update rows by ID (which primary keys have index by default) and the deadlock situation remediated, hopefully.
    – VSB
    Sep 5 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.