SQL Server 2005: Why Name Transactions?

sql-server transactions nested-transactions
user144133 picture user144133 · Aug 13, 2009 · Viewed 11.9k times · Source

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

Chris J picture Chris J · Aug 15, 2009

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.

Related questions

SAVE TRANSACTION vs BEGIN TRANSACTION (SQL Server) how to nest transactions nicely

I have a stored procedure that needs to set a save point so that it can, under certain circumstances, undo everything it did and return an error code to the caller, or accept/commit it and return success to the …

Read More
Correct use of transactions in SQL Server

I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly. What's the problem with the following script? BEGIN TRANSACTION […

Read More
SQL Server - transactions roll back on error?

We have client app that is running some SQL on a SQL Server 2005 such as the following: BEGIN TRAN; INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) VALUES (myValues ...); COMMIT TRAN; It …

Read More