Does the .net TransactionScope handle very complicated database things?

Question

I am tasked with proving or disproving the transaction support in .net. We all know that the TransactionScope can handle committing/rolling back simple things like inserting 3 records in 3 different tables and then deleting 5 records in 5 other tables.

My team is not so sure that TransactionScope can handle the transaction properly if these inserts/deletes have triggers. Or if we execute a SP that has it's own nested transactions.

Does the .net transaction support handle these more complicated situations? Do you have to specify any not-so-obvious options to make it all work?

I've heard that some of our SPs could cause us problems, since a few commit sub transactions on their own. Does anyone know if this particular scenario is handle by TransactionScope?

Answer 1

We are using TransactionScope for some very complex and lengthy database operations involving thousands of rows across a dozen or more tables. It handles it just fine. However, IMO, you DO NOT want to be starting and committing transactions in stored procedures and/or triggers.You should let your calling (C#) code handle that. You lose a great deal of flexibility by handling transactions at the lower stored proc level.

Also, be careful, when using TransactionScope, to limit yourself to a single connection object. If you don't, the transaction will escalate to a MTC transaction, which requires the MTC service to be running on the client and server.