What is the point of "ROLLBACK TRANSACTION named_transaction"?

Question

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.

1. It only works when transactionname is the outermost transaction
2. ROLLBACK always rolls back the entire transaction "stack", except in the case of savepointname

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?

Answer 1

Save points are exactly as the name implies: 'save points' in the log sequence. The log sequence is always linear. If you rollback to a save point, you rollback everything your transaction did between your current log position and the save point. Consider your example:

LSN 1: BEGIN TRAN OUTERTRAN
LSN 2: INSERT INTO ...
LSN 3: BEGIN TRAN INNERTRAN
LSN 4: INSERT INTO ...
LSN 5: ROLLBACK TRAN OUTERTRAN

At Log Sequence Number (LSN) 1 the OUTERTRAN save point is created. The first INSERT creates LSN 2. Then the INNERTRAN creates a save point with LSN 3. Second INSERT creates a new LSN, 4. The ROLLBACK OUTERTRAN is equivalent to 'ROLLBACK log until the LSN 1'. You cannot 'skip' portions of the log, so you must rollback every operation in the log until LSN 1 (when the save point OUTERTRAN was created) is hit.

On the other hand if at the last operation you would issue ROLLBACK INNERTRAN the engine would roll back until the LSN 3 (where the 'INNERTRAN' save point was inserted in the log) thus preserving LSN 1 and LSN 2 (ie. the first INSERT).

For a practical example of save points see Exception handling and nested transactions.