8

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?

  • may i also ask, does sql server place shared locks on records if there is no index even if read_committed_snapshot is turned on? – grassbl8d Apr 10 '13 at 13:00
12

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.

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.