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