Deadlock using 'INSERT BULK'

Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
applied). The SqlBulkCopy class is used within a
System.Transactions.TransactionScope, so MSDTC is implicitely used as a
transactionmanager.
The problem is that a a deadlock occurs the command is issued, and I'm
having problems understanding why this deadlock happens. The -T1204
output is:
Deadlock encountered ... Printing deadlock information
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Wait-for graph
2006-11-22 15:23:56.78 spid3
2006-11-22 15:23:56.78 spid3 Node:1
2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
CleanCnt:2 Mode: Schema Flags: 0x0
2006-11-22 15:23:56.78 spid3 Grant List 0::
2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
UNKNOWN TOKEN Line #: 1
2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
[Field4] Decimal(28,13), [Field4] Bit)
2006-11-22 15:23:56.78 spid3 Requested By:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
The "TAB: 8:565577053" refers to the TableName specified in the Input
Buf.
This problem is reproducable (it happens with multiple tables).
Does anyone have a hint on how to tackle this problem?
Regards,
Tjibbe Chris
<tjibbechris@.gmail.com> wrote in message
news:1164206482.527864.15760@.e3g2000cwe.googlegrou ps.com...
> Using the new System.Data.SqlClient.SqlBulkCopy class in .Net Framework
> 2.0 I'm inserting some 100 rows into a SQL Server 2000 table (sp4
> applied). The SqlBulkCopy class is used within a
> System.Transactions.TransactionScope, so MSDTC is implicitely used as a
> transactionmanager.
> The problem is that a a deadlock occurs the command is issued, and I'm
> having problems understanding why this deadlock happens. The -T1204
> output is:
> Deadlock encountered ... Printing deadlock information
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Wait-for graph
> 2006-11-22 15:23:56.78 spid3
> 2006-11-22 15:23:56.78 spid3 Node:1
> 2006-11-22 15:23:56.78 spid3 TAB: 8:565577053 []
> CleanCnt:2 Mode: Schema Flags: 0x0
> 2006-11-22 15:23:56.78 spid3 Grant List 0::
> 2006-11-22 15:23:56.78 spid3 Owner:0x1ef77980 Mode: Schema
> Flg:0x0 Ref:0 Life:02000000 SPID:77 ECID:0
> 2006-11-22 15:23:56.78 spid3 SPID: 77 ECID: 0 Statement Type:
> UNKNOWN TOKEN Line #: 1
> 2006-11-22 15:23:56.78 spid3 Input Buf: Language Event: insert
> bulk TableName ([Field1] Int, [Field2] DateTime, [Field3] DateTime,
> [Field4] Decimal(28,13), [Field4] Bit)
> 2006-11-22 15:23:56.78 spid3 Requested By:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
> 2006-11-22 15:23:56.78 spid3 Victim Resource Owner:
> 2006-11-22 15:23:56.78 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:77 ECID:0 Ec0x3660BA30) Value:0x1ef77880 Cost0/0)
> The "TAB: 8:565577053" refers to the TableName specified in the Input
> Buf.
> This problem is reproducable (it happens with multiple tables).
> Does anyone have a hint on how to tackle this problem?
>
Are you specifying SqlBulkCopyOptions.TableLock in the SqlBulkCopy
constructor?
Do you have any other work on other connection to SQL Server enlisted in the
transaction? If so try to do that work on the _same_ connection that
SqlBulkCopy is using.
SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. see
http://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx
for a discussion and some possible workarounds.
David
|||Thanks for your quick reply.
I've tried using the tablelock option, but it didn't make any
difference.
There are more statements issued prior to the bulkcopy, all regular
inserts on other tables (using stored procs). Whe're using the
DataAccessApplication block, so I've got no influence on using the same
connection although it does use the same connection for all commands
within the transaction (as seen with SQL Profiler).
I'll look into the link you've presented.
tjibbe chris
On 22 nov, 17:03, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <tjibbech...@.gmail.com> wrote in messagenews:1164206482.527864.15760@.e3g2000cwe.goo glegroups.com...
>
>
>
>
>
> constructor?
> Do you have any other work on other connection to SQL Server enlisted in the
> transaction? If so try to do that work on the _same_ connection that
> SqlBulkCopy is using.
> SQL 2000 doesn't play as nice with System.Transactions as SQL 2005. seehttp://blogs.msdn.com/florinlazar/archive/2005/09/29/475546.aspx
> for a discussion and some possible workarounds.
> David- Tekst uit oorspronkelijk bericht niet weergeven -- Tekst uit oorspronkelijk bericht weergeven -