System.Transactions.TransactionException

Question

I've got the following code :

[TestMethod]
        public void CalculateThatWeHaveAllRetrunFromProxyImmo()
        {
            var test = new BaseDataAccess();
            var sessionDateParam = new SqlParameter("@SessionDate", new DateTime(1900, 01, 31));
            var dateDebutParam = new SqlParameter("@DateDebutHistoricRendement", new DateTime(1999, 01, 31));
            var datefinParam = new SqlParameter("@DateFinHistoricRendement", new DateTime(2015, 01, 31));
            var idProxyParam = new SqlParameter("@IdRealEstateProxy", 15);
            var blocElementParam = new SqlParameter("@BlocNbrElement", 1501);

            for (int index = 0; index < 17; index++)
            {
                Debug.WriteLine("Passage : " + index);
                var ds = new DataSet();
                SqlConnection conn = null;
                using (conn = test.CreateAndOpenConnection())
                {
                    SqlCommand cmd = null;
                    using (cmd = new SqlCommand("[Core].[cp_CalculAllReturnProxyImmo]", conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandTimeout = 1000000;

                        cmd.Parameters.Add(sessionDateParam);
                        cmd.Parameters.Add(dateDebutParam);
                        cmd.Parameters.Add(datefinParam);
                        cmd.Parameters.Add(idProxyParam);
                        cmd.Parameters.Add(blocElementParam);

                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(ds);
                        }

                        cmd.Parameters.Clear();
                    }
                }
            }

And

/// <summary>
        ///     Creates our connection to the database.
        /// </summary>
        /// <returns>The SqlConnection object.</returns>
        public SqlConnection CreateAndOpenConnection()
        {
            SqlConnection connection = null;

            try
            {
                connection = new SqlConnection(Settings.Default.DbConnection);
                connection.Open(); // the error appears here after 14 iterations

            }
            catch (SqlException sqlException)
            {

            }
            catch (Exception exception)
            {

            }

            return connection;
        }

Here is the connection

connectionString="Data Source=xxx\yyy;Initial Catalog=BD;Integrated Security=True;Max Pool Size=5000"

And every time I arrive at the index 14 in my loop, I get a System.Transactions.TransactionException. But I don't have any transaction open in my code. I don't understand.

Here is the complete Stack :

à 

System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
   à System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
   à System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
   à System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
   à System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
   à System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
   à System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)
   à System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, ref DbConnectionInternal connection)
   à System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, ref DbConnectionInternal connection)
   à System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, ref DbConnectionInternal connection)
   à System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   à System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   à System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   à System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   à System.Data.SqlClient.SqlConnection.Open()
   à System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   à System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   à System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   à MarketRisk.dVar.IntegrationTests.Service.Managers.RealEstateProxyCreationManagerFixture.CalculateThatWeHaveAllRetrunFromProxyImmo() dans RealEstateProxyCreationManagerFixture.cs: line 67

The debug trace :

Debug Trace:

Passage : 0
Passage : 1
Passage : 2
Passage : 3
Passage : 4
Passage : 5
Passage : 6
Passage : 7
Passage : 8
Passage : 9
Passage : 10
Passage : 11
Passage : 12
Test method 
MarketRisk.dVar.IntegrationTests.Service.Managers.RealEstateProxyCreationManagerFixture.CalculateThatWeHaveAllRetrunFromProxyImmo Threw exception :
System.Transactions.TransactionException : The operation is not valid for the state of the transaction. ---> System.TimeoutException : Delay transaction time

Answer 1

As requested you added Assert.IsTrue(Transaction.Current == null); to the test. The assert fired. This proves that there is a System.Transactions.Transaction set that you did not know about. That explains the symptoms. You are inadvertently using distributed transactions (you better disable the MSDTC service if you don't use them so that they fail immediately instead of only sometimes!).

Impossible to tell where that transaction is instated. Grep the entire source code for "Transaction". Add more asserts.

Probably, a different test has set that transaction and leaked it. Cross-test interaction is nasty to debug. Narrow it down by gradually reducing the set of tests that you run until the problem disappears.

Should i kill that Transaction

That is inappropriate. find the root cause instead of treating the symptom. There should be no tran that you don't know about.