Deadlock - Key

Hi There

I have the following information for a deadlock trace:
===========================================================================
Deadlock encountered .... Printing deadlock information
   
Wait-for graph
Node:1
KEY: 10:1662733076:1 (c60896dae9f2) CleanCnt:1 Mode: X Flags: 0x0
Grant List 0::
Owner:0x79cd5620 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:157 ECID:0
SPID: 157 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:130 ECID:0 Ec:(0x00D64518) Value:0x3022bda0 Cost:(0/5C)
     
Node:2
KEY: 10:1662733076:3 (350ae0dbc094) CleanCnt:1 Mode: S Flags: 0x0
Grant List 1::
Owner:0x302c19c0 Mode: S        Flg:0x0 Ref:1 Life:00000000 SPID:130 ECID:0
SPID: 130 ECID: 0 Statement Type: EXECUTE Line #: -1
Input Buf: RPC Event: sp_cursorexecute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:157 ECID:0 Ec:(0x06F84518) Value:0x5026be20 Cost:(0/5C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: X SPID:157 ECID:0 Ec:(0x06F84518) Value:0x5026be20 Cost:(0/5C)
===========================================================================
I have 2 questions, firstly the KEY for node 1 is 10:1662733076:1 BUT the KEY for node 2 is 10:1662733076:3, now i find it odd that it is a defferent index for each node, does'nt a deadlock on a key occur on the same index range? I may be completely wrong but all the other key deadlocks i have seen have been for the same index?

Secondly i would like to know what actual row or range the table this is happening on. How do i get this information? Any way to get the actual row or range that the dealock is occuring on would be appreciated.

I have a feeling poifiler is my only real solution but i do not know when the deadlock will occur, and leaving a trace runing for days is a scenario i would rather not have? Any other means to do this?

Thanx
LVL 1
michaelpgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
Your environment is SQL Server 2005, for this that don't know (at least I assume)...

Depending on the circumstances, several different keys (range) values are on the same page (actually, most often), and if SQL Server choose to perform a page lock instead of a row lock, a deadlock could occur on different rows...
To avoid this, you might try to suggest the queries to rather use a rowlock (ROWLOCK hint) than a pagelock, but the hint still remains a hint.

Now, as far as I know the profile can display the queries that are concerned by the deadlock (hover over the graph)...
I don't have a 2k5 box in font, it might be that you enable something somewhere to have the SQL available..
   
michaelpgAuthor Commented:
Hi Angel

No this is for sql2000.
These queries are application driven i do not control the locks, but i am sure they use row locks, my real concern is how to narrow down the query, but i think profiler is the way to go.
Is there anywhere to set the lock timeout for the server to a longer time period?

Thanx
michaelpgAuthor Commented:
Also i do not understand why index 3 is involved in the deadlock, as it is a 1 column key purely used for replication, the application is unaware of it, which really confuses me.???
ShogunWadeCommented:
Indexes are pretty irelevent in deadlock senarios.     Essentially sql 2000 has thre locking modes :  row level, page level and table level.   (well actually there is also extent locks but they dont come into play here).

The thing is that it is fundamentally the data that gets locked not the indexes (light weight latches are used on indexes but locks must be established on the table before a latch is established on an index)  
so in a sence it is irrelevent which index it is reporting as being involved, uless we are talking here about exclusive schema changing locks.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.