deAttach database: Deadlock questions

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...
on[vbcol=seagreen]
> Mode:
that[vbcol=seagreen]
> bcos
> 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.
>
it[vbcol=seagreen]
> Yes. The report in the article is not formatted correctly which makes this
a
> little confusing.
>
>
> 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
>|||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...
S,[vbcol=seagreen]
> on
the[vbcol=seagreen]
requested[vbcol=seagreen]
> that
> against
Is[vbcol=seagreen]
> it
this[vbcol=seagreen]
> a
> so
before[vbcol=seagreen]
> such
which[vbcol=seagreen]
SQL[vbcol=seagreen]
should[vbcol=seagreen]
> the
> for
> order..
index."[vbcol=seagreen]
clustered[vbcol=seagreen]
dbcc.[vbcol=seagreen]
> in
>|||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: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...
> here:
normally -[vbcol=seagreen]
the[vbcol=seagreen]
> Enterprise
in[vbcol=seagreen]
here[vbcol=seagreen]
ECID:0[vbcol=seagreen]
ECID:0[vbcol=seagreen]
> would
mentioned[vbcol=seagreen]
> in
Mode:[vbcol=seagreen]
> lock,
time,[vbcol=seagreen]
and[vbcol=seagreen]
> that
?[vbcol=seagreen]
makes[vbcol=seagreen]
> but
> in
the[vbcol=seagreen]
> Sure
> kilter -
> to?
> this
>|||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
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23COH$PsbEHA.384@.TK2MSFTNGP10.phx.gbl...
would[vbcol=seagreen]
in[vbcol=seagreen]
> S,
lock,[vbcol=seagreen]
> the
> requested
that[vbcol=seagreen]
> Is
> this
but[vbcol=seagreen]
> before
in[vbcol=seagreen]
> which
> SQL
> should
Sure[vbcol=seagreen]
kilter -[vbcol=seagreen]
> index."
> clustered
> dbcc.
to?[vbcol=seagreen]
this[vbcol=seagreen]
>|||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?...osoft.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...
> here:
normally -[vbcol=seagreen]
the[vbcol=seagreen]
> Enterprise
in[vbcol=seagreen]
here[vbcol=seagreen]
ECID:0[vbcol=seagreen]
ECID:0[vbcol=seagreen]
> would
mentioned[vbcol=seagreen]
> in
Mode:[vbcol=seagreen]
> lock,
time,[vbcol=seagreen]
and[vbcol=seagreen]
> that
?[vbcol=seagreen]
makes[vbcol=seagreen]
> but
> in
the[vbcol=seagreen]
> Sure
> kilter -
> to?
> this
>sql

Thursday, March 22, 2012

Deadlock questions

No comments:

Post a Comment

deAttach database

Blog Archive

About Me