Shared locks taken under Read Committed Snapshot Isolation (RCSI)

Question

I am seeing waits for shared locks (LCK_M_S) when using RCSI. My understanding is that this isn't supposed to happen since SELECTs do not require shared locks when using RCSI.

How can I be seeing shared locks? Is it because of foreign keys?

Answer 1

How can I be seeing shared locks? Is it because of foreign keys?

Yes. SQL Server reverts to the locking implementation of the read committed isolation level when accessing a table for the purpose of validating foreign key constraints. This is required for correctness, and cannot be disabled.

The behaviour applies only to data-modification statements. Shared locks are only taken when checking the foreign-key related data. Other data access in the same execution plan can continue to use row versioning.

If SQL Server did not do this, data-modification statements under RCSI could end up violating the foreign key constraint because the integrity check used out-of-date (versioned) data.

Unfortunately, there is currently no supported way to see this change in locking behaviour in an execution plan. It is possible to see the internal locking hints when trace flag 8607 is active.