SQL Server deadlock between two update statements

Question

I have a website that has a very popular forum on it and occasionally throughout the day I see several deadlocks happening between two identical (minus the data within them) update statements on the same forum. I'm not exactly sure why this is happening on this query as there are many other queries on the site that run with high concurrency without issue.

Full Image

The query between the two processes is nearly identical, the graph shows it as:

update [Forum] set [DateModified] = @DateModified, [LatestLocalThreadID] = @LatestLocalThreadID where ID = 310     

Can anyone shed any light on what could be causing this?

Answer 1

This is because there is a foreign key to ForumThreads that generates an S-lock when you set LatestLocalThreadID (to make sure that the row still exist when the statement completes). A possible fix would be to prefix the update statement with

SELECT *
FROM ForumThreads WITH (XLOCK, ROWLOCK, HOLDLOCK)
WHERE ID = @LatestLocalThreadID 

in order to X-lock on that. You can also try UPDLOCK as a less aggressive mode. This can of course cause deadlocks in other places, but it is the best first try.

Answer 2

Basically deadlocks are prevented by accessing the objects (tables, pages, rows) always In the same order. In your example there's one process accessing forum first and forumThread second and another thread doing it vice versa. An update usually searches first for the rows to update and uses S-locks during the search. The rows it has identified to be changed, are locked by X-locks and then the actual change happens.

The quick and dirty solutions might be to do a begin Tran then lock the objects in the order you need and do the update followed by the commit that will release the locks again. But this will bring down the overall thruput of your website because of blocking locks.

The better way is to identify the two statements (you might edit your question and give us the other one when you found it) and the execution plan of them. It should be possible to rewrite the transactions somehow to access all objects in the same order - and prevent the deadlock.