CommandTimeout – How to handle it properly?

ADO.Net Data Provider for SQL Server (System.Data.SqlClient namespace in System.Data.dll assembly) has two common classes we often use: SqlConnection and SqlCommand. SqlCommand class has CommandTimeout property (do not mix it with the ConnectionTimeout which stops long login process). But, do we handle it properly? And what exactly is happening when the command timeout occurs?

If we ommit setting CommandTimeout property, the default of 30 seconds will be used. 0 means wait indefinitely, no timeout will be raised. When timeout occurs, execution of the TSQL stops immediately. It is untrappable which means execution is NOT transferred to the “BEGIN CATCH” TSQL block. Your COMMIT command is also NOT executed if timeout occurred before it is reached (and often is). Timeout can be seen as a sql trace event of a class “Attention”:

At the time of the attention the transaction is not rolled back unless transaction abort (XACT_ABORT) has been enabled. The client needs to submit a rollback. If the client does not submit the rollback and continues other processing the transaction remains open and the behavior scope of the application becomes undefined.

CommandTimeout will only STOP TSQL execution, but the client application is responsible to COMMIT or ROLLBACK the pending transaction (if not already rolled back because of SET XACT_ABORT ON). If client app does not explicitly commit or rollback, the transaction remains open, holding all the locks, blocking other sessions until somebody kills that session! Sounds familiar? Do you have occasional strange blocking with the root blocker that is sleeping session?

The session will stay forever in “sleeping” status. You can do a “proper” try-catch Dispose of command, nicely Close/Dispose the connection or do it with a “using” construct. All that “proper closing” of the connection will not abort the transaction and not even end the sleeping session (because of connection pooling, until that same connection is reused). Transaction is still there, leaked, holding all the locks on a forever “sleeping” session! (sleeping beauty, only this one is rather “ugly”).

That “leaking transaction” behavior is the same on all SqlClient versions I tried (dotnet 2.0, 4.0, 4.5.2) and all SQL Server versions I tried (SQL 2008, 2008R2, 2014). Moreover, it is “by design”. It is “by desgin” to let developers decide what will they do with pending transactions. And we all handle those pending transactions with explicit commit/rollback in our dotnet app, don’t we? We have three choices to do with pending transaction when timeout exception is raised in dotnet code:

commit it

roll it back

leave it opened forever holding all the locks (that is the default “choice” of most of us!)

Would it be nice that Microsoft had a property e.g. CommandTimeoutBehavior on a SqlCommand with those three choices? That would remove the burden from the developers and save lots and lots of hours of many DBAs.

Enough talk, let’s reproduce it and see how it looks like! If you run this script:

Transact-SQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

usetempdb

GO

ifobject_id('test')isnotnulldroptabletest;

CREATETABLEtest(xintprimarykey);

GO

CREATEproceduredbo.p_testas

begin

truncatetabletest;

BEGINTRAN

begintry

PRINT'INSERT1'

INSERTINTOtest(x)values(1)

WAITFORDELAY'00:00:8'

PRINT'INSERT2'

INSERTINTOtest(x)values(2)

PRINT'COMMIT'

IF@@trancount>0COMMITTRAN

endtrybegincatch

PRINT'CATCH!'

IF@@trancount>0ROLLBACKTRAN

endcatch

end

GO

…it will create a table and a procedure p_test in tempdb. Now, in SQL Server Management Studio (SSMS) set the command timeout to 5 seconds (Right click (context menu)->Connection->Change connection->Options):

And run the proc: exec dbo.p_test. After exactly 5 seconds, this error message will strike:

Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Actual System.Data.SqlClient.SqlException is best recognized by property Number = -2:

Transact-SQL

1

2

3

4

5

6

Class : 11

Number : -2

State : 0

Message : Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

The procedure was designed to begin a transaction, insert the row1, wait 8 seconds, insert the row2 and commit. But because CommandTimeout was set to 5 seconds, during that 8 second wait the timeout occured. Let’s check sessions with sp_WhoIsActive and the locks with sys.dm_tran_locks in a SEPARATE query editor window (will be explained later why):

That means, transaction has begun, row1 is inserted, but row2 is NOT, and transaction is never committed – locks are here to stay. Also, the session 57 is in “sleeping” status “forever” with an opened transaction. That is, until we kill that session, close that query editor window, or execute some other command in exactly that same session (that is why we opened the other window).

Summary

When command timeout occurs (based on the CommandTimeout value), if XACT_ABORT is OFF, the explicit transaction that was running will leak. That means, it wont be rolled back, it wont be committed. It will hold all the locks indefinitely (ok, not indefinitely, but for a very long time, until it is killed or somebody reuses exactly that same session). Dotnet application developer needs to handle the exception raised by timeout, and send another command through the same connection object with explicit COMMIT/ROLLBACK to finish the pending transaction.

7 comments on “CommandTimeout – How to handle it properly?”

There is an excellent blog post from MSFT Matt Neerincx that describes various timeouts. It is worth visiting. Although not so new, it still applies. Interesting part is that ConnectionTimeout affects not only login process, but some other functions that send implicit sql commands to the server under the hood. For example:

Thanks for the update, JustRandomReader! When explicit transaction is started within dotnet code (not TSQL!), SqlConnection is aware that transaction exists, and rolls it back without any extra handling code required.
But the handling code is required if transaction is started only within TSQL, which generally I prefer rather than starting transactions from dotnet.

what a great article, it gives huge difference when you have request heavy web apps, when i removed CommandTimeout = 0 the server suddenly calmed down, just magic..
thanks again for sharing knowledge ,
Zura

Thanks Mikhail for the questions.
1) I haven’t tested it with EF, but you can do it yourself. Just make the EF call a procedure that begins a transactions, waits for more than command timeout, and commits. Then see are there any sessions with open transactions hanging around:

2) If you refer to connection pooling, I think it rolls back when reused. sp_resetconnection procedure does it before pooled connection is reused. But, if you have many connections in the connection pool, it might take considerable amount of time for exactly THAT connection to be used again. All that time all the locks it holds will “hang” and potentially block other sessions.

Our Mission

Databases should be fast and simple to use. We make them so. Do not let your customers wait for a slow database response - hire a top professional! We are passionate about creating highly-tuned SQL Server systems. Do you want yours to become one? Simply call us or send email, and we will take care of the rest.

A Random Thought

Keep your transaction log backups at least to second full backup. Even if the latest full backup is corrupt, you can recover without any data loss. E.g. if you take full backup weekly, diff backup daily, and transaction log backup hourly, keep your transaction log backup at least for 2 weeks, so it reaches the second full backup.