Deadlock error, why?
-
Hi All,
I have a multithreded application using ADO .Net with SQL Server 2000. The application rarely is having a sql server deadlock error especially when updating tree structure. I turned on the Trace with DBCC TraceON and analysing the log, I found that the error is on same stored procedure on same line called by two different threads. Surprising the statement at which error occurred is Insert Into Select statement which is first SQL statement in the stored proc. How in the world Insert Into Select statement can have a deadlock. The select part of the statement uses lock hints like With (Index(0), XLOCK). Is SQL Server trying to do some sort of implicit lock escalation while running Insert Into Select statement.
Here is the trace report
Deadlock encountered .... Printing deadlock information
2006-04-12 01:21:49.07 spid4
2006-04-12 01:21:49.07 spid4 Wait-for graph
2006-04-12 01:21:49.07 spid4
2006-04-12 01:21:49.07 spid4 Node:1
2006-04-12 01:21:49.07 spid4 KEY: 8:773577794:1 (0100190ab68f) CleanCnt:1 Mode: U Flags: 0x0
2006-04-12 01:21:49.07 spid4 Grant List 0::
2006-04-12 01:21:49.07 spid4 Owner:0x34d40f00 Mode: U Flg:0x0 Ref:0 Life:02000000 SPID:95 ECID:0
2006-04-12 01:21:49.07 spid4 SPID: 95 ECID: 0 Statement Type: INSERT Line #: 72
2006-04-12 01:21:49.07 spid4 Input Buf: RPC Event: spDSAsset_RecurseStatus;1
2006-04-12 01:21:49.07 spid4 Requested By:
2006-04-12 01:21:49.07 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:59 ECID:0 Ec
0x63863580) Value:0x5635cf40 Cost0/0)2006-04-12 01:21:49.07 spid4
2006-04-12 01:21:49.07 spid4 Node:2
2006-04-12 01:21:49.07 spid4 KEY: 8:773577794:1 (010027617460) CleanCnt:1 Mode: X Flags: 0x0
2006-04-12 01:21:49.07 spid4 Grant List 1::
2006-04-12 01:21:49.07 spid4 Owner:0x771d1f80 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:59 ECID:0
2006-04-12 01:21:49.07 spid4 SPID: 59 ECID: 0 Statement Type: INSERT Line #: 72
2006-04-12 01:21:49.07 spid4 Input Buf: RPC Event: spDSAsset_RecurseStatus;1
2006-04-12 01:21:49.07 spid4 Requested By:
2006-04-12 01:21:49.07 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:95 ECID:0 Ec
0x625B3580) Value:0x74834da0 Cost0/0)2006-04-12 01:21:49.07 spid4 Victim Resource Owner:
2006-04-12 01:21:49.07 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:95 ECID:0 Ec
0x625B3580) Value:0x74834da0 Cost0/0)2006-04-12 01:21:49.07 spid4
2006-04-12 01:21:49.07 spid4 End deadlock search 28448 ... a deadlock was found.
2006-04-12 01:21:49.07 spid4 ----------------------------------
Here is the stored proc
Create Procedure spDSAsset_RecurseStatus (
@decAssetID DSID,
@decSubstatus decimal(5,2),
@decJobID DSID
)
AS
-- Propogate the status of the asset to its parent/s if status is Done Or Error Or Do Not Build
-- by finding the status of other siblings.
--
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
Declare @decParentAssetID DSID
Declare @decParentInstanceID DSID
Declare @decProcessStatus Decimal(5,2)
Declare @decChildSevereError Decimal(5,2)
Declare @decAllChildrenError Decimal(5,2)
Declare @decParentStatus Decimal(5,2)
Declare @decDoNotBuild Decimal(5,2)
Declare @intCurrStatus tinyint
Declare @intReadyForProcess tinyint
Declare @intDone tinyint
Declare @intDoNotBuild tinyint
Declare @intError tinyint
Declare @noAssetsDone int
Declare @noChildren int
Declare @intProcessStyle int
Declare @noAssetsDNB int
Declare @noAssetsSevereError int
Declare @noAssetsError int
Declare @intParentsUpdated int
Set @intParentsUpdated = 0
SET @noAssetsDone = 0
SET @noChildren = 0
SET @noAssetsDNB = 0
SET @noAssetsSevereError = 0
Set @noAssetsError = 0
Set @intDone = 0 -- Code for Done
Set @intReadyForProcess = 2 -- Code for Ready for Processing
Set @intError = 4
Set @intDoNotBuild = 7 -- Code for Do Not Build
Set @decDoNotBuild = 7.02 -- Code for Marked Do Not Build
Set @decChildSevereError = 4.07 -- Code for at least one child failed with severe error
Set @decAllChildrenError = 4.08 -- Code for all children failed with error
Declare @ParentTable Table (AssetID Decimal(18,0), InstanceID Decimal(18,0), NewStatus Decimal(5,2),
Children int, ChildrenDone int, ChildrenDNB int, ChildrenError int, ChildrenSevereError int)
If (@decJobID=0)
Set @decJobID = Null
Set @intCurrStatus = Cast(@decSubstatus as tinyint)
-- start a transaction
Begin Tran
If (@intCurrStatus in (@intError, @intDoNotBuild, @intDone)) -- recurse only if SetToIgnore, DoNotBuild or Done
Begin
Insert Into @ParentTable (AssetID, InstanceID, Children, ChildrenDone, ChildrenDNB, ChildrenError, ChildrenSevereError)
Select PARENTASSET.AssetID, Min(PARENTINST.InstanceID),
Count(Distinct ASSET.AssetID),
Count(Distinct ASSETDONE.AssetID),
Count(Distinct ASSETDNB.AssetID),
Count(Distinct ASSETERROR.AssetID),
Count(Distinct ASSETSEVERE.AssetID)
From DS_AssetInstance CURRINST With (NOLOCK), DS_AssetInstance PARENTINST With (NOLOCK), DS_V_CurrentAsset PARENTASSET With (Index(0), XLOCK),
DS_AssetInstance CHILDINST With (NOLOCK)
Left Outer Join DS_V_CurrentAsset ASSET With (Index(0), UPDLOCK) On ASSET.AssetID=CHILDINST.AssetID
Left Outer Join DS_V_CurrentAsset ASSETDONE With (Index(0), UPDLOCK) On ASSETDONE.AssetID=CHILDINST.AssetID And Cast(ASSETDONE.ProcessStatus as int)=@intDone
Left Outer Join DS_V_CurrentAsset ASSETDNB With (Index(0), UPDLOCK) On ASSETDNB.AssetID=CHILDINST.AssetID And Cast(ASSETDNB.ProcessStatus as int)=@intDoNotBuild
Left Outer Join DS_V_CurrentAsset ASSETERROR With (Index(0), UPDLOCK) On ASSETERROR.AssetID=CHILDINST.AssetID And Cast(ASSETERROR.ProcessStatus as int)=@intError
Left Outer Join DS_V_CurrentAsset ASSETSEVERE With (Index(0), UPDLOCK) Inner Join DS_ProcessStyleErrorStates PSES With (NOLOCK)
On PSES.ProcessStyleID=ASSETSEVERE.ProcessStyleID And ASSETSEVERE.ProcessStatus=PSES.ProcessStatus And PSES.IsSevereError=1
On ASSETSEVERE.AssetID=CHILDINST.AssetID And Cast(ASSETERROR.ProcessStatus as int)=@intError
Where CURRINST.ParentInstanceID=PARENTINST.InstanceID And PARENTINST.AssetID=PARENTASSET.AssetID
And CHILDINST.ParentInstanceID=PARENTINST.InstanceID And CURRINST.AssetID=@decAssetID
And Cast(PARENTASSET.ProcessStatus as int) != @intDoNotBuild
Group By PARENTASSET.AssetID Order By PARENTASSET.AssetID
Declare ParentInstance Cursor Local Dynamic Scroll_locks For
Select Distinct AssetID, InstanceID, Children, ChildrenDone, ChildrenDNB, ChildrenError, ChildrenSevereError From @ParentTable
Open ParentInstance
Fetch Next From ParentInstance Into @decParentAssetID, @decParentInstanceID, @noChildren, @noAssetsDone, @noAssetsDNB, @noAssetsError, @noAssetsSevereError
While (@@FETCH_STATUS = 0)
Begin
If (@noAssetsDNB = @noChildren Or @noAssetsError=@noChildren Or @noAssetsSevereError > 0)
Begin
If (@noAssetsSevereError > 0)
Set @decParentStatus = @decChildSevereError
Else If (@noAssetsDNB = @noChildren)
Set @decParentStatus = @decDoNotBuild
Else
Set @decParentStatus = @decAllChildrenError
-- Update the status of current parent
Update DS_V_CurrentAsset Set rocessStatus=@decParentStatus">ProcessStatus=@decParentStatus Where AssetID=@decParentAssetID And rocessStatus!=@decParentStatus">ProcessStatus!=@decParentStatus
If (@@ROWCOUNT > 0)
Begin
Update @ParentTable Set NewStatus=@decParentStatus Where AssetID=@decParentAssetID
Set @intParentsUpdated = @intParentsUpdated + 1
End
End
Else If ((@noAssetsDone + @noAssetsDNB + @noAssetsError) = @noChildren)
Update DS_V_CurrentAsset Set rocessStatus=@intReadyForProcess">ProcessStatus=@intReadyForProcess, JobID=IsNull(@decJobID, JobID) Where AssetID=@decParentAssetID
Fetch Next From ParentInstance Into @decParentAssetID, @decParentInstanceID, @noChildren, @noAssetsDone, @noAssetsDNB, @noAssetsError, @noAssetsSevereError
End
Close ParentInstance
Deallocate ParentInstance
End
-- complete transaction
Commit Tran
END
GO
-
In my understanding what is happening here is
Node 1 is Mode : U
i.e. update lock, SQL Server intends to modify a row or page, and later promotes the update page lock to an exclusive lock.
In your case after select you are going to Insert into @ParentTable
At the same time another thread 2 wants the same thing. First it tries to get the update lock which is not available, so it automatically escalates to next level and try to see if it can get Exclusive lock (Mode X) but due to exact timing, this lock level is also not available and thus resulted in deadlock.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply