ASP.NET Web Application Blocking - I think this is caused by SQL Server Blocking

Question

Our client web application runs suddenly at random intervals. For each restart, we found this entry in the Windows event log:

Event Type: Warning Event Source: W3SVC-WP Event Category: None Event ID: 2262 Date: 2/21/2010 Time: 1:33:52 PM User: N/A Computer: LIQUID-NXCFZ9DJ Description: ISAPI 'c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_isapi.dll' reported itself as unhealthy for the following reason: 'Deadlock detected'. 

This happened 10 times in 3 weeks, several of them were 2 or 3 times in a few hours, and also after a week without doing this.

In the crash dump that we have, maybe 70-80 client connections, for example:

 GET request for <path here> Mapped To URL <mapped path> HTTP Version HTTP/1.1 SSL Request False Time alive 00:55:24 QueryString <query string here> Request mapped to HTTP Request State HTR_READING_CLIENT_REQUEST Native Request State NREQ_STATE_PROCESS 

(this is 55 minutes !!! there is no reason why the client connection should be so long)

Corresponding entries in the machine.config file:

 <system.net> <connectionManagement> <add address="*" maxconnection="200" /> </connectionManagement> </system.net> 

and (inside):

 <deployment retail="true" /> <!--<customErrors mode="Off"/>--> <processModel autoConfig="true" memoryLimit="60" maxIoThreads="200" minIoThreads="30" minWorkerThreads="40" maxWorkerThreads="200" clientConnectedCheck="00:00:05" /> <httpRuntime minFreeThreads="20" minLocalRequestFreeThreads="10" enableKernelOutputCache="false" maxRequestLength="10240" /> 

Recently, we were able to look at it the way it was now, and looked at about 20 requests in the suspended state on Sql Server. It seems that they were all associated with the same table (the Items table, very central to many different operations).

We were not sure what was best done in the middle of the problem. When the failure occurred, Sql Server cleared.

Any guidance on what is happening or how to find out what is going on will be greatly appreciated.

Answer 1

If it is a dead end, it means a dead end that has a loop that terminates external SQL. This means that you are trying to get process resources (for example, C # lock) while saving SQL resources (i.e. Transactions). To give an example, this can happen considering the following scenario:

• T1 starts a SQL transaction and updates table A in SQL
• T2 blocks an object in C #
• T1 tries to lock the same object in C #, T2 lock blocks
• T2 reads from SQL A table, blocks T1 update
• T1 is waiting for T2 inside your process, T2 is waiting for T1 inside SQL, an undefined deadlock

Situations like this cannot be detected in the SQL stub because the stub cycle ends outside of SQL. How would you diagnose such a problem? For the SQL server side in the loop, you have many powerful tools, primarily sys.dm_exec_requests , which can tell you which queries are blocked by what. But, unfortunately, there are no ready-made tools for the size of the application cycle, so you are on your own. An experienced look may find a problem when checking the code (executing SQL queries while holding C # locks or acquiring C # locks in the middle of SQL transactions is a big return), otherwise you need to either run some WinDbg -fu workshops or measure the code.

You should also consider that this is not a dead end. Your 20 SQL queries can be blocked with a common code defect in the application, for example, a transaction leak for certain queries (i.e., Queries expect a transaction that blocks their commit, but this transaction has leaked into the code and will never be closed) . Again, sys.dm_exec_requests is your friend.

Answer 2

Check the running processes on the SQL server using the activity monitor.

UPDATE: I saw that this particular error is probably not SQL. I found this article on how to generate additional information about the deadlock: http://support.microsoft.com/?ID=828222