THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Arnie Rowland

Discussion of issues related to SQL Server, the MSDN SQL Support Forums, the complex interplay between Developers and SQL Server Administrators, and our sometimes futile attempts to have a 'normal' life.

Is it time for SQL Server to have Isolated Transactions?

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 Server 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?

Published Monday, March 09, 2009 6:09 PM by ArnieRowland
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jacobsebastian said:

I will certainly support this feature request. Two of the use cases Arnie listed (Logging and SB Messages) are very much true in my environment.

I think if an Isolated transaction fails, it should not break the parent transaction. If an error occurs and it is not handled, the execution should jump to the next statement after the END block of the Isolated Transaction.

March 9, 2009 8:51 PM
 

Mike C said:

I've had plenty of problems like you mention trying to log errors in transactions, and that would be my #1 use case for this feature.  I'm 100% for it!

March 9, 2009 10:44 PM
 

MontyMole said:

March 10, 2009 6:28 AM
 

TiborKaraszi said:

Hi Arnie,

I think it is time for this functionality. I'm all for the KISS approach. We do not need more complex transaction semantice in SQL Server. It is bad enough as it is (sometimes I wish MS could begin from a blank piece of paper with their transaction handling commands and semantics).

Btw, I think a more common term for the functionality is "autonomous transactions" (which I probably can't spell).

March 10, 2009 6:31 AM
 

Linchi Shea said:

The other reason is that Oracle has had the feature for a long time. This feature is very useful for writing administrative scripts. You got my vote.

March 10, 2009 9:14 AM
 

Andrew Kelly said:

Yes sir, this would be welcomed by most for sure.

March 10, 2009 9:26 AM
 

RobertDavis said:

I agree with your suggestion for the isolated transaction. Perhaps it could have support for calling ROLLBACK/COMMIT ISOLATED TRANSACTION to only rollback/commit the isolated transaction and ROLLBACK/COMMIT PARENT TRANSACTION to rollback/commit the parent as well.

The caveat would be that if there are other isolated transactions nested within the same parent when the parent rolls back, any already committed isolated transactions are not rolled back and any isolated transactions not started do not start.

Just a thought.

March 10, 2009 4:54 PM
 

BinaryCoder said:

For some time, I have wished that SQL Server were more flexible in its transaction handling. More specifically, it would be useful if it were possible to begin and commit independent transactions nested in parent transactions regardless of whether or not the parent transactions commit or rollback (this enables logging scenarios). It would also be useful for this to be designed in a general-purpose way that allows the programmer fine-grained control over what transactions are committed or rolled back when multiple transactions are in progress (this enables scenarios where a part of the high-level transaction needs to be retried or recovery is to be attempted after part of the high-level transaction has failed).

I have a writeup on some ideas here: http://www.binarycoder.net/misc/nestedtransactions.html

March 14, 2009 2:12 PM
 

oj_n said:

You do not have "autonomous transaction" in SQL yet but you can fake one by using loopback linked server. This is one of the tricks to persist data outside of the current tranx.

Works great for older SQL versions too.

March 15, 2009 5:01 AM
 

bfrasca said:

I can only give a luke-warm endorsement as I think this violates the principles laid out in the ACID model, specifically, the Isolation component of that model. Yes, I know it would be a separate transaction but it could potentially impact the parent transaction negatively.  I would probably be on board if these isolated transactions could be fired off asynchronously or pseudo-asynchronously, i.e. via a spawned process.  I would hate to tell my boss that business transactions were failing because I was trying to create log entries.

One of the issues that must be overcome with this would be implementing an isolated transaction within the two and three-phase commit protocols.  Distributed transactions of all types would need to be considered.

I think of it more as a nice-to-have although I've never really had any issues logging.  It's easy enough to log as part of your rollback "handler", deadlock re-try logging comes to mind, although the need for log entries to persist for a failed transaction seems counter-intuitive to me except for logging problems.  I guess I'd need to hear a more specific logging use case where the log entries need to persist even though the transaction failed.  (I don't know enough about Service Broker to comment on that use case.)  It would certainly make a great debugging tool but I'm not sure it would add a lot of value to justify the added complexity that would be required to use the capability.

March 18, 2009 10:25 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement