Session state DB throws "The current transaction attempted to update a record that has been updated since this transaction started."

[charanpasham]

We recently modified session db to use "In Memory" to improve the performance. Added the following config to the web.config file.

<sessionState cookieless="false" regenerateExpiredSessionId="true" mode="Custom" customProvider="SqlSessionStateProviderAsync">
      <providers>
        <add name="SqlSessionStateProviderAsync" 
             connectionStringName="SessionConnectionString" 
             UseInMemoryTable="true"
             MaxRetryNumber="10"
                         RetryInterval="1000"
             type="Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync, Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync, Version=1.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
      </providers>
    </sessionState>

I am using the retry sql script from Here.

It sporadically throw this error as below.

Here is the stack trace.

 "ClassName": "System.Data.SqlClient.SqlException",
            "Message": "The current transaction attempted to update a record that has been updated since this transaction started. The transaction was aborted.\r\nUncommittable transaction is detected at the end of the batch. The transaction is rolled back.\r\nThe statement has been terminated.",
            "StackTrace": [
                {
                    "LineNumber": 94,
                    "ClassName": "System.Data.SqlClient.SqlConnection",
                    "MethodName": "OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)"
                },
                {
                    "LineNumber": 380,
                    "ClassName": "System.Data.SqlClient.TdsParser",
                    "MethodName": "ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)"
                },
                {
                    "LineNumber": 0,
                    "ClassName": "System.Data.SqlClient.TdsParser",
                    "MethodName": "TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)"
                },
                {
                    "LineNumber": 64,
                    "ClassName": "System.Data.SqlClient.SqlDataReader",
                    "MethodName": "TryConsumeMetaData()"
                },
                {
                    "LineNumber": 60,
                    "ClassName": "System.Data.SqlClient.SqlDataReader",
                    "MethodName": "get_MetaData()"
                },
                {
                    "LineNumber": 243,
                    "ClassName": "System.Data.SqlClient.SqlCommand",
                    "MethodName": "FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)"
                },
                {
                    "LineNumber": 59,
                    "ClassName": "System.Data.SqlClient.SqlCommand",
                    "MethodName": "CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)"
                },
                {
                    "LineNumber": 79,
                    "ClassName": "System.Data.SqlClient.SqlCommand",
                    "MethodName": "InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal)"
                },
                {
                    "LineNumber": 87,
                    "ClassName": "System.Data.SqlClient.SqlCommand",
                    "MethodName": "EndExecuteReaderInternal(IAsyncResult asyncResult)"
                },
                {
                    "LineNumber": 77,
                    "ClassName": "System.Data.SqlClient.SqlCommand",
                    "MethodName": "EndExecuteReaderAsync(IAsyncResult asyncResult)"
                },
                {
                    "LineNumber": 15,
                    "ClassName": "System.Threading.Tasks.TaskFactory`1",
                    "MethodName": "FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)"
                },
                {
                    "LineNumber": 12,
                    "ClassName": "System.Runtime.ExceptionServices.ExceptionDispatchInfo",
                    "MethodName": "Throw()"
                },
                {
                    "LineNumber": 322,
                    "ClassName": "Microsoft.AspNet.SessionState.SqlSessionStateRepositoryUtil+<SqlExecuteReaderWithRetryAsync>d__12",
                    "MethodName": "MoveNext()"
                },
                {
                    "LineNumber": 12,
                    "ClassName": "System.Runtime.ExceptionServices.ExceptionDispatchInfo",
                    "MethodName": "Throw()"
                },
                {
                    "LineNumber": 46,
                    "ClassName": "System.Runtime.CompilerServices.TaskAwaiter",
                    "MethodName": "HandleNonSuccessAndDebuggerNotification(Task task)"
                },
                {
                    "LineNumber": 298,
                    "ClassName": "Microsoft.AspNet.SessionState.SqlInMemoryTableSessionStateRepository+<GetSessionStateItemAsync>d__19",
                    "MethodName": "MoveNext()"
                },
                {
                    "LineNumber": 12,
                    "ClassName": "System.Runtime.ExceptionServices.ExceptionDispatchInfo",
                    "MethodName": "Throw()"
                },
                {
                    "LineNumber": 46,
                    "ClassName": "System.Runtime.CompilerServices.TaskAwaiter",
                    "MethodName": "HandleNonSuccessAndDebuggerNotification(Task task)"
                },
                {
                    "LineNumber": 11,
                    "ClassName": "System.Runtime.CompilerServices.TaskAwaiter`1",
                    "MethodName": "GetResult()"
                },
                {
                    "LineNumber": 161,
                    "ClassName": "Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync+<DoGet>d__29",
                    "MethodName": "MoveNext()"
                },
                {
                    "LineNumber": 12,
                    "ClassName": "System.Runtime.ExceptionServices.ExceptionDispatchInfo",
                    "MethodName": "Throw()"
                },
                {
                    "LineNumber": 46,
                    "ClassName": "System.Runtime.CompilerServices.TaskAwaiter",
                    "MethodName": "HandleNonSuccessAndDebuggerNotification(Task task)"
                },
                {
                    "LineNumber": 14,
                    "ClassName": "System.Runtime.CompilerServices.TaskAwaiter",
                    "MethodName": "ValidateEnd(Task task)"
                },
                {
                    "LineNumber": 462,
                    "ClassName": "Microsoft.AspNet.SessionState.SessionStateModuleAsync+<GetSessionStateItemAsync>d__74",
                    "MethodName": "MoveNext()"
                },
                {
                    "LineNumber": 12,
                    "ClassName": "System.Runtime.ExceptionServices.ExceptionDispatchInfo",
                    "MethodName": "Throw()"
                },
                {
                    "LineNumber": 46,
                    "ClassName": "System.Runtime.CompilerServices.TaskAwaiter",
                    "MethodName": "HandleNonSuccessAndDebuggerNotification(Task task)"
                },
                {
                    "LineNumber": 14,
                    "ClassName": "System.Runtime.CompilerServices.TaskAwaiter",
                    "MethodName": "ValidateEnd(Task task)"
                },
                {
                    "LineNumber": 577,
                    "ClassName": "Microsoft.AspNet.SessionState.SessionStateModuleAsync+<AcquireStateAsync>d__65",
                    "MethodName": "MoveNext()"
                },
                {
                    "LineNumber": 12,
                    "ClassName": "System.Runtime.ExceptionServices.ExceptionDispatchInfo",
                    "MethodName": "Throw()"
                },
                {
                    "LineNumber": 46,
                    "ClassName": "System.Runtime.CompilerServices.TaskAwaiter",
                    "MethodName": "HandleNonSuccessAndDebuggerNotification(Task task)"
                },
                {
                    "LineNumber": 11,
                    "ClassName": "System.Runtime.CompilerServices.TaskAwaiter",
                    "MethodName": "GetResult()"
                },
                {
                    "LineNumber": 52,
                    "ClassName": "Microsoft.AspNet.SessionState.TaskAsyncHelper",
                    "MethodName": "EndTask(IAsyncResult ar)"
                },
                {
                    "LineNumber": 129,
                    "ClassName": "System.Web.HttpApplication+AsyncEventExecutionStep",
                    "MethodName": "InvokeEndHandler(IAsyncResult ar)"
                },
                {
                    "LineNumber": 34,
                    "ClassName": "System.Web.HttpApplication+AsyncEventExecutionStep",
                    "MethodName": "OnAsyncEventCompletion(IAsyncResult ar)"
                }
            ]
        },

I previously used the the retry=10 and RetryInterval = 100 but still throws this error. Even increasing the retry interval to 1000 didn't help. Can anyone help me to solve this issue? What am I missing?

[StephenMolloy]

I'm not exactly sure where to start on this one. The In-Memory SQL storage option does not use any stored procedures (yet... it will in the next update) so I wouldn't expect most of that linked sql script to apply other than the creation of the "ASPStateTempSessions" table. The line numbers in the json-ified stack trace don't make much sense either. But let's just ramble a bit and see if something makes sense.

By default, requests for session state should be serialized, so there is unlikely to be any conflict like this. I would assume this means the appSetting aspnet:AllowConcurrentRequestsPerSession is set to true in this case. In which case, multiple threads racing against the session state store is expected. This is why the repositories included in this project have the retry parameters.

For in-memory tables, the retry interval should be kept low. Without knowing much about the specific usage pattern in your app, I don't really have much reason to recommend changing away from the defaults. Maybe increase the retry count a little and see if that reduces the frequency of these errors?

A couple of other things I can think of off the top of my head. The current release of this module gets added with preCondition="integratedMode". You might try changing that to preCondition="integratedMode,managedHandler" to see if that reduces contention.

Also, for the memory-optimized table option, you should make sure to use "snapshot" isolation. This is not something that is enforced by the current provider.

Finally, I hope to have a refresh of this package available this month. The new memory-optimized repository will use snapshot isolation and the more limited preCondition above. It will also transition to using stored procedures instead of raw SQL statements. You might consider trying the preview release when it is available to see if it helps.

[StephenMolloy]

The 2.0 release is available on nuget now. It includes many updates to the in-memory SQL provider that among other things, uses stored procedures in this release. #75