Our system has randomly started deadlocking in areas where it previously had not, and an analysis of the deadlock graph looks like it should not even be happening to begin with. Here is a screenshot of the deadlock graph.
For reference: The left node is a basic insert into the
sync_entities table, where the right node is:
set A=B, C=D
from sync_entities e
join sync_revisions r on e.id=r.entity_id
where r.id in (<some very large list of IDs>)
Based on my understanding of lock compatibility, requesting an S lock when an IX lock exists would block only if Read Committed Snapshot is off, but it is turned on for this database, and has been for a very long time. Am I missing something?
The deadlock XML says that both processes were using isolationlevel="read committed (2)".