After the timeout

Alexander Kuznetsov — Wed, 09 Jan 2013 18:23:00 GMT

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

Search results matching tags 'Ado.Net' and 'T-SQL'

After the timeout