Results 1 to 4 of 4

Thread: NOLOCK on views

  1. #1
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280

    Unanswered: NOLOCK on views

    Hey guys,

    I came across a SQL statement, thought up by a developer, in which two views were joined with the NOLOCK hint:
    Code:
    SELECT v1.xxx, v2.yyy
    FROM dbo.vw_SomeView  v1 WITH (NOLOCK)
       INNER JOIN dbo.vw_SomeOtherView WITH (NOLOCK) ON v1.id = v2.id
    The views are not created the NOLOCK hint. So my question is: has the NOLOCK hint any effect here?

    I've looked in the BOL and searched on the net but can't find anything on this particular topic.

    Lex

    PS. Personally I don't like to use views in JOINs. I've seen too many cases in which tables are joined twice just because they are part of both views. Further more I don't like the "random" use of NOLOCK because most people don't seem to understand the implications of it. But this is besides the point of my question

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Looks like it's time for a little hands on experiment. Take an update lock on one of the tables used in either of the views in one QA window, and try to run the sql in another.

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Nov 2005
    Posts
    4

    NOLOCK Hints on Views

    Quote Originally Posted by tomh53
    Looks like it's time for a little hands on experiment. Take an update lock on one of the tables used in either of the views in one QA window, and try to run the sql in another.
    I use and recommend (NOLOCK) Optimizer hints on a regular basis. Just know that when a (NOLOCK) hint is used, it performs a "Dirty Read" against the data.

    The primary benefit to a (NOLOCK) hint is to prevent the blocking of objects from occurring when users are selecting data. I would recommend using them if you have contention in your environment with users holding exclusive locks on tables.

    Hope this helps!

  4. #4
    Join Date
    Jun 2011
    Posts
    1

    In case someone else wants to know. . .

    NOLOCK on a view DOES cause a dirty read through the view even if the underlying view does not use NOLOCK.

Posting Permissions

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