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.