There is a 3rd party tool at my company that is causing a lot of deadlocks after an upgrade.
The main problem for me is that it fills the SQL error logs with error messages. This was happening 20 times a day.
It happens when two of these SQL update statements run simultaneously, and one of them gets killed.
set user_id=@P0, directory_id=@P1, attribute_name=@P2, attribute_value=@P3, attribute_lower_value=@P4
Here is the problem. Since this statement updates the user_id (maybe unnecessarily), SQL has to make sure the foreign key constraint is still valid, by checking the table dbo.cwd_user. But this causes deadlocks in some situations. Although I rarely recommend this, removing this foreign key would remove the deadlocks.
I’m guessing since it’s a third party tool, the SQL can’t be changed. But if it can, I would recommend not updating user_id, since that is the logical key of the table, and is not changing anyway. I see this kind of thing a lot with the ORMs.
Here is the drop foreign key command:
ALTER TABLE dbo.cwd_user_attribute
DROP CONSTRAINT fk_user_attribute_id_user_id
Here is the deadlock graph: