All geek questions in one place

The transaction remains open after canceling the request.

In SQL Server 2008, I use this pattern:

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

when I clicked the “Cancel query execution” button in Sql Server Management Studio, it cancels the query and leaves the transaction open.

Is this intended behavior? Or is there a mistake in my picture. Should I cancel a transaction?

+6
source share
1 answer

IMHO, this is the alleged behavior. When you cancel the request, if there was an open transaction, it remains open until you explicitly commit or return it OR until the connection is closed

There are no valuable errors in your template. If you control the manual execution thread (Cancel Executing Query), then you should take care of open transactions in the same way - manually.

Update:

The behavior is controlled by the SSMS parameter. Disconnect after execution of the request - this means that the request is disconnected after execution or cancels and rolls back open transactions: enter image description here

+9
source

Source: https://habr.com/ru/post/904964/

More articles:


All Articles