I seem to be getting a lot of deadlocks when doing select count(*) on a particular table. I have already changed all the required parameters and made them into row only locking.
I've also changed the database to use READ_COMMITTED_SNAPSHOT isolation,
however, it seems that using a select count(*) where column = ? on the table triggers deadlocks or locks on the table..
Am I correct that the select count(*) should only be accessing intermediate rows?, however, it doesn't seem that way and I'm still encountering deadlocks. Proper indexing would probably help,
The question is: Does SQL server 2008 R2 place shared lock on table during select count(*) even when read_committed_snapshot is set to on?