I ran into a deadlock issue "synchronizing" a table multiple times in a short period of time. By synchronize I mean doing the following:
- Insert data to be synchronized into a temp table
- Update existing records in destination table
- Insert new records into the destination table
- Delete records that are not in the synch table under certain circumstances
- Drop temp table
For the INSERT and DELETE statements, I'm using a LEFT JOIN similar to:
INSERT INTO destination_table (fk1, fk2, val1)
FROM #tmp
LEFT JOIN destination_table dt ON dt.fk1 = #tmp.fk1
AND dt.fk2 = #temp.fk2
WHERE dt.pk IS NULL
;
The deadlock graph is reporting the destination_table's primary key is under an exclusive lock. I assume the above query is causing a table or page lock instead of a row lock. How would I confirm that?
I could rewrite the above query with an IN, EXIST, or EXCEPT command. Are there any additional ways of refactoring the code? Will refactoring using any of these commands avoid the deadlock issue? Which one would be the best? I'm assuming EXCEPT.
Aucun commentaire:
Enregistrer un commentaire