SQL Server deadlock between two update statements

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.

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.