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.