Monday, April 28, 2008

After Friday's experiment with transactions in SQL Server I got to wondering about what would happen to actions between the
  ROLLBACK TRAN
and the
  IF @@TRANCOUNT > 0
    COMMIT TRAN
so I amended the code to have an additional INSERT as follows (Note: I also changed the final COMMIT TRAN to be a ROLLBACK TRAN to help show the differenence)

  BEGIN TRAN
  INSERT INTO COUNT VALUES (1)
    BEGIN TRAN
    INSERT INTO COUNT VALUES (4)
    ROLLBACK TRAN
  INSERT INTO COUNT VALUES (99) -- New line
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN

which results in the value 99 being inserted into the table, outside of the scope of any transactions - all transactions were rolled back in the inner ROLLBACK TRAN

To ensure that the second part of the statement, which might be unrelated to the first and therefore not dependent on the result of that transaction, is within a transaction, the following seems to work with the expected results:

  BEGIN TRAN
  INSERT INTO COUNT VALUES (1)
    BEGIN TRAN
    INSERT INTO COUNT VALUES (4)
  ROLLBACK TRAN

  IF @@TRANCOUNT = 0
    BEGIN TRAN
    INSERT INTO COUNT VALUES (99)
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN

No new row is added as a result of this action as now all actions are covered within a transaction.

Labels: ilp, SQL

// posted by Jane @ 1:03 PM   

Comments:

Post a Comment