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

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.

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.