none
Unwanted promotion using TransactionScope

    Question

  • Hi,

    I wanted to test System.Transaction and using TransactionScope. So I wrote a little factory class which has very basic CRUD functionality. One of it's methods is CreateName:



    public static Name CreateName(string nameValue)
    {
       using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.TestDB)) 
       {
          return CreateName(nameValue, connection);
       }
    }

    private
    static Name CreateName(string nameValue, SqlConnection connection)
    {
       
    Name name = new Name(nameValue);
       
    SqlCommand command = new SqlCommand();

       
    try
       
    {
          command.Connection = connection;
          command.CommandType =
    CommandType.StoredProcedure;
          command.CommandText = SP_INSERT_NAME;
          command.Parameters.Add(PAR_GUID,
    SqlDbType.UniqueIdentifier).Value = name.Guid;
          command.Parameters.Add(PAR_NAME,
    SqlDbType.VarChar, 50).Value = name.Value;

          connection.Open();
          command.ExecuteNonQuery();
       
          
    return name;
       }
       
    catch (SqlException exc)
       {   
          
    throw new MyException(Properties.Resources.CouldNotInsertNameInDB, exc);
       }
       
    finally
       
    {
          command.Dispose();
          connection.Close();
       }
    }


     


    The code runs fine when executed outside TransactionScopes. However, when I want to run this method twice within a single TransactionScope (see code below), the TransactionManager wants to promote the Transaction to a distributed transaction when the SqlConnection is opened for the second call. This requires network access for my MSDTC.


    using (TransactionScope ts = new TransactionScope())
    {
       
    NameFactory.CreateName(textBox1.Text);
       
    NameFactory.CreateName(textBox2.Text);
       ts.Complete();
    }


     


    My problem is that I don't want the transaction to be promoted to a distributed transaction since it shouldn't be one (correct me if I'm wrong...).

    I also tried a different version where my factory class had a static SqlConnection object so that the connection used by the transaction remains the same (but also has to be opened twice) and again, on the second Open(), the transaction gets promoted.

    How do you make sure that your transaction manager will not promote your transaction when only one remote resource is being accessed?

    Thanks for any help!

    Jochen
    Thursday, October 20, 2005 10:54 AM

Answers

  • Unfortunately there is no way to prevent the promotion at the second connection. This is a limitation right now. It is considered to be fixed at the future release.

    Thanks,
    Jian


     

    Monday, October 24, 2005 9:37 PM
  • Jochen,

    To answer the question at your original post about why the transaction is promoted when it is unnecessary. This is the limitation of VS 2005. We are aware of the problem and will address it after VS 2005. And I am not aware of any workaround that you can avoid that unless you keep an opened connection and use it for all your need. Otherwise, the transaction always gets promoted after the second Open call on the connection.

    Thanks,
    Jian

    Thursday, November 03, 2005 10:49 PM

All replies

  • Unfortunately there is no way to prevent the promotion at the second connection. This is a limitation right now. It is considered to be fixed at the future release.

    Thanks,
    Jian


     

    Monday, October 24, 2005 9:37 PM
  • But it does not seem to be fixed in the release version of VS2005 and .NET 2.0...
    Wednesday, November 02, 2005 12:22 PM
  • It's not a problem, this behaviour is by design. Transaction auto promotion is really a great feature, I wish it were there in early versions.

    This unwanted promotion, unwanted because you are working on a single datasource, is caused by your "factory" design. Need to understand when and why a promotion from local transaction to distributed occurs. 

    .NET transaction management relies on "Transaction.Current" property. Within a transaction scope, each transaction object is tracked with this "Current" property, for this to work, it also requires all the code within the block runs in the same thread, no multithreads allowed here, the "Current" works using thread local storage. 

    Each local transaction object is associated with a connection object, your way will cause the transaction scope seeing more than one local transactions, because you created one SqlConnection object in each of the two:

    NameFactory.CreateName(textBox1.Text);
    NameFactory.CreateName(textBox2.Text);
          
    calls, as the System.Transaction sees, they are two different local transactions, the same as on two distinct data sources. Even the two SqlConnection objects are connected to the same server, it's not the job of System.Transaction to find that out. 

    If you make sure only one SqlConnection object to be used, define it outside your factory method, so only one SqlConnection object will be used within the entire scope, then there would be no promotion. 

    The design of System.Transaction is ingenious, it puts thread local storage and polymorphism in good use. But developers need to understand how it works.

    Another thing, if the transaction can be handled within SQL server, so one trip to server could get it done, by using SQL code, not .NET transaction, at least the performance would be better.  
    Wednesday, November 02, 2005 3:31 PM
  • I don't quite agree. I wrote the following in the first post:

    I also tried a different version where my factory class had a static SqlConnection object so that the connection used by the transaction remains the same (but also has to be opened twice) and again, on the second Open(), the transaction gets promoted.

    For clarity: the SqlConnection in the CreateName call was twice the same object then, since it was only opened and closed during a CreateName call, but not constructed nor disposed.

    You are ofcourse right that it would be better to use a SQL transaction, or as an intermediate solution, a "CreateNames" method in the factory. But, this was a test for TransactionScope...

    And I can't help being disappointed that the promotion mechanism does not go any further than checking object equality for the resource object to decide upon promotion.

    It seems to me that object equivalence would have been very reasonable (at least for DB connections...) Correct me if I'm wrong, but it since System.Transaction implements auto transaction promotion, I would also expect it to implement some logic to determine whether certain resource objects could be the same (it's easy to compare SqlConnections...).

    At least, that kind of logic wouldn't seem out of place to me... I wrote it before: don't bother to correct me if I'm wrong.
    Wednesday, November 02, 2005 3:50 PM
  • No code in that part of your statement, can't comment.

    But from the code you posted,

    public static Name CreateName(string nameValue)
    {
       using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.TestDB)) 
       {
          return CreateName(nameValue, connection);
       }
    }



    it's clear that each of the salls,

    NameFactory.CreateName(textBox1.Text);
    NameFactory.CreateName(textBox2.Text);


    creates a new SqlConnection object, each time you call that method, the "connection" is local inside the method.  
     

    Wednesday, November 02, 2005 4:02 PM
  • Here's the code (uses only 1 SqlConnection object, but wants to promote):


    private static SqlConnection connection = new SqlConnection(Properties.Settings.Default.TestDB);

    public
    static Name CreateName(string nameValue)
    {
       Name name = new Name(nameValue);
       SqlCommand command = new SqlCommand();

       try
       {
          command.Connection = connection;
          command.CommandType =
    CommandType.StoredProcedure;
          command.CommandText = SP_INSERT_NAME;
          command.Parameters.Add(PAR_GUID,
    SqlDbType.UniqueIdentifier).Value = name.Guid;
          command.Parameters.Add(PAR_NAME,
    SqlDbType.VarChar, 50).Value = name.Value;
          
          connection.Open();
          command.ExecuteNonQuery();

          return
    name;
       }
       catch (SqlException exc)
       {
          throw new MyException(Properties.Resources.CouldNotInsertNameInDB, exc);
       }
       finally
       {
          command.Dispose();
          connection.Close();
       }
    }

     


    This means that not even object equality is used to decide upon Transaction promotion...
    Wednesday, November 02, 2005 4:13 PM
  • You should get code in this execution order:

    // static or not doesn't matter
    private static SqlConnection connection = new SqlConnection(Properties.Settings.Default.TestDB);


    using (TransactionScope ts = new TransactionScope())
    {
       connection.Opne();

       // keep connection.Opne() untill all the commands completed
      
       // Execute Cmd 1

       // Execute Cmd 2

       ts.Complete();
    }


    connection.Close() basically kill the cnnection object, except keeping its connecting string, not much else left for that object.  

    Wednesday, November 02, 2005 4:55 PM
  • Ofcourse it works correct in that way. There never was any doubt about that. The post just wanted to ask:

    Hey, if I use two connection object pointing at the same DB, the transaction gets promoted. This shouldn't be necessary. And that also happens if the same (unchanged!) connection object is opened and closed twice.

    Or: why doesn't the auto promotion mechanism take a look a the connection object at all? It would be able to make much better judgement upon whether to promote or not.

    Is it meant to be the way it is now? It doesn't seem to be the case if I read Jian Zang's reply...

    Apart from that, auto promotion is ofcourse a very nice mechanism, allowing easy transaction management across different resources. But for now, I think it's a better choice to continue to use DbTransactions for DB only transactions...
    Thursday, November 03, 2005 8:19 AM
  • I have not seen the code obviously, just from the class document looking in, here's my guess about what's happening, of cause it could be a totally different way used by MS:

    The SqlConnection.Open() method will check, in addition to many other things to do needed to open a connection, to see if it is in a transaction scope, here "Transaction.Current" property is the key. If so, this Connection object sets up a local transaction for itself, and adds this transaction to an internal list (a Hashtable, ArrayList, better be typed, using enhanced collection with generics.) in the transaction scope. SqlConnection.Close() basically kills itself, free almost all the references it holds, including the reference to its local transaction, but the Connection object, and its transaction, are still alive, because of the list kept by the scope still has references to them. Now the second time, the Connection object knows only the connectionstring, not much else, because Close() called earlier, then the call on Open(), repeates the check stated above, another transaction is added to the list, so you end up with two distinct transactions. By the time calling commit or complete, it will check the transaction list, if more than one in it, it will promotion, DTC kicks in; if just one, it will call the local tran's commit method. This method call selection can be done implicitly using ploymouph behaviour.

    This would roughly be the way if I had to implement this. I did create a SQL Server database provider, for remote access over HTTP, so users will not have to deal with web services or Remoting. It is secure, effcient, feels like the native SQL Data Provider. I call it WebSql Data Provider, for .NET 1.x. It can be used with .NET v2, just no support for the new features in v2, will be in v2 specific version. While working on this kind of work, I have to think about lot of techniques that would not be required for application development. Creating framework stuff is at another level of thinking, compared with using framework to build applications.         

    Thursday, November 03, 2005 2:42 PM
  • By the way, what's the reason of using a factory?
    Thursday, November 03, 2005 2:44 PM
  • I wanted to test TransactionScope behaviour for possible future use. Since that test could have gone quite deep, I started out by simulating the way we work in our company from the start. And for major business objects, we use factories to abstract our storage behaviour. 
    Thursday, November 03, 2005 2:51 PM
  • Sure, this probably is the way it is implemented in rough lines. At a certain stage the second connection is added to the subscription list of Transaction.Current.

    The thing that surprises me is that that fact is enough to decide to promote. There seems to be no check at all that compares the new subscriber to the previous ones. Whilst it seems easy to me to provide some checks which would result in: hey, we don't need to promote here.

    In case of two SqlConnections this could easily be done by comparing the connection strings.

    But ofcourse it would be better to write the code in such a way that the SqlConnection does not get closed in between the inserts in the first place.

    Yet, a better auto-promotion mechanism, which would not promote on sequential use of equivalent SqlConnections, would allow the writing of very flexible and transparent data access layers...
    Thursday, November 03, 2005 3:04 PM
  • If that is the design, it's the transaction object added to the list, at commit time, it does not trace the tranction back to its connectionstring. What if a user just want nested transaction in the same source, whether that makes sense or not?
    Thursday, November 03, 2005 4:44 PM
  • Jochen,

    To answer the question at your original post about why the transaction is promoted when it is unnecessary. This is the limitation of VS 2005. We are aware of the problem and will address it after VS 2005. And I am not aware of any workaround that you can avoid that unless you keep an opened connection and use it for all your need. Otherwise, the transaction always gets promoted after the second Open call on the connection.

    Thanks,
    Jian

    Thursday, November 03, 2005 10:49 PM
  • Thanks!

    Jochen

    Friday, November 04, 2005 8:15 AM
  • This is too bad, we had looked forwards to use TransactionScope in our project, instead of passing explicit transaction objects between our components and layers.

    We use TableAdapters that contains their own connections and that performs open/close when needed. All of these use the exact same connectstring and SQL Server 2005 database. Whenever we use two TableAdapters within a TransactionScope, it promotes to DTC, which is not allowed due to the customer's operational policies.

    We cannot implementation our components with a design that requires that e.g. the presentation layer has to open a connection and pass it to the biz layer, and that again passes it on to components in the data access layer; just to circumvent this limitation of the LTM.

    All we want is for the transaction to stay lightweight, afterall it is the same transaction context and the same connection and database all the time.
    Wednesday, November 16, 2005 12:23 PM
  • I am very disappointed.

    I was looking forward using the TransactionScope new paradigm in our new product architecture because it looked very promising. 

    The promise to simplify distributed transactions with automatic promotions and the fact that we no longer needed to propagate a transactions object across layers and methods WHILE keeping reasonable performance and lightweight objects for simple cases (single server/resource, multiple operations) made the System.Transactions looks like a very good news for developpers in our company.

    But after experimenting the feature, I came to the conclusion this feature was not fulfilling its promises and unfit for usage in a real application. 

    Is there hope for a working (performant) version of this feature in the future?

     

     

    Tuesday, February 14, 2006 8:47 PM
  •  BabelFish wrote:

    I am very disappointed.

    I was looking forward using the TransactionScope new paradigm in our new product architecture because it looked very promising. 

    The promise to simplify distributed transactions with automatic promotions and the fact that we no longer needed to propagate a transactions object across layers and methods WHILE keeping reasonable performance and lightweight objects for simple cases (single server/resource, multiple operations) made the System.Transactions looks like a very good news for developpers in our company.

    But after experimenting the feature, I came to the conclusion this feature was not fulfilling its promises and unfit for usage in a real application. 

    Is there hope for a working (performant) version of this feature in the future?

    At the moment i´m reading System.Transactions concepts, maybe for later use in some applications.´

    Can you be more specific about your conclusions ? What was your objective when using System.Transactions, and why do you became very disappointed with the results.

    Thanks in advance.

    Wednesday, March 08, 2006 11:58 AM
  • One of our team member implement a ConnectionScope class that might be useful for you. Here is the bolgs:

    http://blogs.msdn.com/dataaccess/archive/2006/02/14/532026.aspx

    Thanks,

    Jian

    Saturday, April 01, 2006 12:30 AM
  • Ah, I think this is the answer to my post too...

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=531830&SiteID=1

    Tuesday, July 25, 2006 3:39 PM