CODEWITHSUNDEEP
.netdatabaset-sqlc#-4.0transactions
Sinaesthetic
Sinaesthetic

Reputation: 12192

Will a DB procedure in a transaction still be rolled back from inside an ADO.net transaction?

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?

Upvotes: 1

Views: 370

Answers (2)

cleek
cleek

Reputation: 874

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

Upvotes: 1

Josh E
Josh E

Reputation: 7432

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

Upvotes: 1

Related Questions