count(*) locking a table?

Question

count(*) locking a table?

datsun

SSCrazy

Points: 2397
More actions
March 10, 2020 at 10:46 am
Go to Answer
#3732744

Hello,
Why would select count(*) from a table encounter a lock? The table in question had initial bulk insert (append records) and then update happens on those new records. In the meantime I queried the total rows - count(*) and it locked the whole thing. Eventually I had to kill the query with count(*). Did the same again after few minutes and again it locked.
Thanks,
Vinay
Phil Parkin

SSC Guru

Points: 246960
More actions
March 10, 2020 at 1:19 pm
Answer
#3732794
There are faster/better ways of obtaining total row counts. Here's one:
```
SELECT TotRows = SUM(row_count)
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('schemaname.tablename')
      AND index_id < 2
GROUP BY OBJECT_NAME(object_id);
```
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

I'd suggest next time you run a blocking report to understand why it's blocking. SELECT COUNT(*) has to do a scan, either of the table or an index. It's counting every row. Sure, that's a shared scan, but it can still be blocked by other processes (unclosed transactions, long running transactions, frequent transactions). Here, this could help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

datsun SSCrazy Points: 2397 More actions · Answer 2

Phil,

This one is good as it doesn't lock the table. I just tested a huge update on 30M records and while it's going on, the count using your method immediately gave me the count.

So, how does this count the rows as opposed to the full scan?

Thank you,

Vinay

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

It's the insert and update that are locking the table.

You can ignore these locks by using the nolock hint:

select count(*) 
  from mytable with (nolock)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Jonathan AC Roberts wrote:

It's the insert and update that are locking the table.
You can ignore these locks by using the nolock hint:
select count(*) 
  from mytable with (nolock)

BUT!!!

Know that using the NOLOCK hint you may get less than perfectly accurate information.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 5

Grant Fritchey wrote:

Jonathan AC Roberts wrote:
It's the insert and update that are locking the table.
You can ignore these locks by using the nolock hint:
select count(*) 
  from mytable with (nolock)
BUT!!!
Know that using the NOLOCK hint you may get less than perfectly accurate information.

If it's in the middle of an insert it will give you the current number of rows inserted so far, which can be useful information if you want to see how far through the insert it is. If you don't use nolock it will wait until the transaction has finished before it reads the count.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 6

Grant Fritchey wrote:

Jonathan AC Roberts wrote:
It's the insert and update that are locking the table.
You can ignore these locks by using the nolock hint:
select count(*) 
  from mytable with (nolock)
BUT!!!
Know that using the NOLOCK hint you may get less than perfectly accurate information.

Be aware that READ COMMITTED-level locking has similar issues. Both levels allow duplicate reads and phantom reads. The only difference is that dirty rows won't be read, which is not really so much of an issue for a count.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

Grant Fritchey wrote:

Jonathan AC Roberts wrote:
It's the insert and update that are locking the table.
You can ignore these locks by using the nolock hint:
select count(*) 
  from mytable with (nolock)
BUT!!!
Know that using the NOLOCK hint you may get less than perfectly accurate information.

Considering the flux of a table, the count may be out of date microseconds after the COUNT(*) completes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)