In this blog, I have explored how the XACT_ABORT setting controls Sql Server behavior when it interacts with Desktop and Web applications, in the situation of a query timeout runtime error. It is particularly interesting to see how SQL Server works with web applications that use the connection pooling mechanism in those conditions.  I also briefly covered MARS(Multiple Active Result Sets) technology and how it relates to XACT_ABORT.

The performed tests demonstrate the behavior of a typical stored procedure when executed in a session with the XACT_ABORT set to ON/OFF and in the query timeout situation. The tests covered a Desktop -SSMS  and a web application.

Here is a bit of theory we need to know in order to understand the test results.

XACT_ABORT
It is one of the parameters that define the current session behavior.  The parameter belongs to the group of Transaction statements along with IMPLICIT_TRANSACTIONS, TRANSACTION ISOLATION LEVEL, and REMOTE_PROC_TRANSACTIONS (deprecated in Sql Server 2014). More about the parameter can be found here.

Query Timeout

The queries executed on the client side may be canceled during the execution after a certain period of time. One of the reasons can be the “query timeout”, a scenario in which the app. code decides to cancel the ongoing action if the action takes more time to finish than expected(defined).

Query timeout is a client-side concept. After a certain period of time, a client, more specifically the db provider will raise an Attention event which will signal the query timeout to the db server.  At the next available opportunity, Sql Server will stop/abort the currently executing query in the context of the current connection. The sudden stop will not raise any Sql server errors.

XACT_ABORT defines how Sql Server handles the ongoing transactions in these situations.
It is worth noting to mention the Remote query timeout(Exec sp_configure ‘remote query timeout’). This server-scoped setting is related only to the queries executed by a remote source. e.g. Linked server.

When executing Update/Insert/Delete actions within an explicit transaction in SQL Server, there’s a risk of the transaction remaining open (abandoned) if it’s instructed to stop query execution. In such cases, various SQL Server resources may remain locked, making them unavailable to other queries. By default, XACT_ABORT is set to OFF, which means that, in case of a timeout, SQL Server leaves the handling of uncommitted transactions to the caller (the application code).

The application code can be designed to handle exceptions by either rolling back interrupted transactions and closing the connection or by simply closing the connection, allowing any open transactions to be automatically rolled back*.

*A brief note: In SQL Server, a transaction’s scope is defined differently from the server’s and client’s perspectives. From the server’s point of view, a transaction’s scope is a session. However, from the client’s perspective, the scope is a connection.

In SQL Server, a session can have zero, one, or more* related connections. Internal SQL Server engine processes (with SPID <= 50) don’t have related external connections. When using MARS (Multiple Active Result Sets), a session is associated with multiple hierarchical connections. The client’s connection serves as the parent, and together with the child connections, is logically scoped as ‘one per batch.’ Consequently, the scope of a transaction within the MARS-enabled connection is a batch also known as the  Batch scoped transactions.  This is particularly relevant when the MARS session is in the ‘Local Transaction Active‘ state, where all statements executed in a session run under an explicit or implicit transaction.

To examine the hierarchical organization of transactions, you can use dynamic views like sys.dm_exec_connections, focusing on columns such as net_transport, parent_connection_id, and connection_id.

MARS technology is a super interesting subject for research and certainly cannot fit into a “brief note”  🙂 

Session IDs’ are recyclable and are related to the unique transaction IDs.

SELECT *
FROM sys.dm_exec_sessions
WHERE is_user_process = 1; 
–-the predicate filters out the internal processes.

Connection Pooling

Connecting to the database is a resource-intensive operation. Each time a client interacts with a database, it establishes a new connection, which involves creating a new physical channel/socket, parsing the connection string, performing database authentication, and more. Because of the disconnected nature of the web-based applications, the connections are closed as soon as the client is finished with the db request. Constant opening and closing connections wouldn’t be a problem if there were a small number of clients who don’t interact with the db often. In reality, there is a large number of concurrent clients fighting for server resources.
The idea behind the Connection Pool(CP) mechanism is to allow already existing connections to be reused by the clients. Essentially, CP is a container object that contains a list of connections defined by the same connection string. A connection in this context is a pointer to the memory location where the connection object is stored.

Connection Pools are controlled and maintained by the database providers, like the .NET Framework Data Provider for Sql Server or the ADO.NET set of data access components based on the provider. Connection Pooling is active by default(even if not specified in the connection string). The Pool properties are a part of the connection string properties(key-value pairs).
More about connection pooling and pool fragmentation can be found here.

Another thing that would be interesting to explore is the sequence of events between a web application and Sql server during the connection pooling. The following is one of the typical scenarios.

1. A Client requests data from SqlServer. There is no CP container defined based on a connection string that will be used to define the connection. Client calls the Open() method  of a connection object  e.g based on SlqConnection class if using .NET Data Provider for Sql Server)
2. SqlServer authenticates the user and stores the connection/sql session data. The Audit Login event shows;  sessionid, EventSubClass =”1- Nonpooled”, Connection type  = “1- Non DAC” …etc. The requested connection is not part of the connection pool yet.
3. The client executes the T-SQL batch and closes the connection (Close() method of the connection object). Even if enclosed e.g. in the “using” structure, the connection object won’t be destroyed but only referenced by a pointer now stored in a newly created connection pool object container. The object lives in the app. server memory. At this point, the Sql server is not aware that the client closed (returned) connection to the pool. From sql server point of view, the connection is still open. This also means that the abandoned(not committed) transactions, if any, will remain open – This will be a part of the following tests :)
4. The same or a different client requests to open() the same connection. The code will not create(instantiate) a new connection object. Instead, the pool manager will provide the memory location of the existing connection object. Again, Sql is not aware of what’s happening on the client side and still keeps the information about the original connection.
5. The client now executes a T-SQL batch e.g. using Sqlcommand /ExecuteNonQuery object/method in the context of the connection. It is only at this point that Sql Server receives the information about the connection being reused by someone.
   • a) The event Audit Logout is fired indicating that the existing connection is pooled. The EventSubClass = “2 – Pooled”.
   • b) The data access API layer(in this case SqlClient) executes system stored procedure sp_reset_connection (the sp is not available through T-SQL) to clean* the existing context of the connection and re-validate the authorization of the user-principal through Audit Login and Audit Logout events.  * In this context, cleaning a connection context means cleaning up SqlServer’s session context i.e rollback any active transaction that may have been left open through this session, dropping any local temporary table that has been left behind by the previous owner of the connection, closes any open cursors and deallocates the resources it has been using, resets the CONTEXT_INFO, etc);
   • c) The Audit Login event fires up, again indicating the nature of the connection (EventSubClass = “2- Pooled”)
6.  SqlServer executes tsql batch.
7. The client calls the Close() method and the connection is again returned to the connection pool. (the pointer to the connection object is now available for the next client)

Test case scenarios:

I’ve performed the following tests to investigate how Sql Server reacts with different XACT_ABORT settings. The client applications will initiate a query timeout in the middle of an explicit, ongoing transaction encapsulated in a stored procedure.

Test 1: Desktop application query timeout. (MARS test included)
Test 2: ASP.NET application query timeout with connection pooling ON

Desktop-based client application query timeout

XACT_ABORT OFF (Default)

For this test, I used SSMS as a Windows-based client app. To prepare for the test, create a test environment using the script below:

--create test database
CREATE DATABASE XACT_ABORT_TEST
GO
USE XACT_ABORT_TEST
GO

-- Create a test table
DROP TABLE IF EXISTS dbo.Products; --tsql2016
GO

CREATE TABLE dbo.Products(ProductId INT 
                            CONSTRAINT PK_ProductID 
                                PRIMARY KEY CLUSTERED
                          ,ProductName VARCHAR(1000) NOT NULL
                          ,ProductPrice MONEY NULL );                      
GO
--Insert sample data
INSERT INTO dbo.Products(ProductId
                         ,ProductName
                         ,ProductPrice)     
 SELECT Tab.*
 FROM (VALUES (100,'Hard Drive',80.99)
             ,(200, 'SSD Drive',250.85)
             ,(300, 'Flash Memory 32GB',8.11)
             ,(400, 'Surface Book',4120.00) ) AS Tab(prodId,name,price);
GO
--Check the table
SELECT * FROM dbo.Products;

Stored procedures:

--the procedure updates a product price by the percent
CREATE PROCEDURE spuUpdateProductPrice
     @ProductId INT
    ,@ByPercent DECIMAL(5,2)
    ,@SetXactAbort BIT = 0 --Off by default
AS
BEGIN
    SET NOCOUNT ON;

    --set xact abort
    IF (@@OPTIONS & 16384)!=@SetXactAbort    
     SET XACT_ABORT ON 
    
    --output xact setting
    SELECT IIF( (@@OPTIONS & 16384)=0,'XACT_ABORT IS OFF','XACT_ABORT IS ON')
      
    BEGIN TRANSACTION xactTest;
    BEGIN TRY

        --update price by percent
        UPDATE dbo.Products
            SET ProductPrice += (ProductPrice * (@ByPercent * 0.01))
        WHERE ProductId = @ProductId;

        --this simulates a slow query
        WAITFOR DELAY '00:00:10';

       COMMIT TRANSACTION xactTest;
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT>0
            ROLLBACK TRANSACTION xactTest;

            ;THROW
    END CATCH
  
    RETURN;
END
GO
--select product information
CREATE PROCEDURE spsGetProductDetails
    @ProductId INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ProductId
          ,ProductName
          ,ProductPrice
    FROM dbo.Products
    WHERE ProductId = @ProductId
        OR @productId IS NULL;

    RETURN;
END

SQL Server Profiler:
I’ll use SQL Server Profiler to capture the Attention event when sent by the client application.   Add Error And Warnings/Attention event to the Standard(default) set of the traced events.

SSMS:
SSMS will act as a Desktop client which will cause the query timeout. To simulate the scenario open(CTRL+N) three sessions.

• Session1 – Change the Execution timeout from 0(unlimited) to 3s . The session will be used to run the sp that will cause query timeout (SSMS:RIght click/Connection/Change Connection)

  

The code below will be terminated by the timeout.

USE XACT_ABORT_TEST
GO
-- decrease SSD disk price by 10%
EXEC dbo.spuUpdateProductPrice 
    @ProductId = 200 
    ,@ByPercent = -10

• Session 2– The session will be used to run the second sp that will be reading from the same table used by the procedure initiated in session 1.

USE XACT_ABORT_TEST
GO
EXEC dbo.spsGetProductDetails

• Session 3– Add the code below to monitor the transaction state and the locked resources.

------------------------------
    --check the open transactions
    ------------------------------
    SELECT sTran.transaction_id 
       ,sTran.is_user_transaction AS isUsrTran
       ,sTran.open_transaction_count [NoOfTranPerSession]
       ,cn.parent_connection_id
       ,cn.connection_id
       ,aTran.[name]
       ,aTran.transaction_begin_time
       -- lookup table can be found on 
       -- https://msdn.microsoft.com/en-au/library/ms174302.aspx
       ,CASE aTran.transaction_type
          WHEN 1 THEN 'Read/write'
          WHEN 2 THEN 'Read-only'
          WHEN 3 THEN 'System'
          WHEN 4 THEN 'Distributed'
        END AS [Transaction Type]
        ,CASE aTran.transaction_state
          WHEN 0 THEN 'Not fully initialized'
          WHEN 1 THEN 'Initialized, not started'
          WHEN 2 THEN 'Active'
          WHEN 3 THEN 'Ended' 
          WHEN 4 THEN 'Commit initiated'
          WHEN 5 THEN 'Prepared, awaiting resolution'
          WHEN 6 THEN 'Committed'
          WHEN 7 THEN 'Rolling back'
          WHEN 8 THEN 'Rolled back'
        END AS [Transaction State]
        ,cn.session_id
        ----------- connection params ------------------
        ,cn.net_transport [Conection protocol]
        ,cn.connect_time [Connected At]
        ,cn.client_net_address [Client network address]
        ,cn.client_tcp_port [Client TCP port]
        ,cn.last_read
        ,cn.last_write   
    FROM sys.dm_tran_active_transactions aTran
        INNER JOIN sys.dm_tran_session_transactions sTran
            ON aTran.transaction_id = sTran.transaction_id
                right outer JOIN sys.dm_exec_connections cn
                    ON sTran.session_id = cn.session_id
WHERE sTran.transaction_id IS NOT null

--------------------------------------
--check locks, transactions, sessions
--------------------------------------
SELECT lck.resource_type
      ,DB_NAME(lck.resource_database_id) AS DBName
      ,lck.resource_description AS resDesc
      ,lck.request_mode
      ,lck.request_type
      ,lck.request_status
      ,lck.request_session_id
      ,lck.request_owner_type
      ,ts.transaction_id
      ,tat.name
      ,con.connection_id
      ,tat.transaction_type
FROM sys.dm_tran_locks lck
  INNER JOIN sys.dm_tran_session_transactions ts
    ON lck.request_session_id = ts.session_id
  LEFT OUTER JOIN sys.dm_tran_active_transactions tat
    ON ts.transaction_id = tat.transaction_id
  LEFT OUTER JOIN sys.dm_exec_connections con
    ON ts.session_id = con.session_id

---------------------------------------------
--list of the waiting tasks
SELECT wt.* 
FROM sys.dm_os_waiting_tasks wt
    INNER JOIN sys.dm_exec_sessions s
        ON s.session_id = wt.session_id
WHERE s.is_user_process = 1
---------------------------------------------

To begin the test, enable the Profiler trace and execute the code in session1 and then in session2(sp that just selects data) and finally execute the code in session3.

Analysis:

The sp(Session 1) will take more time(~10s) to execute than the Client’s execution time allows (3s). After 3s, the client sends an Attention event to the Sql server which causes Sequel to immediately stop the sp execution, with no error. Because XACT_ABORT is set to OFF(default), Sql Server will leave the transaction open and let the Client handle the commit/rollback.

The client’s event handler will send the following error message:

Msg -2, Level 11, State 0, Line 3
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Profiler -spids may be different (spid 55 is spid 60 on the previous image)

Now, the sp that runs in Session 2( reads all rows from dbo.Products) has to wait since Session 1’s active transaction(xactTest) holds X(Exclusive) lock on the KEY(the table is a clustered index) and  IX(Intent Exclusive) on the table(object) which is not compatible with the S(Shared Locks) requested by session 2.
The “SELECT” stored procedure will just wait indefinitely(the default execution time for that connection is unlimited).
Session 3 can be used to analyze the open transactions, locked resources, etc.
If we now disconnect Session 1(right click/Disconnect), Sql Server will automatically ROLLBACK all ongoing transactions within the closed connection scope, leaving the DB in the coexistent state – SSD price will not decrease by 10%   :). The stored procedure in Session 2 will finish and if we re-run the code in Session 3 we’ll see that there are no abandoned transactions. All relevant resources will be freed.

Programmers usually use the “using” statement to “wrap” the objects used for the communication with the Database. The structure ensures that all of the objects instantiated within the block will be removed, closed, flushed, disposed, etc.,(C devs won’t like this kind of a black box 🙂

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.ConnectionStr))
{
    using (SqlCommand cmd = new SqlCommand("spuUpdateProductPrice",conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@ProductId", System.Data.SqlDbType.Int).Value = 400; //hardcoded
        cmd.Parameters.Add("@ByPercent", System.Data.SqlDbType.Decimal).Value = -10; //hardcoded
        cmd.Parameters.Add("@SetXactAbort", System.Data.SqlDbType.Bit).Value = 0;//0 - OFF, 1 - ON

        cmd.CommandTimeout = 5; //default is 30s

        conn.Open();
                    
        Console.WriteLine("...about to run dbo.spuUpdateProductPrice");
        try
        {
            cmd.ExecuteNonQuery(); //will take ~10s to execute
        }
        catch (SqlException e)
        {
            //SET UP BREAKPOINT HERE
            Console.WriteLine("Got expected SqlException due to command timeout ");  
            Console.WriteLine(e);
        }
    }    
}

The error handler will handle the timeout and the code will exit the structure closing the connection. The Sql server will then automatically rollback all opened transactions(within the scope of the closed connection) leaving the DB in the coexistent state. This will happen regardless of the XACT_ABORT setting.

To show the difference between XACT_ABORT set to ON/OFF  we can set a break-point at Line:21(any line inside the catch block). With the xact_abort set to OFF the uncommitted transaction will remain open until the code exits the structure. With ON, Sql server will rollback the opened transactions immediately after receiving the Attention signal and before the break-point. This can be monitored through the Session 3 code as mentioned earlier.

XACT_ABORT ON

To test the same scenario with the xact_abort on, reconnect Session 1(Right click/connect – now, the default execution timeout is 3s) and include the third input parameter

USE XACT_ABORT_TEST
GO
-- decrease SSD disk price by 10%
EXEC dbo.spuUpdateProductPrice 
     @ProductId = 200 
    ,@ByPercent = -10
    ,@SetXactAbort = 1 --xact_abort ON

This time Sql server is set up to handle the uncommitted transactions, and when a run-time error occurs – query-timeout in this case, it will rollback the transactions. Session 2 will be able to execute its code. Session 3 will show the “empty resultsets” – no locked resources 🙂

MARS Enabled

Xact_Abort ON/OFF  setting has a different effect on the MARS-enabled connection in the example above.
To test this scenario change the Session 1 connection properties and add an additional connection parameter: MultipleActiveResultSets=True

If we run the test again the results will show that the XACT_ABORT setting does not affect the sql Server behavior* in case of the query timeout run time error. The explicit transaction will be automatically rolled back. Session 2 shows us the MARS transactions hierarchy within the same session.
*The behavior would be different if we executed the stored procedure logic as a script and without explicit transaction. 

ASP.NET application query timeout with connection pooling ON/OFF

The following test demonstrates Sql server’s behavior in case of application timeout when the XACT_ABORT is ON/OFF in combination with the Connection Pooling TRUE/FALSE

The test environment:

1. A simple web application that can perform a) select and b) update actions on a database using one of two available connections – one connection authenticates using the Integrated* security and the other one uses Sql Server authentication.
2. Windows Performance Monitor (perfmon.msc) – to examine connection pool counters
3. Sql Profiler – for tracking events triggered by the app and data provider.
4. Sql Server T-SQL code to track active connections, locks, etc. – Session 3 code from the previous example.

Note: The integrated security will use ApplicationPoolIdentity(AP) to connect to the DB. Execute the script below to create a Login for the AP identity and a Login for an sql server user:

-- windows login - application pool
USE master
GO
--create db server login
CREATE LOGIN  [IIS APPPOOL\DefaultAppPool] FROM WINDOWS
GO

USE XACT_ABORT_TEST
GO
--create db user
CREATE USER  [IIS APPPOOL\DefaultAppPool] 
GO
--add securables to the db user
GRANT EXECUTE ON OBJECT::dbo.spsGetProductDetails TO [IIS APPPOOL\DefaultAppPool]
GO
GRANT EXECUTE ON OBJECT::dbo.spuUpdateProductPrice TO [IIS APPPOOL\DefaultAppPool]
GO

-- Sql Server login
USE master
GO
--
CREATE LOGIN testXact WITH 
    PASSWORD = 'testXact'
    ,CHECK_POLICY =OFF
GO

USE XACT_ABORT_TEST
GO

CREATE USER testXact FOR LOGIN testXact
GO
GRANT EXECUTE ON OBJECT::dbo.spsGetProductDetails TO testXact
GO
GRANT EXECUTE ON OBJECT::dbo.spuUpdateProductPrice TO testXact
GO

The Web app.

The interface allows you to use two different connections to select and update the existing data. The update will cause the timeout run-time error.

The results of the select statements will be displayed in the Grid view

The error message will show under the grid view control.

The code is similar to the code used in the previous example.

using System;
using System.Data.SqlClient;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e){}
    public string ConnString(string auth)
    {      
        {
            if (auth == "IntegratedSecurity")
                return @"Data Source=tcp:(local);
                         Initial Catalog=XACT_ABORT_TEST;
                         Integrated Security=True;
                         Pooling=True;
                         Application Name=WINXactAbort";
            else
                return @"Data Source=tcp:(local);
                         Initial Catalog=XACT_ABORT_TEST;
                         user Id=testXact;
                         password=testXact;
                         Pooling=True;
                         Application Name=SQLXactAbort";  
        }
    }

    //show products
    protected void btnShowProducts_Click(object sender, EventArgs e)
    {
        //show products
        //NOTE: Use the default CommandTimeout: 30s
        using (SqlConnection con = new SqlConnection(ConnString(rBtnLst.SelectedValue)))
        {
            con.Open();

            using (SqlCommand cmd1 = new SqlCommand("spsGetProductDetails", con))
            {
                cmd1.CommandType = CommandType.StoredProcedure;
                cmd1.CommandTimeout = 30;
                try
                {
                    SqlDataReader rd = cmd1.ExecuteReader();
                    Grid1.DataSource = rd;
                    Grid1.DataBind();
                    rd.Close();
                }
                catch (SqlException e1)
                {
                    lblOutputText.Text = e1.Message;
                }
            }
        }
    }

    //Update product
    protected void btnUpdateProducts_Click(object sender, EventArgs e)
    {
        using (SqlConnection con = new SqlConnection(ConnString(rBtnLst.SelectedValue)))
        {
             con.Open();

            using (SqlCommand cmd = new SqlCommand("spuUpdateProductPrice", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 3; //default is 30s

                cmd.Parameters.Add("@ProductId", System.Data.SqlDbType.Int).Value = 400; //hardcoded
                cmd.Parameters.Add("@ByPercent", System.Data.SqlDbType.Decimal).Value = -10; //hardcoded
                cmd.Parameters.Add("@SetXactAbort", System.Data.SqlDbType.Bit).Value = 0;//0 - OFF, 1 - ON

                lblOutputText.Text = "...about to run dbo.spuUpdateProductPrice";
                try
                {
                    cmd.ExecuteNonQuery(); //will take ~10s to execute
                }
                catch (SqlException e2)
                {
                    lblOutputText.Text = e2.Message;
                }
            }
        }
    }
}

Test: Two clients use the app. The first runs an expensive Update which causes the time-out, and the second one tries to Select data from the same table. Both clients use the same connection.Connection Pooling is enabled.

Test preparations:
Open two instances of the web application in two browsers. Run the T-SQL code from the previous example that selects the open transactions, Open SQL Profiler (standard trace + Errors/Attention event, Audit Login/ Audit Logout). Include Filter for ApplicationName in (WINXactAbort, SQLXactAbort). Open Performance Monitor and add .NET Data Provider for SqlServer Counters (example).

Analysis:

1. Two different clients using the same connection type e.g Integrated Security authentication performs the following actions:
   •  1.1  Client1 updates a product price(Update Products). 1st non-pooled connection established.
   • 1.2 During the execution Client2 tries to read the product prices (Show Products). 2nd non-pooled connection established(EventSubClass =”1- Nonpooled”,). Client2 has been blocked by the Client1’s update process. Client2 waits.
   • 1.3 Client1’s update action times out (simulates a long-running query) leaving the products table locked by an uncommitted-abandoned transaction. Attention event raised. Close() method executed. A new CP container has been created. The client “returns” the connection to the newly created CP.  Sql Server thinks that the connection is still active. The CP now has one connection ready to be reused.
   • 1.4 Client2 still waits for Client1 to finish with the update and to unlock the Products table. Eventually, Client2 times out (default command timeout = 30s). The connection is now returned(referenced by a pointer) to the CP. The connection pool now has 2 connections ready to be reused. The Sql Server resources(products table) are still locked by Client1.
   • 1.5 At this point, the Connection Pool has two available (idle) connections. The first still has a non-committed transaction in its context. If a new client, Client3 tries to read the same data, CP will allocate the last connection available using the LIFO(Last In, First Out) algorithm. In this case, the allocated connection will be the one that originally belonged to Client2. As expected, Client3 will get blocked by the same abandoned transaction. Moreover, no client will not be able to access the Products table until the status of the transaction in question gets resolved (rollbacked/committed).

This scenario is common and can cause a lot of frustration since the effects can vary i.e sometimes works, sometimes doesn’t work 🙂

To add to the confusion, sometimes the situation can resolve itself e.g: CP will remove idle connections from the pool after 4 to 7min of inactivity by triggering the Audit Logout event which will, among other things, finally instruct SQL Server to rollback the outstanding transactions in the context of the destroyed connection(session). Another possible scenario is if the  “doomed” connection gets reused by a new Client. In that case, the sp_reset_connection stored proc(mentioned earlier) will instruct Sql Server to “clean the mess” and prepare the clean connection/session context for the new Client.

Profiler – the trace shows the above sequence of events

Connection, session, and transaction metadata

The situation can be avoided using the following approaches:

1. Setting XACT_ABORT to ON. This will instruct Sql server to take action of rolling back the ongoing transactions after receiving the Attention event as a result of the command timeout issued by the client.
2. More thorough data layer coding ie. executing the queries within the properly defined transactions. Properly handle the uncommitted transactions within the catch block.

Both techniques will prevent the unpredictable behavior explained above.

XACT_ABORT ON

To set up the Xact_abort ON change the value of the @SetXactAbort  parameter to 1 and repeat the test.

//show products
...
  cmd.Parameters.Add("@SetXactAbort", System.Data.SqlDbType.Bit).Value = 1;//0 - OFF, 1 - ON
...

The same test shows that as soon as Client1 raises the Attention event, Sql Server stops the code execution and performs rollback on the ongoing transaction. All subsequent read requests(Client 2,3..) will be successful.

Using Connection Transactions 

To test the scenario change the bthUpdateProducts_Click  event to to include transactions. and set parameter @SetXactAbort back to 0.

//Update product
    protected void btnUpdateProducts_Click(object sender, EventArgs e)
    {
        using (SqlConnection con = new SqlConnection(ConnString(rBtnLst.SelectedValue)))
        {
            con.Open();
            // Naming the transaction will make it easier to track using dmvs. 
            // Note that the explicit transaction defined in
            // the sp is now a nested transaction.
            SqlTransaction t1 = con.BeginTransaction("ExplicitTransactionFromCode");

            using (SqlCommand cmd = new SqlCommand("spuUpdateProductPrice", con))
            {
                cmd.Transaction = t1;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandTimeout = 3; //default is 30s

                cmd.Parameters.Add("@ProductId", System.Data.SqlDbType.Int).Value = 400; //hardcoded
                cmd.Parameters.Add("@ByPercent", System.Data.SqlDbType.Decimal).Value = -10; //hardcoded
                cmd.Parameters.Add("@SetXactAbort", System.Data.SqlDbType.Bit).Value = 1;//0 - OFF, 1 - ON

                lblOutputText.Text = "...about to run dbo.spuUpdateProductPrice";
                try
                {
                    cmd.ExecuteNonQuery(); //will take ~10s to execute
                    t1.Commit(); //NOTE: The COMMIT TRANSACTION in sp, if executed would only 
                                //decrease the @@Transcout value by 1.
                }
                catch (SqlException e2)
                {
                    lblOutputText.Text = e2.Message;
                    if (t1.Connection != null)
                    {
                        t1.Rollback();
                    }
                }
            }
        }
    }

This time the code will rollback the transaction within the catch block and the resources will not be blocked. If we now set XACT_ABORT  to ON, and repeat the same test, after the Attention event,  SQL Server will Rollback the transaction (@@Transccount = 0) and the code will skip executing Rollback() method because the Connection property of the transaction object t1 returns null since the transaction is no longer valid.

Conclusion

It is important to understand the XACT_ABORT setting to be able to prevent and/or explain Sql server’s behavior in case of a query timeout run-time error.  When set to OFF, the default value, Sql server will let the client handle the timeout situation and take care of the connection elements i.e., ongoing transactions. Query timeouts are the Client-side concept only and Sql Server does not treat them as errors. As soon as possible, after receiving the Attention event, Sequel will stop the execution reporting no errors. If XACT_ABORT is set to ON, sql server will rollback unfinished transactions and then return to the Client.
Desktop applications may use different approaches when dealing with DB connections. Some applications may handle the query timeout and close the connection(dispose the connection object). This instructs Sql Server to rollback all unfinished transactions in the scope of the session/connection. In this context, the XACT_ABORT setting will not have a direct effect on the execution. Other applications e.g., SSMS, will keep connections open after handling query timeouts. In this case, the abandoned transactions may cause other requests to wait to acquire the required locks and the problem can escalate. In this context, XACT_ABORT’s value “ON” will rollback the abandoned transactions preventing the resource locks.
Web applications usually use the Connection Pooling technology to optimize the database authentication process. The connections(connection objects) will stay “live” and ready to be re-used by other clients. Every CP contains a collection of similar Connections – connections with the same connection string. The availability of the connections follows the LIFO(Last In, First Out) algorithm. For example, if we have a Connection Pool CP1 with  4 connections in it and  ready to be reused CP1 = {c1,c2,c3,c4}. Connection c3 has left an abandoned transaction leaving table T1 locked. Scenario1: A client wants to read data from T2. CP1 will provide the client  with the connection c4. The client clears  the connection/session context left from the previous owner and reads the data with no problems. Scenario2: A client wants to read data from T1. CP1 provides connection c4. After clearing the c4 context the client gets blocked by the abandoned transaction left in the context of the c3. This can cause another timeout.   Scenario3. A client wants to read T1, gets c4, clear the previous context and ends up blocked. At the same time another client wants to read T1. Now CP1 serves c3 to the client. Client clears the connection’s previous context which includes the abandoned transaction rollback. The latest client now can read T1 as well as the previous one.

MARS Enabled Connections may affect SQL Server behavior in the case of a query timeout since the technology changes the scope of a transaction – Batch scoped transactions. This existing technology is something I would like to blog about in one of my future posts.

There are many case scenarios in which the query timeout can cause confusion and unwanted results. There are two main approaches to resolve this; a) more thorough coding on the client side which includes proper error handling and use of the connection transactions. b) using XACT_ABORT ON on the Sequel side which will handle the abandoned transactions.

My personal preference is not to rely on the Client’s code and to always use XACT_ABORT set to ON when needed. During more than 10 years of database programming, I have worked with only a handful of web programmers who were interested in this matter. In most cases, their perception of a database is a simple data store, preferably “controlled” by an ORM framework. After a certain period of production life, these “Agile products” usually become slow, not responsive, and sometimes unpredictable. The Connection pooling/query timeout problem is one of the unwanted scenarios. From our(DB Devs) perspective this may not necessarily be a “bad thing” since we all get a “good dollar value” for repairing the mess afterward… but this can be an inspiration for another, more philosophical post  🙂

Thanks for reading.

Dean Mincic