Post reply

Automation of WITH(NOLOCK) Possible?

  • October 9, 2009 at 7:09 am

    #216156

    Hi all,

    I'm just wondering if there's any kind of option that can be set at the top of a stored procedure so that all tables are used with the WITH(NOLOCK) option ... as opposed to having to remember to type it with every tablename?

    Thanks!

  • October 9, 2009 at 7:21 am

    #1064038

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    should do it... as long as you understand the potential pitfalls of doing this!!

  • October 9, 2009 at 7:21 am

    #1064039

    check out BOL for transaction isolation levels , you'll want READ UNCOMMITTED

    BUT

    here's a good example of that being a very bad idea

    http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx


    Clear Sky SQL
    My Blog[/url]

  • October 9, 2009 at 9:03 am

    #1064137

    hypermommy (10/9/2009)

    I'm just wondering if there's any kind of option that can be set at the top of a stored procedure so that all tables are used with the WITH(NOLOCK) option ... as opposed to having to remember to type it with every tablename?

    You're happy with the possibility of every one of your queries occasionally returning returning incorrect information, perhaps reading rows multiple times, maybe not reading certain rows at all?

    NOLOCK means to SQL Server "Get the data regardless of anyone else reading it, I don't mind if it's inaccurate"

    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

Viewing 4 posts - 1 through 4 (of 4 total)

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