Post reply

with (nolock)

  • November 5, 2008 at 3:25 pm

    #92813

    we have a transactional database & we report directly out of transactional tables via views (derived fields & directly fields).

    But, I'm worried whether there would be any concurrency issues while the users are trying to enter data while reports are run by other users.

    I was thinking if "with (nolock)" can be used to read data inside the view & whether this is a good db design?

    Please help.

  • November 5, 2008 at 3:48 pm

    #894564

    You should make sure that the "reports" are not scanning ( appropriate indexes in place)

    You "can" use with nolock but it should be treated as last resort.

    And yes you can apply the hint inside the views but once again refer to the above comment


    * Noel

  • November 5, 2008 at 3:54 pm

    #894568

    It depends on what the reports are for. NOLOCK will pull inconsistent data (a.k.a. "dirty" data), so your results might not be 100% accurate or current (since it will use cached data if it finds it, regardless of how "fresh" that data might be).

    So - if it's a "good enough for government work" type report, then NOLOCK might work. If it's a "good enough for an IRS audit" type of report, then NOLOCK should NOT be used....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • November 6, 2008 at 7:53 am

    #894887

    Well... I had similar question... SO i thought i should put it here..,.

    I never used NOLOCK hint with select command, I have following question.

    If I issue NOLOCK hint on one of the Table[Table1] inside my select statement, will it be applied to other select statements issued against Table1 as well? or will it be limited to my select statement only.

    I want to make sure that, once I issue NOLOCK hint on Table1, Other user/application who is running Select statements against Table1 should not have NOLOCK, otherwise I am gone!!!...

    Please reply..

    Thanks in advance.

    -RP

  • November 6, 2008 at 9:27 am

    #895014

    If you use nolock (or any locking hint for that matter) in a query it will apply only to that query and only to the table that the hint directly follows.

    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

  • November 6, 2008 at 9:43 am

    #895028

    You should not use NOLOCK hints.

    Look into using SNAPSHOP isolation to prevent report queries from causing blocks or being blocked by other processes.

  • November 6, 2008 at 9:49 am

    #895030

    if you use nolock, you will get all the records (includes all uncommited transactions) but result is not accurate, if you are not using nolock, query will be wait till all the transactions completed on that table. for accessing huge amount of data, use nolock hint otherwise not use nolock.

    Note: While using NOLOCK, SQL Server applies Read only lock.

  • November 6, 2008 at 11:38 am

    #895116

    That was really helpful,

    Thank you very much.

    -RP

  • November 12, 2008 at 1:57 pm

    #897774

    well, I'm not going to put "nolock" hint as I have heard that this hint might also throw some errors while a record in a table is being updated concurrently. So I dont want to take a risk when my application is installed at several locations & they can't edit/modify anything at the database if there are errors due to "nolock"

Viewing 9 posts - 1 through 8 (of 8 total)

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