none
System.Transactions is NOT Providing Atomic Behavior RRS feed

  • Question

  • Since System.Transactions and TransactionScope became available I've been migrating our old COM+ components over to use the new transaction model.  Recently, however, I have found partially committed data that appears to be the result of non-atomic C# transactions.  This appears to be the fault of TransactionScope's.

    Here is a simple repro which I was able to create.  You will need to create the database table provided in the comments. Can someone please explain why this database transaction is NOT atomic?  This is very, very scary to me.   Any help would be very much appreciated.

    using System;
    
    using System.Data.SqlClient;
    
    using System.Transactions;
    
    using System.EnterpriseServices;
    
    using System.Collections.Generic;
    
    using System.Linq;
    
    using System.Text;
    
    
    
    //CREATE TABLE [dbo].[CFG_loggedentry](
    
    // [CFG_loggedentry] [int] IDENTITY(1,1) NOT NULL,
    
    // [description] [varchar](50) NOT NULL,
    
    // CONSTRAINT [PK_CFG_loggedentry] PRIMARY KEY NONCLUSTERED 
    
    // ([CFG_loggedentry] ASC) ON [PRIMARY]
    
    //) ON [PRIMARY]
    
    
    
    //GO
    
    
    
    
    
    namespace ConsoleApplication4
    
    {
    
     class Program
    
     {
    
      class A 
    
      {
    
       public void DoWorkA(string p_ConnectionString)
    
       {
    
        // Use a transaction
    
        using(TransactionScope TransScope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0,0,10)))
    
        {
    
         // Create connection and command
    
         SqlConnection Connection = new SqlConnection(p_ConnectionString);
    
         SqlCommand Command = new SqlCommand(
    
          "INSERT INTO [dbo].[CFG_loggedentry] (description) VALUES (@p_Description)", 
    
          Connection);
    
    
    
         // Add parameters
    
         SqlParameter Param = Command.Parameters.Add("@p_Description", System.Data.SqlDbType.VarChar);
    
    
    
         // Do it in SQL
    
         Connection.Open();
    
         for(int i=0; i<150; i++)
    
         {
    
          Param.Value = "Part of Work A at " + DateTime.Now.ToString("u");
    
          Command.ExecuteNonQuery(); 
    
          System.Threading.Thread.Sleep(100);
    
         }
    
         Connection.Close();
    
    
    
         // Complete
    
         TransScope.Complete();
    
        }
    
       }
    
      }
    
    
    
    
    
    
    
      static void Main(string[] args)
    
      {
    
       try
    
       {
    
        Program.A ObjA = new A();
    
        ObjA.DoWorkA("Integrated Security=SSPI;Data Source=GRSQLD02;Initial Catalog=UfpWarehouse");
    
        Console.WriteLine("Not supposed to get here");
    
       }
    
       catch(Exception Ex)
    
       {
    
        Console.WriteLine("Exception during work. Check [CFG_loggedentry] for partial commits. Exception is " + Ex.ToString());
    
       }
    
      }
    
     }
    
    }
    
    
    
    

     


    David Beavon
    Tuesday, March 8, 2011 5:38 PM

Answers

  • Hi David,

    The new version of .NET has fix the problem, if you want to use the prior versions, there is a way to handle this, here is my test code:

     class A
      {
        private Thread _threadMain;
        private const string _sTIMED_OUT = "Transaction timed out";
        private void TransactionCompleted(object sender, TransactionEventArgs e)
        {
          if (e.Transaction.TransactionInformation.Status == TransactionStatus.Aborted)
          {
            if (_threadMain != null)
            {
              // Blow the main thread up, but pass it a token so it can tell who caused the explosion
              _threadMain.Abort(_sTIMED_OUT);
            }
          }
        }
        public void DoWorkA(string p_ConnectionString)
        {
          // Use a transaction
          SqlConnection Connection = new SqlConnection(p_ConnectionString);
          _threadMain = Thread.CurrentThread;
          try
          {
            using (TransactionScope TransScope = new TransactionScope(TransactionScopeOption.RequiresNew, new TimeSpan(0, 0, 10)))
            {          // Create connection and command
              SqlCommand Command = new SqlCommand(
               "INSERT INTO [dbo].[CFG_loggedentry] (description) VALUES (@p_Description)",
               Connection);
              // Add parameters
              SqlParameter Param = Command.Parameters.Add("@p_Description", System.Data.SqlDbType.VarChar);
              Transaction.Current.TransactionCompleted += new TransactionCompletedEventHandler(TransactionCompleted);
              // Do it in SQL
              Connection.Open();
              for (int i = 0; i < 150; i++)
              {
                Param.Value = "Part of Work A at " + DateTime.Now.ToString("u") + i;
                Command.ExecuteNonQuery();
                System.Threading.Thread.Sleep(100);
                Console.WriteLine(i);
              }
              Connection.Close();
              // Complete
              TransScope.Complete();
            }
          }
          catch (ThreadAbortException ex)
          {
            Debug.WriteLine("abort() called:\n" + ex.ToString());
            if (ex.ExceptionState is string && ex.ExceptionState as string == _sTIMED_OUT)
            {
              Debug.WriteLine("Calling ResetAbort()");
              Thread.ResetAbort(); // essential, otherwise this re-throws and actually kills the thread
              // Do your timeout recovery stuff here
            }
          }
        }
    

    You can also refer this link: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/6afa7c42-8286-48e8-b3fd-be5de574b611/

    @alasdaircs's idea is good!

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, March 14, 2011 7:46 AM
    Moderator

All replies

  •  

    Hi David,

    Thanks for your post.

    According to your description, you mean you find these code can commit partially??

    I don’t think so! Let me share my steps:

    Change your code here:  Param.Value = "Part of Work A at " + DateTime.Now.ToString("u")+i;

    Open SQL Server Profiler, run your code, there is a exception throw about "partial commit".

    Firstly, I select * from [CFG_loggedentry] , and there is none any records.

    Let's see profiler, you will find the insert command with i value, In my computer i is about 85.

    I think it may relates your scope's time span, so I change your code here:

    using(TransactionScope TransScope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0,0,100)))

    I run the code, I find it works ok!

    You can refer the link here: http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

    Your problem is the TimeSpan after which the transaction scope times out and aborts the transaction.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 9, 2011 6:59 AM
    Moderator
  • I found the problem.  You need to run my example with the Microsoft Framework 3.5 or earlier if you want to see only part of the work being committed to the database.

     Apparently Microsoft has given us non-transactional TransactionScopes prior to version 4.0 of their framework.

    Here is some of the research:

     

    SqlConnectionStringBuilder.TransactionBinding

     

    http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.90).aspx

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.transactionbinding.aspx

    http://msdn.microsoft.com/en-us/library/ms172070.aspx

    http://blogs.msdn.com/b/florinlazar/archive/2008/05/05/8460156.aspx

    http://social.msdn.microsoft.com/Forums/en/adodotnetdataproviders/thread/78c6f17c-c612-4aa7-82c0-34a8ad69c07c

    http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/206e0b5d-4dcf-415a-83a8-04ccd90197c4/

     


    David Beavon
    Thursday, March 10, 2011 1:02 AM
  •  

    Hi David,

    Thanks for your feedback!

    You are right, I test your code in 3.5 Framework and repro your scenario, It is a known issue in the prior versions, So we suggest you update your Framework and use VS2010.

    Thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 10, 2011 1:29 AM
    Moderator
  • I really don't have any understanding of this at all.  It is very, very frustrating to discover the problem in framework itself, after looking under every other rock.

     

    The last thing a business application developer wants to hear is that none of his database transactions he has written are actually atomic.  If Microsoft can't deliver on database transaction technology, they shouldn't provide development tools that pretend to do so.

    In the very least we could have received some urgent communication about the issue when it was discovered.  I can't even find a KB article yet!  I suppose this new System.Transactions is not a namespace that companies use for mission-critical work.  It's disappointing to discover this after a long rewrite (we had been using System.EnterpriseServices in earlier versions of the framework).

     

     


    David Beavon
    Friday, March 11, 2011 11:02 AM
  • Hi David,

    The new version of .NET has fix the problem, if you want to use the prior versions, there is a way to handle this, here is my test code:

     class A
      {
        private Thread _threadMain;
        private const string _sTIMED_OUT = "Transaction timed out";
        private void TransactionCompleted(object sender, TransactionEventArgs e)
        {
          if (e.Transaction.TransactionInformation.Status == TransactionStatus.Aborted)
          {
            if (_threadMain != null)
            {
              // Blow the main thread up, but pass it a token so it can tell who caused the explosion
              _threadMain.Abort(_sTIMED_OUT);
            }
          }
        }
        public void DoWorkA(string p_ConnectionString)
        {
          // Use a transaction
          SqlConnection Connection = new SqlConnection(p_ConnectionString);
          _threadMain = Thread.CurrentThread;
          try
          {
            using (TransactionScope TransScope = new TransactionScope(TransactionScopeOption.RequiresNew, new TimeSpan(0, 0, 10)))
            {          // Create connection and command
              SqlCommand Command = new SqlCommand(
               "INSERT INTO [dbo].[CFG_loggedentry] (description) VALUES (@p_Description)",
               Connection);
              // Add parameters
              SqlParameter Param = Command.Parameters.Add("@p_Description", System.Data.SqlDbType.VarChar);
              Transaction.Current.TransactionCompleted += new TransactionCompletedEventHandler(TransactionCompleted);
              // Do it in SQL
              Connection.Open();
              for (int i = 0; i < 150; i++)
              {
                Param.Value = "Part of Work A at " + DateTime.Now.ToString("u") + i;
                Command.ExecuteNonQuery();
                System.Threading.Thread.Sleep(100);
                Console.WriteLine(i);
              }
              Connection.Close();
              // Complete
              TransScope.Complete();
            }
          }
          catch (ThreadAbortException ex)
          {
            Debug.WriteLine("abort() called:\n" + ex.ToString());
            if (ex.ExceptionState is string && ex.ExceptionState as string == _sTIMED_OUT)
            {
              Debug.WriteLine("Calling ResetAbort()");
              Thread.ResetAbort(); // essential, otherwise this re-throws and actually kills the thread
              // Do your timeout recovery stuff here
            }
          }
        }
    

    You can also refer this link: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/6afa7c42-8286-48e8-b3fd-be5de574b611/

    @alasdaircs's idea is good!

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, March 14, 2011 7:46 AM
    Moderator
  • Hi David,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
     
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Sunday, March 20, 2011 6:27 AM
    Moderator