ASP.NET Session State with SQL Server In-Memory OLTP and Session isolation set to snapshot
We deployed a new build on Framework v 4.7.2. We also deployed the async Asp.Net session state module and in memory OLTP provider as documented here
Our testing environment was fine, and the canary server tested fine. We then rolled the build out to the other servers in the farm, and suddenly starting seeing lots of these exceptions:
System.Data.SqlClient.SqlException (0x80131904): Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT
Our provider configuration is as follows:
<providers>
<add name="SqlSessionStateProviderAsync" connectionStringName="Session.ConnectionString"
type="Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync,
Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync,
Version=1.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
UseInMemoryTable="true" MaxRetryNumber="5" RetryInterval="100" />
</providers>
EDIT:
Removed reference to AllowConcurrentRequestsPerSession which is not configured here, but as an app setting
c# asp.net-mvc session-state
sql-server-2017 memory-optimized-tables asked Nov 22 '18 at 14:52
Matthew EvansMatthew Evans
3,51862754
add a comment |
We deployed a new build on Framework v 4.7.2. We also deployed the async Asp.Net session state module and in memory OLTP provider as documented here
Our testing environment was fine, and the canary server tested fine. We then rolled the build out to the other servers in the farm, and suddenly starting seeing lots of these exceptions:
System.Data.SqlClient.SqlException (0x80131904): Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT
Our provider configuration is as follows:
<providers>
<add name="SqlSessionStateProviderAsync" connectionStringName="Session.ConnectionString"
type="Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync,
Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync,
Version=1.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
UseInMemoryTable="true" MaxRetryNumber="5" RetryInterval="100" />
</providers>
EDIT:
Removed reference to AllowConcurrentRequestsPerSession which is not configured here, but as an app setting
c# asp.net-mvc session-state
sql-server-2017 memory-optimized-tables asked Nov 22 '18 at 14:52
Matthew EvansMatthew Evans
3,51862754
2
Does your code set the transaction isolation level anywhere (and in particular, does it set it toSNAPSHOTanywhere)? When connections are recycled in the connection pool, the transaction isolation level is not reset, so if even one spot in the code anywhere changes it from the default, it should be explicitly set everywhere (or, alternatively, code that explicitly changes the isolation level should reset things before disposing the connection, or, alternatively, stored procedures should be used, as these do so implicitly).
– Jeroen Mostert
Nov 22 '18 at 15:03
There are two major database settings that interact with snapshot isolation and in-memory OLTP:READ_COMMITTED_SNAPSHOTandMEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. You may want to check if these are the same across environments. However, I don't think an incorrect setting of either one will produce this particular error (they will produce others).
– Jeroen Mostert
Nov 22 '18 at 15:05
@JeroenMostert - I think thats the answer. Connection re-use - I'll revert
– Matthew Evans
Nov 22 '18 at 15:10
@JeroenMostert - can you convert your comment to an answer, so I can accept it?
– Matthew Evans
Nov 26 '18 at 14:27
add a comment |
We deployed a new build on Framework v 4.7.2. We also deployed the async Asp.Net session state module and in memory OLTP provider as documented here
Our testing environment was fine, and the canary server tested fine. We then rolled the build out to the other servers in the farm, and suddenly starting seeing lots of these exceptions:
System.Data.SqlClient.SqlException (0x80131904): Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT
Our provider configuration is as follows:
<providers>
<add name="SqlSessionStateProviderAsync" connectionStringName="Session.ConnectionString"
type="Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync,
Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync,
Version=1.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
UseInMemoryTable="true" MaxRetryNumber="5" RetryInterval="100" />
</providers>
EDIT:
Removed reference to AllowConcurrentRequestsPerSession which is not configured here, but as an app setting
c# asp.net-mvc session-state
sql-server-2017 memory-optimized-tables asked Nov 22 '18 at 14:52
Matthew EvansMatthew Evans
3,51862754
We deployed a new build on Framework v 4.7.2. We also deployed the async Asp.Net session state module and in memory OLTP provider as documented here
Our testing environment was fine, and the canary server tested fine. We then rolled the build out to the other servers in the farm, and suddenly starting seeing lots of these exceptions:
System.Data.SqlClient.SqlException (0x80131904): Memory optimized tables and natively compiled modules cannot be accessed or created when the session TRANSACTION ISOLATION LEVEL is set to SNAPSHOT
Our provider configuration is as follows:
<providers>
<add name="SqlSessionStateProviderAsync" connectionStringName="Session.ConnectionString"
type="Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync,
Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync,
Version=1.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
UseInMemoryTable="true" MaxRetryNumber="5" RetryInterval="100" />
</providers>
EDIT:
Removed reference to AllowConcurrentRequestsPerSession which is not configured here, but as an app setting
c# asp.net-mvc session-state
sql-server-2017 memory-optimized-tables c# asp.net-mvc session-state
sql-server-2017 memory-optimized-tables asked Nov 22 '18 at 14:52
Matthew EvansMatthew Evans
3,51862754
asked Nov 22 '18 at 14:52
Matthew EvansMatthew Evans
3,51862754
edited Nov 26 '18 at 14:47
Matthew Evans
asked Nov 22 '18 at 14:52
Matthew EvansMatthew Evans
3,51862754
asked Nov 22 '18 at 14:52
Matthew EvansMatthew Evans
3,51862754
asked Nov 22 '18 at 14:52
Matthew EvansMatthew Evans
3,51862754
3,51862754
2
Does your code set the transaction isolation level anywhere (and in particular, does it set it toSNAPSHOTanywhere)? When connections are recycled in the connection pool, the transaction isolation level is not reset, so if even one spot in the code anywhere changes it from the default, it should be explicitly set everywhere (or, alternatively, code that explicitly changes the isolation level should reset things before disposing the connection, or, alternatively, stored procedures should be used, as these do so implicitly).
– Jeroen Mostert
Nov 22 '18 at 15:03
There are two major database settings that interact with snapshot isolation and in-memory OLTP:READ_COMMITTED_SNAPSHOTandMEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. You may want to check if these are the same across environments. However, I don't think an incorrect setting of either one will produce this particular error (they will produce others).
– Jeroen Mostert
Nov 22 '18 at 15:05
@JeroenMostert - I think thats the answer. Connection re-use - I'll revert
– Matthew Evans
Nov 22 '18 at 15:10
@JeroenMostert - can you convert your comment to an answer, so I can accept it?
– Matthew Evans
Nov 26 '18 at 14:27
add a comment |
2
Does your code set the transaction isolation level anywhere (and in particular, does it set it toSNAPSHOTanywhere)? When connections are recycled in the connection pool, the transaction isolation level is not reset, so if even one spot in the code anywhere changes it from the default, it should be explicitly set everywhere (or, alternatively, code that explicitly changes the isolation level should reset things before disposing the connection, or, alternatively, stored procedures should be used, as these do so implicitly).
– Jeroen Mostert
Nov 22 '18 at 15:03
There are two major database settings that interact with snapshot isolation and in-memory OLTP:READ_COMMITTED_SNAPSHOTandMEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. You may want to check if these are the same across environments. However, I don't think an incorrect setting of either one will produce this particular error (they will produce others).
– Jeroen Mostert
Nov 22 '18 at 15:05
@JeroenMostert - I think thats the answer. Connection re-use - I'll revert
– Matthew Evans
Nov 22 '18 at 15:10
@JeroenMostert - can you convert your comment to an answer, so I can accept it?
– Matthew Evans
Nov 26 '18 at 14:27
2
2
Does your code set the transaction isolation level anywhere (and in particular, does it set it to
SNAPSHOT anywhere)? When connections are recycled in the connection pool, the transaction isolation level is not reset, so if even one spot in the code anywhere changes it from the default, it should be explicitly set everywhere (or, alternatively, code that explicitly changes the isolation level should reset things before disposing the connection, or, alternatively, stored procedures should be used, as these do so implicitly).– Jeroen Mostert
Nov 22 '18 at 15:03
Does your code set the transaction isolation level anywhere (and in particular, does it set it to
SNAPSHOT anywhere)? When connections are recycled in the connection pool, the transaction isolation level is not reset, so if even one spot in the code anywhere changes it from the default, it should be explicitly set everywhere (or, alternatively, code that explicitly changes the isolation level should reset things before disposing the connection, or, alternatively, stored procedures should be used, as these do so implicitly).– Jeroen Mostert
Nov 22 '18 at 15:03
There are two major database settings that interact with snapshot isolation and in-memory OLTP:
READ_COMMITTED_SNAPSHOT and MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. You may want to check if these are the same across environments. However, I don't think an incorrect setting of either one will produce this particular error (they will produce others).– Jeroen Mostert
Nov 22 '18 at 15:05
There are two major database settings that interact with snapshot isolation and in-memory OLTP:
READ_COMMITTED_SNAPSHOT and MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. You may want to check if these are the same across environments. However, I don't think an incorrect setting of either one will produce this particular error (they will produce others).– Jeroen Mostert
Nov 22 '18 at 15:05
@JeroenMostert - I think thats the answer. Connection re-use - I'll revert
– Matthew Evans
Nov 22 '18 at 15:10
@JeroenMostert - I think thats the answer. Connection re-use - I'll revert
– Matthew Evans
Nov 22 '18 at 15:10
@JeroenMostert - can you convert your comment to an answer, so I can accept it?
– Matthew Evans
Nov 26 '18 at 14:27
@JeroenMostert - can you convert your comment to an answer, so I can accept it?
– Matthew Evans
Nov 26 '18 at 14:27
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433496%2fasp-net-session-state-with-sql-server-in-memory-oltp-and-session-isolation-set-t%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53433496%2fasp-net-session-state-with-sql-server-in-memory-oltp-and-session-isolation-set-t%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Does your code set the transaction isolation level anywhere (and in particular, does it set it to
SNAPSHOTanywhere)? When connections are recycled in the connection pool, the transaction isolation level is not reset, so if even one spot in the code anywhere changes it from the default, it should be explicitly set everywhere (or, alternatively, code that explicitly changes the isolation level should reset things before disposing the connection, or, alternatively, stored procedures should be used, as these do so implicitly).– Jeroen Mostert
Nov 22 '18 at 15:03
There are two major database settings that interact with snapshot isolation and in-memory OLTP:
READ_COMMITTED_SNAPSHOTandMEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. You may want to check if these are the same across environments. However, I don't think an incorrect setting of either one will produce this particular error (they will produce others).– Jeroen Mostert
Nov 22 '18 at 15:05
@JeroenMostert - I think thats the answer. Connection re-use - I'll revert
– Matthew Evans
Nov 22 '18 at 15:10
@JeroenMostert - can you convert your comment to an answer, so I can accept it?
– Matthew Evans
Nov 26 '18 at 14:27