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 ?
Which SPID is the deadlock victim here ..i.e which SPID was killed ? Is it
SPID 60 ?
Any info in addition to that article is highly appreciated.
ThanksHi 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
>
>
>|||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
> >
> >
> >
> >
> >
>|||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
> > >
> > >
> > >
> > >
> > >
> >
> >
>|||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
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||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
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Also cant view the site you mentioned regarding parallelism deadlock
"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
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I just tried the tinyurl site & it seems to be up now, but in case you still
can't get at it, here's the long version:
http://groups.google.com.au/groups?hl=en&lr=&ie=UTF-8&frame=right&th=e61687c0b5d1727a&seekm=Q9foUNGVBHA.313%40cppssbbsa01.microsoft.com#link1
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uL0J52EcEHA.2812@.TK2MSFTNGP11.phx.gbl...
> Also cant view the site you mentioned regarding parallelism deadlock
>
> "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
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
0x1F1BB5B0)