Post reply

Lock and No Locks

  • September 15, 2009 at 1:26 am

    #226134

    Hi,

    Need help on understanding when to use locks and no locks on sql querys

    Thanks for the help

  • September 15, 2009 at 3:47 am

    #1053679

    Individual locking hints in queries shouldn't really be necassary.

    Have a read up about Isolation Levels and see which is appropriate to your application:

    http://msdn.microsoft.com/en-us/library/ms173763%28SQL.90%29.aspx

  • September 15, 2009 at 6:06 am

    #1053706

    I'd go a bit farther and suggest if you're even thinking about putting locking hints into your query, then you need to reassess your system, it's design, the queries you're writing... pretty much everything. Locking hints should be a last, desperate measure after every other option has been exhausted. They should not be used as a matter of normal operation. Way too often people abandon the tuning process and resort to hints to try to short circuit the process. It almost always leads to other problems, especially when the system is over-used.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 15, 2009 at 6:11 am

    #1053713

    If you want to use nolock , understand the consequences

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


    Clear Sky SQL
    My Blog[/url]

  • September 16, 2009 at 7:17 pm

    #1054781

    Hi,

    I am still confused,whether to go ahead with lock or no lock on queries for example while one user is booking a product or editing a product and some other user is viewing how does this effect

    Please suggest and Thanks for the help

    Thanks

  • September 17, 2009 at 12:12 am

    #1054812

    Never , ever ,ever hold a lock whilst waiting for user interaction. That is a very easy way to lock your system solidy.

    Your best option may be to implement optimistic (or pessimestic) locking

    http://www.mssqltips.com/tip.asp?tip=1501

    http://www.dbasupport.com/forums/archive/index.php/t-7282.html


    Clear Sky SQL
    My Blog[/url]

  • September 17, 2009 at 5:33 am

    #1054908

    forum member (9/16/2009)

    Hi,

    I am still confused,whether to go ahead with lock or no lock on queries for example while one user is booking a product or editing a product and some other user is viewing how does this effect

    Please suggest and Thanks for the help

    Thanks

    Locking is a very difficult topic to understand (I'm still working on it). A very simple rule is, let SQL Server do the work. Stay out of it's way and leave it alone. It will handle the locking just fine in the majority of situations.

    The one thing you might consider, especially if you're working with 2008, is to turn on Read Committed Snapshot. That will allow for row versions, which will reduce lock contention even more.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 21, 2010 at 4:34 am

    #1196047

    thnx 🙂 i got this clear..

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

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