Transaction is still open after cancelling query

Question

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

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?

Answer 1

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.

Update:

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: