TL;DR / Executive Summary: Regarding this part of the Question:
I don't see in what cases the control can be passed inside CATCH
with a transaction that can be committed when XACT_ABORT
is set to ON
.
I have done quite a bit of testing on this now and I cannot find any cases where XACT_STATE()
returns 1
inside of a CATCH
block when @@TRANCOUNT > 0
and the session property of XACT_ABORT
is ON
. And in fact, according to the current MSDN page for SET XACT_ABORT:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
That statement appears to be in agreement with your speculation and my findings.
MSDN article about SET XACT_ABORT
has an example when some statements inside a transaction execute successfully and some fail when XACT_ABORT
is set to OFF
True, but the statements in that example are not within a TRY
block. Those same statements within a TRY
block would still prevent execution for any statements after the one that caused the error, but assuming that XACT_ABORT
is OFF
, when control is passed to the CATCH
block the Transaction is still physically valid in that all of the prior changes did happen without error and can be committed, if that is the desire, or they can be rolled-back. On the other hand, if XACT_ABORT
is ON
then any prior changes are automatically rolled-back, and then you are given the choice to either: a) issue a ROLLBACK
which is mostly just an acceptance of the situation since the Transaction was already rolled back minus resetting @@TRANCOUNT
to 0
, or b) get an error. Not much of a choice, is it?
One possibly important detail to this puzzle that is not apparent in that documentation for SET XACT_ABORT
is that this session property, and that example code even, has been around since SQL Server 2000 (the documentation is nearly identical between the versions), predating the TRY...CATCH
construct which was introduced in SQL Server 2005. Looking at that documentation again and looking at the example (without the TRY...CATCH
), using XACT_ABORT ON
causes an immediate roll-back of the Transaction: there is no Transaction state of "uncommittable" (please note that there is no mention at all of an "uncommittable" Transaction state in that SET XACT_ABORT
documentation).
I think it is reasonable to conclude that:
- the introduction of the
TRY...CATCH
construct in SQL Server 2005 created the need for a new Transaction state (i.e. "uncommittable") and the XACT_STATE()
function to get that information.
- checking
XACT_STATE()
in a CATCH
block really only makes sense if both of the following are true:
XACT_ABORT
is OFF
(else XACT_STATE()
should always return -1
and @@TRANCOUNT
would be all you need)
- You have logic in the
CATCH
block, or somewhere up the chain if the calls are nested, that makes a change (a COMMIT
or even any DML, DDL, etc statement) instead of doing a ROLLBACK
. (this is a very atypical use case)
** please see note at the bottom, in the UPDATE 3 section, regarding an unofficial recommendation by Microsoft to always check XACT_STATE()
instead of @@TRANCOUNT
, and why testing shows that their reasoning does not pan out.
- the introduction of the
TRY...CATCH
construct in SQL Server 2005 has, for the most part, obsoleted the XACT_ABORT ON
session property as it provides for a greater degree of control over the Transaction (you at least have the option to COMMIT
, provided that XACT_STATE()
does not return -1
).
Another way to look at this is, prior to SQL Server 2005, XACT_ABORT ON
provided an easy and reliable way to stop processing when an error occurred, as compared to checking @@ERROR
after each statement.
- The documentation example code for
XACT_STATE()
is erroneous, or at best misleading, in that it shows checking for XACT_STATE() = 1
when XACT_ABORT
is ON
.
The long part ;-)
Yes, that example code on MSDN is a bit confusing (see also: @@TRANCOUNT (Rollback) vs. XACT_STATE) ;-). And, I feel it is misleading because it either shows something that makes no sense (for the reason that you are asking about: can you even have a "committable" transaction in the CATCH
block when XACT_ABORT
is ON
), or even if it is possible, it still focuses on a technical possibility that few will ever want or need, and ignores the reason one is more likely to need it.
If there is a severe enough error inside the TRY block, the control will pass into CATCH. So, if I'm inside the CATCH, I know that transaction has had a problem and really the only sensible thing to do in this case is to roll it back, isn't it?
I think it would help if we made sure that we are on the same page regarding what is meant by certain words and concepts:
"severe enough error": Just to be clear, TRY...CATCH will trap most errors. The list of what will not be caught is listed on that linked MSDN page, under the "Errors Unaffected by a TRY…CATCH Construct" section.
"if I'm inside the CATCH, I know that transaction has had a problem" (emphasis added): If by "transaction" you mean the logical unit of work as determined by you by grouping statements into an explicit transaction, then most likely yes. I think most of us DB folks would tend to agree that rolling-back is "the only sensible thing to do" since we likely have a similar view of how and why we use explicit transactions and conceive of what steps should make up an atomic unit of work.
But, if you mean the actual units of work that are being grouped into the explicit transaction, then no, you don't know that the transaction itself has had a problem. You only know that a statement executing within the explicitly defined transaction has raised an error. But it might not be a DML or DDL statement. And even if it was a DML statement, the Transaction itself might still be committable.
Given the two points made above, we should probably draw a distinction between transactions that you "can't" commit, and ones that you "don't want" to commit.
When XACT_STATE()
returns a 1
, that means that the Transaction is "committable", that you have a choice between COMMIT
or ROLLBACK
. You might not want to commit it, but if for some hard-to-even-come-up-with-an-example-for reason you wanted to, at least you could because some parts of the Transaction did complete successfully.
But when XACT_STATE()
returns a -1
, then you really need to ROLLBACK
because some part of the Transaction went into a bad state. Now, I do agree that if control has been passed to the CATCH block, then it makes sense enough to just check @@TRANCOUNT
, because even if you could commit the Transaction, why would you want to?
But if you notice at the top of the example, the setting of XACT_ABORT ON
changes things a bit. You can have a regular error, after doing BEGIN TRAN
that will pass control to the CATCH block when XACT_ABORT
is OFF
and XACT_STATE() will return 1
. BUT, if XACT_ABORT is ON
, then the Transaction is "aborted" (i.e. invalidated) for any 'ol error and then XACT_STATE()
will return -1
. In this case, it seems useless to check XACT_STATE()
within the CATCH
block as it always seems to return a -1
when XACT_ABORT
is ON
.
So then what is XACT_STATE()
for? Some clues are:
MSDN page for TRY...CATCH
, under the "Uncommittable Transactions and XACT_STATE" section, says:
An error that ordinarily ends a transaction outside a TRY block causes a transaction to enter an uncommittable state when the error occurs inside a TRY block.
The MSDN page for SET XACT_ABORT, under the "Remarks" section, says:
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.
and:
XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server.
The MSDN page for BEGIN TRANSACTION, under the "Remarks" section, says:
The local transaction started by the BEGIN TRANSACTION statement is escalated to a distributed transaction if the following actions are performed before the statement is committed or rolled back:
- An INSERT, DELETE, or UPDATE statement that references a remote table on a linked server is executed. The INSERT, UPDATE, or DELETE statement fails if the OLE DB provider used to access the linked server does not support the ITransactionJoin interface.
The most applicable usage seems to be within the context of Linked Server DML statements. And I believe I ran into this myself years ago. I don't remember all of the details, but it had something to do with the remote server not being available, and for some reason, that error did not get caught within the TRY block and never got sent to the CATCH and so it did a COMMIT when it shouldn't have. Of course, that could have been an issue of not having XACT_ABORT
set to ON
rather than failing to check XACT_STATE()
, or possibly both. And I do recall reading something that said if you use Linked Servers and/or Distributed Transactions then you needed to use XACT_ABORT ON
and/or XACT_STATE()
, but I cannot seem to find that document now. If I do find it, I will update this with the link.
Still, I have tried several things and am unable to find a scenario that has XACT_ABORT ON
and passes control to the CATCH
block with XACT_STATE()
reporting 1
.
Try these examples to see the effect of XACT_ABORT
on the value of XACT_STATE()
:
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRAN;
SELECT 1/0 AS [DivideByZero]; -- error, yo!
COMMIT TRAN;
END TRY
BEGIN CATCH
SELECT @@TRANCOUNT AS [@@TRANCOUNT],
XACT_STATE() AS [XactState],
ERROR_MESSAGE() AS [ErrorMessage]
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK;
END;
END CATCH;
GO ------------------------------------------------
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRAN;
SELECT 1/0 AS [DivideByZero]; -- error, yo!
COMMIT TRAN;
END TRY
BEGIN CATCH
SELECT @@TRANCOUNT AS [@@TRANCOUNT],
XACT_STATE() AS [XactState],
ERROR_MESSAGE() AS [ErrorMessage]
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK;
END;
END CATCH;
GO ------------------------------------------------
SET XACT_ABORT ON;
BEGIN TRY
SELECT 1/0 AS [DivideByZero]; -- error, yo!
END TRY
BEGIN CATCH
SELECT @@TRANCOUNT AS [@@TRANCOUNT],
XACT_STATE() AS [XactState],
ERROR_MESSAGE() AS [ErrorMessage]
END CATCH;
UPDATE
While not part of the original Question, based on these comments on this Answer:
I've been reading through Erland's articles on Error and Transaction Handling where he says that XACT_ABORT
is OFF
by default for legacy reasons and normally we should set it to ON
.
...
"... if you follow the recommendation and run with SET XACT_ABORT ON, the transaction will always be doomed."
Prior to using XACT_ABORT ON
everywhere, I would question: what exactly is being gained here? I have not found it necessary to do and generally advocate that you should use it only when necessary. Whether or not you want to ROLLBACK
can be handle easily enough by using the template shown in @Remus's answer, or the one that I have been using for years that is essentially the same thing but without the Save Point, as shown in this answer (which handles nested calls):
Are we required to handle Transaction in C# Code as well as in stored procedure
UPDATE 2
I did a bit more testing, this time by creating a small .NET Console App, creating a Transaction in the app layer, prior to executing any SqlCommand
objects (i.e. via using (SqlTransaction _Tran = _Connection.BeginTransaction()) { ...
), as well as using a batch-aborting error instead of just a statement-aborting error, and found that:
- An "uncommitable" Transaction is one that has been, for the most part, rolled back already (the changes have been undone), but
@@TRANCOUNT
is still > 0.
- When you have an "uncommitable" Transaction you cannot issue a
COMMIT
as that will generate and error saying that the Transaction is "uncommittable". You also cannot ignore it / do nothing as an error will be generated when the batch finishes stating that the batch completed with a lingering, uncommittable transaction and it will be rolled back (so, um, if it will auto-roll-back anyway, why bother throwing the error?). So you must issue an explicit ROLLBACK
, maybe not in the immediate CATCH
block, but before the batch ends.
- In a
TRY...CATCH
construct, when XACT_ABORT
is OFF
, errors that would terminate the Transaction automatically had they occurred outside of a TRY
block, such as batch-aborting errors, will undo the work but not terminate the Tranasction, leaving it as "uncommitable". Issuing a ROLLBACK
is more of a formality needed to close out the Transaction, but the work has already been rolled-back.
- When
XACT_ABORT
is ON
, most errors act as batch-aborting, and hence behave as described in the bullet point directly above (#3).
XACT_STATE()
, at least in a CATCH
block, will show a -1
for batch-aborting errors if there was an active Transaction at the time of the error.
XACT_STATE()
sometimes returns 1
even when there is no active Transaction. If @@SPID
(among others) is in the SELECT
list along with XACT_STATE()
, then XACT_STATE()
will return 1 when there is no active Transaction. This behavior started in SQL Server 2012, and exists on 2014, but I haven't tested on 2016.
With the above points in mind:
- Given points #4 and #5, since most (or all?) errors will render a Transaction "uncommitable", it seems entirely pointless to check
XACT_STATE()
in the CATCH
block when XACT_ABORT
is ON
since the value returned will always be -1
.
- Checking
XACT_STATE()
in the CATCH
block when XACT_ABORT
is OFF
makes more sense because the return value will at least have some variation since it will return 1
for statement-aborting errors. However, if you code like most of us, then this distinction is meaningless since you will be calling ROLLBACK
anyway simply for the fact that an error occurred.
- If you find a situation that does warrant issuing a
COMMIT
in the CATCH
block, then check the value of XACT_STATE()
, and be sure to SET XACT_ABORT OFF;
.
XACT_ABORT ON
seems to offer little to no benefit over the TRY...CATCH
construct.
- I can find no scenario where checking
XACT_STATE()
provides a meaningful benefit over simply checking @@TRANCOUNT
.
- I can also find no scenario where
XACT_STATE()
returns 1
in a CATCH
block when XACT_ABORT
is ON
. I think it is a documentation error.
- Yes, you can roll-back a Transaction that you did not explicitly begin. And in the context of using
XACT_ABORT ON
, it's a moot point since an error happening in a TRY
block will automatically roll-back the changes.
- The
TRY...CATCH
construct has the benefit over XACT_ABORT ON
in not automatically cancelling the whole Transaction, and hence allowing the Transaction (as long as XACT_STATE()
returns 1
) to be committed (even if this is an edge-case).
Example of XACT_STATE()
returning -1
when XACT_ABORT
is OFF
:
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRAN;
SELECT CONVERT(INT, 'g') AS [ConversionError];
COMMIT TRAN;
END TRY
BEGIN CATCH
DECLARE @State INT;
SET @State = XACT_STATE();
SELECT @@TRANCOUNT AS [@@TRANCOUNT],
@State AS [XactState],
ERROR_MESSAGE() AS [ErrorMessage];
IF (@@TRANCOUNT > 0)
BEGIN
SELECT 'Rollin back...' AS [Transaction];
ROLLBACK;
END;
END CATCH;
UPDATE 3
Related to item #6 in the UPDATE 2 section (i.e. possible incorrect value returned by XACT_STATE()
when there is no active Transaction):
The odd / erroneous behavior started in SQL Server 2012 (so far tested against 2012 SP2 and 2014 SP1)
In SQL Server versions 2005, 2008, and 2008 R2, XACT_STATE()
did not report expected values when used in Triggers or INSERT...EXEC
scenarios: xact_state() cannot be used reliably to determine whether a transaction is doomed (archived page). However, in these 3 versions (I only tested on 2008 R2), XACT_STATE()
does not incorrectly report 1
when used in a SELECT
with @@SPID
.
There is was a Connect bug filed against the behavior mentioned here but is closed as "By Design": XACT_STATE() can return an incorrect transaction state in SQL 2012 (link no longer valid due to incompetent and/or grossly negligent site migration 😾). However, the test was done when selecting from a DMV and it was concluded that doing so would naturally have a system generated transaction, at least for some DMVs. It was also stated in the final response by MS that:
Note that an IF statement, and also a SELECT without FROM, do not start a transaction.
for example, running SELECT XACT_STATE() if you don't have a previously existing transaction will return 0.
Those statements are incorrect given the following example:
SELECT @@TRANCOUNT AS [TRANCOUNT], XACT_STATE() AS [XACT_STATE], @@SPID AS [SPID];
GO
DECLARE @SPID INT;
SET @SPID = @@SPID;
SELECT @@TRANCOUNT AS [TRANCOUNT], XACT_STATE() AS [XACT_STATE], @SPID AS [SPID];
GO
Hence, I filed a new Feedback bug:
XACT_STATE() returns 1 when used in SELECT with some system variables but without FROM clause
PLEASE NOTE that in the "XACT_STATE() can return an incorrect transaction state in SQL 2012" Connect item linked directly above, Microsoft (well, a representative of) states:
@@trancount returns the number of BEGIN TRAN statements. It is thus not a reliable indicator of whether there is an active transaction. XACT_STATE() also returns 1 if there is an active autocommit transaction, and is thus a more reliable indicator of whether there is an active transaction.
However, I can find no reason to not trust @@TRANCOUNT
. The following test shows that @@TRANCOUNT
does indeed return 1
in an auto-commit transaction:
--- begin setup
GO
CREATE PROCEDURE #TransactionInfo AS
SET NOCOUNT ON;
SELECT @@TRANCOUNT AS [TranCount],
XACT_STATE() AS [XactState];
GO
--- end setup
DECLARE @Test TABLE (TranCount INT, XactState INT);
SELECT * FROM @Test; -- no rows
EXEC #TransactionInfo; -- 0 for both fields
INSERT INTO @Test (TranCount, XactState)
EXEC #TransactionInfo;
SELECT * FROM @Test; -- 1 row; 1 for both fields
I also tested on a real table with a Trigger and @@TRANCOUNT
within the Trigger did accurately report 1
even though no explicit Transaction had been started.