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

Cestode

One more reason, not worth an answer IMO, is here. – Stephi

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.

Pinero

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.

Natiha

Really? But the doc says that you can't roll back an inner nested transaction, you can only roll back the outermost transaction. I'm not disputing you because I haven't tried it, I'm just curious which is the way it actually works. – Airlia

You can rollback to a save point. I reckon that's not the same as a named transaction, but I think that is what you're really after. – Natiha

Remus - Good point about the save point. Otherwise , (If not using Save Points, which I currently am not) , no benefit? – Cestode

Naming transactions can catch application errors when the transaction nesting is not the one expected by the developers, because if a compoenent attmpts to roll back the wrong name, an error is generated. – Natiha

Ok, thanks, that makes sense. Nested transactions != savepoints. – Airlia

BTW, I read your article you linked in your response. It looks like a great pattern, and I am going to try it out going forward. Thanks. – Cestode

Answer 3

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

Ladanum

But it doesn't roll back part of your work. Nesting transactions merely increments @@TRANCOUNT. Rolling back a nested transaction rolls back the whole thing. Unless you're talking about save points. – Wetzell