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
Best Answer
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.