xact_state() cannot be used reliably to determine whether a transaction is doomed - by Erland Sommarskog

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


8
0
Sign in
to vote
ID 411709 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 2/5/2009 2:14:50 PM
Access Restriction Public

Description

If you wants to find out whether a transaction is doomed, you are supposed to use xact_state, which would return -1 in this case.

However, xact_state() returns 0 if there is no active user-defined transaction. This means that if the transaction is a system transaction, you cannot tell whether the transaction is committable or not.

There are at least two context where an SQL module can execute in the context of a system-defined transaction: triggers and code run from INSERT-EXEC. See the repro
section for details.

Give that Books Online says that xact_state gives information about user-defined transactions only, the behaviour cannot be construed to be a bug in the true sense of
the word. However, the current behaviour limits the usefulness of the function, and makes it more or less unreliable. (You cannot tell as an SP programmer whether your proc will called from a trigger or INSERT-EXEC three years from now.) So I file this as engine bug,
for reconsideration for SQL 11.

But if the current behaviour is to stand, Books Online should point this out in all places where it discusses xact_state()
Sign in to post a comment.
Posted by Erland Sommarskog on 6/22/2016 at 11:42 AM
Taffmak: you could use sys.dm_tran_session_transactions.is_user_transaction, although it is not unproblematic to put this in application code, as it requires the permission VIEW SERVER STATE. This could be handled with certificate signing, though.

I would be interested to know the scenario where you need to know in a trigger whether the transaction is a user transaction or not.
Posted by taffmak on 6/21/2016 at 5:55 PM
XACT_STATE wasn't broken because the documentation (still, in 2016) states that it relates to the state of a USER transaction.
You have actually changed the behaviour so that it no longer does what the documentation states - in other words you have now broken XACT_STATE.

For those of us that depend upon the specifically documented behaviour what are we supposed to do?

We are in the progress of migration from SQL 2008 to SQL 2012 and have a significant code base that needs to know, within the context of a trigger, whether the current session is within a user transaction or not.
Now that XACT_STATE is broken and no longer does it's documented job - what is the alternative?
Posted by Jānis D on 3/8/2011 at 7:10 AM
Why I got here: was making After Update trigger. There was procedure call inside trigger- and i do not want to know if there is or is not error while executing procedure. Was trying to use TRY CATCH.

After 3 days of background-thinking it hit me- in this case the best solution is to use service broker..

Am I right?
Posted by Microsoft on 5/12/2009 at 12:17 PM
Hi Erland,
We have now fixed the issue for the next major version of SQL Server. XACT_STATE will now return the transaction state for system transactions too. Thanks again for reporting this issue.

--
Umachandar, SQL Programmability Team
Posted by Umachandar Jayachandran - MS on 5/8/2009 at 2:55 PM
Hi Anthony,
If your intent is to just determine if a transaction is active or not, you can check for @@TRANCOUNT only. You can also use @@TRANCOUNT to determine nesting of transactions.
You need XACT_STATE information to determine if you can rollback or commit a transaction. We are looking at correcting the issue with XACT_STATE for the system transactions too and that will give one way to detect transaction state. This will however be done only for the next major version of SQL Server.

Thanks
Umachandar, SQL Programmability Team
Posted by Anthony C Bloesch on 4/28/2009 at 11:10 AM
Here is another repro

The code below returns the following on SQL Server 2008 RTM and SP1. A transaction is active but xact_state is 0. A workaround wound be much appreciated.

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
    Mar 29 2009 10:27:29
    Copyright (c) 1988-2008 Microsoft Corporation
    Developer Edition on Windows NT 6.1 <X86> (Build 7077: )

Msg 50000, Level 16, State 0, Procedure TestTrigger, Line 9
xact_state() = 0; @@trancount = 1

Reproduce case.
use master;
go

if db_id(N'Test') is not null
begin
drop database [Test];
end;
go

create database [Test];
go

use [Test];
go

create table [Test]
(
[Id] int primary key
);
go

create trigger [TestTrigger] on [Test]
after insert
as
begin
declare @xactState nvarchar(10) = convert(nvarchar(10), xact_state());
declare @trancount nvarchar(10) = convert(nvarchar(10), @@trancount);

raiserror('xact_state() = %s; @@trancount = %s', 16,0, @xactState, @trancount);
end;
go

print @@version;

insert into [Test] ([Id])
values (1);
go
Posted by Microsoft on 2/11/2009 at 5:23 PM
Hi Erland,
Thanks for reporting the issue. We will look at it for the next major version of SQL Server. We are considering changes to the error handling behavior and use of XACT_STATE built-in falls in that space. And we will also have to revisit the notion of doomed transaction itself since it creates lot of confusion.

--
Umachandar, SQL Programmability Team