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 ;