Deadlocking & Keylocks

Question

Post reply

Deadlocking & Keylocks

CK-263283

Newbie

Points: 5
More actions
September 23, 2005 at 2:56 pm

#187822

Hi,
If anybody can shed any light, it would be greatly appreciated. I have a routine which is deadlocking and it looks like it is deadlocking around the index keys. This is being run on SQL 2000. Here's the transaction error log information:
===============================================================================
Node:1
KEY: 7:1060198827:1 (fa0049244034) CleanCnt:2 Mode: Range-X-X Flags: 0x0
Grant List 0::
Owner:0x9540ad00 Mode: Range-X-X Flg:0x0 Ref:1 Life:02000000 SPID:54 ECID:0
SPID: 54 ECID: 0 Statement Type: DELETE Line #: 1
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:52 ECID:0 Ec0x5703F560) Value:0xb1240e60 Cost0/16B7D80)
Node:2
KEY: 7:160719625:8 (0604204a640e) CleanCnt:2 Mode: Range-S-U Flags: 0x0
Grant List 0::
Owner:0x35e3a5c0 Mode: Range-S-U Flg:0x0 Ref:311 Life:02000000 SPID:52 ECID:0
SPID: 52 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:54 ECID:0 Ec0x36CF1560) Value:0x8003f960 Cost0/2C8FC)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: X SPID:54 ECID:0 Ec0x36CF1560) Value:0x8003f960 Cost0/2C8FC)
============================================================================
I tried to do a "select object_name(1060198827)" and couldn't get anything for the object name. Does anybody have a good idea of what's happening here or what's being locked out? This is within a Navision database, so I don't have direct control of the SQL statements - Navision automatically creates the SQL statements. This is a conflict between two transactions that are deleting several thousand records in the database. Thanks for any help,
CK

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

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

David.Poole SSC Guru Points: 76224 More actions · Answer 1

What build number do you get when your run SELECT @@VERSION?

Is it 2 separate users firing these processes or a single user?

If it is one person firing off one command and getting this deadlock then I would report it to Microsoft as a bug

LinkedIn Profile

CK-263283 Newbie Points: 5 More actions · Answer 2

Build # is:

Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)

I have two separate machines, but using the same windows user name firing off the task.

Jo Pattyn SSC-Dedicated Points: 32513 More actions · Answer 3

Can you post the routine?

Since the SPID's are different I assume 2 different users.

CK-263283 Newbie Points: 5 More actions · Answer 4

Unfortuantely I can't post the routine - it written in the Navision database language (a subset of pascal) and it does the sql formatting itself. It's basically a routine which does a bunch of deletes across about 5 tables.

Jo Pattyn SSC-Dedicated Points: 32513 More actions · Answer 5

You can make some basic guesses which SQL statements are sent by using the sql profiler.

They delete from tables in the same order?

Any options specified update ... WITH ... ?

CK-263283 Newbie Points: 5 More actions · Answer 6

Here are some of the examples of the delete functions:

#1

Execution Tree

--------------

Clustered Index Delete(OBJECT[Advance].[dbo].[!! SQL !!$Work Order Material].[!! SQL !!$Work Order Material$0]), WHERE[!! SQL !!$Work Order Material].[Work Order No_]=[@P1] AND [!! SQL !!$Work Order Material].[Line No_]=[@P2]))

#2

Execution Tree

--------------

Clustered Index Delete(OBJECT[Advance].[dbo].[!! SQL !!$Work Order].[!! SQL !!$Work Order$0]), WHERE[!! SQL !!$Work Order].[No_]=[@P1]))

CK-263283 Newbie Points: 5 More actions · Answer 7

Actually, after looking through my code, I found the problem - it looks like I overlooked a piece of code that actually does a number of updates and and potential inserts too. Restructuring the code seems to have solved the problem. Thanks for the help.

Jo Pattyn SSC-Dedicated Points: 32513 More actions · Answer 8

Jo Pattyn

SSC-Dedicated

Points: 32513

September 29, 2005 at 11:20 am

#594120

You're welcome.