Problem with dead-locks

We are experiencing some problems with deadlocks in our system. We call
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, s they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>Please turn on TF-1204 and see what is getting deadlocked.
thanks
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, s they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:

> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:

> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 8
6.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-
Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifyi
ng cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt
:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0
Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK IN
SERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT T
AB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\000
0023764_0306
_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar',
FIRSTROW = 1, KEEPIDENTITY
, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = ' ')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schem
a-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-
Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was f
ound.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> Cost0/0)
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> Cost0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanC
nt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\000
0023764_0306_TAB_RENS_TILLADELSE.t
ab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:

> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk