Post reply

Getting lock on a nolock statement

  • October 29, 2007 at 8:38 pm

    #376600

    Hi

    I have a query running on my production server

    SELECT * FROM table1 WITH (NOLOCK).

    But I can see that this spid blocks other spids even though this having NOLOCK hint.

    Can anybody please put some valuable comments on this ..

    binu john

  • October 29, 2007 at 10:56 pm

    #746460

    Select cannot block any process even if you not to use (NOLOCK).

    Because it applies shared locks.

    Look what blocks your SELECT process. There must be some updates involved.

    Or probably you've got some UPDATE just before or after that SELECT.

    UPDATE won't proceed without exclusive lock. And this will block everything from accessing affected records.

    _____________
    Code for TallyGenerator

  • October 29, 2007 at 11:35 pm

    #746466

    Try out this examples for NO LOCK when,where & how to use the same....

    sqljunkies.com/Forums/Search/default.aspx?SearchFor=1&SearchText=AspiringGeek

    channel9.msdn.com/wiki/default.aspx/PerformanceWiki.SQLServer2000Guidelines

    msdn2.microsoft.com/en-us/library/ms998577.aspx

    classicasp.aspfaq.com/general/how-do-i-make-my-asp-pages-more-efficient.html

    http://www.mssqltips.com/tipimages/1253_concurrencyIsolationLockingVersioning.txt

  • October 31, 2007 at 5:59 am

    #747014

    Hi,

    After I have replaced all SELECTs with adding "WITH (NOLOCK)" into the most used stored procedures, the ERP system is working much better. Now, I noticed that people who has used to call stored procedures with these SELECTs, they did not blocked other users. Only themselves at most. So, this "WITH (NOLOCK)" is very good to use if your application is using for each connection a serialization transaction or almost.

    In Theory, theory and practice are the same...In practice, they are not.

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

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