THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

The client code that handles timeouts

After timeouts we need to make sure that active transactions, if any, are rolled back. All timeout handling must be done on the client. This post provides the implementation and unit tests.

Implementation

The following class extends SqlCommand and rolls back active transactions after timeouts:


    
public static class SqlCommandExtentions
    {
        
public static void ExecuteNonQueryWithErrorHandling(this SqlCommand command)
        
{
            
try
            
{
                command.ExecuteNonQuery
();
            
}
            
catch (SqlException e)
            
{
                HandleSqlException
(command, e);
                
throw;
            
}
        }

        
public static SqlDataReader ExecuteReaderWithErrorHandling(this SqlCommand command)
        
{
            
try
            
{
                
return command.ExecuteReader();
            
}
            
catch (SqlException e)
            
{
                HandleSqlException
(command, e);
                
throw;
            
}
        }

        
private static void HandleSqlException(SqlCommand command, SqlException e)
        
{
            
const int timeoutCode = -2;
            
if (e.Number != timeoutCode) return;
            
var rollbackCommand = command.Connection.CreateCommand();
            
rollbackCommand.CommandText = "IF @@TRANCOUNT > 0 BEGIN ; ROLLBACK ;  END ;";
            
rollbackCommand.CommandType = CommandType.Text;
            
rollbackCommand.CommandTimeout = 0;
            
rollbackCommand.ExecuteNonQueryWithErrorHandling();
        
}
    }
  

Testing

We need to unit test the two methods. For each one, we need to test the following four cases:

  • Command succeeds 
  • Command blows up with some other error, which must not be caught by our handling
  • Command times out, active transaction
  • Command times out, no active transaction

The following tests cover these four cases for ExecuteReader. The other four tests are very similar, so there is no need to post them.

 
        [Test]
        
public void ExecuteReaderWithErrorHandling_WorksOnSuccess()
        
{
            
using (var dr = ExecuteReaderWithErrorHandling(sqlConn, "SELECT 1 AS n"))
            
{
                Assert.IsTrue
(dr.Read());
                
Assert.AreEqual(1, dr.GetInt32(0));
            
}
        }

        [Test]
        
public void ExecuteReaderWithErrorHandling_HandlesTimeout_ActiveTransaction()
        
{
            
try
            
{
                ExecuteReaderWithErrorHandling
(sqlConn, "EXEC dbo.IWillTimeOut", timeoutInSeconds: 1);
                
Assert.Fail("Must throw exception");
            
}
            
catch(SqlException e)
            
{
                Assert.AreEqual
(-2, e.Number);
                
VerifyNoOpenTransaction(sqlConn);
            
}
        }

        [Test]
        
public void ExecuteReaderWithErrorHandling_HandlesTimeout
_NoActiveTransaction()
        
{
            
try
            
{
                ExecuteReaderWithErrorHandling
(sqlConn, "EXEC dbo.IWillTimeOutWithoutTransaction", timeoutInSeconds: 1);
                
Assert.Fail("Must throw exception");
            
}
            
catch (SqlException e)
            
{
                Assert.AreEqual
(-2, e.Number);
                
VerifyNoOpenTransaction(sqlConn);
            
}
        }

        [Test]
        
public void ExecuteReaderWithErrorHandling_ThrowsOtherExceptions()
        
{
            
try
            
{
                ExecuteReaderWithErrorHandling
(sqlConn, "EXEC dbo.IThrowError8134");
                
Assert.Fail("Must throw exception");
            
}
            
catch (SqlException e)
            
{
                Assert.AreEqual
(8134, e.Number);
                
VerifyNoOpenTransaction(sqlConn);
            
}
        }

These unit tests utilize a few database objects:

 
CREATE FUNCTION dbo.IWillCompleteInNSeconds ( @NumSeconds INT )
RETURNS INT
AS
    BEGIN
;
      
DECLARE @StartTime DATETIME ,
        
@Toggle INT ;
      
SELECT  @StartTime = GETDATE() ,
              
@Toggle = 1 ;
      
WHILE DATEDIFF(SECOND, @StartTime, GETDATE()) < @NumSeconds
        
BEGIN ;
          
SET @Toggle = 1 - @Toggle ;
        
END ;
      
RETURN 0 ;
    
END ;
GO

CREATE PROCEDURE dbo.IWillTimeOut
AS
  BEGIN
;
    
BEGIN TRANSACTION ;
    
SELECT  dbo.IWillCompleteInNSeconds( 5 ) AS SomeNumber ;
    
COMMIT ;
  
END ;
GO

CREATE PROCEDURE dbo.IWillTimeOutWithoutTransaction
AS
  BEGIN
;
    
WAITFOR DELAY '00:00:05' ;
  
END ;
GO

CREATE PROCEDURE dbo.IThrowError8134
AS
BEGIN
;
    
SET XACT_ABORT ON ;
    
DECLARE @i INT ;
  
SELECT @i = 1/0 ;
  
SELECT 1 AS n;
END ;
 

Published Thursday, January 10, 2013 4:05 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

gbn said:

Why not use SET XACT_ABORT ON so the client code doesn't need to run anything?

Example: code crashes but connections stays open in the connection pool.

See my answers on SO http://stackoverflow.com/search?tab=votes&q=user%3a27535%20%2bset%20%2bxact_abort

January 11, 2013 6:15 AM
 

dan holmes said:

This seems to be a behavior only when 'Connection Pooling' is on.  If you add 'Pooling=False' to the connection string, then the moment the connection object goes out of scope or is closed both the connection and transaction are gone.

--

dan

http://dnhlmssql.blogspot.com/

January 11, 2013 8:58 AM
 

Adam Machanic said:

gbn: XACT_ABORT will *not* abort the transaction in case of an Attention event (a.k.a. timeout).

dan: sure, we could disable connection pooling. But now you've taken a major, major performance and scalability hit. Is it worth it? No way. Use Alex's code (or roll your own) and keep pooling wherever possible.

January 11, 2013 1:58 PM
 

Alexander Kuznetsov said:

@gbn: on one hand, Ken Henderson clearly stated in his blog "There's no such thing as a query timeout": "An attention tells the server to cancel the connection's currently executing query (if there is one) as soon as possible.  An attention doesn't rollback open transactions". Of course, it was written long ago, on 20 Oct 2005.

On the other hand, I just ran a few tests, and XACT_ABORT ON consistently rolls back after a timeout. Does it always do so? Dan Guzman thinks so: "Use Caution with Explicit Transactions in Stored Procedures". Because SQL Server is not open source, we cannot verify ourselves...

January 11, 2013 4:01 PM
 

Alexander Kuznetsov said:

@Dan Holmes: in my experience not using connection pooling is usually more expensive than using it.

January 11, 2013 4:02 PM
 

Adam Machanic said:

@alex and @gbn: I just ran some tests as well, and it seems this did change at some point -- XACT_ABORT does indeed always roll back the transaction when an Attention event occurs.

Here's another reference from Microsoft: https://blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-query-and-be-sure-to-process-your-results.aspx

"At the time of the attention the transaction is not rolled back unless transaction abort (XACT_ABORT) has been enabled."

--Adam

January 12, 2013 12:50 PM
 

Alexander Kuznetsov said:

@gbn I ran more tests, and XACT_ABORT ON consistently rolls back on timeout. Thank you for your comment.

On one hand, when we handle timeouts on the client, we can implement it only once in one place, as I demonstrated in my code samples. This solves our problem right away.

On the other hand, we absolutely want to roll back ASAP, so it makes sense to add SET XACT_ABORT ON to all my reading procedures, and redeploy. (All my writes have SET XACT_ABORT ON already.) This is a better solution in the long run.

@Adam yes probably the behavior changed at some time.

January 14, 2013 3:46 PM
 

handles said:

I just came onto your post and found it quite interesting. I am also associated with Door Handles, Door knobs, kitchen Door Handles, Door Handles suppliers, Door handles UK, Ironmongery Suppliers, Handles, knobs and love to enjoy the stuff on the same as its rarely found on internet. Thanks again for writing such a good post.

February 1, 2013 1:07 AM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement