Tell me more ×
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.
up vote 5 down vote favorite
1

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?

Thanks

share|improve this question
Do you need an exact count (as of the request), or is an approximate count okay? – Jon Seigel Jan 2 at 19:24
actually, I only need to know if shared locks are being placed. I'm trying to investigate a deadlock issue. Thanks – grassbl8d Jan 2 at 21:21
My point was that if you can consider changing the way the count is obtained, that may be the way to solve the deadlock problem. But now that I read the question again, if you need to use a WHERE clause, the method I'm thinking of won't work anyway. – Jon Seigel Jan 2 at 21:54
thanks, yup, need to use where clause for the statement – grassbl8d Jan 2 at 22:03
You can grab approximate counts from metadata tables for a subset of the table if the where clause matches a filtered index. – Aaron Bertrand Feb 1 at 20:16

2 Answers

up vote 1 down vote

Be careful with READ_COMMITTED_SNAPSHOT: if you set it on, it can cause lots of subtle bugs.

Also READ_COMMITTED_SNAPSHOT is the default isolation level, which may be overridden by something. Run DBCC USEROPTIONS to determine the actual isolation level your select runs under.

I would explicitly SET TRANSACTION ISOLATION LEVEL SNAPSHOT right before your select. That way you will be sure your select never embraces in deadlocks, and you do not break any other code, like READ_COMMITTED_SNAPSHOT might.

share|improve this answer
up vote 0 down vote

The locking with Snapshot Isolation doesn't change. What changes is that when pages are being changed under you those pages are copied to the tempdb database so that you can read them from the tempdb database instead of from the normal database. (Yes this is a simplified version of what is happening.)

You mentioned that you don't have proper indexing in place, so you are doing a clustered index scan (or a table scan if it's a heap). That's potentially a lot of data to be moving to the tempdb database. If this query is something which will be run more than once I'd suggest adding the index to the table.

What isolation level is your query using?

share|improve this answer
I'm using read_commited_snapshot isolation level. I turned it on as i'm using the read_committed by default. I'm just interested if locks are being placed during select counts. Thanks, I've already placed indexes btw – grassbl8d Jan 2 at 21:22
Yes locks are still being taken. You can query sys.dm_tran_locks to see the locks that are being taken. Run your query in one window and query sys.dm_tran_locks in another window to see what locks are being taken. You might be escalating to table locks and need to use a hint to force page or even row level locks. – mrdenny♦ Jan 5 at 2:03

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.