Sql – Concurrent access to a database with Entity Framework == EntityException

concurrencyentity-frameworkmultithreadingsqlwcf

I've a MS SQL 2008 database which is accessed through LINQ for data update/retrival.

My linq is accessed by WCF services in a PerCall instantiation mode for an heavy application. This application has several thread which makes calls to the service, and several application are running in the sametime.

I've often some EntityException happening:

System.Data.EntityException was caught
Message=An error occurred while starting a transaction on the provider connection. See the inner exception for details.
Source=System.Data.Entity
StackTrace:
at System.Data.EntityClient.EntityConnection.BeginDbTransaction(IsolationLevel isolationLevel)
at System.Data.EntityClient.EntityConnection.BeginTransaction()
at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
at Infoteam.GfK.TOMServer.DataServer.DataServer.SaveChanges() in D:\Workspace\XYZWASDF\DataServer\DataServer.cs:line 123
InnerException: System.Data.SqlClient.SqlException
Message=Une nouvelle transaction n'est pas autorisée parce que d'autres threads sont en cours d'exécution dans la session.
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=16
LineNumber=1
Number=3988
Procedure=""
Server=ift-srv114
State=1
StackTrace:
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.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName)
at System.Data.SqlClient.SqlInternalConnection.BeginTransaction(IsolationLevel iso)
at System.Data.SqlClient.SqlConnection.BeginDbTransaction(IsolationLevel isolationLevel)
at System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
at System.Data.EntityClient.EntityConnection.BeginDbTransaction(IsolationLevel isolationLevel)
InnerException:

(Sorry it's not very readable). (The message of the internal exception mean "A new transaction is not allowed because there are other threads running in the session."

I've check, I'm not in a loop, it's purely random when it makes this exception, and I've no idea about how to avoid this.

any help will be really appreciated 🙂

Thank you!

EDIT: Here is an example of where I got this exception SOMETIMES

    //My DataServer method, which is a singleton

    [MethodImpl(MethodImplOptions.Synchronized)]
            public void SaveChanges()
            {
                lock (_lockObject)
                {
                    try
                    {
                        _context.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
                        _changeListener.ManageIDAfterInsert();                       
                    }
                    catch (Exception ex)
                    {
                        Logger.Instance.Error("[DataServer:SaveChanges] Got an error when trying to save an object", ex);
                        //HERE I've this error
                    }
                }
            }

//One example where I can have exception sometimes, this is called through a WCF service, so I have a method which attach the object and then save it
private OrderStatus AddOrderStatus(OrderStatus orderStatus)
        {
            DataServer.DataServer.Instance.InsertOrUpdateDetachedObject(orderStatus);

            return orderStatus;
        }

Best Solution

Without seeing your code, the short answer is that EntityFramework is not thread-safe. You're getting this error in a seemingly random pattern when two+ threads overlap when trying to try access your ObjectContext. I assume you've stuffed your context into a static variable. Either make the context a local variable or write locking around access to the ObjectContext.

If you want a more specific answer posting your code will help.

Edit

Your issue is that two threads are trying to use the context at the same time, or 1 thread is leaving a transaction open then and a second thread comes through trying to use your singleton context. Your code snippet raises more questions for me than I had before.

  • In your code example is _lockObject a static variable?
  • Why do you show the lock in SaveChanges but then explain an error is being thrown from InsertOrUpdateDetachedObject? Can we see the code for InsertOrUpdateDetachedObject?
  • Does SaveChanges use the same _lockObject as all other methods that access the context directly?
  • Is your call to _context.SaveChanges the only way you save to the DB or do you have other areas that open transaction contexts on their own?
  • Your using a singleton so your context is shared across multiple calls. It might be preferable to instantiate a new new context for every WFC call.