0

This is using SQL server 2014 SP2 (it was true in SP1 as well).

The database is configured to have Snapshot Isolation on and Is Read Committed Snapshot Isolation is set to true. The processes accessing the database do not set their isolation level, so they are defaulting to read committed.

I have a process that is modifying a specific table by deleting values (80,000 or so on average) from it and then inserting new ones, in a transaction.

I have an application that reads this same table, and only this table, in the database in Read Committed Snapshot Isolation.

The read is blocked with a LCK_M_IS. The Activity monitor shows that it is blocking on Object ID 1171535257 which is this table. The lock in the table is exclusive (Lock Mode X).

There is no foreign key on the table to explain the lock.

Can I prevent the Read process from issuing the LCK_M_IS or otherwise blocking, and have it read the committed data instead?

In a previous version of the question, an index was suspected as the culprit. Further testing has shown that even without any indexes this behavior is observed.

Improve this question
7
  • How many rows are getting deleted on average in the transaction?
    – Antoine Hernandez
    Aug 30, 2016 at 18:34
  • @AntoineHernandez, around 80,000 or so, although the problem was observed at around 65,000
    – Yishai
    Aug 30, 2016 at 18:37
  • 1
    Does the process run more than once concurrently? Are you sure the table read is the head blocker and not the delete/insert process? It's possible SQL Server is doing lock escalation based on amount of rows getting deleted and that could still block a read. Read Committed Snapshot Isolation does not prevent blocking but can reduce it.
    – Antoine Hernandez
    Aug 30, 2016 at 20:07
  • @AntoineHernandez, Either process could run more than once concurrently, although I don't think it was in this case. It is quite possible the delete/insert is the head blocker. What escalation level blocks a RCSI read?
    – Yishai
    Aug 30, 2016 at 21:34
  • Turns out there is a connection pooling problem where the isolation is not reset.
    – Yishai
    Sep 6, 2016 at 19:14

1 Answer 1

Reset to default
1

Are you doing online rebuilds of this index at the same time as the transaction? I have RCSI block if there is an index rebuild simultaneous with a large or long running transaction. Paul Randal has a great explanation here: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-830-unicorns-rainbows-and-online-index-operations/

Improve this answer
1
  • No, there is no online rebuild of the index.
    – Yishai
    Aug 30, 2016 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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