SQL Server allows nesting of Transactions –in a sense. In truth, there is only one Transaction, with each ‘nested transaction’ serving to increase (and decrease) the @@TRANCOUNT indicator. The transaction can be committed or rolled back at any point that the @@TRANCOUNT value is equal to one. Error conditions may or may not cause the transaction to rollback –depending upon the specific nature of the error or the XACT_ABORT setting. (In this discussion, I am not going to address the situations where the @@TRANCOUNT is not properly decremented and also when commit or rollback is not accomplished –leaving a hung transaction.)

And for many usages, the implementation works just fine. All of the work, or none of the work –at least that is what we hope for.  Sometimes it is necessary to jump through seemingly unnecessary ‘hoops’ in order to get it working as we wish.

In the case of Logging, I need to log the activities and be assured that the log entries persist even if the parent transaction fails. With the current implementation, the activities of a ‘nested transaction’ are rolled back if the parent transaction rolls back, even when calls are made to stored procedures –all is lost if the parent transaction rolls back. Yes, there is a work-around. It is common to create a Table Variable prior to starting the parent transaction, populate it with values during the transaction scope, and then after the transaction is completed (either committed or rolled back) to then insert the rows of the Table Variable into the logging table. –BUT, if server power were to fail just before the inserts, any records of the transaction activity would be forever lost. And it makes it impossible to create a self contained logging procedure that can be executed with only one line of code in the transaction.

I suggest that there is a need for a completely ‘Isolated Transaction’. That is, a transaction that can be started in the midst of a ‘regular’ transaction (parent), and that will be durable regardless of the outcome of the parent transaction. Using this ‘Isolated Transaction’, once a commit occurs, even a rollback of the parent transaction will not undo the saved work. The activity log is properly persisted and durable.

In some situations, it may be necessary to force the parent transaction to fail and rollback if entries to the log cannot be accomplished. (I’m thinking of a security implementation where any activity, even unsuccessful activity must be logged.) That can be accomplished by the parent transaction examining the error that caused code execution to fall into the CATCH block and taking appropriate action, or perhaps, proper specification of the XACT_ABORT option.

Consider the following:

   BEGIN TRANSACTION

      TRY

         {do some work}

         BEGIN ISOLATED TRANSACTION

            {do some work}

            {Create Log Entries}

         END ISOLATED TRANSACTION

         {do more work}

      END TRY

      CATCH

         ROLLBACK

         RETURN

      END CATCH

      COMMIT

   END TRANSACTION

I suggest that in the above scenario, the ISOLATED TRANSACTION should automatically commit (and persist) upon successful completion, or ABORT and exit upon any failure, reporting an error condition to the parent transaction. The parent transaction could (depending upon the XACT_ABORT setting) determine whether to continue or abort itself. IF the isolated transaction successfully completes, it is committed and durable regardless of the action taken by the parent transaction.

Now this scenario seems to satisfy my issues of being able to reliably log activities in a transaction, and have those log entries persist if the transaction is rolled back.

But additional questions arise.

Stored Procedures

What should be done IF the isolated transaction exists within a stored procedure that is called from the parent transaction code? How should the procedure handle an error that causes the isolated transaction to rollback? The RAISERROR statement in SQL Server has various quirks and should not be relied upon to communicate with the parent transaction –RAISERROR may itself cause the parent transaction to rollback and not allow the parent transaction to evaluate the error condition in its CATCH block.

Should the procedure be totally autonomous and not attempt to communicate with the parent transaction? How would the procedure best communicate an error condition, either in the isolated transaction or not? Would it be enough if failure in the Isolated Transaction caused an XACT_ABORT error to be passed to the parent transaction?

Dynamic SQL

It is possible to make a compelling argument for an Isolated Transaction in dynamic SQL. How would/should the dynamic SQL communicate to the parent transaction if the Isolate Transaction were to fail and rollback? Should the parent transaction even care?

Service Broker

Having an isolated transaction could solve some issues related to Service Broker implementations –such as starting a batch process, guaranteed messages, and/or mail delivery. Is this a use case scenario that makes sense for you?

Additional Considerations

Some have posited that the isolated transaction ‘should NOT’ automatically rollback on error, but instead require a specific ROLLBACK statement. Something like this:

   BEGIN ISOLATED TRANSACTION

      {do some work}

      IF {condition}

         ROLLBACK

      {do some other work}

   END ISOLATED TRANSACTION

But this raises all sorts of questions and issues. What happens after the ROLLBACK if there is an attempt to INSERT/UPDATE/DELETE? Is a new isolated transaction started? Is it the same transaction? Is it an error condition? What happens if the ROLLBACK is omitted and an error condition occurs?

Personally, I’m in favor of a simple approach to the isolated transaction –not at all bound by any previous SQL Servfer transaction implementation. Automatic COMMIT on successful completion, automatic ROLLBACK on error, and appropriate error messaging down the call stack. What is your opinion?

Your help is needed!

In order to best prepare and lobby for such a suggested ‘improvement’, it is important to have a clear understanding of the use case scenarios that we are attempting to solve with the isolated transactions.

So I ask you, if you see benefit from having an ISOLATED TRANSACTION in SQL Server, what is your use case? How would you use it? What are the pitfalls?