May 6, 2003 at 8:37 am
Anyone any ideas?
I'm having a problem running a query, that in turn spawns several sub processes from the main thread. This problem only actually occurs when SQL Server is set to use both processors on a dual processor machine to execute the query, but OPTION (MAXDOP 1) is not an option for us, as we are querying a table with potentially millions of records.
We want to be able to make use of the multi processors on the machine.
The error that we are receiving is as follows:
"Server: Msg 1205, Level 13, State 2, Line 1
Transaction (Process ID 52) was deadlocked on thread | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
"
Looking at the SQL Server log we noticed the following:
2003-05-02 14:32:05.64 spid4-- next branch --
2003-05-02 14:32:05.64 spid4
2003-05-02 14:32:05.64 spid4Producer: Xid Slot: 2, EC = 0x4b172090, SPID: 60, ECID: 9, Blocking
2003-05-02 14:32:05.64 spid4Producer: Xid Slot: 1, EC = 0x4b112090, SPID: 60, ECID: 8, Blocking
2003-05-02 14:32:05.64 spid4Producer List::
2003-05-02 14:32:05.64 spid4Consumer: Xid Slot: 3, EC = 0x4b12a090, SPID: 60, ECID: 6, Not Blocking
2003-05-02 14:32:05.64 spid4Consumer: Xid Slot: 0, EC = 0x4b15c090, SPID: 60, ECID: 7, Not Blocking
2003-05-02 14:32:05.64 spid4Consumer List::
2003-05-02 14:32:05.64 spid4Coordinator: EC = 0x4acd14f8, SPID: 60, ECID: 0, Not Blocking
2003-05-02 14:32:05.64 spid4Port: 0x42be6200 Xid Slot: 3, EC: 0x4b12a090, ECID: 6 (Consumer), Exchange Wai
2003-05-02 14:32:05.64 spid4Node:6
2003-05-02 14:32:05.64 spid4
2003-05-02 14:32:05.64 spid4Producer: Xid Slot: 2, EC = 0x4b172090, SPID: 60, ECID: 9, Not Blocking
2003-05-02 14:32:05.64 spid4Producer: Xid Slot: 1, EC = 0x4b112090, SPID: 60, ECID: 8, Not Blocking
2003-05-02 14:32:05.64 spid4Producer List::
2003-05-02 14:32:05.64 spid4Consumer: Xid Slot: 3, EC = 0x4b12a090, SPID: 60, ECID: 6, Blocking
2003-05-02 14:32:05.64 spid4Consumer: Xid Slot: 0, EC = 0x4b15c090, SPID: 60, ECID: 7, Blocking
2003-05-02 14:32:05.64 spid4Consumer List::
2003-05-02 14:32:05.64 spid4Coordinator: EC = 0x4acd14f8, SPID: 60, ECID: 0, Not Blocking
2003-05-02 14:32:05.64 spid4Port: 0x42be6200 Xid Slot: 1, EC: 0x4b112090, ECID: 8 (Producer), Exchange Wai
2003-05-02 14:32:05.64 spid4Node:3
2003-05-02 14:32:05.64 spid4
2003-05-02 14:32:05.64 spid4
2003-05-02 14:32:05.64 spid4-- next branch --
2003-05-02 14:32:05.64 spid4
2003-05-02 14:32:05.64 spid4Producer: Xid Slot: 2, EC = 0x4b172090, SPID: 60, ECID: 9, Not Blocking
2003-05-02 14:32:05.64 spid4Producer: Xid Slot: 1, EC = 0x4b112090, SPID: 60, ECID: 8, Not Blocking
2003-05-02 14:32:05.64 spid4Producer List::
2003-05-02 14:32:05.64 spid4Consumer: Xid Slot: 3, EC = 0x4b12a090, SPID: 60, ECID: 6, Blocking
2003-05-02 14:32:05.64 spid4Consumer: Xid Slot: 0, EC = 0x4b15c090, SPID: 60, ECID: 7, Blocking
2003-05-02 14:32:05.64 spid4Consumer List::
2003-05-02 14:32:05.64 spid4Coordinator: EC = 0x4acd14f8, SPID: 60, ECID: 0, Not Blocking
2003-05-02 14:32:05.64 spid4Port: 0x42be6200 Xid Slot: 1, EC: 0x4b112090, ECID: 8 (Producer), Exchange Wai
2003-05-02 14:32:05.64 spid4Node:3
Although we realise that SPID: 60 is the main process id and ECID is the sub-process id there is no indication of which resources are being locked.
We've no idea where to go from here. Any suggestions would be appreciated?
May 9, 2003 at 8:00 am
This was removed by the editor as SPAM
May 12, 2003 at 9:36 am
I really don't know if it is possible....
but if it is a read only query try Set transaction level read uncommited....
Won't solve the paralel query problem but will probaly solve blocking ?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply