September 17, 2015 at 12:01 pm
The other day developer asked me to create a new table that had a FK constraint to an existing largish table on the largish table's primary key.
When i ran the script, it took it about 4 seconds to complete, and in the meantime i had 3 deadlocks show up from other processes attempting to use the table the FK was being created from. The new table was empty at this point.
I've never seen this behavior before so I'm curious if creating a new FK constraint will cause a table lock on the source table for the FK.
September 21, 2015 at 10:53 am
Manic Star (9/17/2015)
The other day developer asked me to create a new table that had a FK constraint to an existing largish table on the largish table's primary key.When i ran the script, it took it about 4 seconds to complete, and in the meantime i had 3 deadlocks show up from other processes attempting to use the table the FK was being created from. The new table was empty at this point.
I've never seen this behavior before so I'm curious if creating a new FK constraint will cause a table lock on the source table for the FK.
In high transaction environments, if the referenced PK is clustered, it is usually wise to create a non-clustered unique constraint/index on the same columns to avoid deadlocks caused by FKs.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply