In MSSQL / Azure SQL, with Snapshots allowed and Read Committed Snapshot Isolation enabled, i have an application that does not use snapshot transactions explicitly (using the default RCSI everywhere). I have a delete statement that takes long time to run due to an suboptimal query plan that is selecting millions of rows to update about 3k rows in an indexed view.

When i try to run this delete with snapshot isolation in SSMS, queries start to pile up immediately as if i've placed table locks on all tables touched by the indexed view. From what i've read, snapshot transactions should not block other transactions but i couldn't find any indication what happens if you mix DML snapshot and RCSI transactions.

EDIT: sessions are waiting on these locks:

lock waits

EDIT 2: the blocker (snapshot) session is holding a few thousand IS locks on pages and these blocker session locks

  • As I understand you correctly you don't know who is blocking who? So, you cannot detect the blocking and the blocked session? – Darko Martinovic Oct 2 at 11:35
  • I can see that a lot of DML queries start to become blocked against my snapshot transaction according to sp_BlitzWho. I just don't know why that would happen, given that it is snapshot. – Suchiman Oct 2 at 11:36
  • 2
    >>>I can see that a lot of DML queries start to become blocked <<< Can you update your question with the request_mode and resource_type of waiting sessions? – sepupic Oct 2 at 12:29
  • @sepupic thanks, i've added the lock waits of the blocked sessions. – Suchiman Oct 2 at 12:57
  • From what you posted the queries shown waits for S-locks on the keys that already have X on them. Are you sure thay RCSI is enabled on this database? Readers in RCSI don't ask for S-locks – sepupic Oct 2 at 13:39

Your Answer

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

Browse other questions tagged or ask your own question.