Hi,
Before we start: This is all taking place on a single database. And I really don't want the overhead of Distributed Transactions.
If I want to have lots of separate data access routines that do work on the database. Sometimes I want these routines to take part in a larger transaction. Sometimes they will just be running in their own transaction.
I looked at TransactionScope and I thought this would do the trick.
I now realise I would need to pass an open connection object in to each routine. TransactionScope takes care of sharing my transaction but it doesn't help me with sharing connections. Becuase... If I have 2 connections I get DTC wading in to "help". I now think I need to write lots of code in all my routines to check if the connection passed in is already open etc. And my code is going to be just as complex as without having transaction scope!
I am concluding that TransactionScope helps simplify your code in these situations:
1-Where you really do want DTC transactions accross multitple databases.
2-Where you run transactions in a single none reusable routine
And it doesn't help much when:
3-You want to join several reusable data access routines in a single transaction.
The problem is category 3 in my mind is what most people do all the time.
Is this behaviour changing (I've seen other posts saying it will in the future)
Is there anything I can do to change this behaviour (Is there an alternative transactionScope out there I can use)
Have I got this all wrong
Is there any guidance on how to use TransactionScope in situation 3 descibed above
Thanks,
MikeG
Using TransactionScope to share transactions between Methods
Saji
Hi,
Thanks for your post I'm currently just doing a proof of concept app...
Any chance of posting some more details, such as any gotchas you came accross and how you overcame them etc. (Or maybe even some code!)
Do you just count your transactions and commit when it goes back to 0
Why do you use Thread storage Is this really just for web apps
I have created my own version of TransactionScope. LocalTransactionScope that is used in just the same pattern with a using contruct and calling ".Complete" just b4 end of the Using block.
LocalTransactionScope that just pushes itself onto a Shared Stack<of LocalTransactionScope> in the consructor and Pops itself in the Dispose method. Not got it working yet but I'm just replacing my database object factory classes to reuse sqlclient connections and create sqlclient transaction objects for me etc.
The code I'm writing at the moment doesn't implement escalation into DTC transactions but I want to be in control of when that happens anyway so I could just wrap it all in a normal TransactionScope.
Currently if I get different connection strings specified or RequiresNew is used then I start a new connection and transaction etc.
wossname
Thanks for those links.. very interesting.
The documentation and a few articles I've seen seem to give the impression that TransactionScope should be used becuase it will automatically promote the transaction for you. This is all very temping and I'm sure lots of developers will have fallen for it. The problem is the docs fail to mention that they will nearly always be getting DTC wading in to help. (Especially with sql2k)
The examples in the documentation are very basic and don't help people realise the subtle trap they could fall into.
I think that due to this lack of clarity there will be a lot of people out there doing distributed transactions without realising it.
It's a real shame because the TransactionScope way of doing things would have made my code a lot cleaner.
About the SQL 2005 thing. In my situation we are using SQL 2000 and .NET 2.0 and will be for some time to come. We need to wait until we have tested all code on SQL 2005 and then we need to wait till all our customers have migrated their servers to SQL 2005 before we can start using SQL 2005 features.
I'm tempted to try looking at copying the way TransactionScope works (using thread storage for the ambient transaction etc.) but do it with the SqlClient connection/transaction objects. Has anybody else thought of this
David Mitchell
Essentially yes, you just count your transactions and commit when you hit 0. We use thread storage in our windows forms application because we are in a multi-threaded environment, where our application server might be servicing requests from multiple clients at once, each on a different thread. I can't think of any major snags we have run into; the only time we got in trouble is when someone decided that there were certain things that needed to be done outside of the scope of the current transaction, in a separate transaction. In that case we had to swap out the current transaction and store it in a local variable while the other one was running. A stack would have worked just as well, but we never had a need for more than two at a time on a thread, so we didnt go to the trouble of implementing the stack. Also, in general, good design should limit the necessity of opening two connections on the same thread.
If you do run into any further issues, feel free to post them, and i will be happy to let you know if anything from my experience can help.
ASP Developer
esandall
If you are using a single database, I personally believe you should still use IDbTransaction ( SqlTransaction ) and just pass in the transaction to your database access routines like usual. You have more control over the transaction and what participates in the transaction.
System.Transactions and TransactionScope are better for situations involving multiple connections / resources that support promotable transactions ( Sql Server 2000 does not ). I wrote a blog post on this with SQL Server 2000 in mind:
http://davidhayden.com/blog/dave/archive/2005/12/09/2615.aspx
However, Sahil has a good blog entry that talks about SqlTransactions vs. System.Transactions in more general terms:
http://codebetter.com/blogs/sahil.malik/archive/2005/06/11/64484.aspx
As I mentioned in my post above, if you are using SQL Server 2000, you will always get a distributed transaction using TransactionScope. If you really want to use TransactionScope and are using SQL Server 2005 and sharing a single connection between your data access routines, you should be fine. If you open up multiple connections in your data access routines and/or other resources enlist themselves into the transaction, the transaction may get promoted to a distributed transaction and you have no control.
TransactionScope really wasn't intended for your typical single database transaction needs in my opinion. Maybe someone on the ADO.NET Team at Microsoft will clarify more on this.
Regards,
Dave