Wednesday, April 30, 2008

Further transaction work revolved around the investigation of naming transactions, and naming savepoints and understanding the differences. Again I worked with the theory of nested transactions to try and understand what was going on.

So, stage one - Labelling the two transactions and committing explicit transactions works fine and as expected

  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    BEGIN TRAN InnerTransaction

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    COMMIT TRAN InnerTransaction -- can commit transaction InnerTransaction - result will be -108

  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

This results in one row, with the value -108 and no transactions left hanging.

However, if we don't explicitly commit the InnerTransaction, when we commit the OuterTransaction the InnerTransaction is left hanging, so the last SELECT @@TRANCOUNT returns 1 and not 0

  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    BEGIN TRAN InnerTransaction

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    -- No inner commit / rollback

  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

this results in one row, with the value of -108, but with a transaction left hanging - as we only issued an explicit COMMIT TRAN against the OuterTransaction

so we would probably be better to have the OuterTransaction statement which is currently
  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction
as
  IF @@TRANCOUNT > 0
    COMMIT TRAN
i.e. with no explicit reference to the transaction

Stage Two, attempting to rollback to a named transaction

  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    BEGIN TRAN InnerTransaction

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    ROLLBACK TRAN InnerTransaction

  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

which results in the following error:
Msg 6401, Level 16, State 1, Line 77
Cannot roll back InnerTransaction. No transaction or savepoint of that name was found.

as well a single row, with the value of -108, and a transaction left open.

This behaviour confused me for quite a while until, after a bit of research and reading, I found the following definition for ROLLBACK TRAN [transaction_name]

transaction_name - Is the name assigned to the transaction on BEGIN TRANSACTION. transaction_name must conform to the rules for identifiers, but only the first 32 characters of the transaction name are used. When nesting transactions, transaction_name must be the name from the outermost BEGIN TRANSACTION statement.

So, instead, we can either rollback to the OuterTransaction explicitly

  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    BEGIN TRAN InnerTransaction

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    ROLLBACK TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

which now results in no rows - because everything is rolled back in the inner statement - and no hanging transactions

or, in this case, the same effect can be gained from issuing a simple ROLLBACK TRAN :

  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    BEGIN TRAN InnerTransaction

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    ROLLBACK TRAN

  IF @@TRANCOUNT > 0
    COMMIT TRAN OuterTransaction

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

which again results in no rows, and no hanging transactions

Step three, use a save point to restrict the amount of data to be rolled back instead of an inner transaction
  DELETE FROM Count -- outside of transaction - a known starting point

  IF @@TRANCOUNT > 0
    ROLLBACK TRAN -- in case there are any transactions still hanging around

  BEGIN TRAN OuterTransaction

  INSERT INTO COUNT VALUES (108)

    SAVE TRAN SavePoint

    UPDATE COUNT SET Count = -108 WHERE Count = 108

    ROLLBACK TRAN SavePoint

  IF @@TRANCOUNT > 0
    COMMIT TRAN

  IF @@TRANCOUNT > 0
    SELECT 'Transactions left hanging.'

  SELECT * FROM COUNT

This results in one row, with a value of 108, showing that the UPDATE statement has been rolled back leaving just the results of the INSERT statement

Labels: SQLServer

// posted by Jane @ 5:09 PM   

Comments:

Post a Comment