|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Avoiding escalation to MSDTC with multiple TableAdapter insertsto 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 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 ------------------------------------------------------------------------------------------- Show quote "Niall" <a***@me.com> wrote in message news:%234z9Ber7FHA.3388@TK2MSFTNGP11.phx.gbl... > 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 > 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 Show quote "Sahil Malik [MVP]" <contactmethrumyblog@nospam.com> wrote in message news:uiecmQs7FHA.1484@tk2msftngp13.phx.gbl... > 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" <a***@me.com> wrote in message > news:%234z9Ber7FHA.3388@TK2MSFTNGP11.phx.gbl... >> 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 >> > > 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 ------------------------------------------------------------------------------------------- Show quote "Niall" <a***@me.com> wrote in message news:uQc41d27FHA.1000@tk2msftngp13.phx.gbl... > 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]" <contactmethrumyblog@nospam.com> wrote in message > news:uiecmQs7FHA.1484@tk2msftngp13.phx.gbl... >> 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" <a***@me.com> wrote in message >> news:%234z9Ber7FHA.3388@TK2MSFTNGP11.phx.gbl... >>> 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 >>> >> >> > > 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 Show quote "Sahil Malik [MVP]" <contactmethrumyblog@nospam.com> wrote in message news:%23qEooa57FHA.2036@TK2MSFTNGP14.phx.gbl... > 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" <a***@me.com> wrote in message > news:uQc41d27FHA.1000@tk2msftngp13.phx.gbl... >> 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]" <contactmethrumyblog@nospam.com> wrote in message >> news:uiecmQs7FHA.1484@tk2msftngp13.phx.gbl... >>> 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" <a***@me.com> wrote in message >>> news:%234z9Ber7FHA.3388@TK2MSFTNGP11.phx.gbl... >>>> 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 >>>> >>> >>> >> >> > > 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 ---------------------------------------------------------------------------- Show quote "Niall" <a***@me.com> wrote in message news:%23kcujb77FHA.2036@TK2MSFTNGP14.phx.gbl... > 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]" <contactmethrumyblog@nospam.com> wrote in message > news:%23qEooa57FHA.2036@TK2MSFTNGP14.phx.gbl... >> 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" <a***@me.com> wrote in message >> news:uQc41d27FHA.1000@tk2msftngp13.phx.gbl... >>> 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]" <contactmethrumyblog@nospam.com> wrote in message >>> news:uiecmQs7FHA.1484@tk2msftngp13.phx.gbl... >>>> 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" <a***@me.com> wrote in message >>>> news:%234z9Ber7FHA.3388@TK2MSFTNGP11.phx.gbl... >>>>> 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 >>>>> >>>> >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics