Post reply

Transaction scope for a batch

  • February 4, 2013 at 12:16 pm

    #270279

    Hi everyone,

    I wanted to know what is the transaction scope for the following batch. Does SQL Server commit the changes after every delete or does it commit after the last delete? And SET IMPLICIT_TRANSACTIONS is set to OFF. Thanks in advance.

    DELETE FROM table1

    DELETE FROM table2

    DELETE FROM table3

    DELETE FROM table4

    DELETE FROM table5

    DELETE FROM table6

  • February 4, 2013 at 8:11 pm

    #1584165

    By default, each statement is a transaction of its own. So, each delete statement is committed before the next one starts.

  • February 5, 2013 at 10:35 am

    #1584516

    Thanks! 🙁 Made a mistake at work by thinking that the whole batch is a transaction. Yikes. Good thing I test my backups regularly.

  • February 5, 2013 at 11:28 am

    #1584541

    However this isn't that much better.

    BEGIN TRANSACTION

    DELETE FROM table1

    DELETE FROM table2

    DELETE FROM table3

    DELETE FROM table4

    DELETE FROM table5

    DELETE FROM table6

    COMMIT TRANSACTION

    What do you think will happen if the delete from table3 fails (foreign key violation)?

    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

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

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