Deadlock on SQL2K

Question

Deadlock on SQL2K

Vinay Kirtane

SSC Enthusiast

Points: 143
More actions
June 2, 2006 at 4:14 am

#115441

We have 2 SQL instances (say SQL1 and SQL2) installed on a production server. SQL1 is defined as a linked server for instance SQL2. There is only one cyclic job that runs on instance SQL2 which uses openquery stmts to extract data from instance SQL1 and dumps in a DB on SQL2. We are having SQL deadlocks for the cyclic jobs once every week (atleast). To get more info about the deadlocks, we used trace flag1204. I am trying to debug the Trace Log. How do I find out what is the cause from the log below........
Do I need to use anyother trace flag (other than 1204 and 3605) to get more info about the other process causing blocking/deadlocks....
The SQL Log for SQL instance1 (SQL1) does not show any activity around the deadlock time. The stored procedure usp_import_from_SQL1 calls series of different sp's to import data from SQL1 so the line number #18 may not be from the same sp......
:TraceLog from SQL2
2006-05-31 18:17:08.73 backup Database backed up: Database: ImportDB, creation date(time): 2006/04/27(20:43:43), pa
2006-06-01 03:39:20.59 spid4 ...
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Waiting for parallel threads to enlist.
2006-06-01 03:39:20.60 spid4 Node:2
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 7, EC = 0x3fd66098, SPID: 59, ECID: 3, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 6, EC = 0x3ccd4098, SPID: 59, ECID: 6, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 5, EC = 0x488a8098, SPID: 59, ECID: 8, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 4, EC = 0x3c934098, SPID: 59, ECID: 2, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 3, EC = 0x3d298098, SPID: 59, ECID: 1, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 2, EC = 0x3f9d0098, SPID: 59, ECID: 5, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 1, EC = 0x38b24098, SPID: 59, ECID: 4, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 0, EC = 0x3e7e2098, SPID: 59, ECID: 7, Blocking
2006-06-01 03:39:20.60 spid4 Producer List::
2006-06-01 03:39:20.60 spid4 Consumer List::
2006-06-01 03:39:20.60 spid4 Coordinator: EC = 0x3db537b0, SPID: 59, ECID: 0, Not Blocking
2006-06-01 03:39:20.60 spid4 Input Buf: RPC Event: usp_import_from_SQL1;1
2006-06-01 03:39:20.60 spid4 SPID: 59 ECID: 0 Statement Type: INSERT Line #: 18
2006-06-01 03:39:20.60 spid4 Port: 0x19d30300 Xid Slot: -1, EC: 0x3db537b0, ECID: 0 (Coordinator), Exchange
2006-06-01 03:39:20.60 spid4 Node:1
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Wait-for graph
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 ResTypeageSupp Stype:'OR' SPID:59 ECID:7 Ec0x3E7E2098) Value:0x3e7e2098 Cos
2006-06-01 03:39:20.60 spid4 Victim Resource Owner:
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 7, EC = 0x3fd66098, SPID: 59, ECID: 3, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 6, EC = 0x3ccd4098, SPID: 59, ECID: 6, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 5, EC = 0x488a8098, SPID: 59, ECID: 8, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 4, EC = 0x3c934098, SPID: 59, ECID: 2, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 3, EC = 0x3d298098, SPID: 59, ECID: 1, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 2, EC = 0x3f9d0098, SPID: 59, ECID: 5, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 1, EC = 0x38b24098, SPID: 59, ECID: 4, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 0, EC = 0x3e7e2098, SPID: 59, ECID: 7, Blocking
2006-06-01 03:39:20.60 spid4 Producer List::
2006-06-01 03:39:20.60 spid4 Consumer List::
2006-06-01 03:39:20.60 spid4 Coordinator: EC = 0x3db537b0, SPID: 59, ECID: 0, Not Blocking
2006-06-01 03:39:20.60 spid4 Port: 0x19d30300 Xid Slot: -1, EC: 0x3db537b0, ECID: 0 (Coordinator), Exchange
2006-06-01 03:39:20.60 spid4 Node:1
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 -- next branch --
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Waiting for parallel threads to enlist.
2006-06-01 03:39:20.60 spid4 Node:17
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 7, EC = 0x3fd66098, SPID: 59, ECID: 3, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 6, EC = 0x3ccd4098, SPID: 59, ECID: 6, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 5, EC = 0x488a8098, SPID: 59, ECID: 8, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 4, EC = 0x3c934098, SPID: 59, ECID: 2, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 3, EC = 0x3d298098, SPID: 59, ECID: 1, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 2, EC = 0x3f9d0098, SPID: 59, ECID: 5, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 1, EC = 0x38b24098, SPID: 59, ECID: 4, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 0, EC = 0x3e7e2098, SPID: 59, ECID: 7, Blocking
2006-06-01 03:39:20.60 spid4 Producer List::
2006-06-01 03:39:20.60 spid4 Consumer List::
2006-06-01 03:39:20.60 spid4 Coordinator: EC = 0x3db537b0, SPID: 59, ECID: 0, Not Blocking
2006-06-01 03:39:20.60 spid4 Port: 0x19d30300 Xid Slot: -1, EC: 0x3db537b0, ECID: 0 (Coordinator), Exchange
2006-06-01 03:39:20.60 spid4 Node:1
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 -- next branch --
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Waiting for parallel threads to enlist.
2006-06-01 03:39:20.60 spid4 Node:14
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 7, EC = 0x3fd66098, SPID: 59, ECID: 3, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 6, EC = 0x3ccd4098, SPID: 59, ECID: 6, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 5, EC = 0x488a8098, SPID: 59, ECID: 8, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 4, EC = 0x3c934098, SPID: 59, ECID: 2, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 3, EC = 0x3d298098, SPID: 59, ECID: 1, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 2, EC = 0x3f9d0098, SPID: 59, ECID: 5, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 1, EC = 0x38b24098, SPID: 59, ECID: 4, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 0, EC = 0x3e7e2098, SPID: 59, ECID: 7, Blocking
2006-06-01 03:39:20.60 spid4 Producer List::
2006-06-01 03:39:20.60 spid4 Consumer List::
2006-06-01 03:39:20.60 spid4 Coordinator: EC = 0x3db537b0, SPID: 59, ECID: 0, Not Blocking
2006-06-01 03:39:20.60 spid4 Port: 0x19d30300 Xid Slot: -1, EC: 0x3db537b0, ECID: 0 (Coordinator), Exchange
2006-06-01 03:39:20.60 spid4 Node:1
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 -- next branch --
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Waiting for parallel threads to enlist.
2006-06-01 03:39:20.60 spid4 Node:11
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 7, EC = 0x3fd66098, SPID: 59, ECID: 3, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 6, EC = 0x3ccd4098, SPID: 59, ECID: 6, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 5, EC = 0x488a8098, SPID: 59, ECID: 8, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 4, EC = 0x3c934098, SPID: 59, ECID: 2, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 3, EC = 0x3d298098, SPID: 59, ECID: 1, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 2, EC = 0x3f9d0098, SPID: 59, ECID: 5, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 1, EC = 0x38b24098, SPID: 59, ECID: 4, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 0, EC = 0x3e7e2098, SPID: 59, ECID: 7, Blocking
2006-06-01 03:39:20.60 spid4 Producer List::
2006-06-01 03:39:20.60 spid4 Consumer List::
2006-06-01 03:39:20.60 spid4 Coordinator: EC = 0x3db537b0, SPID: 59, ECID: 0, Not Blocking
2006-06-01 03:39:20.60 spid4 Port: 0x19d30300 Xid Slot: -1, EC: 0x3db537b0, ECID: 0 (Coordinator), Exchange
2006-06-01 03:39:20.60 spid4 Node:1
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 -- next branch --
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Waiting for parallel threads to enlist.
2006-06-01 03:39:20.60 spid4 Node:8
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 7, EC = 0x3fd66098, SPID: 59, ECID: 3, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 6, EC = 0x3ccd4098, SPID: 59, ECID: 6, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 5, EC = 0x488a8098, SPID: 59, ECID: 8, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 4, EC = 0x3c934098, SPID: 59, ECID: 2, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 3, EC = 0x3d298098, SPID: 59, ECID: 1, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 2, EC = 0x3f9d0098, SPID: 59, ECID: 5, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 1, EC = 0x38b24098, SPID: 59, ECID: 4, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 0, EC = 0x3e7e2098, SPID: 59, ECID: 7, Blocking
2006-06-01 03:39:20.60 spid4 Producer List::
2006-06-01 03:39:20.60 spid4 Consumer List::
2006-06-01 03:39:20.60 spid4 Coordinator: EC = 0x3db537b0, SPID: 59, ECID: 0, Not Blocking
2006-06-01 03:39:20.60 spid4 Port: 0x19d30300 Xid Slot: -1, EC: 0x3db537b0, ECID: 0 (Coordinator), Exchange
2006-06-01 03:39:20.60 spid4 Node:1
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 -- next branch --
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Waiting for parallel threads to enlist.
2006-06-01 03:39:20.60 spid4 Node:5
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 7, EC = 0x3fd66098, SPID: 59, ECID: 3, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 6, EC = 0x3ccd4098, SPID: 59, ECID: 6, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 5, EC = 0x488a8098, SPID: 59, ECID: 8, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 4, EC = 0x3c934098, SPID: 59, ECID: 2, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 3, EC = 0x3d298098, SPID: 59, ECID: 1, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 2, EC = 0x3f9d0098, SPID: 59, ECID: 5, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 1, EC = 0x38b24098, SPID: 59, ECID: 4, Blocking
2006-06-01 03:39:20.60 spid4 Producer: Xid Slot: 0, EC = 0x3e7e2098, SPID: 59, ECID: 7, Blocking
2006-06-01 03:39:20.60 spid4 Producer List::
2006-06-01 03:39:20.60 spid4 Consumer List::
2006-06-01 03:39:20.60 spid4 Coordinator: EC = 0x3db537b0, SPID: 59, ECID: 0, Not Blocking
2006-06-01 03:39:20.60 spid4 Port: 0x19d30300 Xid Slot: -1, EC: 0x3db537b0, ECID: 0 (Coordinator), Exchange
2006-06-01 03:39:20.60 spid4 Node:1
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4
2006-06-01 03:39:20.60 spid4 -- next branch --
2006-06-01 06:44:32.70 backup Database backed up: Database: ImportDB, creation date(time): 2006/04/27(20:43:43), pa

Any help on this is greatly appreciated.........

Thanks,
VK

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Looks like you have deadlocking caused by parallelism.

I'd suggest you have a look around for parallelism and deadlock. One possible solution might be to use the maxdop=1 query hint.

Karl
source control for SQL Server

Vinay Kirtane SSC Enthusiast Points: 143 More actions · Answer 2

Thanks for your reply....

Not sure how SQL determines degree of parallelism for query execution but If it;s parallelism on it's own then should it not be a daily occurance?

SQLZ SSChampion Points: 12872 More actions · Answer 3

To be honest I haven't experienced deadlocking caused by parallelism before so I cannot provide more details.

I suspect though that just like normal deadlocking, it will depend on a variety of conditions being met at the same time for it to occur.

There are a couple of msdn articles out there on parallelism and deadlocking. Do a search for "Deadlocks involving parllelism".

Karl
source control for SQL Server