Why would a SQL Server query request the same lock twice and deadlock?

All we need is an easy explanation of the problem, so here it is.

I have SQL Server database that keeps getting deadlocked when two queries attempt to acquire locks they already own to the same table.
Why would a SQL Server query request the same lock twice and deadlock?
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:
Why would a SQL Server query request the same lock twice and deadlock?

The query for process on the right is:
Why would a SQL Server query request the same lock twice and deadlock?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply