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?
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.
15 People are following this question.
