SQL Server对超时请求执行什么操作？

Suppose that I use C# to run a long running SQL Server stored procedure (lets say 30 minutes). Further suppose that I put a 1 hour timeout period on the query in C# such that if for whatever reason this SP takes longer than expected, I don't end up monopolizing the DB. Lastly, suppose that this stored procedure has a try/catch block in it to catch errors and do some clean-up should any steps inside it fail.

假设我使用C＃运行一个长时间运行的SQL Server存储过程（比方说30分钟）。进一步假设我在C＃中对查询设置了1小时的超时时间，这样如果由于某种原因这个SP花费的时间比预期的要长，我最终不会垄断数据库。最后，假设此存储过程中有一个try / catch块来捕获错误，并在其中的任何步骤失败时进行一些清理。

Some code (C#):

一些代码（C＃）：

using (SqlCommand comm = new SqlCommand("longrunningstoredproc"))
{
    comm.Connection = conn;
    comm.CommandType = CommandType.StoredProcedure;
    comm.CommandTimeout = 3600;
    comm.ExecuteNonQuery();
}
/* Note: no transaction is used here, the transactions are inside the stored proc itself. */

T-SQL (basically amounts to the following):

T-SQL（基本上等于以下）：

BEGIN TRY
   -- initiailize by inserting some rows into a working table somewhere
   BEGIN TRANS
     -- do long running work
   COMMIT TRANS
   BEGIN TRANS
     -- do long running work
   COMMIT TRANS
   BEGIN TRANS
     -- do long running work
   COMMIT TRANS
   BEGIN TRANS
     -- do long running work
   COMMIT TRANS
   BEGIN TRANS
     -- do long running work
   COMMIT TRANS
   -- etc.

   -- remove the rows from the working table (and set another data point to success)
END TRY
BEGIN CATCH
   -- remove the rows from the working table (but don't set the other data point to success)
END CATCH

My question is, what will SQL Server do with the query when the command times out from the C# side? Will it invoke the catch block of the SP, or will it just cut it off altogether such that I would need to perform the clean-up in C# code?

我的问题是，当命令从C＃端超时时，SQL Server将如何处理查询？它是否会调用SP的catch块，还是会完全切断它以便我需要在C＃代码中执行清理？

2 个解决方案

#1

The timeout is enforced by ADO.NET. SQL Server does not know such a thing as a command timeout. The .NET client will send an "attention" TDS command. You can observe this behavior with SQL Profiler because it has an "attention" event.

超时由ADO.NET强制执行。 SQL Server不知道命令超时这样的事情。 .NET客户端将发送“注意”TDS命令。您可以使用SQL事件探查器观察此行为，因为它有一个“注意”事件。

When SQL Server receives the cancellation it will cancel the currently running query (just like SSMS does when you press the stop button). It will abort the batch (just like in SSMS). This means that no catch code can run. The connection will stay alive.

当SQL Server收到取消时，它将取消当前运行的查询（就像按下停止按钮时SSMS一样）。它将中止批次（就像在SSMS中一样）。这意味着不能运行任何catch代码。连接将保持活跃状态。

In my experience the transaction will be rolled back immediately. I don't think this is guaranteed though.

根据我的经验，交易将立即回滚。我不认为这是有保证的。

TL;DR: A timeout in ADO.NET behaves the same as if you had pressed stop in SSMS (or called SqlCommand.Cancel).

TL; DR：ADO.NET中的超时行为与在SSMS中按下stop（或称为SqlCommand.Cancel）时的行为相同。

Here is reference for this: http://blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-query-and-be-sure-to-process-your-results.aspx

以下是对此的参考：http：//blogs.msdn.com/b/psssql/archive/2008/07/23/how-it-works-attention-attention-or-should-i-say-cancel-the-查询和待确定对过程的-results.aspx

#2

The timeout is something that happens on the connection, not the running query.

超时是连接上发生的事情，而不是正在运行的查询。

This means that your BEGIN CATCH will not execute in the event of a timeout, as the query has no idea about it.

这意味着您的BEGIN CATCH将不会在超时时执行，因为查询不知道它。

Write your cleanup in C#, in a catch(SqlException ex) block (testing for a timeout).

在C＃中编写清理，在catch（SqlException ex）块中（测试超时）。

#1