Linq-To-Sql and MARS woes - A severe error occurred on the current command. The results, if any, should be discarded

Question

We have built a website based on the design of the Kigg project on CodePlex:

http://kigg.codeplex.com/releases/view/28200

Basically, the code uses the repository pattern, with a repository implementation based on Linq-To-Sql. Full source code can be found at the link above.

The site has been running for some time now and just about a year ago we started to get errors like:

• There is already an open DataReader associated with this Command which must be closed first.
• ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

These are the closest error examples I can find based on my memory. These errors started to occur when the site traffic started to pick up. After banging my head against the wall, I figured out assumed that the problem is inherit within Linq-To-Sql and how we are using the same connection to call multiple commands in a single web request.

Evenually, I discovered MARS (Multiple Active Result Sets) and added that to the data context's connection string and like magic, all of my errors went away.

Now, fast forward about 1 year and the site traffic has increased tremendously. Every week or so, I will get an error in SQL Server that reads:

A severe error occurred on the current command. The results, if any, should be discarded

Immediately after this error, I receive hundreds to thousands of InvalidCastException errors in the error logs. Basically, this error shows up for each and every call to the Linq-To-Sql data context. Only after I restart the web server do these errors clear up.

I read a post on the Micosoft Support site that descrived my problem (minus the InvalidCastException errors) and stating the solution is that if I'm going to use MARS that I should also use Asncronous Processing=True. I tried this, but it did not solve my problem either.

Not really sure where to go from here. Hopefully someone here has seen and solved this problem before.

Ash

@AaronBertrand, thanks. The version is Microsoft SQL Server 2012 - 11.0.2218.0 (X64) – Ash

You might consider service packs / cumulative updates. A lot of these "severe error" problems are fixed quietly (and for all I know, this one was fixed non-quietly, but I have not reviewed all the publicly exposed fixes). Also I have not heard a lot of success stories with MARS. Are you sure you need it? – Valuable

I figured out that the problem is inherit withing Linq-To-Sql No is not. Attempting to create multiple results sets is a bug in your code. – Aged

All error messages that you posted except for the severe error point to threading bugs because they are non-deterministic. Are you using static state? Are you reusing connections? – Pentheus

@RemusRusanu, interesting perspective... I never thought of it that way... ;) To confirm we are talking about the same thing, this is when a command (find a user by id) is executed on the same connection as another command (list all users) at the same time? – Ash

@usr, we are using Unity to instantiate an IDatabase (Data Context) on a PerWebRequest basis. Then we are instantiating the appropriate repository (IUserRepostory as an example) PerWebRequest. We aren't using singltons and I assume a new connection gets pulled from the pool each time (per web request) the IDatabase gets instantiated. – Ash

@SwisherSweet that sounds very safe. What did your debugging turn out? What did the stacks tell you? Why are the errors non-deterministic? – Pentheus

@AaronBertrand, thank you. I will definitely install these and cross my fingers that this is all that there is wrong. – Ash

@usr, the problem is extremely intermittent and I have never been able to reproduce it in a dev/test/staging environment. I guess the next step would be to try to debug the app whilst running a load tester against the app on my desktop. Regarding the stack trace, the last trace just showed the exception (a severe error occured...) on the simplest of database call. I was simply finding a user by ID. I don't quite understand what you meant by "Why are the errors non-deterministic?" Thank you. – Ash

I never thought of it that way: the fact that you are applying 'solutions' that you do not understand ('I discovered MARS', 'use Async Processing') tells me all I need to know about the quality of your investigation. SELECT is not broken. – Aged

@Remus Rusanu, I thought I had already made fun of my arragant statement and have already corrected it. Trust me, we understood what MARS was before we applied it. MARS solved our problem and has been working for over about a year now. The Asyn Processing was a shot in the dark but alas, it did not solve this problem as the Microsoft employee suggested it would. – Ash

@SwisherSweet "Why are the errors non-deterministic?" is a key question you need to answer. It is non-determinstic, right? The errors are intermittent and kind of random. That points to bug with connection reuse across threads. Why do you think this random behavior exists? (We don't and can't know - you need to debug because we don't have access to the code). – Pentheus

A severe error occurred on the current command: open a support case with CSS, they have the means to investigate it. Do not assume is the same problem, since all server dumps manifest the same client error message. For the open result set issue, get to the root cause and address it, don't guess. – Aged

@usr, excellent point. The only think I can think of is that fact that we execute multiple linq-to-sql commands during a single web request and on a single connection, such as listing all users and looking up a value in the database. However, we are not spinning up multiple threads to do this... this all happens in the lifetime of the web request and I assume within the same thread. As you said, this seams pretty safe, right? Perhaps the issue is related to a SP fix as Aaron suggested. – Ash

"Only after I restart the web server do these errors clear up." Why is that? This points to static state that was corrupted. Requests are supposed to be independent (which I think you understand!). So just one request should be hosed, not the entire process. I think you'll find this bug with load testing. Either replay production load or just spin up 256 threads and fire at the server (be sure to turn off all connection limits and such). A high thread count causes many different thread schedulings. – Pentheus

@usr, thanks for the tip. I will do that. Regarding your question, "What is that?" When the sever error occurs, then the other errors (InvalidCastException) occur in the hundres to thousands on every linq-to-sql call after that. Every call on every thread is then corrupt/thows those errors. They are like "Connect Cast DateTime as String" and other similar errors, but after you restart the server, they no longer happen. Only after the "server error". – Ash

Answer 1

So after much refactoring and re-architecting, we figured out that problem all along is MARS (Multiple Active Result Sets) itself. Not sure why or what happens exactly but MARS somehow gets result sets mixed up and doesn't recover until the web app is restarted.

We removed MARS and the errors stopped.

If I remember correctly, we added MARS to solve the problem where a connection/command was already closed using LinqToSql and we tried to access an object graph that hadn't been loaded. Without MARS, we'd get an error. But when we added MARS, it seemed to not care about it. This is really a great example of us not really understanding what the heck we were doing and we learned some valuable (and expensive) lessons from this.

Hope this helps others who have experienced this.

Thanks to all how have contributed their comments and answers.

Ash

Answer 2

I have the same issue. Once the errors start, I have to restart the IIS Application Pool to fix.

I have not been able to reproduce the bug in dev despite trying many different scenarios involving multi-threading, leaving connections open, etc etc.

One possible lead I do have is that amongst the errors in the server Event Log is an OutOfMemoryException for the Application Pool. Perhaps this is the underlying cause of the spurious SQL Datareader errors (a memory leak elsewhere). Although again I haven't been able to reproduce this in dev.

Obviously if you are using a 64 bit OS then this is probably not the cause in your case.

Misleading

Thanks for your suggestion. I was able to reproduce the problem using Grinder. As I mentioned, MARS was the root cause of all my problems. – Ash

Answer 3

I understand you figured out the solution..

Following is not a direct solution to the problem; but it is good for others to take a look at

1. What does "A severe error occurred on the current command. The results, if any, should be discarded." SQL Azure error mean?

2. http://social.msdn.microsoft.com/Forums/en-US/bbe589f8-e0eb-402e-b374-dbc74a089afc/severe-error-in-current-command-during-datareaderread

Hallucinosis