SQL Server locked between two update operations

Question

I have a website that has a very popular forum, and sometimes during the day I see several deadlocks occurring between two identical (minus the data inside them) update statements on the same forum. I'm not quite sure why this happens on this request, as there are many other requests on the site that work with high concurrency without any problems.

Full image

The request between the two processes is almost identical, the graph shows this as:

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

Can anyone shed light on what might be causing this?

Answer 1

This is because ForumThreads has a foreign key that generates S-lock when setting LatestLocalThreadID (to make sure the string still exists when the statement completes). A possible solution would be to prefix the update statement with

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

to fix it. You can also try UPDLOCK as a less aggressive mode. This may, of course, cause deadlocks in other places, but this is the best first attempt.

Answer 2

In general, locks are prevented by accessing objects (tables, pages, rows) always in the same order. In your example, there is one process that refers to forum first and forumThread second, and the other is the other way around. update usually searches first for rows to update and uses S-locks during the search. The lines that he defined for the change are blocked by X-locks, and then the actual change occurs.

Quick and dirty decisions can be to make begin Tran and then lock objects in the correct order and perform an update, followed by a commit that will release the locks again. But this will lead to a decrease in the total volume of your site due to blocking locks.

It is best to identify two statements (you can edit your question and give us another when you find it) and a plan of implementation. It should be possible to rewrite transactions one way or another in order to access all objects in the same order - and prevent a deadlock.