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.
Best Answer
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/