I need help.
We keep having deadlocking. The deadlocking trace points me to a statistic
update. The KEY: 5:242972092:25 index lock it points to is a SQL Server
automatically created statistic. It is on a foreign key column.
I have tried turning autoUpdate Stats off and we still get the deadlock.
Trace Listed below. Does anyone have any ideas? I have never seen a deadlock
on a statistic.
01/12/2006 13:36:30,spid4,Unknown,Node:1
01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:1 (de001a40a963)
CleanCnt:1 Mode: X Flags: 0x0
01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
01/12/2006 13:36:30,spid4,Unknown,Owner:0x2e84d360 Mode: X Flg:0x0
Ref:0 Life:02000000 SPID:61 ECID:0
01/12/2006 13:36:30,spid4,Unknown,SPID: 61 ECID: 0 Statement Type: UPDATE
Line #: 82
01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
up_updateShipmentRequestLine;1
01/12/2006 13:36:30,spid4,Unknown,Requested By:
01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)
01/12/2006 13:36:30,spid4,Unknown,
01/12/2006 13:36:30,spid4,Unknown,Node:2
01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:25 (be027cd3b404)
CleanCnt:1 Mode: S Flags: 0x0
01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
01/12/2006 13:36:30,spid4,Unknown,Owner:0x4e241860 Mode: S Flg:0x0
Ref:0 Life:02000000 SPID:56 ECID:0
01/12/2006 13:36:30,spid4,Unknown,SPID: 56 ECID: 0 Statement Type: SELECT
Line #: 9
01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
01/12/2006 13:36:30,spid4,Unknown,Requested By:
01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: X
SPID:61 ECID:0 Ec:(0x235D5528) Value:0x5bc13d40 Cost:(0/C4)
01/12/2006 13:36:30,spid4,Unknown,Victim Resource Owner:
01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)I see an exclusive lock generated by:
UPDATE Line #: 82 in up_updateShipmentRequestLine;1
and a shared lock generated by
SELECT Line #: 9 in
up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
You may want to look in the code in these two stored procedures (?). You may
be accessing tables in reverse order.
Probably the fix should go into the [up_updateShipmentRequestLine].
If you have
SELECT @.bExists = Field1 FROM Table1
and then
IF @.bExists = someVal
UPDATE Table1 ...
Instead do first:
UPDATE Table1 SET Field1 = @.Val1
IF @.@.ROWCOUNT == 0
INSERT ...
Ok. I'm making assumptions here since I do not know your code but the rule
is that you want to get the highest lock since the beginning of the sproc an
d
there are many ways you can do that. One is above.
If you do not want to change the logic of the code, place a Locking Hints
using
WITH( ... )
for example WITH(UPDLOCK).
If you want more details then you need to post some code so I can point you
exactly to code that generates the deadlock.
"JI" wrote:
> I need help.
> We keep having deadlocking. The deadlocking trace points me to a statistic
> update. The KEY: 5:242972092:25 index lock it points to is a SQL Server
> automatically created statistic. It is on a foreign key column.
> I have tried turning autoUpdate Stats off and we still get the deadlock.
> Trace Listed below. Does anyone have any ideas? I have never seen a deadlo
ck
> on a statistic.
> 01/12/2006 13:36:30,spid4,Unknown,Node:1
> 01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:1 (de001a40a963)
> CleanCnt:1 Mode: X Flags: 0x0
> 01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
> 01/12/2006 13:36:30,spid4,Unknown,Owner:0x2e84d360 Mode: X Flg:0x0
> Ref:0 Life:02000000 SPID:61 ECID:0
> 01/12/2006 13:36:30,spid4,Unknown,SPID: 61 ECID: 0 Statement Type: UPDATE
> Line #: 82
> 01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
> up_updateShipmentRequestLine;1
> 01/12/2006 13:36:30,spid4,Unknown,Requested By:
> 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
> SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)
> 01/12/2006 13:36:30,spid4,Unknown,
> 01/12/2006 13:36:30,spid4,Unknown,Node:2
> 01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:25 (be027cd3b404)
> CleanCnt:1 Mode: S Flags: 0x0
> 01/12/2006 13:36:30,spid4,Unknown,Grant List 3::
> 01/12/2006 13:36:30,spid4,Unknown,Owner:0x4e241860 Mode: S Flg:0x0
> Ref:0 Life:02000000 SPID:56 ECID:0
> 01/12/2006 13:36:30,spid4,Unknown,SPID: 56 ECID: 0 Statement Type: SELECT
> Line #: 9
> 01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event:
> up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
> 01/12/2006 13:36:30,spid4,Unknown,Requested By:
> 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: X
> SPID:61 ECID:0 Ec:(0x235D5528) Value:0x5bc13d40 Cost:(0/C4)
> 01/12/2006 13:36:30,spid4,Unknown,Victim Resource Owner:
> 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner
Stype:'OR' Mode: S
> SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0)
>
>|||The update proc is one that I wrote a proc generator to create. It does a
simple update...it does not access any other or the same table before the
update. The interesting thing with the deadlock trace information is the
index that is says deadlocks is a statistic. One created by SQL Server...
I will post the update shipment request line proc below anyway.
alter proc [dbo].[up_updateShipmentRequestLine]
@.iError int OUTPUT
,@.guidShipmentRequestLineId uniqueidentifier
,@.guidShipmentRequestId uniqueidentifier
,@.iLineNumber int
,@.guidLotId uniqueidentifier
,@.sPurchaseOrderNumber char(50)
,@.sFullLotInd char(1)
,@.iMinimumCount int
,@.daDateNeeded datetime
,@.dcQuantity decimal(18,0)
,@.guidDestinationPlantId uniqueidentifier
,@.guidShipmentStatusId uniqueidentifier
,@.sShippingGroup char(3)
,@.sLineCreateUserName char(50)
,@.daLineCreateDate datetime
,@.sLineModifyUserName char(50)
,@.daLineModifyDate datetime
,@.daModifyDateTime datetime
,@.guidModifyUserId uniqueidentifier
,@.guidReferenceId uniqueidentifier
,@.useBitMap char(1) = 'F'
as
begin
Set NoCount On
Declare @.iCnt int
,@.bitMap varbinary(10)
,@.bitMapByte1 int
,@.bitMapByte2 int
,@.bitMapByte3 int
,@.bitMapByte4 int
,@.bitMapByte5 int
,@.bitMapByte6 int
,@.bitMapByte7 int
,@.bitMapByte8 int
,@.bitMapByte9 int
,@.bitMapByte10 int
If @.useBitMap = 'T' Begin
Select @.bitMapByte1 = Case When @.guidShipmentRequestLineId is null Then 0
Else Power(2,0) End
+ Case When @.guidShipmentRequestId is null Then 0 Else Power(2,1) End
+ Case When @.iLineNumber is null Then 0 Else Power(2,2) End
+ Case When @.guidLotId is null Then 0 Else Power(2,3) End
+ Case When @.sPurchaseOrderNumber is null Then 0 Else Power(2,4) End
+ Case When @.sFullLotInd is null Then 0 Else Power(2,5) End
+ Case When @.iMinimumCount is null Then 0 Else Power(2,6) End
+ Case When @.daDateNeeded is null Then 0 Else Power(2,7) End
Select @.bitMapByte2 = Case When @.dcQuantity is null Then 0 Else Power(2,0)
End
+ Case When @.guidDestinationPlantId is null Then 0 Else Power(2,1) End
+ Case When @.guidShipmentStatusId is null Then 0 Else Power(2,2) End
+ Case When @.sShippingGroup is null Then 0 Else Power(2,3) End
+ Case When @.sLineCreateUserName is null Then 0 Else Power(2,4) End
+ Case When @.daLineCreateDate is null Then 0 Else Power(2,5) End
+ Case When @.sLineModifyUserName is null Then 0 Else Power(2,6) End
+ Case When @.daLineModifyDate is null Then 0 Else Power(2,7) End
Select @.bitMapByte3 = Case When @.daModifyDateTime is null Then 0 Else
Power(2,2) End
+ Case When @.guidModifyUserId is null Then 0 Else Power(2,3) End
+ Case When @.guidReferenceId is null Then 0 Else Power(2,6) End
select @.bitmap = convert(binary(1),isNull(@.bitMapByte1,0)
)
+convert(binary(1),isNull(@.bitMapByte2,0
))
+convert(binary(1),isNull(@.bitMapByte3,0
))
+convert(binary(1),isNull(@.bitMapByte4,0
))
+convert(binary(1),isNull(@.bitMapByte5,0
))
+convert(binary(1),isNull(@.bitMapByte6,0
))
+convert(binary(1),isNull(@.bitMapByte7,0
))
+convert(binary(1),isNull(@.bitMapByte8,0
))
+convert(binary(1),isNull(@.bitMapByte9,0
))
+convert(binary(1),isNull(@.bitMapByte10,
0))
End
begin transaction
Update ShipmentRequestLine
Set [ShipmentRequestLineId] = Case isNull(substring(@.bitmap,1,1),1) & 1 when
1 Then @.guidShipmentRequestLineId Else [ShipmentRequestLineId] End
,[ShipmentRequestId] = Case isNull(substring(@.bitmap,1,1),2) & 2 when 2 Then
@.guidShipmentRequestId Else [ShipmentRequestId] End
,[LineNumber] = Case isNull(substring(@.bitmap,1,1),4) & 4 when 4 Then
@.iLineNumber Else [LineNumber] End
,[LotId] = Case isNull(substring(@.bitmap,1,1),8) & 8 when 8 Then @.guidLotId
Else [LotId] End
,[PurchaseOrderNumber] = Case isNull(substring(@.bitmap,1,1),16) & 16 when 16
Then @.sPurchaseOrderNumber Else [PurchaseOrderNumber] End
,[FullLotInd] = Case isNull(substring(@.bitmap,1,1),32) & 32 when 32 Then
@.sFullLotInd Else [FullLotInd] End
,[MinimumCount] = Case isNull(substring(@.bitmap,1,1),64) & 64 when 64 Then
@.iMinimumCount Else [MinimumCount] End
,[DateNeeded] = Case isNull(substring(@.bitmap,1,1),128) & 128 when 128 Then
@.daDateNeeded Else [DateNeeded] End
,[Quantity] = Case isNull(substring(@.bitmap,2,1),1) & 1 when 1 Then
@.dcQuantity Else [Quantity] End
,[DestinationPlantId] = Case isNull(substring(@.bitmap,2,1),2) & 2 when 2
Then @.guidDestinationPlantId Else [DestinationPlantId] End
,[ShipmentStatusId] = Case isNull(substring(@.bitmap,2,1),4) & 4 when 4 Then
@.guidShipmentStatusId Else [ShipmentStatusId] End
,[ShippingGroup] = Case isNull(substring(@.bitmap,2,1),8) & 8 when 8 Then
@.sShippingGroup Else [ShippingGroup] End
,[LineCreateUserName] = Case isNull(substring(@.bitmap,2,1),16) & 16 when 16
Then @.sLineCreateUserName Else [LineCreateUserName] End
,[LineCreateDate] = Case isNull(substring(@.bitmap,2,1),32) & 32 when 32 Then
@.daLineCreateDate Else [LineCreateDate] End
,[LineModifyUserName] = Case isNull(substring(@.bitmap,2,1),64) & 64 when 64
Then @.sLineModifyUserName Else [LineModifyUserName] End
,[LineModifyDate] = Case isNull(substring(@.bitmap,2,1),128) & 128 when 128
Then @.daLineModifyDate Else [LineModifyDate] End
,[ModifyDateTime] = isNull(@.daModifyDateTime,getDate())
,[ModifyUserId] = Case isNull(substring(@.bitmap,3,1),8) & 8 when 8 Then
@.guidModifyUserId Else [ModifyUserId] End
,[ReferenceId] = Case isNull(substring(@.bitmap,3,1),64) & 64 when 64 Then
@.guidReferenceId Else [ReferenceId] End
where ShipmentRequestLineId = @.guidShipmentRequestLineId
SELECT @.iError=@.@.ERROR, @.iCnt = @.@.rowCount
If @.iError <> 0 begin
Rollback Transaction
End
Else Begin
Commit Transaction
End
Return @.iCnt
End
"Daniel P." <DanielP@.discussions.microsoft.com> wrote in message
news:6FC61F2E-A1FD-43F1-917A-9A3BD6A7E782@.microsoft.com...
>I see an exclusive lock generated by:
> UPDATE Line #: 82 in up_updateShipmentRequestLine;1
> and a shared lock generated by
> SELECT Line #: 9 in
> up_findShipmentRequestLineByShipmentRequ
estNumberAndLineNumber;1
> You may want to look in the code in these two stored procedures (?). You
> may
> be accessing tables in reverse order.
> Probably the fix should go into the [up_updateShipmentRequestLine].
> If you have
> SELECT @.bExists = Field1 FROM Table1
> and then
> IF @.bExists = someVal
> UPDATE Table1 ...
> Instead do first:
> UPDATE Table1 SET Field1 = @.Val1
> IF @.@.ROWCOUNT == 0
> INSERT ...
> Ok. I'm making assumptions here since I do not know your code but the rule
> is that you want to get the highest lock since the beginning of the sproc
> and
> there are many ways you can do that. One is above.
> If you do not want to change the logic of the code, place a Locking Hints
> using
> WITH( ... )
> for example WITH(UPDLOCK).
> If you want more details then you need to post some code so I can point
> you
> exactly to code that generates the deadlock.
>
> "JI" wrote:
>|||Set the transaction isolation level as serializable or add the hint
WITH(TABLOCKX) and see if you still get the deadlock.
"JI" wrote:
> The update proc is one that I wrote a proc generator to create. It does a
> simple update...it does not access any other or the same table before the
> update. The interesting thing with the deadlock trace information is the
> index that is says deadlocks is a statistic. One created by SQL Server...
> I will post the update shipment request line proc below anyway.
> alter proc [dbo].[up_updateShipmentRequestLine]
> @.iError int OUTPUT
> ,@.guidShipmentRequestLineId uniqueidentifier
> ,@.guidShipmentRequestId uniqueidentifier
> ,@.iLineNumber int
> ,@.guidLotId uniqueidentifier
> ,@.sPurchaseOrderNumber char(50)
> ,@.sFullLotInd char(1)
> ,@.iMinimumCount int
> ,@.daDateNeeded datetime
> ,@.dcQuantity decimal(18,0)
> ,@.guidDestinationPlantId uniqueidentifier
> ,@.guidShipmentStatusId uniqueidentifier
> ,@.sShippingGroup char(3)
> ,@.sLineCreateUserName char(50)
> ,@.daLineCreateDate datetime
> ,@.sLineModifyUserName char(50)
> ,@.daLineModifyDate datetime
> ,@.daModifyDateTime datetime
> ,@.guidModifyUserId uniqueidentifier
> ,@.guidReferenceId uniqueidentifier
> ,@.useBitMap char(1) = 'F'
> as
> begin
> Set NoCount On
> Declare @.iCnt int
> ,@.bitMap varbinary(10)
> ,@.bitMapByte1 int
> ,@.bitMapByte2 int
> ,@.bitMapByte3 int
> ,@.bitMapByte4 int
> ,@.bitMapByte5 int
> ,@.bitMapByte6 int
> ,@.bitMapByte7 int
> ,@.bitMapByte8 int
> ,@.bitMapByte9 int
> ,@.bitMapByte10 int
> If @.useBitMap = 'T' Begin
> Select @.bitMapByte1 = Case When @.guidShipmentRequestLineId is null Then 0
> Else Power(2,0) End
> + Case When @.guidShipmentRequestId is null Then 0 Else Power(2,1) End
> + Case When @.iLineNumber is null Then 0 Else Power(2,2) End
> + Case When @.guidLotId is null Then 0 Else Power(2,3) End
> + Case When @.sPurchaseOrderNumber is null Then 0 Else Power(2,4) End
> + Case When @.sFullLotInd is null Then 0 Else Power(2,5) End
> + Case When @.iMinimumCount is null Then 0 Else Power(2,6) End
> + Case When @.daDateNeeded is null Then 0 Else Power(2,7) End
> Select @.bitMapByte2 = Case When @.dcQuantity is null Then 0 Else Power(2,0)
> End
> + Case When @.guidDestinationPlantId is null Then 0 Else Power(2,1) End
> + Case When @.guidShipmentStatusId is null Then 0 Else Power(2,2) End
> + Case When @.sShippingGroup is null Then 0 Else Power(2,3) End
> + Case When @.sLineCreateUserName is null Then 0 Else Power(2,4) End
> + Case When @.daLineCreateDate is null Then 0 Else Power(2,5) End
> + Case When @.sLineModifyUserName is null Then 0 Else Power(2,6) End
> + Case When @.daLineModifyDate is null Then 0 Else Power(2,7) End
> Select @.bitMapByte3 = Case When @.daModifyDateTime is null Then 0 Else
> Power(2,2) End
> + Case When @.guidModifyUserId is null Then 0 Else Power(2,3) End
> + Case When @.guidReferenceId is null Then 0 Else Power(2,6) End
> select @.bitmap = convert(binary(1),isNull(@.bitMapByte1,0)
)
> +convert(binary(1),isNull(@.bitMapByte2,0
))
> +convert(binary(1),isNull(@.bitMapByte3,0
))
> +convert(binary(1),isNull(@.bitMapByte4,0
))
> +convert(binary(1),isNull(@.bitMapByte5,0
))
> +convert(binary(1),isNull(@.bitMapByte6,0
))
> +convert(binary(1),isNull(@.bitMapByte7,0
))
> +convert(binary(1),isNull(@.bitMapByte8,0
))
> +convert(binary(1),isNull(@.bitMapByte9,0
))
> +convert(binary(1),isNull(@.bitMapByte10,
0))
> End
>
> begin transaction
> Update ShipmentRequestLine
> Set [ShipmentRequestLineId] = Case isNull(substring(@.bitmap,1,1),1) & 1 when
> 1 Then @.guidShipmentRequestLineId Else [ShipmentRequestLineId] End
> ,[ShipmentRequestId] = Case isNull(substring(@.bitmap,1,1),2) & 2 when 2 Then
> @.guidShipmentRequestId Else [ShipmentRequestId] End
> ,[LineNumber] = Case isNull(substring(@.bitmap,1,1),4) & 4 when 4 Then
> @.iLineNumber Else [LineNumber] End
> ,[LotId] = Case isNull(substring(@.bitmap,1,1),8) & 8 when 8 Then @.guidLotId
> Else [LotId] End
> ,[PurchaseOrderNumber] = Case isNull(substring(@.bitmap,1,1),16) & 16 when 16
> Then @.sPurchaseOrderNumber Else [PurchaseOrderNumber] End
> ,[FullLotInd] = Case isNull(substring(@.bitmap,1,1),32) & 32 when 32 Then
> @.sFullLotInd Else [FullLotInd] End
> ,[MinimumCount] = Case isNull(substring(@.bitmap,1,1),64) & 64 when 64 Then
> @.iMinimumCount Else [MinimumCount] End
> ,[DateNeeded] = Case isNull(substring(@.bitmap,1,1),128) & 128 when 128 Then
> @.daDateNeeded Else [DateNeeded] End
> ,[Quantity] = Case isNull(substring(@.bitmap,2,1),1) & 1 when 1 Then
> @.dcQuantity Else [Quantity] End
> ,[DestinationPlantId] = Case isNull(substring(@.bitmap,2,1),2) & 2 when 2
> Then @.guidDestinationPlantId Else [DestinationPlantId] End
> ,[ShipmentStatusId] = Case isNull(substring(@.bitmap,2,1),4) & 4 when 4 Then
> @.guidShipmentStatusId Else [ShipmentStatusId] End
> ,[ShippingGroup] = Case isNull(substring(@.bitmap,2,1),8) & 8 when 8 Then
> @.sShippingGroup Else [ShippingGroup] End
> ,[LineCreateUserName] = Case isNull(substring(@.bitmap,2,1),16) & 16 when 16
> Then @.sLineCreateUserName Else [LineCreateUserName] End
> ,[LineCreateDate] = Case isNull(substring(@.bitmap,2,1),32) & 32 when 32 Then
> @.daLineCreateDate Else [LineCreateDate] End
> ,[LineModifyUserName] = Case isNull(substring(@.bitmap,2,1),64) & 64 when 64
> Then @.sLineModifyUserName Else [LineModifyUserName] End
> ,[LineModifyDate] = Case isNull(substring(@.bitmap,2,1),128) & 128 when 128
> Then @.daLineModifyDate Else [LineModifyDate] End
> ,[ModifyDateTime] = isNull(@.daModifyDateTime,getDate())
> ,[ModifyUserId] = Case isNull(substring(@.bitmap,3,1),8) & 8 when 8 Then
> @.guidModifyUserId Else [ModifyUserId] End
> ,[ReferenceId] = Case isNull(substring(@.bitmap,3,1),64) & 64 when 64 Then
> @.guidReferenceId Else [ReferenceId] End
> where ShipmentRequestLineId = @.guidShipmentRequestLineId
> SELECT @.iError=@.@.ERROR, @.iCnt = @.@.rowCount
>
> If @.iError <> 0 begin
> Rollback Transaction
> End
> Else Begin
> Commit Transaction
> End
> Return @.iCnt
> End
> "Daniel P." <DanielP@.discussions.microsoft.com> wrote in message
> news:6FC61F2E-A1FD-43F1-917A-9A3BD6A7E782@.microsoft.com...
>
>
0x77D6D550)