L' Eomot Inversé (10/28/2013)
Has one small problem though: the code leaves a transaction open - nothing is actually committed.
It's clearly stated in BOL:-
Although each COMMIT TRANSACTION statement has a transaction_name parameter, there is no relationship between the COMMIT TRANSACTION and BEGIN TRANSACTION statements. The transaction_name parameters are simply readability aids to help the programmer ensure that the proper number of commits are coded to decrement @@TRANCOUNT to 0 and thereby commit the outer transaction.
Thus the COMMIT TRAN A stement simply decrements the trancount to close the innermost the innermost open transaction since there are still two transactions open when it is encountered: the transaction named A and the transaction named B, since ROLLBACK TRAN B rolled back to the savepoint labelled B, and that savepoint includes some work done in teh transaction named B. The transaction named A, which is the outermost transaction, remains uncommitted.
Eagle eye L'eomot.
I checked the transaction level one by one and here is what i found:
BEGIN TRAN A
INSERT INTO TranTable (col) Values ('abc')
select @@TRANCOUNT as 'trancount 1' -- 1
BEGIN TRAN B
INSERT INTO TranTable (col) Values ('def')
select @@TRANCOUNT as 'trancount 2' -- 2
SAVE TRAN B
select @@TRANCOUNT as 'trancount 3' -- 2
BEGIN TRAN C
INSERT INTO TranTable (col) Values ('ghi')
select @@TRANCOUNT as 'trancount 4' -- 3
COMMIT TRAN C
select @@TRANCOUNT as 'trancount 5' -- 2
ROLLBACK TRAN B
select @@TRANCOUNT as 'trancount 6' -- 2
INSERT INTO TranTable (col) Values ('xyz')
COMMIT TRAN A
select @@TRANCOUNT as 'trancount 7' -- 1
The following link states that "ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT."
Really important point L'eomot. Cheers 🙂