Properly closing a connection to Sql Server in DotNet is more challenging than one might think. Classic code looks something like this MSDN example:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    Console.WriteLine("ServerVersion: {0}", connection.ServerVersion);
}

using (SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

Console.WriteLine("ServerVersion: {0}", connection.ServerVersion);

}

Or a mixture of “using” and “try-catch” constructs like in this nice example.

But, none of them handles the leaked transactions which stay opened after command timeout occurs. Not many people know that the SqlCommand timeout event immediately interrupts tsql when occurs, and even tsql’s catch block is NOT executed, so rollbacks/commits in tsql are not reached! Therefore, transaction stays opened holding all the locks, and there is no chance for the interrupted tsql to close that transaction. It must be done from the client (dotnet catch block). Have you ever seen how catch block that properly handles the timeout exception looks like? I haven’t. So I decided to write one here that everyone can make use of.

This is written in PowerShell 4.0 ISE, but it uses dotnet SqlClient classes so it is easily convertable to any dotnet language:

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=.;Initial Catalog=tempdb;Integrated Security=true;"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $conn

try
{
    $conn.Open()

    $cmd.CommandTimeout = 5
    $cmd.CommandText = "exec dbo.p_test"

    echo "Here it goes..."
    $cmd.ExecuteNonQuery()
}
catch [System.Data.SqlClient.SqlException]
{
    # Do something. E.g. log the error

    # Handle transactions leaked after command timeot
    if($_.Exception.Number -eq -2) # -2=command timeout occured!
    {   # Closing explicit transactions. Closing connection wont clear them!
        # IMPORTANT: Closing tran must be done with the same connection that created them!
        echo "Closing leaked transaction (if any)"
        #$cmd.CommandText = "WHILE @@TRANCOUNT>0 COMMIT TRAN;" # if you want to save
        $cmd.CommandText = "IF @@TRANCOUNT>0 ROLLBACK TRAN;" # if you want to discard
        $cmd.ExecuteNonQuery()
    }

    # Re-raise the error or raise a more user-friendly one.
    # Do not hide errors as they did not happened!
    throw;
}
finally
{
    $cmd.Dispose()
    #$conn.Close() # not essential because it will be called within Dispose()
    $conn.Dispose() # calls Close() plus other cleanup
}

$conn = New-Object System.Data.SqlClient.SqlConnection

$conn.ConnectionString = "Server=.;Initial Catalog=tempdb;Integrated Security=true;"

$cmd = New-Object System.Data.SqlClient.SqlCommand

$cmd.Connection = $conn

try

{

$conn.Open()

$cmd.CommandTimeout = 5

$cmd.CommandText = "exec dbo.p_test"

echo "Here it goes..."

$cmd.ExecuteNonQuery()

}

catch [System.Data.SqlClient.SqlException]

{

# Do something. E.g. log the error

# Handle transactions leaked after command timeot

if($_.Exception.Number -eq -2) # -2=command timeout occured!

{ # Closing explicit transactions. Closing connection wont clear them!

# IMPORTANT: Closing tran must be done with the same connection that created them!

echo "Closing leaked transaction (if any)"

#$cmd.CommandText = "WHILE @@TRANCOUNT>0 COMMIT TRAN;" # if you want to save

$cmd.CommandText = "IF @@TRANCOUNT>0 ROLLBACK TRAN;" # if you want to discard

$cmd.ExecuteNonQuery()

}

# Re-raise the error or raise a more user-friendly one.

# Do not hide errors as they did not happened!

throw;

}

finally

{

$cmd.Dispose()

#$conn.Close() # not essential because it will be called within Dispose()

$conn.Dispose() # calls Close() plus other cleanup

}

Important lines are in the “catch” block. If timeout SqlException is detected (e.Number=-2), additional command is sent to SQL within the same connection. That is very important, because we can only close transaction if we are in the same connection that started that transaction. The command can be:

WHILE @@TRANCOUNT>0 COMMIT TRAN; — To commit the transaction. “While” is to work even with nested transactions.
IF @@TRANCOUNT>0 ROLLBACK TRAN; — To rollback the transaction. Single rollback exits all levels of nested transactions, no need to loop.

Summary

I wanted to provide you a code that properly handles a command timeout event when it occurs, without leaving opened transactions and locks behind. That is a common scenario that DBA’s usually solved by killing connections, but I think it is much better to solve it by properly handling the event. So violent kills and blockings caused by unclosed transactions will be prevented to occur at the very root. It will make your app more reliable, users happier, DBAs less work, and developers less banging their heads on how to solve that heck of a “leaking transaction” problem that sporadically occurs causing havoc on the database.

3 Comments on “CommandTimeout – How to handle it properly – code example”

Joel says:

2016-03-16 at 03:45

Thanks for this. We’ve been having trouble that I think may be due to command timeouts and while I read (and tested) that you can use “SET XACT_ABORT ON” in your query to make sure transactions are rolled back and locks released, I’ve been thinking that there must be a way to handle command timeouts from the client, but I couldn’t find any example code.

Vedran says:

2016-04-11 at 09:32

Update: changed the code to prefer ROLLBACK in timeout handler.

shweta says:

2019-06-25 at 11:14

must read….
nive collection…helpful….
thanks ……keep going on…….

CommandTimeout – How to handle it properly – code example

Summary

3 Comments on “CommandTimeout – How to handle it properly – code example”

Leave a Reply Cancel reply