Avoiding escalation to MSDTC with multiple TableAdapter inserts

[Niall]

I'm trying out the new TableAdapters in ADO.Net 2.0. I was a bit surprised
to see control of transactions being somewhat taken away from me and placed
into the System.Transactions namespace. I had a read and saw all the stuff
about distributed transactions, which seems quite cool for managing access
to multiple resources of different types. However, for plain old SQL
Transactions, it seems like if you're not careful, you might end up paying a
performance hit.

So I wrote a small app that inserts rows in a few tables in one function.
This all needs to be inside the one transaction. So I use the
TransactionScope, and everything seemed sweet while I was only posting the
first TableAdapter. Once I started posted the other two, I received
exceptions complaining that the MSDTC was not running. After reading the
generated code for the TableAdapters, it appears that what I have to do is
ensure that the first adapter's connection is open before it posts
(otherwise it will close it again), and that the other adapters all have
their connections assigned to be the first adapter's connection. This seems
to leave SQLServer 2005 happy enough to do it all on one transaction and not
involve the MSDTC.

Is this the "proper" way to get around your transaction getting promoted up
to the MSDTC? Also, can I/should I close the SqlConnection I manually opened
before/after completing the TransactionScope, or should I leave it?

Niall

 

[Sahil Malik [MVP]]

If you are working with a single database, open your single SqlConnection
and keep it open throughout the duration of the transaction, that will keep
the tranasction on LTM for SQL2k5.

The control hasn't been "taken away" from you - you can still do
SqlTransaction.

Also, please read this
http://codebetter.com/blogs/sahil.malik/archive/2005/06/13/64533.aspx
and this -
http://codebetter.com/blogs/sahil.malik/archive/2005/06/11/64484.aspx

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Niall]

Hi Sahil,

I've read quite a few of your posts as I searched about TableAdapters and
Transactions.

Perhaps I wasn't quite clear enough with my original post. What I'm trying
to find out is: what is the way the ADO.Net team intended us to use multiple
TableAdapters with transactions? As the class generated is partial, and you
have access to the connection, I can see many different ways of altering the
code for the class or handing it a connection you control, etc, to take
implicit control over the implicit transaction handling - ie you're not
managing the transaction itself, you are moulding the tableadapters into the
right shape so that they don't do the wrong thing and cause the transaction
to get pushed up to the MSDTC. And, of course, you could always just use
data adapters or plain SqlCommands with the old SqlTransaction objects.

However, none of these really click with me. Handling the connection object
yourself so that all adapters posting in a transaction is easy enough when
you're doing all your posting in one method, but can cause a big change in
interface in an enterprise scale system to make sure all relevant code has
the right connection. Altering/adding code to the TableAdapter is even worse
from the perspective of an enterprise scale system.

If you've ever read the blog of people like Rico Mariani, he talks about the
"Pit of success", where using a tool is intuitive to the point that you fall
into using it the best way without realising it. I'm not getting the feeling
that I'm doing this when I use transactions with TableAdapters, so I'm
wondering what the "right" way is supposed to be. It's not that I can't do
it, it's that I haven't found a way to do it yet that doesn't leave a bad
taste in the back of my mouth.

Niall

 

[Sahil Malik [MVP]]

Niall,

Check this blogpost out -
http://codebetter.com/blogs/sahil.malik/archive/2005/10/19/133309.aspx

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
-------------------------------------------------------------------------------------------

 

[Niall]

Thanks Sahil, the contents of the post is what I referring to in my previous
post in terms of the techniques that I know can be used to achieve the
effect I want.

The point I am trying to make is that all of these techniques seem, in one
way or another, unideal. It seems to me that there should be a good way to
make TableAdapters work with transactions with a minimal investment in code,
as companies with hundreds of tables in their database will be deterred by
having to modify the code of every TableAdapter they use. It seems the
"best" way is to share the connection object, but even that entails a not
entirely simple or clean change to architecture design when you are talking
about several hundred tables.

It seems, at the end of the day, that TableAdapter is aimed at quite small
scale solutions. I would have thought they would have been designed with
larger scale use in mind as well, but I guess not.

Niall

 

[Sahil Malik [MVP]]

Niall,

I doubt a perfect OSFA (one size fits all) approach for transactions is even
possible. When you start talking about transactions with disconnected data,
the first thing that hits you is concurrency technique, which depends to a
great extent on your table structures.

Maybe that problem is even solv-able, but the next issue that hits you is
what do you lock and for how long?

Followed by, when you are updating disconnected data, and you have 3 changes
to make, do you update 1,2, and if the 3 errors out - do you leave 1 and 2
as is? Do you roll them back? Or do you keep moving to the 4th? And in that
scenario, how do you reconcile the changes done, back to the disconnected
cache, and finally back to the UI? This gets quite hairy.

I can't speak for microsoft, as I'm not a microsoft employee , but IMO
OSFA transactional support are quite big shoes to fill.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx