locked
How to solve error: transaction operation cannot be performed because there are pending requests working on this transaction? RRS feed

  • Question

  • Hi

    I have a Windows Service which via System.Timers executes various methods periodically.
    The timers seems to execute on different threads.

    I am using EF for data access.

    When I run the Windows Service and it executes it's methods I get the error:
    System.Data.EntityException: The underlying provider failed on Commit. ---> System.Data.SqlClient.SqlException: The transaction operation cannot be performed because there are pending requests working on this transaction.
    (Full error message at the end of this post)

    On the ConceptualEntityModel I have set the Lazy Loading Enabled to False.

    The methods which the timers make execute are called in the class ServiceLogic.cs.
    In the constructor of ServiceLogic.cs
    I initialize an instance of my EF repository:
    repository = new EfAteaRepository();

    All my methods which access EF lives in the class EfAteaRepository.

    The method which causes the error lives in EfAteaRepository and look like this:

    public bool InsertRowTblIncomingOrders(TblIncomingOrdersDto dto)
    {
    using (entityFramework = new AteaEntities())
        {
            var incomingOrder = new DAL.tbl_IncomingOrders();
    incomingOrder.CustomerOrderNumber = dto.CustomerOrderNumber;
    entityFramework.tbl_IncomingOrders.AddObject(incomingOrder);
            entityFramework.SaveChanges();
    }
    }

    There are multiple similar other methods doing update, persistence, etc.

    Do you have a solution to my problem?

    Full error message:

    System.Data.EntityException: The underlying provider failed on Commit. ---> System.Data.SqlClient.SqlException: The 

    transaction operation cannot be performed because there are pending requests working on this transaction.
       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, 

    BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType 

    request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction 

    transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
       at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, 

    String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean 

    isDelegateControlRequest)
       at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String 

    name, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
       at System.Data.SqlClient.SqlInternalTransaction.Commit()
       at System.Data.SqlClient.SqlTransaction.Commit()
       at System.Data.EntityClient.EntityTransaction.Commit()
       --- End of inner exception stack trace ---
       at System.Data.EntityClient.EntityTransaction.Commit()
       at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
       at System.Data.Objects.ObjectContext.SaveChanges()
       at TelecareAteaService.DAL.EfAteaRepository.InsertRowTblIncomingOrders(TblIncomingOrdersDto dto) in C:\Projects

    \Atea\TelecareAteaService\TelecareAteaService\DAL\EfAteaRepository.cs:line 361
    C:\Projects\Atea\TelecareAteaService\TelecareAteaService\DAL\EfAteaRepository.cs
    Friday, July 5, 2013 2:04 PM

Answers

  • I refactored from using timers to make and endless loop which I started with a worker thread.
                   workerThread = new Thread(ExecuteServiceWorkerMethods);
                    workerThread.Name = "ServiceWorkerThread";
                    workerThread.IsBackground = false;
                    workerThread.Start();
    Thus the above error disappeared.
    • Marked as answer by rune0071 Sunday, July 7, 2013 11:39 AM
    Sunday, July 7, 2013 11:39 AM

All replies

  • I refactored from using timers to make and endless loop which I started with a worker thread.
                   workerThread = new Thread(ExecuteServiceWorkerMethods);
                    workerThread.Name = "ServiceWorkerThread";
                    workerThread.IsBackground = false;
                    workerThread.Start();
    Thus the above error disappeared.
    • Marked as answer by rune0071 Sunday, July 7, 2013 11:39 AM
    Sunday, July 7, 2013 11:39 AM
  • Hi rune,

    Thanks for sharing your solution. It will help other community members who have similar problems.

    Have a nice day!

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Tuesday, July 9, 2013 6:31 AM
  • Hi,

    May be i am wrong there but how this solution will work for SaveChanges() method of entity framework. May be using this method we will introduced another concurrency issue in system because each update is executing in separate thread. 

    I will be great if you can tell how the locking will work in that case.

    Thanks in advance..

     

    Friday, July 24, 2015 2:48 PM