Results 1 to 8 of 8

Thread: Global NOLOCK

  1. #1
    Join Date
    May 2009
    Location
    Brooksville, FL
    Posts
    3

    Question Unanswered: Global NOLOCK

    Is there a setting within SQL Server 2008 to specify ALL SELECTS as NOLOCK? If so, how do I get to it?

    I don't mind a "dirty read" on my SELECTS only. This is a website that has absolutely no reason to lock the tables during a SELECT.

    So, instead of going through all 300+ stored procedures and add NOLOCK hints, I would like to know if I can specify it at the server level? database level? etc.

    Thank you very much. I am anxiously anticipating a quick response and appreciate it in advance.

  2. #2
    Join Date
    Mar 2009
    Posts
    349
    i could have sworn there was a way to SET the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED at the database level, but for the the life of me I can not remember how. I no longer DBA.

    If you only have select statements, setting your database to read only does the trick.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by TWDO
    Is there a setting within SQL Server 2008 to specify ALL SELECTS as NOLOCK? If so, how do I get to it?

    I don't mind a "dirty read" on my SELECTS only. This is a website that has absolutely no reason to lock the tables during a SELECT.
    If you are on SQL Server 2008 you should be able to change the database to "snapshot isolation".

    That should get rid of those stupid read locks.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Of course, you could just tune the SQL so that they use indexes, and cause less contention in the first place. Just sayin'.

  5. #5
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    also reading NOLOCK can simply fail if pages split, etc, during the read.

  6. #6
    Join Date
    May 2009
    Location
    Brooksville, FL
    Posts
    3
    I know that there are other ways to achieve a portion of my goal, with indexes, etc. I do NOT want to create any more indexes than I already have on this database, period. That is not an option.

    I know that some of you actually think NOLOCK is an evil thing. I just want to know if it possible to set all SELECT statements at either the server scope or the database scope to automatically, by default, to use WITH (NOLOCK) hints. And, if so, how can I get to and change that setting?

    If you don't know and just want to tell me that I should be doing this or that, I honestly appreciate it, but it doesn't help me achieve the task at hand.

    Thanks again in advance. Someone out there HAS to know this one and be able to point me in the right direction. Opening all stored procedures and adding (NOLOCK) to every FROM and JOIN would be a multiple day task. I'm just trying to save myself some time as well as my company.

  7. #7
    Join Date
    Mar 2009
    Posts
    349
    FYI, usually when people get snitty here, they get ignored.

    ok Mr Sr Software developer. I threw you a couple of hints.

    You dio not have to use NOLOCK. You can start your procs with...

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    I do not think there is a DB or server level setting. I could be in error, but I did a quick GOOGLE and Books Online check and I am fairly confident there is not one.

    Now get lost.

  8. #8
    Join Date
    May 2009
    Location
    Brooksville, FL
    Posts
    3
    I wasn't trying to be snitty/snotty at all, what-so-ever. I was just trying to get my point across, nice and clear. That way, no one sent me down other avenues or paths that did not pertain to the question at hand. If I offended anyone for being direct and to the point, I apologize. I appreciate you getting back to me promptly and for the information that you gave me.

    On the same note, you complained about me being "snitty", which I wasn't trying to be at all, but in return you are the same way to me? I completely understand where you're coming from, but if you are going to complain about someone's attitude, wouldn't it make more sense not to be that way back and be a hypocrite?

    I honestly wasn't trying to be a jerk. I just wanted to be blunt, direct, and receive responses about the exact issue that I had in question. Thanks again for pointing me in the right direction. If anyone else has more information, I would appreciate it.

Posting Permissions

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