How to solve the conflict between INSERT and DELETE

  • zxmgh

    SSC Eights!

    Points: 911

    Hi, I have an appliction which run the following two SQLs in the order:

    1.INSERT INTO tab1 WITH (ROWLOCK)

    2.DELETE from tab1 WITH (ROWLOCK)

    This appliction is executed by hundreds of users at the same time. Normally these SQLs only takes less than 1 seoncd. Sometimes I saw significantly delay on these SQLs - around 10 seconds. It seems that they are blocking each other (when updating clustered index?), is there a way to solve this conflict?

    Thanks

  • johncyriac

    SSCommitted

    Points: 1542

    Use Transactions is better then rowloc

    begin tran

    1.INSERT INTO tab1

    2.DELETE from tab1

    commit tran

    regards

    john

  • TheSQLGuru

    SSC Guru

    Points: 134017

    Do you really execute the DELETE with no WHERE clause?  That simply can't be good/right.    And if it is, then you certainly don't want rowlock hint specified.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Jeff Moden

    SSC Guru

    Points: 994647

    I believe adding the transaction increases the chances of a deadlock... better to let it wait than to form deadlocks.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

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

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