Diagnosing Deadlocks in SQL Server 2005

According to MSDN:

http://msdn.microsoft.com/en-us/library/ms191242.aspx

When either the READ COMMITTED SNAPSHOT or ALLOW SNAPSHOT ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. Every time a row is modified by a specific transaction, the instance of the Database Engine stores a version of the previously committed image of the row in tempdb. Each version is marked with the transaction sequence number of the transaction that made the change. The versions of modified rows are chained using a link list. The newest row value is always stored in the current database and chained to the versioned rows stored in tempdb.

For short-running transactions, a version of a modified row may get cached in the buffer pool without getting written into the disk files of the tempdb database. If the need for the versioned row is short-lived, it will simply get dropped from the buffer pool and may not necessarily incur I/O overhead.

There appears to be a slight performance penalty for the extra overhead, but it may be negligible. We should test to make sure.

Try setting this option and REMOVE all NOLOCKs from code queries unless it’s really necessary. NOLOCKs or using global methods in the database context handler to combat database transaction isolation levels are Band-Aids to the problem. NOLOCKS will mask fundamental issues with our data layer and possibly lead to selecting unreliable data, where automatic select / update row versioning appears to be the solution.

ALTER Database [StackOverflow.Beta] SET READ_COMMITTED_SNAPSHOT ON

Typical read/write deadlock comes from index order access. Read (T1) locates the row on index A and then looks up projected column on index B (usually clustered). Write (T2) changes index B (the cluster) then has to update the index A. T1 has S-Lck on A, wants S-Lck on B, T2 has X-Lck on B, wants U-Lck on A. Deadlock, puff. T1 is killed. This is prevalent in environments with heavy OLTP traffic and just a tad too many indexes :). Solution is to make either the read not have to jump from A to B (ie. included column in A, or remove column from projected list) or T2 not have to jump from B to A (don't update indexed column). Unfortunately, linq is not your friend here...

Setting your default to read uncommitted is not a good idea. Your will undoubtedly introduce inconsistencies and end up with a problem that is worse than what you have now. Snapshot isolation might work well, but it is a drastic change to the way Sql Server works and puts a huge load on tempdb.

Here is what you should do: use try-catch (in T-SQL) to detect the deadlock condition. When it happens, just re-run the query. This is standard database programming practice.

There are good examples of this technique in Paul Nielson's Sql Server 2005 Bible.

Here is a quick template that I use:

-- Deadlock retry template

declare @lastError int;
declare @numErrors int;

set @numErrors = 0;

LockTimeoutRetry:

begin try;

-- The query goes here

return; -- this is the normal end of the procedure

end try begin catch
    set @lastError=@@error
    if @lastError = 1222 or @lastError = 1205 -- Lock timeout or deadlock
    begin;
        if @numErrors >= 3 -- We hit the retry limit
        begin;
            raiserror('Could not get a lock after 3 attempts', 16, 1);
            return -100;
        end;

        -- Wait and then try the transaction again
        waitfor delay '00:00:00.25';
        set @numErrors = @numErrors + 1;
        goto LockTimeoutRetry;

    end;

    -- Some other error occurred
    declare @errorMessage nvarchar(4000), @errorSeverity int
    select    @errorMessage = error_message(),
            @errorSeverity = error_severity()

    raiserror(@errorMessage, @errorSeverity, 1)

    return -100
end catch;    

One thing that has worked for me in the past is making sure all my queries and updates access resources (tables) in the same order.

That is, if one query updates in order Table1, Table2 and a different query updates it in order of Table2, Table1 then you might see deadlocks.

Not sure if it's possible for you to change the order of updates since you're using LINQ. But it's something to look at.

I agree with Jeremy on this one. You ask if you should create a new data context for each controller or per page - I tend to create a new one for every independent query.

I'm building a solution at present which used to implement the static context like you do, and when I threw tons of requests at the beast of a server (million+) during stress tests, I was also getting read/write locks randomly.

As soon as I changed my strategy to use a different data context at LINQ level per query, and trusted that SQL server could work its connection pooling magic, the locks seemed to disappear.

Of course I was under some time pressure, so trying a number of things all around the same time, so I can't be 100% sure that is what fixed it, but I have a high level of confidence - let's put it that way.

It's fine with me if my profile is even several minutes out of date.

Are you re-trying the read after it fails? It's certainly possible when firing a ton of random reads that a few will hit when they can't read. Most of the applications that I work with are very few writes compared to the number of reads and I'm sure the reads are no where near the number you are getting.

If implementing "READ UNCOMMITTED" doesn't solve your problem, then it's tough to help without knowing a lot more about the processing. There may be some other tuning option that would help this behavior. Unless some MSSQL guru comes to the rescue, I recommend submitting the problem to the vendor.