Hi:

I have a situation.
I am running a query as follows:

Select * from table 1
where not exists (select * from table 2
where join condition)

There are 50,000 rows in table 1 and 10,000 in table 2.

This query has been working fine for the past 6 months and now all of a sudden when I run it i get the message like below:

Transaction (process id 55) deadlocked by another process...
also in the message there is buffer thread mentioned. when i run the same query on my test database it works fine. I also started a deadlock trace by dbcc traceon(1204) i did get some information in the errorlog. I may have run the query more than once. I have pasted all the errorlog output below:
I need to know how I can resolve this deadlock. Also, there is tempdb.dbo.lockinfo54 showing in my locls/objects in enterprise manager. What does that mean ?


Node:1
2003-08-15 18:41:44.14 spid3 Port: 0x42be8280 Xid Slot: 0, EC: 0x2ec05518, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2003-08-15 18:41:44.14 spid3 SPID: 55 ECID: 0 Statement Type: SELECT Line #: 1
2003-08-15 18:41:44.14 spid3 Input Buf: Language Event: SELECT D.ADMCode,
RTRIM(CoCode) as 'CoCode',
RTRIM(EmpNum) as 'EmpNum',
RTRIM(EmpName) as 'EmpName',
RTRIM(Emp_Address1) as 'Emp_Address1',
RTRIM(Emp_Address2) as 'Emp_Address2',
RTRIM(Emp_City) as 'Emp_City',
2003-08-15 18:41:44.14 spid3 Coordinator: EC = 0x2ec05518, SPID: 55, ECID: 0, Not Blocking
2003-08-15 18:41:44.14 spid3 Consumer List::
2003-08-15 18:41:44.14 spid3 Consumer: Xid Slot: 0, EC = 0x2ec05518, SPID: 55, ECID: 0, Not Blocking
2003-08-15 18:41:44.14 spid3 Producer List::
2003-08-15 18:41:44.14 spid3 Producer: Xid Slot: 1, EC = 0x549c6098, SPID: 55, ECID: 2, Blocking
2003-08-15 18:41:44.14 spid3 Producer: Xid Slot: 2, EC = 0x3fad0098, SPID: 55, ECID: 1, Blocking
2003-08-15 18:41:44.14 spid3
2003-08-15 18:41:44.14 spid3 Node:2
2003-08-15 18:41:44.14 spid3 Port: 0x42be8580 Xid Slot: 1, EC: 0x549c6098, ECID: 2 (Consumer), Exchange Wait Type :e_etypeCXPacket
2003-08-15 18:41:44.14 spid3 Coordinator: EC = 0x2ec05518, SPID: 55, ECID: 0, Not Blocking
2003-08-15 18:41:44.14 spid3 Consumer List::
2003-08-15 18:41:44.14 spid3 Consumer: Xid Slot: 0, EC = 0x3fad0098, SPID: 55, ECID: 1, Not Blocking
2003-08-15 18:41:44.14 spid3 Consumer: Xid Slot: 1, EC = 0x549c6098, SPID: 55, ECID: 2, Not Blocking
2003-08-15 18:41:44.14 spid3 Producer List::
2003-08-15 18:41:44.14 spid3 Producer: Xid Slot: 2, EC = 0x3faec098, SPID: 55, ECID: 6, Blocking
2003-08-15 18:41:44.14 spid3 Producer: Xid Slot: 3, EC = 0x3fac6098, SPID: 55, ECID: 5, Blocking
2003-08-15 18:41:44.14 spid3
2003-08-15 18:41:44.14 spid3 Node:3
2003-08-15 18:41:44.14 spid3 Waiting for parallel threads to enlist.
2003-08-15 18:41:44.14 spid3
2003-08-15 18:41:44.14 spid3 -- next branch --
2003-08-15 18:41:44.14 spid3
2003-08-15 18:41:44.14 spid3
2003-08-15 18:41:44.14 spid3 Node:2
2003-08-15 18:41:44.14 spid3 Port: 0x42be8580 Xid Slot: 1, EC: 0x549c6098, ECID: 2 (Consumer), Exchange Wait Type :e_etypeCXPacket
2003-08-15 18:41:44.14 spid3 Coordinator: EC = 0x2ec05518, SPID: 55, ECID: 0, Not Blocking
2003-08-15 18:41:44.14 spid3 Consumer List::
2003-08-15 18:41:44.14 spid3 Consumer: Xid Slot: 0, EC = 0x3fad0098, SPID: 55, ECID: 1, Not Blocking
2003-08-15 18:41:44.14 spid3 Consumer: Xid Slot: 1, EC = 0x549c6098, SPID: 55, ECID: 2, Not Blocking
2003-08-15 18:41:44.14 spid3 Producer List::
2003-08-15 18:41:44.14 spid3 Producer: Xid Slot: 2, EC = 0x3faec098, SPID: 55, ECID: 6, Blocking
2003-08-15 18:41:44.14 spid3 Producer: Xid Slot: 3, EC = 0x3fac6098, SPID: 55, ECID: 5, Blocking
2003-08-15 18:41:44.14 spid3
2003-08-15 18:41:44.14 spid3 Node:6
2003-08-15 18:41:44.14 spid3 Port: 0x42be8580 Xid Slot: 3, EC: 0x3fac6098, ECID: 5 (Producer), Exchange Wait Type :e_etypeCXPacket
2003-08-15 18:41:44.14 spid3 Coordinator: EC = 0x2ec05518, SPID: 55, ECID: 0, Not Blocking
2003-08-15 18:41:44.14 spid3 Consumer List::
2003-08-15 18:41:44.14 spid3 Consumer: Xid Slot: 0, EC = 0x3fad0098, SPID: 55, ECID: 1, Blocking
2003-08-15 18:41:44.14 spid3 Consumer: Xid Slot: 1, EC = 0x549c6098, SPID: 55, ECID: 2, Blocking
2003-08-15 18:41:44.14 spid3 Producer List::
2003-08-15 18:41:44.14 spid3 Producer: Xid Slot: 2, EC = 0x3faec098, SPID: 55, ECID: 6, Not Blocking
2003-08-15 18:41:44.14 spid3 Producer: Xid Slot: 3, EC = 0x3fac6098, SPID: 55, ECID: 5, Not Blocking
2003-08-15 18:41:44.14 spid3
2003-08-15 18:41:44.14 spid3 Node:7
2003-08-15 18:41:44.14 spid3 Port: 0x42be8580 Xid Slot: 0, EC: 0x3fad0098, ECID: 1 (Consumer), Exchange Wait Type :e_etypeCXPacket
2003-08-15 18:41:44.14 spid3 Coordinator: EC = 0x2ec05518, SPID: 55, ECID: 0, Not Blocking
2003-08-15 18:41:44.14 spid3 Consumer List::
2003-08-15 18:41:44.14 spid3 Consumer: Xid Slot: 0, EC = 0x3fad0098, SPID: 55, ECID: 1, Not Blocking
2003-08-15 18:41:44.14 spid3 Consumer: Xid Slot: 1, EC = 0x549c6098, SPID: 55, ECID: 2, Not Blocking
2003-08-15 18:41:44.14 spid3 Producer List::
2003-08-15 18:41:44.14 spid3 Producer: Xid Slot: 2, EC = 0x3faec098, SPID: 55, ECID: 6, Blocking
2003-08-15 18:41:44.14 spid3 Producer: Xid Slot: 3, EC = 0x3fac6098, SPID: 55, ECID: 5, Blocking
2003-08-15 18:41:44.15 spid3
2003-08-15 18:41:44.15 spid3

Node:1
2003-08-15 18:41:44.15 spid3 Port: 0x42be8280 Xid Slot: 0, EC: 0x2ec05518, ECID: 0 (Coordinator), Exchange Wait Type :e_etypeCXPacket
2003-08-15 18:41:44.15 spid3 Coordinator: EC = 0x2ec05518, SPID: 55, ECID: 0, Not Blocking
2003-08-15 18:41:44.15 spid3 Consumer List::
2003-08-15 18:41:44.15 spid3 Consumer: Xid Slot: 0, EC = 0x2ec05518, SPID: 55, ECID: 0, Not Blocking
2003-08-15 18:41:44.15 spid3 Producer List::
2003-08-15 18:41:44.15 spid3 Producer: Xid Slot: 1, EC = 0x549c6098, SPID: 55, ECID: 2, Blocking
2003-08-15 18:41:44.15 spid3 Producer: Xid Slot: 2, EC = 0x3fad0098, SPID: 55, ECID: 1, Blocking
2003-08-15 18:41:44.15 spid3 Victim Resource Owner:
2003-08-15 18:41:44.15 spid3 ResType:PageSupp Stype:'OR' SPID:55 ECID:6 Ec0x3FAEC098)