LINQ-to-SQL Locks SQL Server during query?

Question

Having a weird problem. I'm testing an ASP.NET application I wrote that queries SQL server via AJAX. The application is using LINQ-to-SQL to load data from approx 8 or so tables in a join, and every once in a while, the call to SQL server locks up and never returns.

My first thought was that it was getting deadlocked, but from what I've read, SQL server should elect to kill one of the offending threads if this happens. In the case of this app, there aren't any other users accessing the database either, so I don't see how this could be the problem.

Other symptoms:

1. The processor usage on the server spikes at around 40% when executing this call, and stays there, even after the application is closed ("Stopped" in Visual Studio).

2. The server continues executing the call until I actually go and kill the Visual Studio web server (Cassini) that lives in the tray.

3. When looking at what the SQL server is doing, all we can see is that it is executing the LINQ-to-SQL call, nothing looks out of the ordinary.

Anyone have any thoughts about what this "smells" like?

Thanks, Sam

Answer 1

First, update your sql server to the latest service pack level. You might want to look at the Cumulative Updates that have been released since that service pack and see if anything sounds like your particular problem. It may have been resolved.

SQL Server will elect to kill off one of the items in a dead lock, but it also depends on what the query time out settings are. If your timeout is high enough (300 seconds...), SQL Server could continue executing the call for awhile.

If possible you might use NOLOCK on your tables in the sql call so that it is a non-blocking select. For Linq-To-Sql, set the Transaction Isolation level to Read Uncommitted, it is the same as NOLOCK.

I would further put a trace on the linq-to-sql calls, and re run them in sql profiler to see if there is something that can be tuned a bit better, like better indexes, etc.

Answer 2

I've seen similar behavior in linq if a transaction starts, but never completes.