Post reply

Does (nolock) work the same on views?

  • October 7, 2009 at 11:55 pm

    #211866

    Hi,

    Just wondering if I have an indexed view and perform a query like this:

    select * from MyIndexedView (nolock) where state = 'CA'

    would this mean that the (nolock) will work on the underlying tables or should I better put (nolock) into the view definition?

    Thanks.

  • October 8, 2009 at 1:10 am

    #1063250

    You should get rid of the NOLOCK hint, both from the view and the query.

    NOLOCK could return inconsitent results (eg. when a page spilt is involved).

    You can use it safely if you're querying data on the fly from SSMS, but simply don't use it in the code.

    -- Gianluca Sartori

  • October 8, 2009 at 1:35 am

    #1063256

    This is the best illustration i've found on the perils of nolock

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


    Clear Sky SQL
    My Blog[/url]

  • October 12, 2009 at 9:14 pm

    #1065016

    My data is pretty static, so I am not worried about dirty reads and other evils of (nolock), but want to use it to my advantage.

    So, back to my question, how do I make sure that the underlying tables for the view do not get locked?

    Thanks.

  • October 12, 2009 at 9:39 pm

    #1065018

    Gianluca Sartori (10/8/2009)

    You should get rid of the NOLOCK hint, both from the view and the query.

    NOLOCK could return inconsitent results (eg. when a page spilt is involved).

    You can use it safely if you're querying data on the fly from SSMS, but simply don't use it in the code.

    I largely agree, but there are valid reasons to use NOLOCK, but you need to keep in mind whether your data is likely to change while you are reading it.

    You do want to be careful though..

    CEWII

  • October 13, 2009 at 1:29 am

    #1065060

    Roust_m (10/12/2009)

    My data is pretty static, so I am not worried about dirty reads and other evils of (nolock), but want to use it to my advantage.

    So, back to my question, how do I make sure that the underlying tables for the view do not get locked?

    Thanks.

    You can simply try yourself:

    create table testTable ( i int)

    go

    create view testView

    as

    select * from testTable

    go

    begin transaction

    insert into testTable values (1)

    insert into testTable values (2)

    insert into testTable values (3)

    Open a new query and run:

    select * from testView with(nolock)

    As you can see, no locks used to access the underlying tables.

    -- Gianluca Sartori

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

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