I have data about deadlocks, but I can't understand why they occur

Question

I am receiving a lot of deadlocks in my big web application.

How to automatically re-run deadlocked transaction? (ASP.NET MVC/SQL Server)

Here I wanted to re-run deadlocked transactions, but I was told to get rid of the deadlocks - it's much better, than trying to catch the deadlocks.

So I spent the whole day with SQL Profiler, setting the tracing keys etc. And this is what I got.

There's a Users table. I have a very high usable page with the following query (it's not the only query, but it's the one that causes troubles)

UPDATE Users
SET views = views + 1
WHERE ID IN (SELECT AuthorID FROM Articles WHERE ArticleID = @ArticleID)

And then there's the following query in ALL pages:

User = DB.Users.SingleOrDefault(u => u.Password == password && u.Name == username);

That's where I get User from cookies.

Very often a deadlock occurs and this second Linq-to-SQL query is chosen as a victim, so it's not run, and users of my site see an error screen.

This is information from the .XDL graph captured by SQL Profiler (It's just the first deadlock, it's not the only one. The whole list is gigantic.):

<deadlock-list>
    <deadlock victim="process824df048">
        <process-list>
            <process id="process824df048" taskpriority="0" logused="0" waitresource="PAGE: 7:1:13921" waittime="1830" ownerId="91418" transactionname="SELECT" lasttranstarted="2010-05-31T12:17:37.663" XDES="0x868175e0" lockMode="S" schedulerid="2" kpid="5076" status="suspended" spid="72" sbid="0" ecid="2" priority="0" trancount="0" lastbatchstarted="2010-05-31T12:17:37.663" lastbatchcompleted="2010-05-31T12:17:37.663" clientapp=".Net SqlClient Data Provider" hostname="WIN-S41KV2CLS67" hostpid="6920" isolationlevel="read committed (2)" xactid="91418" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="74" sqlhandle="0x02000000de1cb30b5b2e40e31ffb345af3c7529430b559c2">
*password-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------     </frame>
                    <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
                </executionStack>
                <inputbuf>
                </inputbuf>
            </process>
            <process id="process8765fb88" taskpriority="0" logused="216" waitresource="PAGE: 7:1:14196" waittime="1822" ownerId="91408" transactionname="UPDATE" lasttranstarted="2010-05-31T12:17:37.640" XDES="0x86978e90" lockMode="IX" schedulerid="2" kpid="5216" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-05-31T12:17:37.557" lastbatchcompleted="2010-05-31T12:17:37.557" clientapp=".Net SqlClient Data Provider" hostname="WIN-S41KV2CLS67" hostpid="6920" loginname="sdfkj93jks9sl" isolationlevel="read committed (2)" xactid="91408" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
                <executionStack>
                    <frame procname="database.dbo.UpdateUserStats" line="31" stmtstart="1794" stmtend="2088" sqlhandle="0x03000700bac8836333e58f00879d00000100000000000000">
UPDATE Users
    SET Views = Views + 1
    WHERE ID IN (SELECT AuthorID FROM Articles WHERE ArticleID = @ArticleID)     </frame>
                    <frame procname="adhoc" line="1" stmtstart="84" sqlhandle="0x01000700b7c78e0760dd3f81000000000000000000000000">
EXEC @RETURN_VALUE = [dbo].[UpdateUserStats] @UserID = @p0    </frame>
                    <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
                </executionStack>
                <inputbuf>
(@p0 int,@RETURN_VALUE int output)EXEC @RETURN_VALUE = [dbo].[UpdateUserStats] @UserID = @p0   </inputbuf>
            </process>
            <process id="process86ce0988" taskpriority="0" logused="10000" waittime="1806" schedulerid="1" kpid="2604" status="suspended" spid="72" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2010-05-31T12:17:37.663" lastbatchcompleted="2010-05-31T12:17:37.663" clientapp=".Net SqlClient Data Provider" hostname="WIN-S41KV2CLS67" hostpid="6920" loginname="sdfkj93jks9sl" isolationlevel="read committed (2)" xactid="91418" currentdb="7" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
                <executionStack>
                    <frame procname="adhoc" line="1" stmtstart="74" sqlhandle="0x02000000de1cb30b5b2e40e31ffb345af3c7529430b559c2">
*password-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------     </frame>
                    <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
                </executionStack>
                <inputbuf>
*password--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    </inputbuf>
            </process>
        </process-list>
        <resource-list>
            <pagelock fileid="1" pageid="13921" dbid="7" objectname="database.dbo.Users" id="lock85535c80" mode="IX" associatedObjectId="72057594046382080">
                <owner-list>
                    <owner id="process8765fb88" mode="IX"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process824df048" mode="S" requestType="wait"/>
                </waiter-list>
            </pagelock>
            <pagelock fileid="1" pageid="14196" dbid="7" objectname="database.dbo.Users" id="lock8469f980" mode="SIU" associatedObjectId="72057594046382080">
                <owner-list>
                    <owner id="process86ce0988" mode="S"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process8765fb88" mode="IX" requestType="convert"/>
                </waiter-list>
            </pagelock>
            <exchangeEvent id="Pipe894b0680" WaitType="e_waitPipeGetRow" nodeId="0">
                <owner-list>
                    <owner id="process824df048"/>
                </owner-list>
                <waiter-list>
                    <waiter id="process86ce0988"/>
                </waiter-list>
            </exchangeEvent>
        </resource-list>
    </deadlock>

I read a lot about deadlocks... And I don't understand why this is causing a deadlock.

So obviously both of this queries run very often. At least once a second. Maybe even more often (300-400 users online). So they can be run at the same time very easily, but why does it cause a deadlock? Please help.

Thank you

Dilapidate

Are you on SQL2005 or later? If so did you get the Deadlock Graph from SQL Profiler? Also do you know what transaction isolation level your queries are running under? – Zymolysis

Yes I got the graph. That's where I got this info from. I don't know anything about transaction isolation. Where can I check it? – Dilapidate

In the profiler trace right click the deadlock graph event and choose extract event data, save it as xml. Then open that in notepad and find "isolationlevel" – Zymolysis

I just checked, it's always "read committed (2)" – Dilapidate

Answer 1

You need to capture the deadlock graph. Attach Profiler and capture the Deadlock Graph Event class. Save the .XDL graph and add that info to your post.

Until then, is pretty obvious that your DB.Users.SingleOrDefault query requires an index on Name at least, if not on Name and Password:

CREATE INDEX idxUsersNamePassword on Users(Name,Password);

I expect Users already has an index on ID, and Articles has an index on ArticleID which covers AuthorID too. Assuming the Users.ID and Articles.ArticleID are PKs in they're respective tables, they are probably the respective's clustered key so it true. It worth double checking, though.

And, as I already answered you once in your previous post you decided to move on and leave un-answered, you should consider turning on Snapshot Isolation:

ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON

Besides that, storing password in clear text is a major #fail.

Update after deadlock info

There are three processes (requests):

• A) ...F048 which is running the SELECT ... FROM Users WHERE Password = ... and Name = ...
• B) ...0988 which is running the SELECT ... FROM Users WHERE Password = ... and Name = ...
• C) ...FB88 which is running the UPDATE ...

The deadlock cycle is:

1. C waits on Page IX lock, is blocked by A's S lock
2. B waits on Page S lock, is blocked by C's IX lock
3. A waits on parallel exchange resources, is blocked by B

The cycle therefore is C->A->B->C.

From the fact that the two SELECTs involved decide to 1) use a parallel plan and 2) use page locks is obvious that they do an end-to-end scan of the entire Users table. so the problem is, as I predicted, a lack of index on (Name, Password) on Users which causes the query to scan way too much data. Adding the index would turn the SELECT into a straight SEEK on the Nc index and a lookup on the Clustered index, and this would dramatically reduce the window of overlap with the UPDATE. Right now the UPDATE is pretty much guaranteed to conflict with all SELECTs, since every SELECT is guaranteed to read every row.

Adding the index will aleviate the immediate problem. Using Snapshot Isolation will mask the problem, since the end-to-end scans are still going to occur unless the (Name, Password) index is added. Or only (Name) will likely work too.

For future scalability, updating the Views column on every page view will not work. Delayed update, batch aggregate count update, vertically partition the Users table and take out the Views column are viable alternatives.

Hermia

Sorry, I forgot to accept your answer. Yes, I'm going to be updating the password storing system. I will update my question with .XDL info. – Dilapidate

Thank you so much for such a detailed answer! I never came across indexing any other columns different from primary keys. What do I need to do to add the (Name, Password) index? I found the indexes/keys window in the designer of the table. It has a PK_Users key, and I can add IX_Users key for username, but how can I associate it with the password? Thank you again. – Dilapidate

@Dilapidate - Agreed on the quality of the answer. The answer is at the top of Remus's post CREATE INDEX idxUsersNamePassword on Users(Name,Password); And you might find this link useful as a starting point for identifying other potential missing indexes to investigate blogs.msdn.com/b/bartd/archive/2007/07/19/… – Zymolysis

Unbelievable! I just registered Name column as a IX unique index, and it... WORKS! No more deadlocks, not a single one. I don't know how to express how happy I am. Thank you guys. I feel like I also need to contribute and help someone else here. It's so great that there are people willing to help for free, because they just want to help. – Dilapidate

Answer 2

Your issue has a lot of parallels with that here Diagnosing Deadlocks in SQL Server 2005

(Linq to SQL, Read Only transaction being deadlocked by a Read Write transaction)

If you are on SQL2005 or later perhaps setting up snapshot isolation as discussed on that thread will do the job. Otherwise please update your post with details of the version you are using.

Zymolysis

Answer 3

In this situation (i.e. the type of data you're reading and the nature of the updates occurring on that data) I would run the user lookup query at read uncommitted isolation.

Alternatively, a more involved change. From the description you've posted, I would consider not maintaining the views count on the user record. I would instead record ViewCount against the Article, then dervive the total views for a user from the sum of Articles.ViewCount by AuthorID.

Sikorsky