Post reply

locking, blocking, and read only access

  • February 27, 2004 at 2:30 am

    #82329

    Hi,

    Are there ANY occasions where running a select statement may cause locking and/or blocking?  How does 'lock starvation' come into the picture?

    Thanks

     

     

     

  • February 27, 2004 at 6:52 am

    #496180

    Absolutely a select statement may cause blocking and locking if you are reading committed, this mainly happens when you are attempting to do a large select. The worst is when you get the row lock to page lock escalation.

    You can prevent this by either putting (NOLOCK) after the tables or aliases used in the select command, or by putting SET ISOLATION LEVEL READ UNCOMMITTED at the start of the query and SET ISOLATION LEVEL READ COMMITTED at the end of the query. This will read the data uncommitted, which means that in a higly transactional environment that there is the posibility of returning incorrect data, however around 95% of the time this is not an issue. Just be careful as to the statements/prcedures that you add this to. Assess how critical it is that the data be fully committed against the potential benefits (no locking, blocking and a little faster).


    Shamless self promotion - read my blog http://sirsql.net

  • February 27, 2004 at 8:08 am

    #496209

    besides that try to make your transactions as short as possible and that will also mitigate the chances for locks


    * Noel

  • February 28, 2004 at 4:13 pm

    #496359

    Can you get your hands on Inside SQL Server 2000?

    The book has an indepth explanation on locking. But BOL is a good start, too.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • March 1, 2004 at 5:32 am

    #496424

    Thanks for all f your replies,

    I'll try to get holld of Inside SQL 2K

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

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