Post reply

Is this correct use of WITH (NOLOCK, READUNCOMMITTED) in delete?

  • November 16, 2011 at 7:47 am

    #246247

    DELETE

    dbo.Reporting_BondIndicative

    FROM

    dbo.Reporting_BondIndicative Reporting_BondIndicative

    INNER JOIN

    (

    SELECT

    SecurityId,

    MIN(CUSIP) AS CUSIP

    FROM

    Fireball_Reporting..Reporting_BondIndicative -- WITH (NOLOCK, READUNCOMMITTED)

    GROUP BY

    SecurityId

    ) MinCusip ON

    Reporting_BondIndicative.CUSIP = MinCusip.CUSIP AND

    Reporting_BondIndicative.SecurityId = MinCusip.SecurityId

    WHERE

    Reporting_BondIndicative.SecurityId IN

    (

    SELECT

    SecurityId

    FROM

    dbo.Reporting_BondIndicative -- WITH (NOLOCK, READUNCOMMITTED)

    GROUP BY

    SecurityId

    HAVING

    COUNT(*) > 1

    )

    as there is select statement so please tell me i'm correct or not?

  • November 16, 2011 at 7:51 am

    #1408539

    Firstly those 2 hints mean exactly the same thing, so you're just repeating yourself.

    Secondly, those hints have NO place in most code most of the time.

    Thirdly, did you read through all the repeated warning about those hints? NOLOCK is NOT a go-faster switch. If you're having problems with blocking, consider the read committed snapshot isolation.

    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 16, 2011 at 7:51 am

    #1408540

    Only if you don't mind deleting the wrong data, or getting your connection killed (normal behavior with nolock).

    http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

  • November 16, 2011 at 7:53 am

    #1408541

    Ah! nice catch Gail.

    Please don't cross post. It just wastes everyone's time and our answer won't change from one thread to another.

  • November 16, 2011 at 7:53 am

    #1408544

    i.m just using this stored procedure for reporting purpose..

    please let me know my usage is at right or not?

  • November 16, 2011 at 7:56 am

    #1408550

    ashuthinks (11/16/2011)

    i.m just using this stored procedure for reporting purpose..

    please let me know my usage is at right or not?

    Why do you NEED to use this?

    It's NOT a go fast magic red button.

  • November 16, 2011 at 7:58 am

    #1408553

    ashuthinks (11/16/2011)

    i.m just using this stored procedure for reporting purpose..

    please let me know my usage is at right or not?

    Since when does reporting run deletes?

    Why are you so determined to use this hint? It's not as if you're paid by the number of keywords or features that you use.

    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 16, 2011 at 7:58 am

    #1408554

    many reports are accessing this stored procedure at the same time so TO AVOID DEADLOCK I'M USING THI 🙂

  • November 16, 2011 at 8:01 am

    #1408560

    There's no deadlocks on selects.

    Reports should have to delete anything in permanant tables.

    Why do you need to delete the base tables' data for that report?

  • November 16, 2011 at 8:03 am

    #1408564

    ashuthinks (11/16/2011)

    many reports are accessing this stored procedure at the same time so TO AVOID DEADLOCK I'M USING THI 🙂

    So, if you don't use it you are definitely getting deadlocks? Or are you guessing that you might?

    Deadlocks are completely fixable (except in one or 2 really odd edge cases) with tuning code and modifying indexes.

    p.s. Please don't shout at me.

    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 16, 2011 at 8:05 am

    #1408568

    its a huge long store procedure depending upon conditions

    i insert data , update data and delete data from tables..

    what i have posted is just a sample code of SP.

    but i'm not sure about the space of WITH(NOLOCK) into above delete block

    so thats why i posted a question .

    NOLOCK is working with select only and as delete block is having select statement

    so i wonder that I can use it or not ?

  • November 16, 2011 at 8:07 am

    #1408572

    @gila yes sir...

    i knew that if i don't use NOLOCK i will definitely get a deadlock so for successful

    execution i need to use NOLOCK

  • November 16, 2011 at 8:10 am

    #1408576

    ashuthinks (11/16/2011)

    @gila yes sir...

    i knew that if i don't use NOLOCK i will definitely get a deadlock so for successful

    execution i need to use NOLOCK

    It's a mam actually..

    ...backs away slowly 😉

  • November 16, 2011 at 8:11 am

    #1408578

    OPPS 😛

  • November 16, 2011 at 8:11 am

    #1408579

    ashuthinks (11/16/2011)

    its a huge long store procedure depending upon conditions

    i insert data , update data and delete data from tables..

    what i have posted is just a sample code of SP.

    but i'm not sure about the space of WITH(NOLOCK) into above delete block

    so thats why i posted a question .

    NOLOCK is working with select only and as delete block is having select statement

    so i wonder that I can use it or not ?

    That's why temp table were invented. You can't get deadlocks there.

    Also it avoids the collision between multiple reports messing with one another's data.

Viewing 15 posts - 1 through 15 (of 76 total)

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