I suspect there must be some kind of key violation with delete and insert going at same time..?
Broken Link
2010-06-24 18:28:58
A:
+1
A:
One possible scenario: one connection insert a row in Element, and that row refers to a row in DLevel, and that row in DLevel is being deleted by another connection. Your nolock hint does not apply to foreign keys.
AlexKuznetsov
2010-06-24 18:51:33
These statements are executed in separate connections. The rows (of the DLevel table) referenced by the insert and delete statements are always different as the DFileID is different between the two. If the nolock does not apply to foreign keys, then maybe the two statements are running into page locking conflicts. I am aware of turning off row and page locking, but is there any way to force row level locking?
Elan
2010-06-24 19:42:52
Removed join to DLevel table in the "insert into Element..." query. Still get the deadlock.
Elan
2010-06-25 15:10:28
+1
A:
You might try removing the Foreign Key to DLevel.DFileID from the Element table. Since you have a Primary Key on DLevel.DlevelID and you reference that as a Foreign key in Element, the DFileID foreign key is not really needed.
MikeTWebb
2010-06-25 14:57:22