Database cache that I'm not aware of?

Question

I'm using asp.net mvc, linq2sql, iis7 and sqlserver express 2008.

I get these intermittent server errors, primary key conflicts on insertion. I'm using a different setup on my development computer so I can't debug. After a while they go away. Restarting iis helps. I'm getting the feeling there is cache somewhere that I'm not aware of. Can somebody help me sort out these errors?

Cannot insert duplicate key row in object 'dbo.EnquiryType' with unique index 'IX_EnquiryType'.

Edits regarding Venemos answer

Is it possible that another application is also accessing the same database simultaneously? Yes there is, but not this particular table and no inserts or updates. There is one other table with which I experience the same problem but it has to do with a different part of the model.

How often an in what context do you create a new DataContext instance? Only once, using the singleton pattern.

Are the primary keys generated by the database or by the application? Database.

Which version of ASP.NET MVC and which version of .NET are you using? RC2 and 3.5.

Answer 1

Well, it can be because of many reasons. I'll give you some questions. Please edit your question with the info about these, and it will be much more straightforward for us to help you.

• Is it possible that another application is also accessing the same database simultaneously?
  This would be the most likely reason. I experienced this, too.
• How often an in what context do you create a new DataContext instance?
  The best way would be to create a new one per request.
• Are the primary keys generated by the database or by the application?
• Which version of ASP.NET MVC and which version of .NET are you using?

Answer 2

Two guesses for you:

1) If you've got a singleton DataContext, wouldn't that mean it's shared by all threads?

The MSDN reference for DataContext says an instance is "designed to last for one unit of work" and typically created "at method scope or as a member of short-lived classes".

I'd try moving away from the singleton pattern and creating a new context each time it is needed.

2) When you say your keys are generated by the database, is that through Identity fields, or some kind of "select max + 1" pattern? If not by an identity, then you may have concurrent connections obtaining the same "next" key value. Check your transaction isolation levels.

EDIT - following on from comment Are you sure that the index violation is on the primary key? Using SQL Server Management Studio, primary keys are normally given a PK_ prefix, and IX_ is used for further indexes. Check the fields which go to make up "IX_EnquiryType" and ensure it's not just a rare logic problem that's causing your problems.