locked
Insert, update and delete statements in transactions RRS feed

  • Question

  • Hello. I would like to know when using a transaction for Insert Update and Delete statements. Do you put all statements in one transaction or make separate transactions for each. Each statement will be working with six tables. The insert statement is working already.

    Thanks for all your help in advance.


    PCRider
    Thursday, February 3, 2011 10:18 PM

Answers

  • Transactions need to define a logical unit of work: a set of modifications that have to be atomic, which means either all of the modifications are made or none of them are made.

    The classic example of a logical unit of work is an account transfer at a bank. That involves two operations: deleting money from one account, and then adding the same amount to another account, such as deleting $100 from your checking account and depositing $100 in your savings account. If the delete happens but the deposit does not, then you are not happy because you just lost $100. If the delete fails but the deposit happens, then the bank is not happy because they have to figure out where to get the extra $100 that magically appeared in your savings account.

    You can make sure that either both actions succeed or fail by enclosing them in a transaction:

    begin transaction

    delete $100 from checking

    insert $100 into savings

    if error rollback transaction

    else commit transaction

    What determines the boundaries of a transaction are the business rules for whatever operation you are trying to code. All the DB knows is when the application tells it to start a transaction, the set of modifications the application requests, and whether the application requests that the transaction be either rolled back or committed. The person coding the application has to ensure that it has the right logic to support the required business rules.

    SQL Server has a default to consider each statement an independent transaction, but in many cases that does not properly support the busines rules the application should be enforcing, at which point the developer needs to start coding begin/commit/rollback transactions.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Naomi NEditor Friday, February 4, 2011 2:35 AM
    • Marked as answer by PCRider Saturday, February 5, 2011 12:57 AM
    Friday, February 4, 2011 2:14 AM

All replies

  • If you want all the updates to be related and to either all be completed or all be rollbacked, then you put all of them into one transaction. Otherwise you don't need to use transactions at all.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, February 3, 2011 10:30 PM
    Answerer
  • The answer is it depends.  But if you are executing multiple statements as a batch and this batch makes it a single transaction; i.e. that everything in transaction must succeed or nothing.  Then a single transaction is smart way to go; however if one statement in batch has no affect on outcome of the other?  Then single transaction works.

    Cheers!


    Mohit K. Gupta --- MCTS, MCITP, MCC2011 http://sqllearnings.blogspot.com/
    Thursday, February 3, 2011 10:32 PM
  • So Naomi, what you're saying is put them in with the Insert statement as one transaction?

    PCRider
    Thursday, February 3, 2011 11:43 PM
  • If these statements should operate together as a unit, then yes, put them all in one transaction.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, February 4, 2011 12:00 AM
    Answerer
  • Mohit. So if I were to say insert say two records the first I update info that I wasn't able to insert into the first time and decided to delete the second record, would that be considered separate transactions or one?

    PCRider
    Friday, February 4, 2011 12:16 AM
  • Transactions need to define a logical unit of work: a set of modifications that have to be atomic, which means either all of the modifications are made or none of them are made.

    The classic example of a logical unit of work is an account transfer at a bank. That involves two operations: deleting money from one account, and then adding the same amount to another account, such as deleting $100 from your checking account and depositing $100 in your savings account. If the delete happens but the deposit does not, then you are not happy because you just lost $100. If the delete fails but the deposit happens, then the bank is not happy because they have to figure out where to get the extra $100 that magically appeared in your savings account.

    You can make sure that either both actions succeed or fail by enclosing them in a transaction:

    begin transaction

    delete $100 from checking

    insert $100 into savings

    if error rollback transaction

    else commit transaction

    What determines the boundaries of a transaction are the business rules for whatever operation you are trying to code. All the DB knows is when the application tells it to start a transaction, the set of modifications the application requests, and whether the application requests that the transaction be either rolled back or committed. The person coding the application has to ensure that it has the right logic to support the required business rules.

    SQL Server has a default to consider each statement an independent transaction, but in many cases that does not properly support the busines rules the application should be enforcing, at which point the developer needs to start coding begin/commit/rollback transactions.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Naomi NEditor Friday, February 4, 2011 2:35 AM
    • Marked as answer by PCRider Saturday, February 5, 2011 12:57 AM
    Friday, February 4, 2011 2:14 AM