Post reply

What is the output if used more than one DML command on same object at the same time?

  • June 17, 2011 at 12:35 am

    #253151

    Hi All,

    I have a doubt that if i used more than one DML command on same object

    i mean if i update table and insert same table on same time what will the output? is table update or new data to be inserted at the same time?

    give me reply.

  • June 17, 2011 at 1:28 am

    #1340214

    Depends which runs first. SQL will take locks so that they can't run simultaneously if they affect the same data.

    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

  • June 17, 2011 at 1:37 am

    #1340218

    Thanks Buddy. grt

  • June 18, 2011 at 4:34 am

    #1340771

    Buddy LOL 😀

    Jayanth Kurup[/url]

  • June 18, 2011 at 9:47 am

    #1340789

    Just to be sure... pay close attention to what Gail said. There are two inferrals to her answer. To explicity state the not-so-obvious inferral, it [font="Arial Black"]is[/font] possible for rows in the same table to be UPDATEd at the same time that INSERTs are occuring if they aren't the same rows or in the same pages or extents depending on what the locks (lock escalation, in particular) have done.

    --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.

    Change is inevitable... Change for the better is not.

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

  • June 20, 2011 at 1:04 pm

    #1341407

    Additional information :-

    Transaction isolation levels allow you to control behavior such as dirty reads.

    Also lock escalation decide if the entire table gets locked and prevent updates and inserts from happening simultaneously.

    Jayanth Kurup[/url]

  • June 20, 2011 at 6:48 pm

    #1341552

    Just a word of caution... actually, a warning of danger. 😀 Seemingly innocent table hints such as WITH(NOLOCK) and similar Transaction Isolation Level hints can produce some very incorrect data including duplication of rows when another process is updating the table. Although there's certainly the rare exception, my general advice is that if you actually need to use such hints, you've probably written a bad query or the system has a whole bunch of other bad queries in it or maybe even a bad database design. The best thing to do would to be to fix those problems instead of simply covering them up with a table hint.

    --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.

    Change is inevitable... Change for the better is not.

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

Viewing 7 posts - 1 through 6 (of 6 total)

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