Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    229

    Unanswered: Still getting blocked despite WITH (NOLOCK)

    I'm running a heavy SELECT query using WITH (NOLOCK). This still causes other processes trying to INSERT in one of the tables to get blocked.
    I thought the locking hint would prevent from blocking other processes?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Check to see what object(s) you are blocking on... My guess is that you're looking at the wrong object!

    -PatP

  3. #3
    Join Date
    Jul 2002
    Posts
    229
    But if I'm using WITH (NOLOCK), no objects used by the query should get locked, or have I misunderstood things?

  4. #4
    Join Date
    Jul 2002
    Posts
    229
    Hmmm... I should have put a locking hint on the subquery as well... I forgot the subquery.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Usually once you've eliminated all of the possible sources of conflict, you can start working on the impossible ones.

    There's often a temp table or system table at the root of a blocking query that uses NOLOCK, although subqueries can byte you too!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •