Re: Deadlock questions
From: Greg Linwood (g_linwoodQhotmail.com)
Date: 07/22/04
- Next message: Vikram Jayaram [MS]: "RE: Register server wizard malfunction"
- Previous message: Vikram Jayaram [MS]: "RE: Question on ProcCache"
- In reply to: Hassan: "Re: Deadlock questions"
- Next in thread: Hassan: "Re: Deadlock questions"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 23 Jul 2004 08:45:52 +1000
Hi Hassan
Yep, the VictimResourceOwner section of the report displays which SPID is
the victim. In this case, it's 55..
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538)
> Value:0x193
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@hotmail.com> wrote in message
news:Oq4hKIDcEHA.556@tk2msftngp13.phx.gbl...
> So now based on your rectified report, is Spid 55 the victim..i.e the one
> being killed ??? Confused
>
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uDiqgU5bEHA.1652@TK2MSFTNGP09.phx.gbl...
> > Hi Hassan
> >
> > There's an example of a parallelism deadlock in the newsgroup archive
> here:
> > http://tinyurl.com/4atwl. These can be nasty to solve.
> >
> > As far as how the report structure from the article would read
normally -
> > the nodes basically have their information represented upside down in
the
> > article, as if the report has been partially pulled from the SQL
> Enterprise
> > Manager's log viewer. Most people who actually read & use these reports
in
> > the real world access them directly from the log files (not the SQL EM
> > viewer) so that they can be read in the intended order. The key thing
here
> > is that the locked resources are declared at the top of the Node report
> > block, which makes the report slightly intuitive. From the log, this
> > particular report would be formatted something like this:
> >
> > Wait-for graph
> > Node:1
> > KEY: 8:1653632984:2 (da00ce043a9e) CleanCnt:1 Mode: U Fl ags: 0x0
> > Grant List 0::
> > Owner:0x1937f2a0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:55
ECID:0
> > SPID: 55 ECID: 0 Statement Type: EXECUTE Line #: 1
> > Input Buf: RPC Event: sp_cursoropen;1
> > Requested By:
> > ResType:LockOwner Stype:'OR' Mode: X SPID:60 ECID:0 Ec:(0x1F1BB5B0)
> > Value:0x193
> > Victim Resource Owner:
> > ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538)
> > Value:0x193
> >
> > Node:2
> > KEY: 8:1653632984:1 (2d018af70d80) CleanCnt:1 Mode: X Flags: 0x0
> > Grant List 0::
> > Owner:0x1936e420 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:60
ECID:0
> > SPID: 60 ECID: 0 Statement Type: UPDATE Line #: 1
> > Input Buf: Language Event: Update tblQueuedEvents Set NotifyID = 2,
> > ResynchDate
> > Requested By:
> > ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x33AE1538)
> > Value:0x193
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Hassan" <fatima_ja@hotmail.com> wrote in message
> > news:%23COH$PsbEHA.384@TK2MSFTNGP10.phx.gbl...
> > > Thanks Greg,
> > >
> > > How would the graph actually read ? Have you any idea how the graph
> would
> > > look like should it involve parallelism or threads as is also
mentioned
> in
> > > the article ?
> > >
> > > "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> > > news:uf1WjzibEHA.3596@tk2msftngp13.phx.gbl...
> > > > Hi Hassan
> > > >
> > > > Answered inline:
> > > >
> > > > "Hassan" <fatima_ja@hotmail.com> wrote in message
> > > > news:eOyTG6dbEHA.2840@TK2MSFTNGP11.phx.gbl...
> > > > > I had some questions based on this article
> > > > > http://www.support.microsoft.com/?id=832524
> > > > >
> > > > > With regards to the deadlock graph, it states
> > > > >
> > > > > "Next, in Node 1, Request By, SPID 55 requested a shared lock,
Mode:
> > S,
> > > on
> > > > > IndexId=1. In Node 2, Request By, SPID 60 requested an exclusive
> lock,
> > > > Mode:
> > > > > X, on IndexId=2. Because these lock requests occur at the same
time,
> > the
> > > > > deadlock occurs. Each SPID's granted locks are preventing the
> > requested
> > > > > locks from continuing"
> > > > >
> > > > > How does it know that SPID 55 is requesting a lock on IndexId=1
and
> > > that
> > > > > SPID 60 is requesting a lock on IndexId=2. Am i suppose to assume
> that
> > > > bcos
> > > > > of the granted locks ?
> > > >
> > > > Yes, that's correct. It makes sense because this is the nature of a
> > > > deadlock - that resource owners are waiting on each other for locks
> > > against
> > > > the resources each other already has granted.
> > > >
> > > > >
> > > > > Which SPID is the deadlock victim here ..i.e which SPID was killed
?
> > Is
> > > it
> > > > > SPID 60 ?
> > > >
> > > > Yes. The report in the article is not formatted correctly which
makes
> > this
> > > a
> > > > little confusing.
> > > >
> > > > >
> > > > > Any info in addition to that article is highly appreciated.
> > > >
> > > >
> > > > I have responded to the private newsgroup about this article already
> but
> > > so
> > > > far I've been ignored. I was hoping this article would be corrected
> > before
> > > > being released so I'm disappointed to see that you've encountered it
> in
> > > such
> > > > a confused state.
> > > >
> > > > Here are some additional comments I posted to the private newsgroup
> > which
> > > > you might find helpful:
> > > >
> > > > A few issues I picked up in this article:
> > > >
> > > > (a) "The following is a sample of the output that you might see in
the
> > SQL
> > > > Server error log when you use the -T1205 startup parameter. ". That
> > should
> > > > be -T1204, not -T1205
> > > >
> > > > (b) The example graph report is messed up & does not read in order.
> Sure
> > > the
> > > > EM reads log files in reverse, but this one seems right out of
> kilter -
> > > for
> > > > the purpose of the article, it should probably be set out in proper
> > > order..
> > > >
> > > > (c) The statement "An IndexId that is equal to 2 is a nonclustered
> > index."
> > > > seems not to communicate it's intended message very well. A non
> > clustered
> > > > index may have a value of 2 or greater, not just 2.
> > > >
> > > > (d) The article suggests first to use startup parameters rather than
> > dbcc.
> > > > Why suggest to users that they stop the server when they don't have
> to?
> > > >
> > > > Otherwise, this would be a useful article to the many who ask about
> this
> > > in
> > > > the NGs..
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Vikram Jayaram [MS]: "RE: Register server wizard malfunction"
- Previous message: Vikram Jayaram [MS]: "RE: Question on ProcCache"
- In reply to: Hassan: "Re: Deadlock questions"
- Next in thread: Hassan: "Re: Deadlock questions"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
