"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 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_findShipmentRequestLineByShipmentRequestNumberAndLineNumber;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)
>
>
>

"Daniel P." <Dani***@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_findShipmentRequestLineByShipmentRequestNumberAndLineNumber;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:
>
>> 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_findShipmentRequestLineByShipmentRequestNumberAndLineNumber;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)
>>
>>
>>

"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." <Dani***@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_findShipmentRequestLineByShipmentRequestNumberAndLineNumber;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:
> >
> >> 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_findShipmentRequestLineByShipmentRequestNumberAndLineNumber;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)
> >>
> >>
> >>
>
>
>