deAttach database: graph

Showing posts with label graph. Show all posts

Sunday, March 25, 2012

Deadlock trace interpretation

I have the following from a DBCC Trace:
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:1
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:216 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:215 ECID:0 Ec:(0x9017B5B0) Value:0x1dec9180 Cost:(0/1E4)
06/08/2006 16:29:02,spid4,Unknown,
06/08/2006 16:29:02,spid4,Unknown,Node:2
06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
CleanCnt:3 Mode: Range-S-S Flags: 0x0
06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
Ref:1 Life:02000000 SPID:215 ECID:0
06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
Line #: 237
06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
06/08/2006 16:29:02,spid4,Unknown,Requested By:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: Range-
Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
06/08/2006 16:29:02,spid4,Unknown,
Since there are range locks, it looks as if the transaction isolation level
SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
insert into a user table based on a select statement against a temporary
table. Prior to this insert that is being deadlocked, the temporary table is
populated and then updated by joining on several user tables, including the
table being used in the deadlock insert. Should I be focusing on the update
to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
insert upon which the deadlock is occurring and putting a HOLDLOCK on the
select statement of the temporary table that populates the user table?
--
Message posted via http://www.sqlmonster.comHi cbrichards
Without the code (and probably the DDL) it's impossible to say.
You are right that there must be SERIALIZABLE isolation in order to get the
range locks. And if you are already in SERIALIZABLE isolation, adding
HOLDLOCK would be redundant.
Sometimes you can avoid deadlock by requesting an X lock on data in a SELECT
statement, so there is no chance of another process also reading it and
holding onto the locks, until the first process is done. But again, without
any more details from you, there's little else to say.
--
HTH
Kalen Delaney, SQL Server MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:617d199ca3401@.uwe...
>I have the following from a DBCC Trace:
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Wait-for graph
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:1
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1df4bc00 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:216 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 216 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:215 ECID:0 Ec:(0x9017B5B0) Value:0x1dec9180 Cost:(0/1E4)
>
> 06/08/2006 16:29:02,spid4,Unknown,
> 06/08/2006 16:29:02,spid4,Unknown,Node:2
> 06/08/2006 16:29:02,spid4,Unknown,KEY: 82:2048478822:9 (ffffffffffff)
> CleanCnt:3 Mode: Range-S-S Flags: 0x0
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 0::
> 06/08/2006 16:29:02,spid4,Unknown,Grant List 1::
> 06/08/2006 16:29:02,spid4,Unknown,Owner:0x1e064100 Mode: Range-S-S Flg:0x0
> Ref:1 Life:02000000 SPID:215 ECID:0
> 06/08/2006 16:29:02,spid4,Unknown,SPID: 215 ECID: 0 Statement Type: INSERT
> Line #: 237
> 06/08/2006 16:29:02,spid4,Unknown,Input Buf: RPC Event: MyStoredProc;1
> 06/08/2006 16:29:02,spid4,Unknown,Requested By:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
>
> 06/08/2006 16:29:02,spid4,Unknown,Victim Resource Owner:
> 06/08/2006 16:29:02,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode:
> Range-
> Insert-Null SPID:216 ECID:0 Ec:(0x9014D5B0) Value:0x1e31b200 Cost:(0/1D4)
> 06/08/2006 16:29:02,spid4,Unknown,
>
> Since there are range locks, it looks as if the transaction isolation
> level
> SERIALIZABLE is in use. Profiler shows the SPID victim is attempting to
> insert into a user table based on a select statement against a temporary
> table. Prior to this insert that is being deadlocked, the temporary table
> is
> populated and then updated by joining on several user tables, including
> the
> table being used in the deadlock insert. Should I be focusing on the
> update
> to the temp table (as far as putting in place a WITH(HOLDLOCK)) or on the
> insert upon which the deadlock is occurring and putting a HOLDLOCK on the
> select statement of the temporary table that populates the user table?
> --
> Message posted via http://www.sqlmonster.com|||Hi Karen,
The stored proc code and DDL is included. Thanks for your help.
ALTER PROCEDURE [dbo].[MyStoredProc]
@.E_UID INT OUTPUT,
@.LKey Int,
@.RFID Int,
@.CID Int = Null,
@.CName varchar(255),
@.CCmID VARCHAR(30),
@.PID varchar(255),
@.CmS varchar(3),
@.VID varchar(38),
@.CmPtAm money
AS
DECLARE @.sVIDScrub varchar(38)
-- Determine the value to stuff in the VID field. First get rid of any non-
numerics
SET @.sVIDScrub = dbo.udf_Alpha(@.VID, 1)
CREATE TABLE #TRes (
LKey int,
RFID int,
CID int,
CName varchar(255),
CCmID varchar(30),
PID varchar(255),
CmS varchar(3),
VID varchar(38),
CmPtAm money,
CFID int,
CCode char(8),
PRIMARY KEY (LKey))
INSERT #TRes
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
CmPtAm,
CFID,
CCode )
SELECT @.LKey,
@.RFID,
@.CID,
@.CName,
@.CCmID,
@.PID,
@.CmS,
@.VID,
@.CmPtAm,
null,
null
/* Determine what value to stick into CFID
In order to match up car:
1) try for an exact CPID match; if none, then
2) see if the E CPID is contained in any car CPID or vice versa.
*/
-- Get car information based on CID
UPDATE TR
SET TR.CFID = Cast(Car.CUID AS varchar(10)),
TR.CCode = RTrim(Car.CCode),
TR.CName = Car.CName
FROM #TRes TR
JOIN dbo.RCm RCm
ON RCm.RFID = @.RFID
AND RCm.LKey = @.LKey
JOIN dbo.CarHist CBH
ON CBH.CmID = RCm.CmID
AND CBH.LKey = RCm.LKey
JOIN dbo.Cars Car
ON Car.CUID = CBH.CFID
AND Car.LKey = CBH.LKey
WHERE RCm.LKey = @.LKey
AND CBH.LKey = @.LKey
AND Car.LKey = @.LKey
--Insert the record
INSERT dbo.RCm
( LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
CmID,
VID,
CmPtAm,
CFID,
CCode )
SELECT LKey,
RFID,
CID,
CName,
CCmID,
PID,
CmS,
VID,
-- Only fill the VID column if the size fits the column data type
CASE
WHEN Len(@.sVIDScrub) < 10 THEN Cast(@.sVIDScrub AS int)
ELSE 0
END,
CmPtAm,
CFID,
CCode
FROM #TRes
-- Return the new id
SET @.@.E_UID = SCOPE_IDENTITY()
--****************************************************************************
CREATE TABLE [dbo].[Cars](
[LKey] [int] NOT NULL,
[CUID] [int] IDENTITY(1,1) NOT NULL,
[CCode] [varchar](8) NOT NULL,
[CName] [varchar](35) NOT NULL,
[CUser] [char](12) NOT NULL,
[CDate] [datetime] NOT NULL
CONSTRAINT [PK_Cars] PRIMARY KEY NONCLUSTERED
(
[CUID] ASC,
[LKey] ASC
) ON [PRIMARY],
CONSTRAINT [Unique_CCode] UNIQUE NONCLUSTERED
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_CUID] ON [dbo].[Cars]
(
[LKey] ASC,
[CUID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CCode] ON [dbo].[Cars]
(
[CCode] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
--***********************************************************************
CREATE TABLE [dbo].[RCm](
[E_UID] [int] IDENTITY(1,1) NOT NULL,
[LKey] [int] NOT NULL,
[RFID] [int] NOT NULL,
[CID] [int] NULL,
[CName] [varchar](100) NULL,
[CCmID] [varchar](30) NULL,
[PID] [varchar](255) NULL,
[CmS] [varchar](3) NULL,
[CmID] [varchar](38) NOT NULL,
[VID] [int] NOT NULL DEFAULT ((-1)),
[CmPtAm] [money] NULL,
[CFID] [int] NULL,
[CCode] [char](8) NULL,
CONSTRAINT [PK_RCm] PRIMARY KEY NONCLUSTERED
(
[E_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE UNIQUE CLUSTERED INDEX [IX_LKey_E_UID] ON [dbo].[RCm]
(
[Key] ASC,
[E_UID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_RCm_RFID] ON [dbo].[RCm]
(
[RFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[CarHist](
[LKey] [int] NOT NULL,
[CarHist_UID] [int] IDENTITY(1,1) NOT NULL,
[CDetFID] [int] NOT NULL CONSTRAINT [DF__CarCharge] DEFAULT (1),
[CFID] [int] NOT NULL,
[CCode] [varchar](8) NOT NULL,
[VFID] [int] NULL,
[CmID] AS (convert(varchar(10),[VisitFID]) + ltrim([ClaimIDSuffix])),
CONSTRAINT [PK_CarHist] PRIMARY KEY NONCLUSTERED
(
[CarHist_UID] ASC,
[LKey] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE CLUSTERED INDEX [IDX1_CBH_CDetFID] ON [dbo].[CarHist]
(
[CDetFID] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX1_CBH_VFID] ON [dbo].[CarHist]
(
[VFID] ASC,
[LKey] ASC
) ON [PRIMARY]
GO
Kalen Delaney wrote:
>Hi cbrichards
>Without the code (and probably the DDL) it's impossible to say.
>You are right that there must be SERIALIZABLE isolation in order to get the
>range locks. And if you are already in SERIALIZABLE isolation, adding
>HOLDLOCK would be redundant.
>Sometimes you can avoid deadlock by requesting an X lock on data in a SELECT
>statement, so there is no chance of another process also reading it and
>holding onto the locks, until the first process is done. But again, without
>any more details from you, there's little else to say.
>>I have the following from a DBCC Trace:
>[quoted text clipped - 51 lines]
>> insert upon which the deadlock is occurring and putting a HOLDLOCK on the
>> select statement of the temporary table that populates the user table?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1

Deadlock questions

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...[vbcol=seagreen]
> 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.
> 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[vbcol=seagreen]
> 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...[vbcol=seagreen]
> 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
> 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[vbcol=seagreen]
> 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
>
|||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?h...soft.com#link1
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uL0J52EcEHA.2812@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> 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
> Sure
> kilter -
> to?
> this
>

Thursday, March 22, 2012

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...
> > 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
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Deadlock questions

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...
> 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
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

Sunday, March 25, 2012

Deadlock trace interpretation

Deadlock questions

Thursday, March 22, 2012

Deadlock questions

Deadlock questions

deAttach database

Blog Archive

About Me