Results 1 to 3 of 3

Thread: Deadlock

  1. 08-15-2003, 08:49 PM #1
    ndba
    ndba is offline Registered User
    Join Date
    Sep 2002
    Posts
    78

    Deadlock

    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)
    Reply With Quote Reply With Quote
  2. 08-16-2003, 09:12 AM #2
    skhanal
    skhanal is offline Experts
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Check what other query is doing,

    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',


    See if it is optimized. This will provide you a clue.
    Reply With Quote Reply With Quote
  3. 08-19-2003, 08:52 AM #3
    ndba
    ndba is offline Registered User
    Join Date
    Sep 2002
    Posts
    78

    problem fixed!

    Hi
    I fixed the problem by creating a new non clustered index on 2 columns on one of the tables and now there seems to be on problems.

    ndba
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules