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