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

XACT_STATE() returns 1 when used in SELECT with some system variables but without FROM clause

When selecting some combination of system variables and built-in functions but not using a FROM clause, XACT_STATE() can return a value of 1 even when there is no active transaction. When combining with only @@TRANCOUNT it will correctly return 0. But when combining with @@SPID (among others) it will incorrectly return 1.

This is the same issue as reported in this Connect bug:

https://connect.microsoft.com/SQLServer/Feedback/Details/846574

which was closed as "By Design", but only due to the testing being too narrowly focused on being used with DMVs. The final response from MS in that item states:

"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."

However, that is clearly incorrect as the repro code shows.

I have tested on both SQL Server 2012 SP2 and 2014 SP1 but have not yet tested on 2016.

------------------------------------

Also, in that duplicate item (# 846574), the statement is made:

"@@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."

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.

The only cases that I could find where XACT_STATE() returns 1 when @@TRANCOUNT returns 0 is in an UPDATE or regular INSERT statement. However, those are not times when XACT_STATE() or @@TRANCOUNT are being checked in order to determine how to proceed.

1 vote
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

4 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Solomon Rutzky commented  ·   ·  Flag as inappropriate

    So, I found some non-"@@" functions that also cause this issue. I tested the following on the same two SQL Server versions mentioned in the previous comment, except that I did not test "CURRENT_TRANSACTION_ID()" on SQL Server 2012 as it was not available in that version.

    -- The following all return 1 for XACT_STATE() when there is no active transaction:
    SELECT XACT_STATE(), CURRENT_REQUEST_ID();
    SELECT XACT_STATE(), CURRENT_TRANSACTION_ID()

  • Solomon Rutzky commented  ·   ·  Flag as inappropriate

    I tested the following on SQL Server 2017 Developer CU7 (14.0.3026.27) and 2012 Express Local DB SP4-GDR (11.0.7462.6), both returning the same results.

    -- The following all return 1 for XACT_STATE() when there is no active transaction:
    SELECT XACT_STATE(), @@CONNECTIONS;
    SELECT XACT_STATE(), @@CPU_BUSY
    SELECT XACT_STATE(), @@DATEFIRST;
    SELECT XACT_STATE(), @@DBTS;
    SELECT XACT_STATE(), @@DEF_SORTORDER_ID;
    SELECT XACT_STATE(), @@DEFAULT_LANGID;
    SELECT XACT_STATE(), @@IDENTITY;
    SELECT XACT_STATE(), @@IDLE;
    SELECT XACT_STATE(), @@IO_BUSY;
    SELECT XACT_STATE(), @@LANGID;
    SELECT XACT_STATE(), @@LANGUAGE;
    SELECT XACT_STATE(), @@LOCK_TIMEOUT;
    SELECT XACT_STATE(), @@MAX_CONNECTIONS;
    SELECT XACT_STATE(), @@MAX_PRECISION;
    SELECT XACT_STATE(), @@MICROSOFTVERSION;
    SELECT XACT_STATE(), @@NESTLEVEL;
    SELECT XACT_STATE(), @@OPTIONS;
    SELECT XACT_STATE(), @@PACK_RECEIVED;
    SELECT XACT_STATE(), @@PACK_SENT;
    SELECT XACT_STATE(), @@PACKET_ERRORS;
    SELECT XACT_STATE(), @@PROCID;
    SELECT XACT_STATE(), @@REMSERVER;
    SELECT XACT_STATE(), @@SERVERNAME;
    SELECT XACT_STATE(), @@SERVICENAME;
    SELECT XACT_STATE(), @@SPID;
    SELECT XACT_STATE(), @@TEXTSIZE;
    SELECT XACT_STATE(), @@TIMETICKS;
    SELECT XACT_STATE(), @@TOTAL_ERRORS;
    SELECT XACT_STATE(), @@TOTAL_READ;
    SELECT XACT_STATE(), @@TOTAL_WRITE;
    SELECT XACT_STATE(), @@VERSION;

    There might be scenarios beyond these internal variables / functions that cause XACT_STATE() to erroneously return 1, but so far I have only tested all available "@@" variables / functions.

Feedback and Knowledge Base