ASP.NET web app deadlocking - think it's caused by SQL Server locking

Question

Our client's web app restarts suddenly at random intervals. For each restart, we've found an entry like this 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 has happened 10 times in 3 weeks, several of those being 2 or 3 times in several hours, and also going over a week without it happening.

In the crash dump that we have maybe 70-80 client connections, like so:

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

(that's 55 minutes!!! there's no reason a client connection should be around that long)

Relevant entries in machine.config:

<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" />

This latest time we were able to look at it as it was happening, and saw about 20 queries all in 'suspended' status in Sql Server. It looked like they could have all been related to one table (the Items table, a very central one for lots of different operations).

We weren't sure what the best thing to do was in the middle of the problem. When the crash occurred, Sql Server cleared out.

Any guidance on what's going on, or how to find out what's going on, would be much appreciated.

Answer 1

Check the running processes in SQL server using the Activity monitor.

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

Answer 2

If it's a deadlock, it means is a deadlock that has a loop that completes outside SQL. Meaning you are trying to acquire process resources (ie. C# 'lock') while holding SQL resources (ie. a transaction). To give an example houw this can happen consider the following scenario:

1. T1 starts a SQL transaction and updates a table A in SQL
2. T2 locks an object in C#
3. T1 tries to lock the same object in C#, blocks on T2's lock
4. T2 reads from SQL table A, blocks on T1's update
5. T1 waits on T2 inside your process, T2 waits for T1 inside SQL, undetectable deadlock

Situations like this cannot be detected inside SQL's deadlock monitoring, since the deadlock loop completes outside SQL. How would you diagnose such a problem? For the SQL server side of the loop you have a lot of powerful tools at your disposal, primarily sys.dm_exec_requests which can tell you which requests are blocked by what. But unfortunately on the app size of the loop there is no out-of-the-box instrumentation, so you are on your own. An experienced eye can detect the problem on code inspection (doing SQL calls while holding C# locks or acquiring C# locks in the middle of SQL transactions are a big give away), otherwise you have to either exercise some masterful WinDbg-fu, or instrument the code.

You should also consider that this is not a deadlock at all. You can have your 20 SQL requests blocked by an ordinary code defect in your application, like a transaction leak on certain requests (ie. the requests wait for a transaction that blocks them to commit, but that transaction has leaked in code and will never be closed). Again, sys.dm_exec_requests is your friend.