取消查询后交易仍处于打开状态 [英] Transaction is still open after cancelling query

问题描述

在 sql server 2008 中，我使用的是这样的模式:

In sql server 2008, I am using a pattern like this:

begin transaction

begin try

/* do something */

end try

begin catch

if @@TRANCOUNT > 0
rollback

 DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int                              
  SELECT @ErrMsg = ERROR_MESSAGE(),                              
         @ErrSeverity = ERROR_SEVERITY()                              

  RAISERROR(@ErrMsg, @ErrSeverity,1)     

end catch

if @@TRANCOUNT > 0
commit transaction

当我点击 Sql Server Management Studio 上的取消执行查询"按钮时，它会取消查询并使事务保持打开状态.

when I hit "Cancel Executing Query" button on Sql Server Management Studio it cancels the query and leaves the transaction open.

这是预期的行为吗?还是我的模式有错误.不应该回滚事务吗?

Is this the intended behavior? Or is there a mistake in my pattern. Shouldn't it rollback the transaction?

推荐答案

恕我直言，这是一种预期行为.当您取消查询运行时，如果有打开的事务 - 它保持打开状态，直到您明确提交或回滚它或直到连接未关闭

IMHO, it is an intended behavior. When you cancels query running if there was open transaction - it remains open until you explicitly commit or roll it back OR until connection is not closed

您的模式中没有任何有价值的错误.如果您手动控制执行流程(取消执行查询)，那么您应该以相同的方式处理打开的事务 - 手动.

There is no any valuable mistakes in your pattern. If you control execution flow manually (Cancel Executing Query), then you should care of opened transactions in the same manner - manually.

更新:

该行为由 SSMS 选项控制查询执行后断开连接 - 这意味着查询在执行或取消后断开连接并回滚打开的事务:

The behavior is controlled by SSMS option Disconnect after the query executes - which means that the query disconnects after execution or cancel and rolls back opened transactions:

这篇关于取消查询后交易仍处于打开状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！