I've read through MSDN on ROLLBACK TRANSACTION and nesting transactions. While I see the point of ROLLBACK TRANSACTION savepointname
, I do not understand ROLLBACK TRANSACTION transactionname
.
- It only works when
transactionname
is the outermost transaction ROLLBACK
always rolls back the entire transaction "stack", except in the case ofsavepointname
Basically, as I read the documentation, except in the case of a save point, ROLLBACK
rolls back all transactions (to @@TRANCOUNT=0
). The only difference I can see is this snippet:
"If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction."
From the reading, this suggests to me that rolling back a named transaction (which must be the name of the outermost transaction), only the nested transactions will be rolled back. This would give some meaning to rolling back a named transaction. So I set up a test:
CREATE TABLE #TEMP (id varchar(50))
INSERT INTO #TEMP (id) VALUES ('NO')
SELECT id AS NOTRAN FROM #TEMP
SELECT @@TRANCOUNT AS NOTRAN_TRANCOUNT
BEGIN TRAN OUTERTRAN
INSERT INTO #TEMP (id) VALUES ('OUTER')
SELECT id AS OUTERTRAN FROM #TEMP
SELECT @@TRANCOUNT AS OUTERTRAN_TRANCOUNT
BEGIN TRAN INNERTRAN
INSERT INTO #TEMP (id) VALUES ('INNER')
SELECT id AS INNERTRAN FROM #TEMP
SELECT @@TRANCOUNT AS INNERTRAN_TRANCOUNT
ROLLBACK TRAN OUTERTRAN
IF @@TRANCOUNT > 0 ROLLBACK TRAN
SELECT id AS AFTERROLLBACK FROM #TEMP
SELECT @@TRANCOUNT AS AFTERROLLBACK_TRANCOUNT
DROP TABLE #TEMP
results in (all "X row(s) affected" stuff removed)
NOTRAN
--------------------------------------------------
NO
NOTRAN_TRANCOUNT
----------------
0
OUTERTRAN
--------------------------------------------------
NO
OUTER
OUTERTRAN_TRANCOUNT
-------------------
1
INNERTRAN
--------------------------------------------------
NO
OUTER
INNER
INNERTRAN_TRANCOUNT
-------------------
2
AFTERROLLBACK
--------------------------------------------------
NO
AFTERROLLBACK_TRANCOUNT
-----------------------
0
Note that there is no difference to the output when I change
ROLLBACK TRAN OUTERTRAN
to simply
ROLLBACK TRAN
So what is the point of ROLLBACK TRANSACTION named_transaction
?