CODEWITHSUNDEEP
sql-servertransactionsrollback
Lajos Arpad
Lajos Arpad

Reputation: 76454

Why does SQL Server keep variable state even if transaction is rolled back?

I have written a script to do a little experiment:

use MyDatabase;
declare @variable int;
set @variable = 5;
begin tran test
    set @variable = 6;
rollback tran test;
select @variable;

As you can see, @variable is declared at the start of the script, initialized with 5 and then inside a transaction it is set to 6. After the transaction is rolled back, we display @variable, which still has a value of 6, despite the fact that its value change was inside a transaction which was rolled back since then. I would have expected the selection to yield a result of 5. What is the cause of this behavior?

Upvotes: 2

Views: 660

Answers (2)

Ben Thul
Ben Thul

Reputation: 32697

Consider what it would take for the variable to obey transactional semantics. You would need to write all changes to a transaction log (perhaps not the transaction log) where they could be rolled forward and back arbitrarily. And for what? Variables by their nature are ephemeral, so you wouldn't restore them in the case of a database or server crash. So, it's a high cost, low reward operation. Not the quadrant you want to be in.

Upvotes: 2

STW
STW

Reputation: 46366

Because transactions are used to maintain the real data, the data in tables (not variables, not table variables). That's about all there is to it.

This is useful for scenarios where you may want to rollback but return some individual value from within the transaction as an indication of why you rolled back (perhaps throwing or just retuning a variable status code).

Upvotes: 6

Related Questions