none
SQL Server connection fails to open "The operation is not valid for the state of the transaction." RRS feed

  • Question

  • Hello, Wasnt really sure which exact forum to post to but since it relates to the SqlConnection class library to posting it here.

    We have a code block like: 

    SqlTransaction transaction = null;
    SqlDataReader reader = null;
    
    using (SqlConnection sqlConnection = new SqlConnection(_sqlEndpoint.ConnectionString))
    {
         sqlConnection.Open(); **// Randomly fails here**
    
        try
        {
            switch (_sqlEndpoint.ResultType)
            {
            case ResultSetType.None:
                transaction = sqlConnection.BeginTransaction();
                using (SqlCommand command = new SqlCommand(_sqlEndpoint.Query, sqlConnection, transaction))
                {
                    command.CommandTimeout = _sqlEndpoint.CommandTimeout;
                    int rowsAffected = command.ExecuteNonQuery();
                    exchange.Message.SetHeader("RowsAffected", rowsAffected);
                }
                break;
    
            case ResultSetType.Single:
                using (SqlCommand command = new SqlCommand(_sqlEndpoint.Query, sqlConnection))
                {
                    command.CommandTimeout = _sqlEndpoint.CommandTimeout;
                    exchange.Message.Body = command.ExecuteScalar();
                }
                break;
    
            default:
                using (SqlCommand command = new SqlCommand(_sqlEndpoint.Query, sqlConnection))
                {
                    command.CommandTimeout = _sqlEndpoint.CommandTimeout;
                    reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                    exchange.Message.Body = reader;
                }
                break;
            }
        }
        catch (SqlException ex)
        {
             Log.ErrorFormat("[{0}] Error occured while fetching data from sql server: {1}", _sqlEndpoint.RouteName, ex.Message);
        }                
    }

    Opening a connection to the SQL server works 7-10 time, but randomly fail the other times. The issue seems to be random, and may occur at any attempt. The stack trace we get is:

    The operation is not valid for the state of the transaction.
       at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx, IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction, Guid promoterType)
       at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Guid promoterType)
       at System.Transactions.Transaction.EnlistPromotableSinglePhase(IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
       at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
       at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()

    I have tried the following steps:

    • Changing the connection string to use UserId and Password creds instead of SSPI to eliminate any AD related issues
    • Tried Clearing the specific connection from the pool by calling SqlConnection.ClearPool(conn) so that a new connection is made rather than getting from the pool
    • Checked that MSDTC service is running on the production server as well as on the SQL server

    Running out of ideas on this one, any help or pointers are appreciated.

    Thanks,

    J


    • Edited by Jinish Tuesday, December 27, 2016 10:13 AM Typo
    Tuesday, December 27, 2016 10:12 AM

All replies

  • According to documentation, the transaction should be explicitly finished using Commit (or Rollback). It is not clear if such functions are executed by your program.

    Also note that there is a series of retriable exceptions (“transient faults”), where you can simply repeat the failed operation several times.

    Tuesday, December 27, 2016 6:49 PM
  • Thanks for your reply. We have actually done both these things. As an answer the above functionality runs under a parent TransacationScope created from the parent class and on completion of the operation Complete is called on the scope.

    Although, the parent class initializes multiple instances of the above functionality (which is encapsulated inside a class) each of which runs under a new TransactionScope created by the parent.

    We have seen some cases when the operation performed under the transaction takes more time than the default time for the transaction and in which case as expected we get the "Transaction aborted" exception when trying to Dispose off the transaction. But the above behavior may happen even without the transaction timeout.

    We have also implemented a retry mechanism as below:

    public static class RetryManager
        {
            #region Methods
    
            /// <summary>
            /// Performs an action repeteadly based on the time interval and retry count provided
            /// </summary>
            /// <param name="action">Action to perform</param>
            /// <param name="retryInterval">Retry interval</param>
            /// <param name="retryCount">Number of times to retry. Default is 3</param>
            public static void Execute(Action action, TimeSpan retryInterval, int retryCount = 3)
            {
                Execute<object>(() =>
                {
                    action();
                    return null;
                }, retryInterval, retryCount);
            }
    
            /// <summary>
            /// Performs an action repeteadly based on the time interval and retry count provided
            /// </summary>
            /// <typeparam name="T">Return type of the action delegate</typeparam>
            /// <param name="action">Action to be performed</param>
            /// <param name="retryInterval">Retry interval</param>
            /// <param name="retryCount">Number of times to retry. Default is 3</param>
            /// <returns>Retrun value from the action delegate</returns>
            public static T Execute<T>(Func<T> action, TimeSpan retryInterval, int retryCount = 3)
            {
                var exceptions = new List<Exception>();
                for (int i = 0; i < retryCount; i++)
                {
                    try
                    {
                        Thread.Sleep(retryInterval);
                        if (i == 0) 
                        {
                            // The first time we execute the delegate regardless of any conditions
                            return action(); 
                        }
                        else
                        {
                            // We only want to retry if there has been an exception from the previous retry attempt
                            if (exceptions.Any()) return action();
                        }
                    }
                    catch (Exception ex)
                    {
                        exceptions.Add(ex);
                    }
                }
                // Aggregate the exceptions and  throw them back to the client
                throw new AggregateException(exceptions);
            }
    
            #endregion
        }

    which we then use as:

    RetryManager.Execute(() => sqlConnection.Open(), new TimeSpan(0, 0, 0), 3);

    The application is Multi threaded. I have been thinking about trying a few things (Do you think it makes sense?):

    1. Add a longer transaction timeout in the machine.config (for around an hour, since we have a query that does takes a long time over the weekend)
    2. Enable MSDTC trace logs and see if I can dig up anything there

    Any further ideas are most welcome and appreciated.

    Thanks,

    J


    • Edited by Jinish Wednesday, December 28, 2016 9:30 AM Typo in the code
    Wednesday, December 28, 2016 9:30 AM
  • Hi Jinish,

    Based on your description, I think that Multi threaded cause the issue. one thread want to operate the record, but it does not commit the transaction, but the other thread want to create a transaction and operation the same record, which could cause the issue.

    I would suggest that you could use c# lock to avoid it, like this:

    SqlTransaction transaction = null;
    SqlDataReader reader = null;
    
    private Object thisLock = new Object();  
    
    
    using (SqlConnection sqlConnection = new SqlConnection(_sqlEndpoint.ConnectionString))
    {
    
      lock (thisLock)  
      {
         sqlConnection.Open(); **// Randomly fails here**
    
        try
        {
            switch (_sqlEndpoint.ResultType)
            {
            case ResultSetType.None:
                transaction = sqlConnection.BeginTransaction();
                using (SqlCommand command = new SqlCommand(_sqlEndpoint.Query, sqlConnection, transaction))
                {
                    command.CommandTimeout = _sqlEndpoint.CommandTimeout;
                    int rowsAffected = command.ExecuteNonQuery();
                    exchange.Message.SetHeader("RowsAffected", rowsAffected);
                }
                break;
    
            case ResultSetType.Single:
                using (SqlCommand command = new SqlCommand(_sqlEndpoint.Query, sqlConnection))
                {
                    command.CommandTimeout = _sqlEndpoint.CommandTimeout;
                    exchange.Message.Body = command.ExecuteScalar();
                }
                break;
    
            default:
                using (SqlCommand command = new SqlCommand(_sqlEndpoint.Query, sqlConnection))
                {
                    command.CommandTimeout = _sqlEndpoint.CommandTimeout;
                    reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                    exchange.Message.Body = reader;
                }
                break;
            }
    
            transaction.Commit();
        }
        catch (SqlException ex)
        {
              transaction.Rollback();
    
             Log.ErrorFormat("[{0}] Error occured while fetching data from sql server: {1}", _sqlEndpoint.RouteName, ex.Message);
        }    
      }            
    }

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 30, 2016 8:48 AM
    Moderator