1

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.

enter image description here

For reference: The left node is a basic insert into the sync_entities table, where the right node is:

update sync_entities 
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)".

2

Read Committed Snapshot don't affect the lock compatibility matrix.

For reads it means that shared locks aren't acquired, and reads will use row versioning instead. Assuming you are using read committed, that is, since this is the only isolation level that is "reconfigured" by the Read Committed Snapshot database knob.

You evidently have shared locks, and such will block/be blocked by IX locks, according to the log compatibility matrix at https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105)?redirectedfrom=MSDN.

So the question for you to investigate is why you have shared locks. Perhaps some parts of your app don't use Read Committed?

Paul mentions in a different thread that S locks are used for FK validations when you modify data in RCSI, which can be an explanation for the S locks:

Shared locks taken under Read Committed Snapshot Isolation (RCSI)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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