1

We're using new relic to monitor our app and we're getting this error rather frequently, and I'm having a difficult time tracking it down:

System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_ASPStateTempSess_267ABA7A'. Cannot insert duplicate key in object 'dbo.ASPStateTempSessions'. The statement has been terminated.

It seems like an easy fix if we could find the root cause, just check that the primary key does not exist before inserting, but this seems like default functionality that I don't know if we have the ability to modify. The are no specific lines of code mentioned, so I really don't know where to begin. I've done a bunch of searching on here and on google but can't find this exact problem. The app is running on 2 load balanced servers and I've checked that the web.configs are the same, with a common aspstate database. Here's the stack trace if it helps:

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(TdsParserStateObject\ stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand\ cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler,\ TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,\ RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,\ RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,\ RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult\ result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.SessionState.SqlSessionStateStore.CreateUninitializedItem(HttpContext\ context, String id, Int32 timeout)

Does anyone have any experience with something like this or can maybe point me in another direction of other things to look at?

Westwick
  • 2,367
  • 3
  • 28
  • 51
  • Well, it's going to be from `TempInsertUninitializedItem`, `TempInsertStateItemShort` or `TempInsertStateItemLong`. But I suspect the problem is actually that the procedure is being called multiple times concurrently for the same `@sessionId` (the procedures do not validate whether they should update or insert). Is this on a web farm that does not pin sessions to a server? – Aaron Bertrand Jun 27 '13 at 18:48
  • What would be calling those TempInsert functions? AFAIK modifying those functions isn't possible, and the stack trace isn't telling me where they're getting called from. This is part of a web farm (2 servers load balanced), but what do you mean by pinning the sessions to a server? They web.config for both servers points to the same database for storing sessions. – Westwick Jun 27 '13 at 20:14
  • The load balancer should have the ability to pin a session to one server once it's been established. Otherwise, its next request may ring off the other server, and this might be where the conflict is. It thinks it's a new session, so it calls the "create a new session" procedure, and oops, the other server already did that. And yes, you can modify those stored procedures; [I give some other optimization ideas here](http://www.sqlperformance.com/2013/01/t-sql-queries/optimize-aspstate) (but they won't fix your problem). – Aaron Bertrand Jun 27 '13 at 20:18
  • Oh didn't realize those were stored procedures... thanks for the link. Not sure where to go with this problem though o_O – Westwick Jun 27 '13 at 20:22
  • Are you doing any custom session state provider code anywhere in terms of generating IDs or anything? – Haney Jun 27 '13 at 20:42
  • No.. we are generating our own GUIDs but outside of the standard Session["key"] context... I'm thinking now, if we're not reading or writing session variables in this way, it's probably safe to just set – Westwick Jun 27 '13 at 20:53
  • @Drew Ever find a resolution for this? – svidgen Oct 24 '14 at 16:52
  • possible duplicate of [dbo.TempGetStateItemExclusive3 called repeatedly](http://stackoverflow.com/questions/15572994/dbo-tempgetstateitemexclusive3-called-repeatedly) – svidgen Apr 21 '15 at 17:10

0 Answers0