deadlock issue

How SQL server 2000 tracks the deadlocks - per PID or per connection? I started to have multiple deadlocks, made the trace and logged the deadlock events in the error file, so I know where I'm now but...

Here's the situation:

The app starts and logs as PID.Then I could see multiple process running with the same PID. One of this process select and updates records in the staging table. The second process takes the same records loaded in the bus and process them with specific logic. Then it starts from the beginning. When I have 4-5 of this PIDs with several processes inside each and they are all running against the same tables I get huge amount deadlocks. If I run the same application on Oracle I could see each of these processes with separate connection and I do not have deadlocks. My colleagues is running this on Oracle with no problems. My question is how the SQL server handles these connections differently?
One more complication - if instead of a staging table I use a flat file which the application reads, then there's no deadlocks on SQL server - why reading a tables is different and why it causes contention? I also tried to create the same staging table in separate database - in this case I have much less deadlocks - like only 10%.
Can somebody help me to try to explain and possible to troubleshoot this?
Thanks a lot, mj

I am not sure what you mean by PID. Are you talking about SPID or the process id? That reflects a connection. Deadlocks can happen even within the same connection / statement. It is best to think of deadlocks happening between multiple tasks or transactions. Based on your description it is really hard to understand the type of operations you are doing to result in deadlock, number of tables involved, schema, indexes etc. You could post the deadlock trace output so we can try to figure out why the deadlock happens or the lock resource for example. Please check out the link below for some troubleshooting tips for SQL Server 2000. There is similar link for SQL Server 2005 or you can look it up in the Books Online.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp