I am receiving a lot of deadlocks in my big web application.
http://stackoverflow.com/questions/2941233/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.
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
