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

After the timeout

After the timeout, we may sometimes, but not always, end up with an outstanding transaction. If this is the case, we need to rollback the transaction ourselves. Otherwise the consequent commands which use the same connection might not work as expected.

Let us debug through some client code and see what is happening in more detail.

Prerequisites

We shall need a few database objects, as follows:

 CREATE TABLE dbo.Log1(InTran VARCHAR(20));
GO
CREATE PROCEDURE dbo.TestTran
AS
BEGIN
   IF
@@TRANCOUNT > 0 BEGIN
                       INSERT
dbo.Log1(InTran) VALUES('In Tran');
                      
END
                      ELSE BEGIN
                       INSERT
dbo.Log1(InTran) VALUES('Not In Tran');
                      
END ;
END ;
GO

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
 

Running the example

In the following test, add three breakpoints at the placeholder statements:

       [Test]
        
public void TimeoutTest()
        
{
            
for (var i = 0; i < 1000; i++)
            
{
                
using (var conn = new SqlConnection(localConnString))
                
{
                    conn.Open
();
                    
try
                    
{
                        
const string iwilltimeout = "EXEC dbo.IWillTimeOut";
                        
ExecuteSql(conn, iwilltimeout, 1);
                    
}
                    
catch (SqlException e)
                    
{
                        System.Console.WriteLine
(e.Message + " " + e.Number);
                    
}
                    
const string saveTrancount = "EXEC dbo.TestTran";
                    
ExecuteSql(conn, saveTrancount);
                    
System.Console.WriteLine("Breakpoint placeholder 1");
                
}
                
using (var conn = new SqlConnection(localConnString))
                
{
                    conn.Open
();
                    
const string saveTrancount = "EXEC dbo.TestTran";
                    
System.Console.WriteLine("Breakpoint placeholder 2");
                    
ExecuteSql(conn, saveTrancount);
                    
System.Console.WriteLine("Breakpoint placeholder 3");
                
}
            }
        }
  

Let us start a profiling session that captures individual SP:StmtStarting and SP:StmtCompleted events. Also let us debug through this code.

First breakpoint - outstanding transaction

When we stop at the first breakpoint, let us run the following query:

SELECT * FROM dbo.Log1 WITH(NOLOCK)

It will return "In Tran", which means that after the timeout dbo.TestTran was called in the middle of an outstanding transaction.

Note: not all timeouts result in outstanding transactions. For example, later you can rerun this example using a different stored procedure:


ALTER PROCEDURE dbo.IWillTimeOut
AS
  BEGIN
;
    
BEGIN TRANSACTION ;
    
WAITFOR DELAY '00:00:05' ;
    
COMMIT ;
  
END ;
GO

You can see for yourself that when this procedure times out, there will be no outstanding transaction.

Second breakpoint - still outstanding transaction

Let us debug to the second breakpoint. Technically the first connection went out of scope and has been reused by the second using block, but we can still see the same uncommitted data:

SELECT * FROM dbo.Log1 WITH(NOLOCK)

The connection has not been reset yet - we do not see sp_reset_connection in the Profiler yet.

Third breakpoint - connection reset

Only when we actually get to run a command against the reused connection, the connection is actually reset. We can observe sp_reset_connection call in the Profiler, and the uncommitted data inserted by the first invocation of dbo.TestTran is gone.

Note: to my best knowledge, there is no guarantee that the same connection will be reused. It consistently happens on my box, but it could work differently on yours. As usual with closed source systems, one can never know for sure.

Consequences of continuing to use the same connection after the timeout

As we have seen, if we catch a timeout and later modify the database using the same connection, all the modifications done after the timeout may be eventually rolled back and lost, without us getting any error messages.

Conclusion

We need to rollback the outstanding transaction ourselves. We must also be aware that not all timeouts result in outstanding transactions. I have written two extension methods that implement it, and I will post it soon.

You may want to read more about connection pools here

Published Wednesday, January 09, 2013 1:23 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

 

Adam Machanic said:

Hi Alex,

I don't think that your conclusion logically follows... here's a slightly reformatted version:

"If we return the connection to the pool and if it is reused, the transaction will be rolled back."

Rephrase the second "if" as a "when" and now you have a problem -- because you can't tell me, with any certainty, when that connection will be reused and the transaction rolled back. Which means that you can have a transaction sitting there holding some resource locks for a long time. Maybe even blocking someone else, whose command didn't time out (yet).

So my conclusion would be different than yours. It would be: "You should always roll back any active transaction after a timeout."

--Adam

January 9, 2013 3:47 PM
 

Alexander Kuznetsov said:

Hi Adam,

I agree, and I have already implemented two extension methods that roll back active transactions, as well as edited my post.

Thanks!

January 9, 2013 4:04 PM

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