count(*) locking a table?

  • 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

  • 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

  • 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

  • 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

     

  • 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)
  • 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

  • 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.

  • 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".

  • 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)

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

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