deAttach database: lock

Showing posts with label lock. Show all posts

Tuesday, March 27, 2012

DeadLocking

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

Deadlocked on the same resource (same index)

0x7C1615D8)
Value:0x52dd7340 Cost

0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec

0x5A2E5578)
Value:0x52fa6780 Cost

0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec

0x7C1615D8)
Value:0x52dd7340 Cost

0/0)
Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.

> It almost seems like the two processes are requesting locks (that they
> already have?) and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.

> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

Deadlocked on the same resource (same index)

0x7C1615D8)
Value:0x52dd7340 Cost

0x5A2E5578)
Value:0x52fa6780 Cost

0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec

0x7C1615D8)
Value:0x52dd7340 Cost

0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.

> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.

> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robertsql

Deadlocked on the same resource (same index)

I'm seeing a deadlock issue that traces out the following 1204 report
below. You can see that one process is granted a shared lock (Mode: S)
on the index and another process is granted an exclusive lock on the
same index.
How is that possible? What scenarios could lead to this? I know that
deadlocks can happen over the same resource when one or two processes
are trying to raise the isolation level, but that doesn't seem to be
the case here.
It almost seems like the two processes are requesting locks (that they
already have') and waiting for the other to release. What scenarios
could lead to this?
Unfortunately I can't show any code. Here is the trace file:
Michael Swart
Wait-for graph
Node:1
KEY: 7:2133582639:3 (180223bc5cb5) CleanCnt:1 Mode: X Flags: 0x0
Grant List 3::
Owner:0x52e00720 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:98
ECID:0
SPID: 98 ECID: 0 Statement Type: UPDATE Line #: 34
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)
Node:2
KEY: 7:2133582639:3 (a80172417f28) CleanCnt:1 Mode: S Flags: 0x0
Grant List 0::
Owner:0x52e2e7c0 Mode: S Flg:0x0 Ref:0 Life:00000001 SPID:93
ECID:0
SPID: 93 ECID: 0 Statement Type: INSERT Line #: 2
Input Buf: Language Event: EXEC LoadDataPartitions
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:98 ECID:0 Ec:(0x5A2E5578)
Value:0x52fa6780 Cost:(0/1129C)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:93 ECID:0 Ec:(0x7C1615D8)
Value:0x52dd7340 Cost:(0/0)Michael Swart wrote:
> I'm seeing a deadlock issue that traces out the following 1204 report
> below. You can see that one process is granted a shared lock (Mode: S)
> on the index and another process is granted an exclusive lock on the
> same index.
> How is that possible? What scenarios could lead to this? I know that
> deadlocks can happen over the same resource when one or two processes
> are trying to raise the isolation level, but that doesn't seem to be
> the case here.
The "classic" deadlock scenario is where two processes try to acquire
locks on two resources in different order.
> It almost seems like the two processes are requesting locks (that they
> already have') and waiting for the other to release. What scenarios
> could lead to this?
Different order of table accesses within two transactions for example.
> Unfortunately I can't show any code. Here is the trace file:
> Michael Swart
<snip/>
Unfortunately I'm no expert at trace file reading. But you can try to
catch the deadlock with Enterprise Manager. Then you can directly see SQL
statements that lead to the deadlock. HTH.
Kind regards
robert

deadlocked on lock resources. SQL Server 2000

Hi, i am getting this error when i am running a stored procedure.

Transaction (Process ID XXXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

i think so it is getting this error becasue it blocking it self at one point in the SP

DECLARE cty_Cursor CURSOR FOR
SELECT Country FROM TB_Country

declare @.cty varchar(2)

OPEN cty_Cursor;
FETCH NEXT FROM cty_Cursor into @.cty;
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC SP_DO_SOMETHING @.cty
FETCH NEXT FROM cty_Cursor into @.cty;
END;
CLOSE cty_Cursor;
DEALLOCATE cty_Cursor;

i think so it calls the SP then before SP finsih its working it calls it back from cursor with other argument.

how we can make it sure it finish it execution before it is being called again. i think so we need some sort of lock here but i am not able to find right solution . please anyone suggest something.

Regards,

Haroon

what happens when you run the stored procedure outside of the cursor?

what's in the stored procedure?

deadlocked on lock

What can I do to prevent this type of error messages occuring? Theese errros
are coming basicly from readonly database transaction. One part of our
massageboard on IIS. What causes this erros to occur? There is quite a lot
of insertrs and updates happening at the same time users are reading thos
message board pages... But this page is read only.
Transaction (Process ID 125) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.
TLehtinen wrote:
> What can I do to prevent this type of error messages occuring? Theese
> errros are coming basicly from readonly database transaction. One
> part of our massageboard on IIS. What causes this erros to occur?
> There is quite a lot of insertrs and updates happening at the same
> time users are reading thos message board pages... But this page is
> read only.
> Transaction (Process ID 125) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.
- Minimize yout transaction duration - do not leave any transactions
open on a client - BEGIN TRAN... Exec T-SQL... COMMIT TRAN as quickly as
possible
- When you delete, insert or update from tables make sure to access
tables in the same order in all procedures
- Use Profiler to help determine your long running SQL statements - add
indexes where needed to address performance concerns
- If all else fails, consider using a NOLOCK hint on your SELECT
statements, assuming your application can deal with the possibility of
dirty reads
Here's more information:
http://www.sql-server-performance.com/deadlocks.asp
David Gugick - SQL Server MVP
Quest Software

deadlocked on lock

What can I do to prevent this type of error messages occuring? Theese errros
are coming basicly from readonly database transaction. One part of our
massageboard on IIS. What causes this erros to occur? There is quite a lot
of insertrs and updates happening at the same time users are reading thos
message board pages... But this page is read only.
Transaction (Process ID 125) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.TLehtinen wrote:
> What can I do to prevent this type of error messages occuring? Theese
> errros are coming basicly from readonly database transaction. One
> part of our massageboard on IIS. What causes this erros to occur?
> There is quite a lot of insertrs and updates happening at the same
> time users are reading thos message board pages... But this page is
> read only.
> Transaction (Process ID 125) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.
- Minimize yout transaction duration - do not leave any transactions
open on a client - BEGIN TRAN... Exec T-SQL... COMMIT TRAN as quickly as
possible
- When you delete, insert or update from tables make sure to access
tables in the same order in all procedures
- Use Profiler to help determine your long running SQL statements - add
indexes where needed to address performance concerns
- If all else fails, consider using a NOLOCK hint on your SELECT
statements, assuming your application can deal with the possibility of
dirty reads
Here's more information:
http://www.sql-server-performance.com/deadlocks.asp
David Gugick - SQL Server MVP
Quest Software

deadlocked on lock

What can I do to prevent this type of error messages occuring? Theese errros
are coming basicly from readonly database transaction. One part of our
massageboard on IIS. What causes this erros to occur? There is quite a lot
of insertrs and updates happening at the same time users are reading thos
message board pages... But this page is read only.
Transaction (Process ID 125) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.TLehtinen wrote:
> What can I do to prevent this type of error messages occuring? Theese
> errros are coming basicly from readonly database transaction. One
> part of our massageboard on IIS. What causes this erros to occur?
> There is quite a lot of insertrs and updates happening at the same
> time users are reading thos message board pages... But this page is
> read only.
> Transaction (Process ID 125) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.
- Minimize yout transaction duration - do not leave any transactions
open on a client - BEGIN TRAN... Exec T-SQL... COMMIT TRAN as quickly as
possible
- When you delete, insert or update from tables make sure to access
tables in the same order in all procedures
- Use Profiler to help determine your long running SQL statements - add
indexes where needed to address performance concerns
- If all else fails, consider using a NOLOCK hint on your SELECT
statements, assuming your application can deal with the possibility of
dirty reads
Here's more information:
http://www.sql-server-performance.com/deadlocks.asp
David Gugick - SQL Server MVP
Quest Softwaresql

Sunday, March 25, 2012

Deadlock victim (-2147467259)

Error number: -2147467259
Error description: Transaction (Process ID xxx) was deadlocked on lock
resources with another process and has been chosen as the deadlock victim.
Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
SQLState = 40001, Native Error = 1205.
When SQLServer reports this, it would be REALLY helpful if it also provided
the following information:
The SQL that was associated with this Process ID
The SQL that was associated with the Process ID that caused the deadlock
(but was not terminated).
This would greatly improve a developer's ability to debug this issue!
Ideally, released in a patch for SQLServer 2000....
This is a suggestion to Microsoft (vote for this if you agree)
--
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?mid=cb2097f1-e3c0-49b8-bf62-533a51932bad&dg=microsoft.public.sqlserver.serverTry to turn trace flag 1204 on: DBCC TRACEON(1204).
That will cause SQL Server to write an extended info on every deadlock
situation to SQL Server error log. Hopefully, that's what you want
"Griff" <Griff@.discussions.microsoft.com> wrote in message
news:CB2097F1-E3C0-49B8-BF62-533A51932BAD@.microsoft.com...
> Error number: -2147467259
> Error description: Transaction (Process ID xxx) was deadlocked on lock
> resources with another process and has been chosen as the deadlock victim.
> Rerun the transaction., Source = Microsoft OLE DB Provider for SQL Server,
> SQLState = 40001, Native Error = 1205.
> When SQLServer reports this, it would be REALLY helpful if it also
> provided
> the following information:
> The SQL that was associated with this Process ID
> The SQL that was associated with the Process ID that caused the deadlock
> (but was not terminated).
> This would greatly improve a developer's ability to debug this issue!
> Ideally, released in a patch for SQLServer 2000....sql

Deadlock transaction

I have a customer using our program with SQL server and is
occasionally getting a "Transaction (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?On Jun 22, 8:17 am, Altman <balt...@.easy-automation.comwrote:

Quote:

Originally Posted by

Read "Analyzing Deadlocks with SQL Server Profiler" in BOL.

http://sqlserver-tips.blogspot.com/|||Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

"Altman" <baltman@.easy-automation.comwrote in message
news:1182518265.867797.118630@.k79g2000hse.googlegr oups.com...

Quote:

Originally Posted by

>I have a customer using our program with SQL server and is
occasionally getting a "Transaction (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?
>

|||Oscar Santiesteban (o_santiesteban@.bellsouth.net) writes:

Quote:

Originally Posted by

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Jun 23, 4:10 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Oscar Santiesteban (o_santieste...@.bellsouth.net) writes:

Quote:

Originally Posted by

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

>
This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I Think that the nolock will work for me. I understand the
implications and I think that my program will be able to handle it.
What I would've liked better was something like read committed but
didn't lock records.|||On Jun 26, 10:30 am, Altman <balt...@.easy-automation.comwrote:

Quote:

Originally Posted by

On Jun 23, 4:10 am, Erland Sommarskog <esq...@.sommarskog.sewrote:
>
>
>

Quote:

Originally Posted by

Oscar Santiesteban (o_santieste...@.bellsouth.net) writes:

Quote:

Originally Posted by

Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.

Quote:

Originally Posted by

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

Quote:

Originally Posted by

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

>
I Think that the nolock will work for me. I understand the
implications and I think that my program will be able to handle it.
What I would've liked better was something like read committed but
didn't lock records.

If you are on 2005, consider snapshot isolation.

http://sqlserver-tips.blogspot.com

Thursday, March 22, 2012

Deadlock problem

Can somebody help me.
I spend 2 days on this problem, and i stuck. i`ve got this error:
Transaction (Process ID ***) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction...
There is a trace:
2006-06-01 23:36:34.22 spid57 DBCC TRACEON 1204, server process ID
(SPID) 57.
2006-06-01 23:36:34.22 spid57 DBCC TRACEON 3605, server process ID
(SPID) 57.
2006-06-01 23:36:34.22 spid57 DBCC TRACEON -1, server process ID
(SPID) 57.
2006-06-01 23:37:43.24 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Wait-for graph
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Node:1
2006-06-01 23:37:43.24 spid4 KEY: 8:862678171:1 (3c0209b5b29f)
CleanCnt:2 Mode: X Flags: 0x0
2006-06-01 23:37:43.24 spid4 Grant List 0::
2006-06-01 23:37:43.24 spid4 Owner:0x92afca40 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:43.24 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:43.24 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:43.24 spid4 Requested By:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
2006-06-01 23:37:43.24 spid4
2006-06-01 23:37:43.24 spid4 Node:2
2006-06-01 23:37:43.24 spid4 KEY: 8:894678285:1 (3c0209b5b29f)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:43.24 spid4 Grant List 3::
2006-06-01 23:37:43.24 spid4 Owner:0x92975500 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:107 ECID:0
2006-06-01 23:37:43.24 spid4 SPID: 107 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:43.24 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:43.24 spid4 Requested By:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x84f95ec0 Cost:(0/254)
2006-06-01 23:37:43.24 spid4 Victim Resource Owner:
2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Wait-for graph
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:1
2006-06-01 23:37:55.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 3::
2006-06-01 23:37:55.74 spid4 Owner:0x9339cb20 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:164 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 164 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:2
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Wait List:
2006-06-01 23:37:55.74 spid4 Owner:0x93212e40 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:142 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 142 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:3
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 0::
2006-06-01 23:37:55.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:142 ECID:0 Ec:(0x955A1370) Value:0x93212e40 Cost:(0/0)
2006-06-01 23:37:55.74 spid4 Victim Resource Owner:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:142 ECID:0 Ec:(0x955A1370) Value:0x93212e40 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Wait-for graph
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:1
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 0::
2006-06-01 23:37:55.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:157 ECID:0 Ec:(0x93353370) Value:0x9339dd60 Cost:(0/0)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:2
2006-06-01 23:37:55.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:37:55.74 spid4 Grant List 3::
2006-06-01 23:37:55.74 spid4 Owner:0x9339cb20 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:164 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 164 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:37:55.74 spid4
2006-06-01 23:37:55.74 spid4 Node:3
2006-06-01 23:37:55.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:37:55.74 spid4 Wait List:
2006-06-01 23:37:55.74 spid4 Owner:0x9339dd60 Mode: S
Flg:0x0 Ref:1 Life:02000000 SPID:157 ECID:0
2006-06-01 23:37:55.74 spid4 SPID: 157 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:37:55.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:37:55.74 spid4 Requested By:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:37:55.74 spid4 Victim Resource Owner:
2006-06-01 23:37:55.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:164 ECID:0 Ec:(0x952BD370) Value:0x92974fc0 Cost:(0/0)
2006-06-01 23:38:00.74 spid4
Deadlock encountered ... Printing deadlock information
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Wait-for graph
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:1
2006-06-01 23:38:00.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:38:00.74 spid4 Grant List 0::
2006-06-01 23:38:00.74 spid4 Owner:0x9339ddc0 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 122 ECID: 0 Statement Type:
UPDATE Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:157 ECID:0 Ec:(0x93353370) Value:0x9339dd60 Cost:(0/0)
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:2
2006-06-01 23:38:00.74 spid4 KEY: 8:1266155606:1 (fd01799b0761)
CleanCnt:2 Mode: S Flags: 0x0
2006-06-01 23:38:00.74 spid4 Grant List 3::
2006-06-01 23:38:00.74 spid4 Owner:0x9339d7a0 Mode: S
Flg:0x0 Ref:1 Life:00000000 SPID:130 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 130 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: Language Event: SELECT
round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
(sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
complex, so.New_name FROM New_ServiceOrderProduct sop INNER
JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x93216460 Cost:(0/254)
2006-06-01 23:38:00.74 spid4
2006-06-01 23:38:00.74 spid4 Node:3
2006-06-01 23:38:00.74 spid4 KEY: 8:1234155492:1 (fd01799b0761)
CleanCnt:3 Mode: X Flags: 0x0
2006-06-01 23:38:00.74 spid4 Wait List:
2006-06-01 23:38:00.74 spid4 Owner:0x9339dd60 Mode: S
Flg:0x0 Ref:1 Life:02000000 SPID:157 ECID:0
2006-06-01 23:38:00.74 spid4 SPID: 157 ECID: 0 Statement Type:
SELECT Line #: 1
2006-06-01 23:38:00.74 spid4 Input Buf: RPC Event:
sp_executesql;1
2006-06-01 23:38:00.74 spid4 Requested By:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode:
S SPID:130 ECID:0 Ec:(0x95807370) Value:0x9339d860 Cost:(0/0)
2006-06-01 23:38:00.74 spid4 Victim Resource Owner:
2006-06-01 23:38:00.74 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:130 ECID:0 Ec:(0x95807370) Value:0x9339d860 Cost:(0/0)(gaploid@.yandex.ru) writes:
> Can somebody help me.
> I spend 2 days on this problem, and i stuck. i`ve got this error:
> Transaction (Process ID ***) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction...
>...
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Wait-for graph
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Node:1
> 2006-06-01 23:37:43.24 spid4 KEY: 8:862678171:1 (3c0209b5b29f)
> CleanCnt:2 Mode: X Flags: 0x0
> 2006-06-01 23:37:43.24 spid4 Grant List 0::
> 2006-06-01 23:37:43.24 spid4 Owner:0x92afca40 Mode: X
> Flg:0x0 Ref:0 Life:02000000 SPID:122 ECID:0
> 2006-06-01 23:37:43.24 spid4 SPID: 122 ECID: 0 Statement Type:
> UPDATE Line #: 1
> 2006-06-01 23:37:43.24 spid4 Input Buf: RPC Event:
> sp_executesql;1
> 2006-06-01 23:37:43.24 spid4 Requested By:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
> S SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
> 2006-06-01 23:37:43.24 spid4
> 2006-06-01 23:37:43.24 spid4 Node:2
> 2006-06-01 23:37:43.24 spid4 KEY: 8:894678285:1 (3c0209b5b29f)
> CleanCnt:2 Mode: S Flags: 0x0
> 2006-06-01 23:37:43.24 spid4 Grant List 3::
> 2006-06-01 23:37:43.24 spid4 Owner:0x92975500 Mode: S
> Flg:0x0 Ref:1 Life:00000000 SPID:107 ECID:0
> 2006-06-01 23:37:43.24 spid4 SPID: 107 ECID: 0 Statement Type:
> SELECT Line #: 1
> 2006-06-01 23:37:43.24 spid4 Input Buf: Language Event: SELECT
> round (sum(sop.new_totalpriceusd),2) AS totalUSD, round
> (sum(sop.new_totalpricerur),2) AS totalRUR, co.New_ComplexOrderId AS
> complex, so.New_name FROM New_ServiceOrderProduct sop INNER
> JOIN New_ServiceOrder so ON sop.New_ServiceOrderId = s
> 2006-06-01 23:37:43.24 spid4 Requested By:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode:
> X SPID:122 ECID:0 Ec:(0x95A5D370) Value:0x84f95ec0 Cost:(0/254)
> 2006-06-01 23:37:43.24 spid4 Victim Resource Owner:
> 2006-06-01 23:37:43.24 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:107 ECID:0 Ec:(0x95825370) Value:0x933e50a0 Cost:(0/0)
> 2006-06-01 23:37:55.74 spid4
Without knowing tables, and not seeing the text of the UDPATE statement
is a bit difficult to say for sure.
But there is an UPDATE statement, and the locks involve the clustered
index of two different tables. That would indicate that the UPDATE
includes a cascading foreign key - or a trigger. Or that it's part
of a longer transaction.
As a starting point, post the full text of the SELECT statement,
the full text of the UPDATE statement and the table definitions,
including constraints and indexes for the tables. Also, include
the output of
SELCECT object_name(894678285), object_name(862678171)
I assume that you know which database is database 8.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I solve this problem. in my select statement i have a inner join to
another table, so i simply redisign my select query and make it without
inner join.
thx for your answer.

> Without knowing tables, and not seeing the text of the UDPATE statement
> is a bit difficult to say for sure.
> But there is an UPDATE statement, and the locks involve the clustered
> index of two different tables. That would indicate that the UPDATE
> includes a cascading foreign key - or a trigger. Or that it's part
> of a longer transaction.
> As a starting point, post the full text of the SELECT statement,
> the full text of the UPDATE statement and the table definitions,
> including constraints and indexes for the tables. Also, include
> the output of
> SELCECT object_name(894678285), object_name(862678171)
> I assume that you know which database is database 8.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Wednesday, March 21, 2012

Deadlock on TAB lock

I have a small database and a smalll table ( Table ID=565577053,with two
indexes on this table). when more than one user connected, I got the deadlock on the index KEY and PAGE lock. I setup index with "DisallowRowLock" and "DisallowPageLock" , seems kill the index KEY and PAGE lock problem, but I got this TAB lock situation instead as following:

2006-01-18 09:51:37.87 spid4 -----------
2006-01-18 09:51:37.87 spid4 Starting deadlock search 15

Deadlock encountered ... Printing deadlock information
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Wait-for graph
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:1
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03ba0 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:77 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 77 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:64 ECID:0 Ec:(0x4AEAF530) Value:0x42c0df00 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:2
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03e00 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:64 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 64 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4 Victim Resource Owner:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 End deadlock search 15 ... a deadlock was
found.
2006-01-18 09:51:37.87 spid4 -----------

How can I get rid of this deadlock without changing the application
code(without using set the isolation level or NOLOCK hint). When I load more
data, will this problem goes away?
Any kind of help will be appreciate.

HansonHow can I get rid of this deadlock without changing the application
code(without using set the isolation level or NOLOCK hint). When I load more
data, will this problem goes away?

The short answers ... You can't and no

More information:
It is the application causing the deadlock, but you only exacerbated the problem by disallowing the row and page locking mechanism. SQL Server will try to take the lowest level lock it needs to accomplish the task. If I have a table with 1 million rows and I need to update 1 row, SQL Server will lock the row (in most cases). If I disallow row locks, it will have to lock the page ... so if there are 100 rows on an 8K page, 100 rows are locked instead of 1. If I then disallow page locks, the next level is a TABLE lock (TAB). So you have really screwed yourself by doing that.

Now to the deadlock ... spid XX locks row 12345 in Table A and needs to lock row 23456 in Table B. spif YY has locked row 23456 in Table B and needs to lock row 12345 in Table A. Each spid is competing for the exact same resource. SQL Server resolves the problem by killing and rolling back one of the spids.

Now your problem could be a spid locking a resource longer than needed, or allowing a user to hold a lock while going off to lunch, or maybe the app accesses the tables in two different sequences ... whatever the problem, it's the app and not the database.

Long term solution ... allow sqlserver to determine the proper locking mechanism and fix the app!|||The short answers ... You can't and no

More information:
It is the application causing the deadlock, but you only exacerbated the problem by disallowing the row and page locking mechanism. SQL Server will try to take the lowest level lock it needs to accomplish the task. If I have a table with 1 million rows and I need to update 1 row, SQL Server will lock the row (in most cases). If I disallow row locks, it will have to lock the page ... so if there are 100 rows on an 8K page, 100 rows are locked instead of 1. If I then disallow page locks, the next level is a TABLE lock (TAB). So you have really screwed yourself by doing that.

Now to the deadlock ... spid XX locks row 12345 in Table A and needs to lock row 23456 in Table B. spif YY has locked row 23456 in Table B and needs to lock row 12345 in Table A. Each spid is competing for the exact same resource. SQL Server resolves the problem by killing and rolling back one of the spids.

Now your problem could be a spid locking a resource longer than needed, or allowing a user to hold a lock while going off to lunch, or maybe the app accesses the tables in two different sequences ... whatever the problem, it's the app and not the database.

Long term solution ... allow sqlserver to determine the proper locking mechanism and fix the app!

tomh53:

Your information is very helpful.
I agree with you that the proper fix should be done on the application rather than database. it is a third party application so I don't have any source code, now the only thing I can do is escalate to them and force them to update the code.

Another approch I like to do is load more data in, make the deadlock less chance happen.

Thanks,

HANSON|||Smells like Access and you're returning all the rows to a form...which should be a shared lock.

We need more background on the application and what you're doing...which doesn't sound good...|||it is a third party application so I don't have any source code, now the only thing I can do is escalate to them and force them to update the code.

You have a 3rd party code that you bought, and it causes deadlocks? Make them fix the damn code.

Can you let us know who they are? They got a home page?|||Why would using this ever be advantageous? I can see where DisAllowPageLock might be helpful, but not this one.|||hmmm...load more data so this won't happen...

You're hired!

Make sure you keep the deep fryers clean when you clock out|||DisAllowRowLock would use fewer lock resources. Systems that have correctly designed applications hitting them and don't have deadlock issues can really benefit from this.|||DisAllowRowLock would use fewer lock resources. Systems that have correctly designed applications hitting them and don't have deadlock issues can really benefit from this.

Really? Would you post your sources for review?|||I don't have current sources, or hard number, but some experience back when it was debated about row-level locking entering SQL Server, and when to use it. Search for "lock escalation" in BOL. Each lock is a small amount of memory, and is something for the server to manage. Normally, the server handles lock escalation in a fairly intelligent manner, but the option is there if you need it. In almost all circumstances letting the server manage the overhead is acceptable. Looking at my post, I shouldn't have implied a big gain. Still the post is correct in that that:

1) DisAllowRowLock would use fewer lock resources.

But

2) Correctly designed applications must be used, or you will have deadlocking issues.

You can easily (as the initial poster did) cause more problems by fiddling with the lock level.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers

Monday, March 19, 2012

Deadlock diagnosis

We are experiencing the following deadlock error on a SQL Server 2000
system:
"Transaction (Process ID 53) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction."
I've done some research to see how I can track where the deadlock is
occurring and I've come across a couple of recommendations to use DBCC
traces 1204 & 1205. I'm trying to track where exactly in our system this is
occurring as we are interacting with 10 different tables within a
transaction.
Can someone explain the full process to using tracing to diagnose deadlocks
as I've never done this before? Also, is this the best option for SQL
Server 2000?
Thanks in advance.Cipher wrote:
> We are experiencing the following deadlock error on a SQL Server 2000
> system:
> "Transaction (Process ID 53) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun
> the transaction."
> I've done some research to see how I can track where the deadlock is
> occurring and I've come across a couple of recommendations to use DBCC
> traces 1204 & 1205. I'm trying to track where exactly in our system
> this is occurring as we are interacting with 10 different tables
> within a transaction.
> Can someone explain the full process to using tracing to diagnose
> deadlocks as I've never done this before? Also, is this the best
> option for SQL Server 2000?
> Thanks in advance.
Using trace flags and tracing are two different things. To use trace
flags, you need to turn them on using DBCC TRACEON/TRACEOFF. You can
send deadlock info to the error log using:
DBCC TRACEON (1204,3605,-1)
You can also monitor deadlocks using Profiler or running a server-side
trace and including the two deadlock events (Lock:Deadlock and
Lock:DeadlockChain). If you use Profiler to monitor these events (even
Lock:Deadlock by itself will do) along with using the 1204 trace flag,
you'll know when to look in the error log.
If you want to use a server-side trace instead of trace flags, you'll
need to include a few SQL/SP events to make sure you know what each SPID
involved in the deadlock was running at the time. You can do this, but
you should use a server-side trace instead of using Profiler because of
the added event collection activity. Add SQL:StmtStarting, RPC:Starting,
and SP:StmtStarting to the two deadlock events for comprehensive event
collection. If you can filter these results to a set of users or
applications you know are having the problem, that can limit the
collection somewhat.
To run a server-side trace, you can create the trace in Profiler and
script it out using the File - Script Trace option. You need to have the
trace save activity to a file on the server itself (not a network
share). Once it's running, you'll need to stop it manually using
sp_trace_setstatus. You can view the results in Profiler orby using the
fn_trace_gettable function.
I would try the small Profiler trace along with trace flag 1204 first.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi
In addition to Davids post check out
http://support.microsoft.com/kb/271509/EN-US/
John
"Cipher" wrote:

> We are experiencing the following deadlock error on a SQL Server 2000
> system:
> "Transaction (Process ID 53) was deadlocked on lock resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction
."
> I've done some research to see how I can track where the deadlock is
> occurring and I've come across a couple of recommendations to use DBCC
> traces 1204 & 1205. I'm trying to track where exactly in our system this
is
> occurring as we are interacting with 10 different tables within a
> transaction.
> Can someone explain the full process to using tracing to diagnose deadlock
s
> as I've never done this before? Also, is this the best option for SQL
> Server 2000?
> Thanks in advance.
>
>
>|||When you are able to get the trace dump into the log file, you might want to
check "Troubleshooting Deadlocks" in BOL
inorder to interpret the dump file.
Gopi
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:90FB9903-EECE-4BC8-87FF-5FF09AFEFB3B@.microsoft.com...
> Hi
> In addition to Davids post check out
> http://support.microsoft.com/kb/271509/EN-US/
> John
> "Cipher" wrote:
>

deadlock detection

Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?
Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
David Gugick
Imceda Software
www.imceda.com

deadlock detection

Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
David Gugick
Imceda Software
www.imceda.com

deadlock detection

Hi,
when looking at the SQL Profiler during deadlock,
I can see only one objectid.
in a dead lock there should be at list 2 resources.
how can I know which resources are participating in te
deadlock event?Have a look at trace flag 1204, also check this out:
FIX: Deadlock Information Reported with SQL Server 2000 Profiler Is
Incorrect
http://support.microsoft.com/?id=282749
Tips for Reducing SQL Server Deadlocks
http://www.sql-server-performance.com/deadlocks.asp
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?|||Amit wrote:
> Hi,
> when looking at the SQL Profiler during deadlock,
> I can see only one objectid.
> in a dead lock there should be at list 2 resources.
> how can I know which resources are participating in te
> deadlock event?
You need to add the DeadlockChain event and then read the article Mark
posted about the incorrect reporting of SPIDs on some of the events (the
correct SPID is really in the TextData column).
--
David Gugick
Imceda Software
www.imceda.com

Sunday, March 11, 2012

Deadlock (strange update deadlock)

hi,
Need help in identifying deadlock issue. we are almost getting 10-15
deadlock issues in a day and with almost same type of log, lock type.
I supposed that are fragmentation, rebuild the indexes and change fillfactor,
but the problem continues. The table has a lot of updates daily, the table as
4 nonclustered indexes and a clustered PK.
below is the log..
Deadlock encountered ... Printing deadlock information
2006-07-11 19:22:43.57 spid3
2006-07-11 19:22:43.57 spid3 Wait-for graph
2006-07-11 19:22:43.57 spid3
2006-07-11 19:22:43.57 spid3 Node:1
2006-07-11 19:22:43.57 spid3 PAG: 7:4:38320 CleanCnt:2
Mode: UIX Flags: 0x2
2006-07-11 19:22:43.57 spid3 Grant List 3::
2006-07-11 19:22:43.57 spid3 Owner:0x44239a00 Mode: UIX Flg:0x0
Ref:2 Life:02000000 SPID:89 ECID:0
2006-07-11 19:22:43.57 spid3 SPID: 89 ECID: 0 Statement Type: UPDATE
Line #: 1
2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE sigam.
agenda_exame
SET id_agendamento=1567614,
id_paciente=5046281,
sequencia=2601052,
id_exame='UA02',
duracao_exame=20,
ind_multiplo='S',
ind_modulo='1',
id_usuario_agendador='PAULA',
data_agendamento=GETDATE(),
id_usuario_tran
2006-07-11 19:22:43.57 spid3 Requested By:
2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
88 ECID:0 Ec:(0x4FB514F8) Value:0x503dc5e0 Cost:(0/0)
2006-07-11 19:22:43.57 spid3
2006-07-11 19:22:43.57 spid3 Node:2
2006-07-11 19:22:43.57 spid3 PAG: 7:4:36528 CleanCnt:2
Mode: U Flags: 0x2
2006-07-11 19:22:43.57 spid3 Grant List 2::
2006-07-11 19:22:43.57 spid3 Owner:0x472bbf40 Mode: U Flg:0x0
Ref:0 Life:00000001 SPID:88 ECID:0
2006-07-11 19:22:43.57 spid3 SPID: 88 ECID: 0 Statement Type: UPDATE
Line #: 1
2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE sigam.
agenda_exame
SET id_agendamento=1567612,
id_paciente=5027055,
sequencia=2601051,
id_exame='AM01',
duracao_exame=10,
ind_multiplo='N',
ind_modulo='0',
id_usuario_agendador='CRISM',
data_agendamento=GETDATE(),
id_usuario_tran
2006-07-11 19:22:43.57 spid3 Requested By:
2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
89 ECID:0 Ec:(0x4FC254F8) Value:0x503ddd40 Cost:(0/3C8)
2006-07-11 19:22:43.57 spid3 Victim Resource Owner:
2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
88 ECID:0 Ec:(0x4FB514F8) Value:0x503dc5e0 Cost:(0/0)
Thanks for any help.renatofts wrote:
> hi,
> Need help in identifying deadlock issue. we are almost getting 10-15
> deadlock issues in a day and with almost same type of log, lock type.
> I supposed that are fragmentation, rebuild the indexes and change fillfactor,
> but the problem continues. The table has a lot of updates daily, the table as
> 4 nonclustered indexes and a clustered PK.
> below is the log..
> Deadlock encountered ... Printing deadlock information
> 2006-07-11 19:22:43.57 spid3
> 2006-07-11 19:22:43.57 spid3 Wait-for graph
> 2006-07-11 19:22:43.57 spid3
> 2006-07-11 19:22:43.57 spid3 Node:1
> 2006-07-11 19:22:43.57 spid3 PAG: 7:4:38320 CleanCnt:2
> Mode: UIX Flags: 0x2
> 2006-07-11 19:22:43.57 spid3 Grant List 3::
> 2006-07-11 19:22:43.57 spid3 Owner:0x44239a00 Mode: UIX Flg:0x0
> Ref:2 Life:02000000 SPID:89 ECID:0
> 2006-07-11 19:22:43.57 spid3 SPID: 89 ECID: 0 Statement Type: UPDATE
> Line #: 1
> 2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE sigam.
> agenda_exame
> SET id_agendamento=1567614,
> id_paciente=5046281,
> sequencia=2601052,
> id_exame='UA02',
> duracao_exame=20,
> ind_multiplo='S',
> ind_modulo='1',
> id_usuario_agendador='PAULA',
> data_agendamento=GETDATE(),
> id_usuario_tran
> 2006-07-11 19:22:43.57 spid3 Requested By:
> 2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
> 88 ECID:0 Ec:(0x4FB514F8) Value:0x503dc5e0 Cost:(0/0)
> 2006-07-11 19:22:43.57 spid3
> 2006-07-11 19:22:43.57 spid3 Node:2
> 2006-07-11 19:22:43.57 spid3 PAG: 7:4:36528 CleanCnt:2
> Mode: U Flags: 0x2
> 2006-07-11 19:22:43.57 spid3 Grant List 2::
> 2006-07-11 19:22:43.57 spid3 Owner:0x472bbf40 Mode: U Flg:0x0
> Ref:0 Life:00000001 SPID:88 ECID:0
> 2006-07-11 19:22:43.57 spid3 SPID: 88 ECID: 0 Statement Type: UPDATE
> Line #: 1
> 2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE sigam.
> agenda_exame
> SET id_agendamento=1567612,
> id_paciente=5027055,
> sequencia=2601051,
> id_exame='AM01',
> duracao_exame=10,
> ind_multiplo='N',
> ind_modulo='0',
> id_usuario_agendador='CRISM',
> data_agendamento=GETDATE(),
> id_usuario_tran
> 2006-07-11 19:22:43.57 spid3 Requested By:
> 2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
> 89 ECID:0 Ec:(0x4FC254F8) Value:0x503ddd40 Cost:(0/3C8)
> 2006-07-11 19:22:43.57 spid3 Victim Resource Owner:
> 2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
> 88 ECID:0 Ec:(0x4FB514F8) Value:0x503dc5e0 Cost:(0/0)
> Thanks for any help.
What does the full UPDATE statement look like?
http://www.sql-server-performance.com/deadlocks.asp
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy, thanks for help, the updates look like that:
UPDATE sigam.agenda_exame
SET id_motiv_bloq_sala='$1',
id_usuario_agendador='EDSON',
data_agendamento=GETDATE()
WHERE id_posto=2
AND id_setor='AM'
AND id_sala='MAI1'
AND data='2006-08-11 15:00:00.0'
and
UPDATE sigam.agenda_exame
SET id_agendamento=1565410,
id_paciente=100100,
sequencia=2597022,
id_exame='AM02',
duracao_exame=60,
ind_multiplo='S',
ind_modulo='1',
id_usuario_agendador='RENATO',
data_agendamento=GETDATE(),
id_usuario_transferidor='*',
id_motiv_bloq_sala='01',
conselho_executor='URP1',
codigo_executor='025332SP',
conselho_acompanhante='*',
codigo_acompanhante='0',
conselho_solicitante='*',
codigo_solicitante='0',
tipo_convenio='P',
id_convenio='PAR',
ind_forcado='0',
ind_estouro='N',
id_fase='',
preco='{"0.0"}'
WHERE id_posto=2
AND id_setor='AM'
AND id_sala='MAI1'
AND data='2006-08-11 14:10:00.0'
How do the table get deadlocks on itself ? Can I simulate this ?
Tks
Tracy McKibben wrote:
>> hi,
>> Need help in identifying deadlock issue. we are almost getting 10-15
>[quoted text clipped - 94 lines]
>> Thanks for any help.
>What does the full UPDATE statement look like?
>http://www.sql-server-performance.com/deadlocks.asp
>http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1|||renatofts via SQLMonster.com wrote:
> Hi Tracy, thanks for help, the updates look like that:
> UPDATE sigam.agenda_exame
> SET id_motiv_bloq_sala='$1',
> id_usuario_agendador='EDSON',
> data_agendamento=GETDATE()
> WHERE id_posto=2
> AND id_setor='AM'
> AND id_sala='MAI1'
> AND data='2006-08-11 15:00:00.0'
> and
> UPDATE sigam.agenda_exame
> SET id_agendamento=1565410,
> id_paciente=100100,
> sequencia=2597022,
> id_exame='AM02',
> duracao_exame=60,
> ind_multiplo='S',
> ind_modulo='1',
> id_usuario_agendador='RENATO',
> data_agendamento=GETDATE(),
> id_usuario_transferidor='*',
> id_motiv_bloq_sala='01',
> conselho_executor='URP1',
> codigo_executor='025332SP',
> conselho_acompanhante='*',
> codigo_acompanhante='0',
> conselho_solicitante='*',
> codigo_solicitante='0',
> tipo_convenio='P',
> id_convenio='PAR',
> ind_forcado='0',
> ind_estouro='N',
> id_fase='',
> preco='{"0.0"}'
> WHERE id_posto=2
> AND id_setor='AM'
> AND id_sala='MAI1'
> AND data='2006-08-11 14:10:00.0'
> How do the table get deadlocks on itself ? Can I simulate this ?
> Tks
>
Are there update triggers on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||No, but there are 4 indexes that references to id_motiv_bloq_sala, sequencia,
id_agendamento columns, I dropped two indexes and deadlocks decreases.
Tracy McKibben wrote:
>> Hi Tracy, thanks for help, the updates look like that:
>[quoted text clipped - 41 lines]
>> Tks
>Are there update triggers on this table?
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1|||renatofts via SQLMonster.com wrote:
> No, but there are 4 indexes that references to id_motiv_bloq_sala, sequencia,
> id_agendamento columns, I dropped two indexes and deadlocks decreases.
>
Hmmm... You could be dealing with index fragmentation, or even disk
fragmentation, or just poor disk I/O overall, causing the index updates
to take longer than necessary.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Okay, but how do i get performance without index fragmentation, cause these
columns are important to perfmorm my queries. Thanks a lot.
Tracy McKibben wrote:
>> No, but there are 4 indexes that references to id_motiv_bloq_sala, sequencia,
>> id_agendamento columns, I dropped two indexes and deadlocks decreases.
>Hmmm... You could be dealing with index fragmentation, or even disk
>fragmentation, or just poor disk I/O overall, causing the index updates
>to take longer than necessary.
>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1|||renatofts via SQLMonster.com wrote:
> Okay, but how do i get performance without index fragmentation, cause these
> columns are important to perfmorm my queries. Thanks a lot.
>
What does the execution plan look like for the two sample updates that
you posted? Also, post the output of sp_helpindex from this table.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Deadlock (strange update deadlock)

0x4FB514F8) Value:0x503dc5e0 Cost

0/0)
2006-07-11 19:22:43.57 spid3
2006-07-11 19:22:43.57 spid3 Node:2
2006-07-11 19:22:43.57 spid3 PAG: 7:4:36528 CleanCnt:2
Mode: U Flags: 0x2
2006-07-11 19:22:43.57 spid3 Grant List 2::
2006-07-11 19:22:43.57 spid3 Owner:0x472bbf40 Mode: U Flg:0x0
Ref:0 Life:00000001 SPID:88 ECID:0
2006-07-11 19:22:43.57 spid3 SPID: 88 ECID: 0 Statement Type: UPDATE
Line #: 1
2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE sigam.
agenda_exame
SET id_agendamento=1567612,
id_paciente=5027055,
sequencia=2601051,
id_exame='AM01',
duracao_exame=10,
ind_multiplo='N',
ind_modulo='0',
id_usuario_agendador='CRISM',
data_agendamento=GETDATE(),
id_usuario_tran
2006-07-11 19:22:43.57 spid3 Requested By:
2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPI
D:
89 ECID:0 Ec

0x4FC254F8) Value:0x503ddd40 Cost

0/3C8)
2006-07-11 19:22:43.57 spid3 Victim Resource Owner:
2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:
88 ECID:0 Ec

0x4FB514F8) Value:0x503dc5e0 Cost

0/0)
Thanks for any help.renatofts wrote:
> hi,
> Need help in identifying deadlock issue. we are almost getting 10-15
> deadlock issues in a day and with almost same type of log, lock type.
> I supposed that are fragmentation, rebuild the indexes and change fillfact
or,
> but the problem continues. The table has a lot of updates daily, the table
as
> 4 nonclustered indexes and a clustered PK.
> below is the log..
> Deadlock encountered ... Printing deadlock information
> 2006-07-11 19:22:43.57 spid3
> 2006-07-11 19:22:43.57 spid3 Wait-for graph
> 2006-07-11 19:22:43.57 spid3
> 2006-07-11 19:22:43.57 spid3 Node:1
> 2006-07-11 19:22:43.57 spid3 PAG: 7:4:38320 CleanCnt:2
> Mode: UIX Flags: 0x2
> 2006-07-11 19:22:43.57 spid3 Grant List 3::
> 2006-07-11 19:22:43.57 spid3 Owner:0x44239a00 Mode: UIX Flg:0x
0
> Ref:2 Life:02000000 SPID:89 ECID:0
> 2006-07-11 19:22:43.57 spid3 SPID: 89 ECID: 0 Statement Type: UPDAT
E
> Line #: 1
> 2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE siga
m.
> agenda_exame
> SET id_agendamento=1567614,
> id_paciente=5046281,
> sequencia=2601052,
> id_exame='UA02',
> duracao_exame=20,
> ind_multiplo='S',
> ind_modulo='1',
> id_usuario_agendador='PAULA',
> data_agendamento=GETDATE(),
> id_usuario_tran
> 2006-07-11 19:22:43.57 spid3 Requested By:
> 2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U S
PID:
> 88 ECID:0 Ec

0x4FB514F8) Value:0x503dc5e0 Cost

0/0)
> 2006-07-11 19:22:43.57 spid3
> 2006-07-11 19:22:43.57 spid3 Node:2
> 2006-07-11 19:22:43.57 spid3 PAG: 7:4:36528 CleanCnt:2
> Mode: U Flags: 0x2
> 2006-07-11 19:22:43.57 spid3 Grant List 2::
> 2006-07-11 19:22:43.57 spid3 Owner:0x472bbf40 Mode: U Flg:0x
0
> Ref:0 Life:00000001 SPID:88 ECID:0
> 2006-07-11 19:22:43.57 spid3 SPID: 88 ECID: 0 Statement Type: UPDAT
E
> Line #: 1
> 2006-07-11 19:22:43.57 spid3 Input Buf: Language Event: UPDATE siga
m.
> agenda_exame
> SET id_agendamento=1567612,
> id_paciente=5027055,
> sequencia=2601051,
> id_exame='AM01',
> duracao_exame=10,
> ind_multiplo='N',
> ind_modulo='0',
> id_usuario_agendador='CRISM',
> data_agendamento=GETDATE(),
> id_usuario_tran
> 2006-07-11 19:22:43.57 spid3 Requested By:
> 2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U S
PID:
> 89 ECID:0 Ec

0x4FC254F8) Value:0x503ddd40 Cost

0/3C8)
> 2006-07-11 19:22:43.57 spid3 Victim Resource Owner:
> 2006-07-11 19:22:43.57 spid3 ResType:LockOwner Stype:'OR' Mode: U SPI
D:
> 88 ECID:0 Ec

0x4FB514F8) Value:0x503dc5e0 Cost

0/0)
> Thanks for any help.
What does the full UPDATE statement look like?
http://www.sql-server-performance.com/deadlocks.asp
http://realsqlguy.com/twiki/bin/vie...latingADeadlock
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy, thanks for help, the updates look like that:
UPDATE sigam.agenda_exame
SET id_motiv_bloq_sala='$1',
id_usuario_agendador='EDSON',
data_agendamento=GETDATE()
WHERE id_posto=2
AND id_setor='AM'
AND id_sala='MAI1'
AND data='2006-08-11 15:00:00.0'
and
UPDATE sigam.agenda_exame
SET id_agendamento=1565410,
id_paciente=100100,
sequencia=2597022,
id_exame='AM02',
duracao_exame=60,
ind_multiplo='S',
ind_modulo='1',
id_usuario_agendador='RENATO',
data_agendamento=GETDATE(),
id_usuario_transferidor='*',
id_motiv_bloq_sala='01',
conselho_executor='URP1',
codigo_executor='025332SP',
conselho_acompanhante='*',
codigo_acompanhante='0',
conselho_solicitante='*',
codigo_solicitante='0',
tipo_convenio='P',
id_convenio='PAR',
ind_forcado='0',
ind_estouro='N',
id_fase='',
preco='{"0.0"}'
WHERE id_posto=2
AND id_setor='AM'
AND id_sala='MAI1'
AND data='2006-08-11 14:10:00.0'
How do the table get deadlocks on itself ? Can I simulate this ?
Tks
Tracy McKibben wrote:
>[quoted text clipped - 94 lines]
>What does the full UPDATE statement look like?
>http://www.sql-server-performance.com/deadlocks.asp
>http://realsqlguy.com/twiki/bin/vie...latingADeadlock
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||renatofts via droptable.com wrote:
> Hi Tracy, thanks for help, the updates look like that:
> UPDATE sigam.agenda_exame
> SET id_motiv_bloq_sala='$1',
> id_usuario_agendador='EDSON',
> data_agendamento=GETDATE()
> WHERE id_posto=2
> AND id_setor='AM'
> AND id_sala='MAI1'
> AND data='2006-08-11 15:00:00.0'
> and
> UPDATE sigam.agenda_exame
> SET id_agendamento=1565410,
> id_paciente=100100,
> sequencia=2597022,
> id_exame='AM02',
> duracao_exame=60,
> ind_multiplo='S',
> ind_modulo='1',
> id_usuario_agendador='RENATO',
> data_agendamento=GETDATE(),
> id_usuario_transferidor='*',
> id_motiv_bloq_sala='01',
> conselho_executor='URP1',
> codigo_executor='025332SP',
> conselho_acompanhante='*',
> codigo_acompanhante='0',
> conselho_solicitante='*',
> codigo_solicitante='0',
> tipo_convenio='P',
> id_convenio='PAR',
> ind_forcado='0',
> ind_estouro='N',
> id_fase='',
> preco='{"0.0"}'
> WHERE id_posto=2
> AND id_setor='AM'
> AND id_sala='MAI1'
> AND data='2006-08-11 14:10:00.0'
> How do the table get deadlocks on itself ? Can I simulate this ?
> Tks
>
Are there update triggers on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||No, but there are 4 indexes that references to id_motiv_bloq_sala, sequenci
a,
id_agendamento columns, I dropped two indexes and deadlocks decreases.
Tracy McKibben wrote:
>[quoted text clipped - 41 lines]
>Are there update triggers on this table?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||renatofts via droptable.com wrote:
> No, but there are 4 indexes that references to id_motiv_bloq_sala, sequen
cia,
> id_agendamento columns, I dropped two indexes and deadlocks decreases.
>
Hmmm... You could be dealing with index fragmentation, or even disk
fragmentation, or just poor disk I/O overall, causing the index updates
to take longer than necessary.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Okay, but how do i get performance without index fragmentation, cause these
columns are important to perfmorm my queries. Thanks a lot.
Tracy McKibben wrote:
>Hmmm... You could be dealing with index fragmentation, or even disk
>fragmentation, or just poor disk I/O overall, causing the index updates
>to take longer than necessary.
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||renatofts via droptable.com wrote:
> Okay, but how do i get performance without index fragmentation, cause thes
e
> columns are important to perfmorm my queries. Thanks a lot.
>
What does the execution plan look like for the two sample updates that
you posted? Also, post the output of sp_helpindex from this table.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Tuesday, March 27, 2012

DeadLocking

Deadlocked on the same resource (same index)

Deadlocked on the same resource (same index)

Deadlocked on the same resource (same index)

deadlocked on lock resources. SQL Server 2000

deadlocked on lock

deadlocked on lock

deadlocked on lock

Sunday, March 25, 2012

Deadlock victim (-2147467259)

Deadlock transaction

Thursday, March 22, 2012

Deadlock problem

Wednesday, March 21, 2012

Deadlock on TAB lock

Monday, March 19, 2012

Deadlock diagnosis

deadlock detection

deadlock detection

deadlock detection

Sunday, March 11, 2012

Deadlock (strange update deadlock)

Deadlock (strange update deadlock)

deAttach database

Blog Archive

About Me