Crowd deadlock

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.

update cwd_user_attribute
set user_id=@P0, directory_id=@P1, attribute_name=@P2, attribute_value=@P3, attribute_lower_value=@P4
where id=@P5

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:

Image

Advertisements
Aside | This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s