sql中的超时错误 [英] Timeout error in sql

问题描述

 Hello Friends，
在存储过程中，我尝试捕获块。 
我在try块中做了大量的计算。因此，如果数据很大，有时会出现超时错误。 
因此在catch块中我回滚了事务。 
现在我的问题是，如果超时发生，catch块中的代码是否会执行？ 
我的交易会被回滚吗？ 
如果没有，那么有没有办法回滚这些交易？ 
 
谢谢，
 Lok .. 

解决方案

首先，如果你能给我更多关于你的超时错误的详细信息会很好。



有时会绕过CATCH块（KILL命令和超时）。为了确保整个事务终止并回滚，您需要在存储过程中添加以下行：

  SET   XACT_ABORT   ON  

请查看以下链接：

http://technet.microsoft.com/en-us/library/ms188792(v = sql.100）.aspx [ ^ ]



另外，您可以考虑更改一些超时设置...

当我发生这种事情时，我总是在连接设置中将timeout参数设置为0 。 
实际上我总是将超时设置设置为零 - 这基本上意味着连接不会超时。 
 
然后你可以在那之后修复慢查询 - 我的规则是，如果查询需要很长时间，它不会导致用户出现异常。

Hello Friends,
In a stored procedure, I have try and catch blocks.
I do heavy calculations in try block. So sometimes I get timeout error if the data is large.
So in a catch block I rollback the transactions.
Now my question is, if the time out occurs, will the code in catch block ever execute? 
Will my transactions get rolled back?
If not, then is there any way to rollback those transactions?

Thanks,
Lok..

解决方案

Hi,

First of all, it would be good if you could give me more details about your timeout error.

Sometimes CATCH blocks are bypassed (KILL command and timeout). In order to make sure that the entire transaction is terminated and rolled back, you need to add this line in your stored procedure:

SET XACT_ABORT ON

Please take a look at the following link:
http://technet.microsoft.com/en-us/library/ms188792(v=sql.100).aspx[^]

Also, you may consider changing some timeout settings...

When I get this sort of thing happening I always set the timeout parameter to 0 in the connection setting.
In fact I pretty much always set the timeout setting to zero -  which basically means the connection will not time out.

You can then go about fixing slow queries after that point - my rule being that if a query takes a long time it should not cause an exception for the user.

这篇关于sql中的超时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！