none
MSDTC RRS feed

  • Question

  • I'm using a TransactionScope block, and within that I'm calling the same business object twice to create two records.  The business object instantiates its own Data Context inside of a "using" block and therefore disposes of it when it's done.  Everything works fine when I have MSDTC running, but when it's not I get an error.

    It looks like the transaction is getting promoted to a distributed transaction because of the two data contexts (and therefore two connections), within the transaction scope.

    Is there a way around this, or do we have to use MSDTC in this scenario?

    Thanks for any help.

    -Larry

    Thursday, December 18, 2008 8:15 PM

Answers

  • You should be able to avoid XA/DTC escalation by providing a connection to the datacontext instead of letting the datacontext handle connections.

    E.g.:

    using (TransactionScope ts = new TransactionScope(...))  
    {  
        using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection("Data Source=SomeServer;Initial Catalog=SomeDB;Integrated Security=True"))  
        {  
            connection.Open();  
            using (SomeDataContext dc = new SomeDataContext(connection))  
            {
               //do something
            }  
     
            using (SomeDataContext dc = new SomeDataContext(connection))  
            {
               //do something else
            }  
            connection.Close();  
        }          
        ts.Complete();  

    http://www.huagati.com/dbmltools/ - add new features to the Linq-to-SQL and Entity Framework designers
    Friday, December 19, 2008 2:04 AM
    Answerer
  • It is a shame the TransactionScope doesn't base its transaction promotion logic on connection string equality (rather than connection instance). I've never been able to find a good tranactional approach that avoids MSDTC when working with ASP.NET Membership Provider and LINQ to SQL.
    Friday, December 19, 2008 1:52 PM
    Answerer

All replies

  • You should be able to avoid XA/DTC escalation by providing a connection to the datacontext instead of letting the datacontext handle connections.

    E.g.:

    using (TransactionScope ts = new TransactionScope(...))  
    {  
        using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection("Data Source=SomeServer;Initial Catalog=SomeDB;Integrated Security=True"))  
        {  
            connection.Open();  
            using (SomeDataContext dc = new SomeDataContext(connection))  
            {
               //do something
            }  
     
            using (SomeDataContext dc = new SomeDataContext(connection))  
            {
               //do something else
            }  
            connection.Close();  
        }          
        ts.Complete();  

    http://www.huagati.com/dbmltools/ - add new features to the Linq-to-SQL and Entity Framework designers
    Friday, December 19, 2008 2:04 AM
    Answerer
  • It is a shame the TransactionScope doesn't base its transaction promotion logic on connection string equality (rather than connection instance). I've never been able to find a good tranactional approach that avoids MSDTC when working with ASP.NET Membership Provider and LINQ to SQL.
    Friday, December 19, 2008 1:52 PM
    Answerer
  • MattBrooks said:

    It is a shame the TransactionScope doesn't base its transaction promotion logic on connection string equality (rather than connection instance). I've never been able to find a good tranactional approach that avoids MSDTC when working with ASP.NET Membership Provider and LINQ to SQL.


    That would have some other negative side-effects, such as tying connections to transactions a'la the way it was done in good old MTS... I prefer to have to do that (tie connections to transactions, that is) explicitly instead of implicitly...


    http://www.huagati.com/dbmltools/ - new productivity boosting features for the Linq-to-SQL and Entity Framework designers
    Friday, December 19, 2008 3:40 PM
    Answerer
  • Thanks for the clarification guys.  I was curious about why the tran got promoted with two connections for the exact same connection string, but I guess that's how it works.

    I'll look into instantiating the data contexts with a connection.

    -Larry
    Monday, December 22, 2008 3:23 PM
  • Does DataContext not use ADO.Net Connection Pooling ???  

    It is ridiculous that using 2 DataContexts with the same ConnectionString, all within a TransactionScope, does not reuse the same Connection, as it should and as documented behavior in http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx.

    To have client code creating the Connection and passing it to the DAL is preposterous.  It breaks the interface - the client should not need to know anything about what the DAL is doing. 

    Consider this article: http://msdn.microsoft.com/en-us/library/bb386986.aspx
    It says "Because LINQ to SQL is a part of the ADO.NET family of technologies and is based on services provided by ADO.NET, you can reuse a connection between an ADO.NET command and a DataContext. "  So, if ADO.Net code and DataContext wind up sharing aS SQLConnection because their ConnectionString is the same, then why should not two DataContext objects share the same SQLCOnnection?  If Linq-to-SQL is part of ADO.Net family, why does it not adhere to Connection Pooling ???

    If you ask me, this is a mistake that Microsoft has made, and they are covering it up by saying it is supposed to be like this.  This issue will lead to inferior design of clients, and later when Microsoft corrects this (they will call it an evolution or something, and it will come in about 2 service packs after Framework 4, is my prediction) then there is going to be a lot of client code written as if it were 2-tier that needs to be rewritten.

    This is wrong.  Please Microsoft, provide a Constructor Option or Attribute or something to DataContext so it will share connections based on Transaction & ConnectionString as it is supposed to.
    Tuesday, December 15, 2009 1:13 AM