SQL Server Performance

Use of NOLOCK more

Discussion in 'SQL Server 2005 General Developer Questions' started by umimetha, Apr 12, 2007.

  1. umimetha New Member

    Hi,

    When I went through a SP I found this part;

    ================================================================================
    IF NOT EXISTS (SELECT NULL FROM App WHERE AppID = @AppID)
    BEGIN
    SET @ValErr = 1
    SET @ValString = 'No'
    GOTO ErrHandler_Validation
    END

    SET @ApplicationID = (
    SELECT AppID
    FROM App (NOLOCK)
    WHERE ApplicantID = @AppID
    )
    ====================================================================================

    I wondered why the developer used (NOLOCK) condition only in SELECT AppID
    FROM App (NOLOCK)
    WHERE ApplicantID = @AppID
    and why not used in (SELECT NULL FROM App WHERE AppID = @AppID).

    I feel we can use the nolock condition in (SELECT NULL FROM App WHERE AppID = @AppID)
    too.

    Can anyone please explain if there is a difference.

  2. MohammedU New Member

    May be two reasons...

    1. Developer want to read the committed data in IF NOT EXISTS statement to make sure he get the committed data... if the record exists then he wants to read the dirty read becasue he knows it is there...

    2. Developers forgot to add the NOLOCK hint...

    If you use the following code you can skip additional call to the table when ApplicationID exists...

    SELECT @ApplicationID = AppID
    FROM App a (NOLOCK)
    WHERE ApplicantID = @AppID

    if @ApplicationID is null
    BEGIN
    SET @ValErr = 1
    SET @ValString = 'No'
    GOTO ErrHandler_Validation
    END


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  3. DilliGrg Member

    I would have to agree with MohammedU's second reasoning based on the queries you have posted supposedly from <b>your developer</b> so far on this NOLOCK topic. OR the coder doesn't seem to know what NOLOCK does. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] Happy coding.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Thanks,<br /><br />Name<br />--------- <br />Dilli Grg <br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code">
  4. satya Moderator

Share This Page