SqlConnection does not support parallel transactions

Hi

we have recently moved to VPS. On Server, we keep getting SqlConnection does not support parallel transactions error. and then Application shutdown and restart. This error comes every 1 or 2 hours time.    

We are using Nop 4.0 version and SQL server 2016 express with advanced services with service pack 2.

We are getting below error from a log file. Can anyone please help with this?

Unhandled Exception: System.Data.Entity.Core.EntityException: An error occurred while starting a transaction on the provider connection. See the inner exception for details. ---> System.InvalidOperationException: SqlConnection does not support parallel transactions.
   at System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName, Boolean shouldReconnect)
   at System.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso, String transactionName)
   at System.Data.SqlClient.SqlConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.<BeginTransaction>b__0(DbConnection t, BeginTransactionInterceptionContext c)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.BeginTransaction(DbConnection connection, BeginTransactionInterceptionContext interceptionContext)
   at System.Data.Entity.Core.EntityClient.EntityConnection.<>c__DisplayClassf.<BeginDbTransaction>b__d()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.EntityClient.EntityConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.EntityConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.Entity.Core.EntityClient.EntityConnection.BeginTransaction()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at Nop.Data.EfRepository`1.Insert(T entity)
   at Nop.Services.Logging.DefaultLogger.InsertLog(LogLevel logLevel, String shortMessage, String fullMessage, Customer customer)
   at Nop.Services.Logging.LoggingExtensions.FilteredLog(ILogger logger, LogLevel level, String message, Exception exception, Customer customer)
   at Nop.Services.Tasks.TaskThread.Run()
   at Nop.Services.Tasks.TaskThread.TimerHandler(Object state)
   at System.Threading.TimerQueueTimer.CallCallbackInContext(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.TimerQueueTimer.CallCallback()
   at System.Threading.TimerQueueTimer.Fire()
   at System.Threading.TimerQueue.FireQueuedTimerCompletion(Object state)
   at System.Threading.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem()
   at System.Threading.ThreadPoolWorkQueue.Dispatch()
   at System.Threading._ThreadPoolWaitCallback.PerformWaitCallback()

Someone reported the same issue here (happens when inserting a log message)
https://www.nopcommerce.com/boards/t/51564/nop-4-sqlconnection-does-not-support-parallel-transactions.aspx
but there was no follow-up.  So, I'll ask the same questions ;)

Was it working before and just started happening?
Have you customized the code?
Post your DataConnectionString from /App_Data/dataSetting.json file (remove servername/pwd )

Thank you for your reply

We haven't change any code, we just move from our old hosting provider to a new VPS provider.

Here is the dataSetting.json details.

{
  "DataProvider": "sqlserver",
  "DataConnectionString": "Data Source=<<our Database server>>;Initial Catalog=<<sql dbname>>;Integrated Security=False;Persist Security Info=False;User ID=<<username>>;Password=<<password>>",
  "RawDataSettings": {}
}

[email protected] wrote:


Most probably your previous sql transaction was not completed. Check sql server is running perfectly.

Also getting this error on the odd occasion (4.0). My log is slightly different though:

Unhandled Exception: System.Data.Entity.Core.EntityException: An error occurred while starting a transaction on the provider connection. See the inner exception for details. ---> System.InvalidOperationException: SqlConnection does not support parallel transactions.
   at System.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, String transactionName, Boolean shouldReconnect)
   at System.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso, String transactionName)
   at System.Data.SqlClient.SqlConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.BeginTransaction(DbConnection connection, BeginTransactionInterceptionContext interceptionContext)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.EntityClient.EntityConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   --- End of inner exception stack trace ---
   at System.Data.Entity.Core.EntityClient.EntityConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.Entity.Core.EntityClient.EntityConnection.BeginTransaction()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
   at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at Nop.Data.EfRepository`1.Insert(T entity)
   at Nop.Services.Logging.DefaultLogger.InsertLog(LogLevel logLevel, String shortMessage, String fullMessage, Customer customer)
   at Nop.Services.Logging.LoggingExtensions.FilteredLog(ILogger logger, LogLevel level, String message, Exception exception, Customer customer)
   at Nop.Services.Logging.LoggingExtensions.Error(ILogger logger, String message, Exception exception, Customer customer)
   at Nop.Services.Tasks.TaskThread.Run()
   at Nop.Services.Tasks.TaskThread.TimerHandler(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.TimerQueueTimer.CallCallback()
   at System.Threading.TimerQueueTimer.Fire()
   at System.Threading.TimerQueue.FireNextTimers()

I am going to add some error handling to InsertLog in the DefaultLogger service (I will revert back here if it seems to resolve the issue).

This error seems to also be followed by 3 or 4 of these, which may be related to the error above (based on the log files), but I cant determine the time they occur:

fail: Microsoft.AspNetCore.Server.Kestrel[13]
      Connection id "0HLNM3LD3MB8A", Request id "0HLNM3LD3MB8A:00000001": An unhandled exception was thrown by the application.
System.ArgumentNullException: Value cannot be null.
Parameter name: values
   at System.String.Join(String separator, IEnumerable`1 values)
   at Microsoft.Extensions.RazorViews.BaseView.EndWriteAttribute()
   at Microsoft.AspNetCore.Hosting.Views.ErrorPage.<ExecuteAsync>d__6.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.Extensions.RazorViews.BaseView.<ExecuteAsync>d__29.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.Frame`1.<ProcessRequestsAsync>d__2.MoveNext()