Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Make it possible to have transactions doomed only where there is a reason to

Today when you enter a CATCH handler, the transaction may be doomed, meaning that you cannot continue or roll back to a savepoint. And this is perfectly OK for concurrency errors like deadlock or errors with severity >= 17, like running out of disk space. The problem is that many other errors also doom the transaction for no good reason at all. And when SET XACT_ABORT ON is in effect, all errors doom the transaction.

This means that it is not possible to implement a recoverable error handling for unexpected errors. For instance, if you are in a procedure that has its own transaction which may be called by a outer procedure which also has a transaction, you cannot rollback only to a savepoint to let the caller decide what to do. Or if you are in an activation procedure for Service Broker and there is an error. In this case, you would like to rollback to a savepoint where the message is still off the queue and then divert it elsewhere.

Changing this right off the bat, so that transactions are only doomed when there is a reason would cause backwards compatibility issues. It is difficult to see how an application would suffer from a change, but people out there do funny things.

Therefore, I think the best is to keep the default behaviour, but add an option that permits you to state that you only want transactions doomed for good reasons. I think this is reasonable, since after all, in most CATCH handlers, all you want to do is to rollback.

The sugested syntax would be an addition to the BEGIN CATCH statement. For instance

BEGIN CATCH WITH REASONABLE DOOMING

Note that the setting of XACT_ABORT would not affect the behaviour. That is, with this clause, transactions would normally not be doomed, no matter the setting of XACT_ABORT.

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Erland Sommarskog shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base