Cancel query and transactions

Question

Cancel query and transactions

kuopaz

Default port

Points: 1449
More actions
August 18, 2022 at 5:38 pm
Go to Answer
#4078048

I open a new query window in SSMS and execute: EXEC stored procedure. The stored procedure contains BEGIN TRAN and COMMIT / ROLLBACK TRAN.
I hit the cancel button and get told it has been done, almost instantaneously. When I then close the query window, I get the message: "There are uncommitted transactions. Do you wish to commit these transactions before closing the window?"
I thought that cancelling the query automatically initiated a rollback? If so, why am I being asked to decided whether to commit or rollback the transaction?
ScottPletcher

SSC Guru

Points: 101095
More actions
August 18, 2022 at 7:08 pm
Answer
#4078088

No. For an explicit transaction, you need an explicit COMMIT or ROLLBACK.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Scott P. - Just so I get a better understanding, had the OP used a TRY/CATCH set of blocks, would the cancellation of the query in SSMS have triggered the CATCH block ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

ScottPletcher SSC Guru Points: 101095 More actions · Answer 2

It depends. Unfortunately SQL can only trap certain errors; for some errors, even a CATCH won't "catch" an error.

For example, if you use and invalid column name -- one that does not appear in the table -- SQL won't transfer control to the CATCH block but will abort instead.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

sgmunson SSC Guru Points: 110640 More actions · Answer 3

I guess the only way to find out is to test it, then...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

kuopaz wrote:

I open a new query window in SSMS and execute: EXEC stored procedure. The stored procedure contains BEGIN TRAN and COMMIT / ROLLBACK TRAN.
I hit the cancel button and get told it has been done, almost instantaneously. When I then close the query window, I get the message: "There are uncommitted transactions. Do you wish to commit these transactions before closing the window?"
I thought that cancelling the query automatically initiated a rollback? If so, why am I being asked to decided whether to commit or rollback the transaction?

Cancelling a query doesn't necessarily mean cancelling a transaction. With that being said, look into SET XACT_ABORT ON. It fixes a wealth of such issues.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kuopaz Default port Points: 1449 More actions · Answer 5

sgmunson wrote:

Scott P. - Just so I get a better understanding, had the OP used a TRY/CATCH set of blocks, would the cancellation of the query in SSMS have triggered the CATCH block ?

The stored procedure did have a TRY / CARCH block in it.

As SSMS knows an action has to be taken by the user - to commit or rollback - why not ask this on hitting the cancel button, rather than leaving things hanging.