Hello

my DTS package deadlocks when I execute multiple instances. The following error is reported in the SQL error log and I do not see how this constitutes a deadlock. Any help in interpreting this information is appreciated

2004-05-04 20:19:57.91 spid4 Deadlock encountered .... Printing deadlock informatio
2004-05-04 20:19:57.91 spid4
2004-05-04 20:19:57.91 spid4 Wait-for grap
2004-05-04 20:19:57.93 spid4
2004-05-04 20:19:57.93 spid4 Node:
2004-05-04 20:19:57.93 spid4 KEY: 41:1366295927:1 (e3009cc68859) CleanCnt:1 Mode: X Flags: 0x
2004-05-04 20:19:57.93 spid4 Grant List 1:
2004-05-04 20:19:57.93 spid4 Owner:0x5360dde0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:332 ECID:
2004-05-04 20:19:57.93 spid4 SPID: 332 ECID: 0 Statement Type: BULK INSERT Line #:
2004-05-04 20:19:57.93 spid4 Input Buf: No Event:
2004-05-04 20:19:57.93 spid4 Requested By:
2004-05-04 20:19:57.93 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:150 ECID:0 Ec0x2DB515B0) Value:0x432ad20 Cost0/180C
2004-05-04 20:19:57.93 spid4
2004-05-04 20:19:57.93 spid4 Node:
2004-05-04 20:19:57.93 spid4 KEY: 41:1366295927:1 (db00d216176c) CleanCnt:1 Mode: X Flags: 0x
2004-05-04 20:19:57.93 spid4 Grant List 3:
2004-05-04 20:19:57.93 spid4 Owner:0x78c15fa0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:150 ECID:
2004-05-04 20:19:57.93 spid4 SPID: 150 ECID: 0 Statement Type: BULK INSERT Line #:
2004-05-04 20:19:57.93 spid4 Input Buf: No Event:
2004-05-04 20:19:57.93 spid4 Requested By:
2004-05-04 20:19:57.93 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:332 ECID:0 Ec0x754495B0) Value:0x5a35a580 Cost0/FBC4
2004-05-04 20:19:57.93 spid4 Victim Resource Owner
2004-05-04 20:19:57.93 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:150 ECID:0 Ec0x2DB515B0) Value:0x432ad20 Cost0/180C

What are the conflicting code lines? You can play with the statement
sequence to make tables accessed in the same sequence in both lines of code
if possible. Or you can elevate TRANSACTION ISOLATION LEVEL. Or you can
trap the error and re-execute the code.

"Michael" <anonymous (AT) discussions (DOT) microsoft.com> wrote

a deadlock. Any help in interpreting this information is appreciated.

It deadlocks during a datapump operation. Both instances are executing a bulk insert. The DTS package is copying data into an Archive Database that is currently not being accesed by any other app. To move more data in less time I am trying to run the same package multiple times simultaneously. The only operations on the Archive databases are the bulk inserts. Therefore I would understand if I saw blocking, but I do not see how this can result in a deadlock.

I am not clear on whether you use datapump or bulk insert or both. Are you
using any of lookups in datapump?

"Michael" <anonymous (AT) discussions (DOT) microsoft.com> wrote

is currently not being accesed by any other app. To move more data in less
time I am trying to run the same package multiple times simultaneously. The
only operations on the Archive databases are the bulk inserts. Therefore I
would understand if I saw blocking, but I do not see how this can result in
a deadlock.

It is a datapump task with the "Use Fast Load" option checked which results in a bulk insert. No lookups are being performed.

If your table(s) had primary - foreign key relations with other tables that
could produce a deadlock. Other then that... You can try changing DTS Pump
to an insert statement with Execute SQL task or ActiveX if possible.
Elevating isolation level would defeat the purpose of multi treading.

Ilya

"Michael" <anonymous (AT) discussions (DOT) microsoft.com> wrote