Are you using System.Transaction and Does it Get promoted to MSDTC WHEN it shouldn’t???

Posted: March 16, 2006 in Uncategorized
Are you using the new Transactional feature available with VS2005 namely the System.transaction??Not you are not using ??However you might opt for it once you know the feature provided thru it ..
I had a simply write up sometimes back on the usage of system.transaction here
However that post was mere an introduction into system.transaction and its capabilities.In short what System.Transaction provides you is An LTM(LIghtWeight Transaction Manager) and it will be auto upgraded to a MSDTC upon getting more RMs(ResourceManager).Not only this with the new TransactionScope you can wrap up the transactionscope Nicely.
The biggest wonder with System.transaction in WindowsVista timeframe would be the TRansactionalFileSystem without you to ship ur own CRM .You can read the msdnmag issue on the same subject line.
The system.transaction also provides you a commitable transaction which can span multiple threads.I had posted the post by Jim JhonSon on the same few days back.If you are looking for a link on this check JIM’s Blog here
However not only this the system.transaction support for PSPE(promotablesinglephaseenlistment) with the LTM to MSDTC is probably the faster tracsaction.
Further in the same line you can see that it builts-in a view of providing the next generation of transaction thus you can perform the task creation of your own CRM or BYOT features in a non-legacy way(no com+ here).
For more on transaction you can check florin’s blog here:-
 
 
Howvere my today’s post is not related to the faetures associated with system.transaction but what i am going to discuss is if you are running a code usign system.transaction your obvious aim would be that to use the LTM based transaction feature so that you ll get promoted to the expensive MSDTC as less  as possible.
Often due to a simple mistake and/or limitation with current transaction support  while dealing with system.transaction you can ends up with MSDTC transaction:-
See the below code :-
if you are using something like the following or trying to reuse the connection you ll endsup with MSDTC:-
try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    using (SqlConnection con = new SqlConnection("Data Source=SHREEMAN;Initial Catalog=test;Integrated Security=True"))
                    {
                        con.Open();
                        using(SqlCommand cmd1=new SqlCommand("update dbo.employee set lastname=’Suyama5′ where employeeid=6",con))
                        {
                            cmd1.ExecuteNonQuery();
                        }
                    }
                    using(SqlConnection con1=new SqlConnection ("Data Source=SHREEMAN;Initial Catalog=test;Integrated Security=True"))
                    {
                        con1.Open();
                        using(SqlCommand cmd2=new SqlCommand("update dbo.employee set lastname=’Suyama’ where employeeid=6",con1))
                        {
                            cmd2.ExecuteNonQuery();
                        }
                    }
                    Thread.Sleep(5000);
                    // use to see the transaction in dtc console
                    scope.Complete();
                }
            }
            catch(TransactionAbortedException ex)
            {
                MessageBox.Show(ex.Message);
            }
 
Now the workaround :-
 
The workaround is if you have multiple connection with same connectionstring(diff connection in most of the scenarios(although not always) ll ends up with multiple RM thus promoted to DTC anyway) pulls the conn and pass the same from the same local transaction and use it .
See Alazel’s post on connectionscope on the same.
For a detail library also see John Doty’s TransactionAdapter
 
 
More on different types of transactions and usage in future post
 
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s