Timeouts calling SQL from C#: TransactionScope appears to override DbCommand CommandTimeout

Question

My understanding of these timeouts is as follows:

• DbCommand.CommandTimeout controls how long a Sql command will run for, before spontaneously ceasing and returning control to the C# calling code (in the form of a thrown timeout SqlException.)
  • i.e. if your query is WAITFOR DELAY '00:00:05' And you set your DbCommand.CommandTimeoutto00:00:01`, then your C# will throw an Exception after 1 second, not 5 seconds.
• The scopeTimeout ctor parameter on TransactionScope controls whether or not the scope rolls-back when it is disposed. It cannot interrupt a SQL execution - it will only trigger an Exception when the scope.Dispose() method is called.
  • i.e. if your query is WAITFOR DELAY '00:00:05' And you set your scopeTimeout to 00:00:01, then your C# will throw an Exception after 5 seconds, not be interrupted before then.

I written tests to verify both of these behaviours in my codebase, on the assumption that only one of the 2 kinds of timeout is present, and they work fine.

If I create the test for "scopeTimeout = 3s; CommandTimeout = 1s; SQL WAITFOR = 5s", then I expect that the CommandTimeout will take precedence and it'll throw a SQL Exception after 1 second. A Test confirms that to be true too.

And finally if I create the test for "scopeTimeout = 1s; CommandTimeout = 3s; SQL WAITFOR = 5s", then I would have expected that the command will still run for 3s before throwing, and then the scope will do [something, TBC] when it Disposes.

But what I actually see is that the SQL runs for the full 5 seconds! i.e. ...

Setting a short scopeTimeout appears to disable the CommandTimeout entirely!!

Why does this happened, and is there a way to prevent it?

Answer 1

"all server side transactionScopes have Infinite timeouts" is a good approach for SQL Server.

And the point in your code where you create the TransactionScope you don't really know whether any operations inside the transaction might be long-running.