Strange Behavior with (nolock)

  • First post here so hope I don't come of as too newb with this.

    I have a table on a MSSQL 2000 with about 4700 rows.

    My query:

    SELECT * FROM Product with (nolock);

    only comes back with about 250 rows, sometimes slightly less or more. This query also takes awhile to complete. Longer than these:

    SELECT colname FROM Product with (nolock);

    SELECT * FROM Product;

    which come back with the correct number of records.

    I'm a little mystified. What is it about the combination of * and NOLOCK that causes this?

    Nothing unusual stands out to me about the table design. It is a clustered index with a few non-clustered indexes on non-key columns. DBCC SHOWCONTIG(Product) looks reasonable.

    Anyone have any ideas? or next steps to investigate?

    **EDIT - symptoms changed from original description. See my next post.

  • Nolock means 'with potentially incorrect data', not usually this incorrect, but you should expect to not always get correct results when you use nolock. It's why it's not recommended for general usage.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • josh 92884 (10/21/2013)


    First post here so hope I don't come of as too newb with this.

    I have a table on a MSSQL 2000 with about 4700 rows.

    My query:

    SELECT * FROM Product with (nolock);

    only comes back with about 250 rows, sometimes slightly less or more. This query also takes awhile to complete. Longer than these:

    SELECT colname FROM Product with (nolock);

    SELECT * FROM Product;

    which come back with the correct number of records.

    I'm a little mystified. What is it about the combination of * and NOLOCK that causes this?

    Nothing unusual stands out to me about the table design. It is a clustered index with a few non-clustered indexes on non-key columns. DBCC SHOWCONTIG(Product) looks reasonable.

    Anyone have any ideas? or next steps to investigate?

    I've never seen WITH(NOLOCK) cause such a large disparity in the number of rows like that before. Any clues in the execution plan?

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

  • Nothing groundbreaking in the plan. It shows a Clustered Index Scan -> Compute Scalar (I think it's resolving *)-> SELECT.

    The Clustered Index Scan get 100% of cost. Estimated rows is correct.

    One of the symptoms has changed. The following query is also short records now:

    SELECT * FROM Product;

    So possibly this problem is to do with the * wildcard? or one of the columns in the table?

  • DBCC CheckDB (<database name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

    I'd still remove the nolocks, they definitely can cause you problems, by design they can result in incorrect data. As I said, not usually this incorrect though.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • thanks Gila

    CHECKDB found 0 errors and repaired 0 errors. Took about 32 minutes on a 77GB db with reasonably modern hardware.

    I hear you on NOLOCK. Based on what I'm reading about dirty reads, it's not going to be worth using.

    I spread this issue around to some devs on my team. They aren't having any problems at all like I am.... :crazy:

    Are my tools borked? I'm going to re-install...

  • josh 92884 (10/22/2013)


    CHECKDB found 0 errors and repaired 0 errors.

    :sick: I didn't say run it with a repair option...

    I spread this issue around to some devs on my team. They aren't having any problems at all like I am.... :crazy:

    Sure you're looking at the correct table in the correct database on the correct server?

    Are my tools borked? I'm going to re-install...

    No.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (10/22/2013)


    josh 92884 (10/22/2013)Sure you're looking at the correct table in the correct database on the correct server?

    And the correct schema.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • GilaMonster (10/22/2013)


    josh 92884 (10/22/2013)


    CHECKDB found 0 errors and repaired 0 errors.

    :sick: I didn't say run it with a repair option...

    I ran:

    DBCC CheckDB (mydbname) WITH NO_INFOMSGS, ALL_ERRORMSGS

    The results I saw are what showed up in the event log. Query Analyzer came back with 'The command(s) completed successfully'

    I spread this issue around to some devs on my team. They aren't having any problems at all like I am.... :crazy:

    Sure you're looking at the correct table in the correct database on the correct server?

    It's the only DB on that instance. This particular table is only found in the dbo schema. I've re-confirmed everyone is using the same server.

    Are my tools borked? I'm going to re-install...

    No.

    I'll hold off for now. I know this is truly bizarre and I appreciate all the input so far.

  • GilaMonster (10/22/2013)


    DBCC CheckDB (<database name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

    I'd still remove the nolocks, they definitely can cause you problems, by design they can result in incorrect data. As I said, not usually this incorrect though.

    Heh... to be sure, I absolutely agree. I wasn't suggesting to leave the nolocks in place. I've just never seen such a large problem with it before.

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

  • I suggest running this to see the count with and without the NOLOCK to see if it is even related to the NOLOCK hint:

    declare @st datetime;

    set @st = getdate()

    SELECT count(*) FROM dbo.Product with (nolock);

    select Elapsed_Milliseconds = datediff(ms,@st,getdate())

    set @st = getdate()

    SELECT count(*) FROM dbo.Product;

    select Elapsed_Milliseconds = datediff(ms,@st,getdate())

Viewing 11 posts - 1 through 10 (of 10 total)

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