sql server - 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

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.