Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm getting LCK_M_S on read_committed_snapshot isolation. From my understanding, this isn't supposed to happen since my selects are not placing shared locks anymore.

From my logs, I can see that my database are blocking on select statements, why am I getting this?

What is the possibility that I get LCK_M_S on read_committed snapshot? Is this because of foreign keys?

Also does select place shared locks as well during foreign key validation on read committed snapshot isolation?

Thanks! Carlo

share|improve this question
    
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
    
I'm also seeing this behaviour with SQL Server 2008: a transaction in READ UNCOMMITTED isolation level is taking on a LCK_M_S Shared lock on the target table of a foreign key that has been set up on a table that the transaction is writing to. This seems to contradict the MS docs that say that READ UNCOMMITTED transactions never take shared locks: technet.microsoft.com/en-us/library/ms173763.aspx. The question at dba.stackexchange.com/questions/6905/… links to a now-dead MS blog post confirming this observation. –  Max Bolingbroke Dec 12 '13 at 21:42
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.