SQL Server 2005: Why Name Transactions?

Question

I've been sorting out the whole nested transaction thing in SQL server, and I've gleamed these nuggets of understanding of behavior of nested trans':

• When nesting transactions, only the outermost commit will actually commit.
• "Commit Trans txn_name", when nested , will always apply to the innermost transaction, even if txn_name refers to an outer transaction.
• "ROLLBACK TRAN" (no name) , even in an inner transaction, will rollback all transactions.
• "ROLLBACK TRAN txn_name" - txn_name must refer to the outermost txn name. If not, it will fail.

Given these , is there any benefit of naming transactions? You cannot use it to target a specific tranasction, either for commit or rollback. Is it only for code commenting purposes?

Thanks,

Yoni

Answer 1

The idea is to roll back part of your work, like a nested transaction. Does not always work as intended. Stored procedures using old-style error handling and savepoints may not work as intended when they are used together with TRY … CATCH blocks: Avoid mixing old and new styles of error handling.

Already discussed here @@ERROR and/or TRY - CATCH

Answer 2

You can have procedures rollback only their own work on error, allowing the caller to decide wether to abandon the entire transaction or recover and try an alternate path. See Exception handling and nested transactions for a procedure template that allows this atomic behavior.

Answer 3

Effectively it's just a programmers aide memoire. If you're dealing with a Tx that has a number of inner transactions, giving each meaningful names can help you make sure that the tranactions are appropriately nested and may catch logic errors.