question

Wilfred van Dijk avatar image
Wilfred van Dijk asked

do foreign keys increase a deadlock situation?

I am currently investigating a deadlock issue at a customer. The database is seems to be well designed (integers as clustered primary keys, foreign keys are defined and trusted). Application is used at several locations. I have a deadlock on 2 tables, rentals and vehicles. [rentals] has a foreign key to the primary key on [vehicles], which is a clustered index. If an update happens on both tables at the same time, you'll get the deadlock. I was wondering: if multiple tables have multiple FK, the change on a deadlock may increase, because of the FK check/read which as to be done. Am I right? And if so, any solution for this?
t-sqldeadlocklockingforeign-key
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
It sounds like the code must be accessing the tables out of order for the deadlocks. I have not seen a substantial increase in the likelihood of a deadlock caused just by the foreign key constraint and its checks. It's almost always because of an additional lock coming from a trigger or because one process updates the other table or reads from it or something, prior to just running the necessary updates. In general, no, I wouldn't say that that just the foreign key, all by itself, increases the chances of deadlocks.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.