Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    South of France
    Posts
    11

    Unanswered: SQL SERVER 2000 SELECT waiting and locks

    Is anyone have already encountered such a problem :

    When a table is locked by several locks (key, pag), either a SELECT request which does not need to lock any row cannot succeed.

    In SQL Server Enterprise Manager, I see that my request is waiting for a lock "LCK_M_S" !!!

    How I can avoid this kind of situation which seems to me completely abnormal.

    I have never been faced with this kind of problem in Oracle.

    Thanks for anyone who will help me to resolve this "bug".
    ALaupenie

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Any SELECT against a table needs to have a lock in order to protect against concurency problems. This is one of the bad habits that Oracle users often acquire, since Oracle's default behavior is to permit non-repeatable reads.

    If you truly have a SELECT that requires no locks (doesn't include any tables or table-valued functions), then SQL Server won't take any locks. If you want SQL Server to behave like Oracle does, you can turn off locking at either the table or the session (spid) level.

    This is dangerous behavior, and it is not recommended!

    -PatP

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A recent thread dealt with this topic in some detail, you might want to read it too.

    -PatP

  4. #4
    Join Date
    Sep 2003
    Location
    South of France
    Posts
    11

    Thanks for a so clear response

    Hi PatP,

    Thanks for your explanation which is very clear and detailed.

    Now, I have to choose the least bad solution.

    Best regards
    ALaupenie

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by LAUPENIE
    Now, I have to choose the least bad solution.
    That sounds like the story of my life!

    I wish that I had better news, but I'm glad that I was able to explain things so you can make the choice that suits your needs best.

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