The select query is the victim!
Ok..let me put it down much more clearer of what I am dealing with. I know am dealing with a screwed set of databases but i didnt have such problems earlier.
I have Database A- This has 5-6 tables that contain raw data from text files. These tables did not have any kind of keys or constraints set. Its just got identity fields and data is refreshed in these tables on a daily basis.
I have a second database B- This has tables designed with proper keys and indices. There are about 8 tables in it.
Now- There is a requirement where you move fresh data from Database A to Database B on a daily basis. SAy Table 1 in Database A has the core data and it needs to join with Table 2, 3 and 4 from the same Database A to derive 6-7 fields (look up tables).
There is also a need to look up few tables in Database B to derive 3-4 more fields. So ultimately its a select query from table 1 of Database A, having joins with Table 2,3 and 4 from same DB and few more joins with the tables in Database B.
Output of this Select query is fed into another table in Database A itself before moving the same to Database B (as next step)
The select query that loads the intermediate table is now failing as the victim in the deadlock.
I also face a deadlock issue in a third database C. This database C, is nothing but a copy of Database B. There are no processes running on Database C other than business object reports.
Database A and Database B would have insert and update statements running in parallel.
If the deadlock was caused by an update or delete, shouldnt it be caught in the deadlock graph from the trace component of lock using profiler?
I suspect I am missing here.