2 Replies Latest reply: Oct 13, 2008 12:28 AM by 664782 RSS

    Data unavailable after distributed transaction commits

    603933
      Hello,

      We had been observing some strange behavior and have isolated the problem to data not being available in an Oracle database immediately after a distributed transaction commits. The problem is reproducible by the code below: It creates a table with a single column and tries to insert a row in this table and send an MSMQ message in a single transaction. Immediately after the transaction commits, the same row is retrieved. Sometimes this retrieval fails (select returns the empty set), where "sometimes" often seems about 50 out of 10,000 transactions.

      If a Thread.Sleep(20) is inserted prior to the retrieval, the retrieval always seems to succeed - so some asynchrony seems to be evident, although this is not documented anywhere and would indeed not be expected to be the default behavior when using the ambient transaction approach as shown below. We have not been able to find other reports about this issue, so either we are doing something wrong, despite the obvious simplicity of the example, or we have found some new issue. Any advise, hints and pointers are indeed appreciated! I'll try cross-posting in an MSDN forum.

      We have tried various variations with the same result; below the System.Data.OracleClient standard .NET driver is used, but the same behavior is seen with the Oracle.DataAccess odp.net driver (ODAC 10.2.0.2.21).

      The distributed transactions as such seem to work fine (except for the intermittently missing data) - that is, no data is committed or message added to the queue in MSMQ until the transaction commits.

      The system configurations tried look as follows:

      Configuration 1:

      Windows XP SP 2
      Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
      Microsoft .NET Framework 1.1, 1.1 Hotfix, 2.0 and 3.0 installed

      Configuration 2:

      Windows Server 2003 Standard Edition SP 1
      Oracle Database 10g 10.2.0.1.0
      Microsoft .NET Framework 2.0 and 3.0 installed

      The following interesting .NET components are referenced by the test application:

      System.Data
      System.Data.OracleClient
      System.Transactions
      System.Messaging
      System.Transactions

      All of them are tagged with "Version 2.0.0.0" and "Runtime v2.0.50727".

      Thanks in advance for any assistance!

      Ulrik

      ==============================================================
      using System;
      using System.Data.OracleClient;
      using System.Messaging;
      using System.Transactions;

      namespace XaTester
      {
          public class Program
          {
              private const string connectionString = "Data Source=localhost/xe;User Id=xa_test; Password=xa_test";
              private const string queueName = ".\\private$\\XA_TEST_QUEUE";

              static void Main()
              {
                  ExecSql("CREATE TABLE  \"XA_TEST_TABLE\" ( \"ID\" NUMBER(*,0) NOT NULL ENABLE, " +
                          "CONSTRAINT \"XA_TEST_TABLE_PK\" PRIMARY KEY (\"ID\") ENABLE )");
                  try
                  {
                      using (MessageQueue queue = GetMsmqConnection())
                      {
                          for (int i = 0; i < 10000; i++)
                          {
                              if (i % 100 == 0)
                              {
                                  Console.WriteLine("Test: " + i);
                              }
                              InsertAndSend(i, queue);
                          }
                      }
                  }
                  catch (Exception ex)
                  {
                      Console.WriteLine(ex);
                      Console.ReadKey();
                  }
              }

              private static MessageQueue GetMsmqConnection()
              {
                  if (!MessageQueue.Exists(queueName))
                  {
                      MessageQueue.Create(queueName, true);
                  }
                  MessageQueue queue = new MessageQueue(queueName);
                  queue.Purge();
                  return queue;
              }

              private static object ExecSql(string sqlStr)
              {
                  using (OracleConnection connection = new OracleConnection(connectionString))
                  {
                      connection.Open();
                      using (OracleCommand cmd = new OracleCommand(sqlStr, connection))
                      {
                          return cmd.ExecuteOracleScalar();
                      }
                  }
              }

              private static void InsertAndSend(int i, MessageQueue queue)
              {
                  using (TransactionScope ts = new TransactionScope())
                  {
                      ExecSql(string.Format(@"INSERT INTO XA_TEST_TABLE (ID) VALUES ({0})", i));
                      queue.Send("message", DateTime.Now.ToLongTimeString(),
                          MessageQueueTransactionType.Automatic);
                      ts.Complete();
                  }
                  //
                  // Transaction must have been committed at this point
                  //
                  if (ExecSql(string.Format("SELECT ID FROM XA_TEST_TABLE WHERE ID = {0}", i)) == null)
                  {
                      Console.WriteLine("ERROR: Row does not exist even though tx committed " +
                                        "successfully (id = {0})!", i);
                  }
              }
          }
      }
        • 1. Re: Data unavailable after distributed transaction commits
          603933
          Just FYI: I have had a reply in the MSDN forum: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2287433&SiteID=1. The asynchronous behavior seems to be per MS DTC design, they considered making it possible to synchronize on the phase two commit, but this idea was abandoned.

          Ulrik
          • 2. Re: Data unavailable after distributed transaction commits
            664782
            Is there anyway to check when the data is indeed committed to the db?

            It's not unusual to have programming logic like this:


            using(TransactionScope tx = ...)
            +{+
            +....+
            save entity
            tx.Complete();
            +}+

            load the saved data, to update the UI, open a new form etc.


            With the above mentioned behavior of MSDTC, the above logic will just break, since the load will work some times but not always. We could add a sleep statement, but now it's an issue as to how long to sleep.

            Any suggestion?