Friday, April 25, 2008

After Bruce and I were looking at DBVerse earlier, we started specifiying how our tool would work, especially with regards to transaction. Our outer process will need to handle its own transactions, and will need to be aware of inner errors and inner transactions to ensure that errors don't ensue.

For example, in the following scenario
BEGIN TRAN
INSERT INTO COUNT VALUES (1)
  BEGIN TRAN
  INSERT INTO COUNT VALUES (4)
  ROLLBACK TRAN
COMMIT TRAN
the following error is produced
Msg 3902, Level 16, State 1, Line 17
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

This is because the inner ROLLBACK TRAN rollsback ALL transactions, not just the inner transaction. So, by the time the COMMIT TRAN is called there are no longer any transactions.

To get around this, the code can be changed to be:
BEGIN TRAN
INSERT INTO COUNT VALUES (1)
  BEGIN TRAN
  INSERT INTO COUNT VALUES (4)
  ROLLBACK TRAN

IF @@TRANCOUNT > 0
  COMMIT TRAN
which works happily and will not cause an error.

NOTE: Count is just a test table I created with a single column of Count which is defined as an int. Nothing very exciting.

Labels: ilp, SQL

// posted by Jane @ 6:01 PM   

Comments:

Post a Comment