1

Sorry, that was a hard question to word:

Say I have a stored procedure that does some inserting. It is wrapped in a transaction and commits, provided every goes well inside of that transaction.

Now, I call that transaction from a .net function. In this function, I have to call a few other functions that ALSO call stored procedures that are built in a similar fashion. Example:

bool SaveTicket()
{
   using(MyTransaction)
   {
      try
      {
         SaveTicketInfo(); //calls sproc 1
         SaveComments(); //calls sproc 2
         SaveAttachments(); //calls sproc 3
      }
      catch(Exception)
      {
         MyTransactionRollback(); //i would normally wrap this in its own try/catch
      }
   }
}

Ok, so that's the skeleton of the process. What I actually do in my code is to check the return values of each of the three processes and if they return false, I throw an exception which triggers the rollback.

What I'm wondering, is if there are commits in those stored procedure, will my roll back in my .net function still work? Or is it completely useless?

Sinaesthetic
  • 11,426
  • 28
  • 107
  • 176
  • possible dupe of http://stackoverflow.com/questions/189534/net-transactionscope-class-and-t-sql-tran-commit-and-rollback?rq=1 – Josh E Jul 18 '12 at 17:08

2 Answers2

1

Yes, it will be rolled back.

Transactions in SQL Server can be nested. Inner transactions are essentially ignored by SQL Server, and the only way to close an inner transaction is to commit it (i.e. to commit an outer transaction, all inner transactions must be committed). Rollback statements apply only to the outermost transaction.

You can read more about it here: http://msdn.microsoft.com/en-us/library/ms189336(v=sql.105).aspx

cleek
  • 874
  • 8
  • 15
  • So in other words, no? The whole point is to be able to run several sprocs but roll them all back if something goes wrong before all of them have completed successfully. – Sinaesthetic Jul 18 '12 at 22:53
  • Sinaesthetic, I apologize for my obtuse original answer. I edited my answer for clarity. – cleek Jul 22 '12 at 07:06
1

I guess I would first ask whether you really need to have separate transactions within your sprocs in the first place - putting them there really makes it a lot harder to maintain your app code, and places application logic into your persistence store.

My gut feeling says that since the sprocs create and commit their own transactions, those will NOT be rolled back when MyTransaction is rolled back, but the real answer could depend on how your DTC is configured.

See this answer for more details

Community
  • 1
  • 1
Josh E
  • 7,390
  • 2
  • 32
  • 44
  • It's not a matter of needing them. It's a matter of they're already there and fat chance convincing my dba to let ME handle the transactions – Sinaesthetic Jul 18 '12 at 22:51
  • lol convincing a dba to yield control to a dev on something is a definite challenge! – Josh E Jul 19 '12 at 20:20