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