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:
- C waits on Page IX lock, is blocked by A's S lock
- B waits on Page S lock, is blocked by C's IX lock
- 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.
