Post reply

Better Solution for this deadlock?

  • February 26, 2009 at 5:35 pm

    #131318

    I have same stored procedure called my different processes. Both will wait near the update statement and block each other for U lock thus forming deadlock.

    Isolation level is READ COMMITTED.

    Both the SPIDS hold X lock on a clustered index of a table and waiting for U lock..

    Pls suggest..

    --Sudhie

  • February 26, 2009 at 10:32 pm

    #950987

    Lookup "deadlocks [SQL Server], avoiding" in Books Online.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 27, 2009 at 2:19 am

    #951031

    Are you getting deadlocks, or is this a theoretical question?

    If you are, use traceflag 1222 to write the deadlock graph into the error log. Post the error log, the procedures and the definition of the tables involved (including indexes)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 27, 2009 at 10:21 am

    #951282

    Gail - This is the deadlock graph

    2009-02-26 17:46:20.70 spid4s Deadlock encountered .... Printing deadlock information

    2009-02-26 17:46:20.70 spid4s Wait-for graph

    2009-02-26 17:46:20.70 spid4s

    2009-02-26 17:46:20.70 spid4s Node:1

    2009-02-26 17:46:20.70 spid4s KEY: 19:72057594073645056 (a001ac05653e) CleanCnt:3 Mode:X Flags: 0x0

    2009-02-26 17:46:20.70 spid4s Grant List 1:

    2009-02-26 17:46:20.70 spid4s Owner:0x0000000004F5E700 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:85 ECID:0 XactLockInfo: 0x00000007426F6B98

    2009-02-26 17:46:20.70 spid4s SPID: 85 ECID: 0 Statement Type: UPDATE Line #: 71

    2009-02-26 17:46:20.70 spid4s Input Buf: Language Event: exec CopyPlan 173,'CLC 03 10 09','03/10/2009',-1,0,0,'N'

    2009-02-26 17:46:20.70 spid4s Requested By:

    2009-02-26 17:46:20.70 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000670326A90 Mode: U SPID:87 BatchID:0 ECID:0 TaskProxy:(0x00000007185FC598) Value:0x7dc5640 Cost:(0/242436)

    2009-02-26 17:46:20.70 spid4s

    2009-02-26 17:46:20.70 spid4s Node:2

    2009-02-26 17:46:20.70 spid4s KEY: 19:72057594073645056 (8901f9c31fc2) CleanCnt:2 Mode:X Flags: 0x0

    2009-02-26 17:46:20.70 spid4s Grant List 0:

    2009-02-26 17:46:20.70 spid4s Owner:0x000000064CB0C7C0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:87 ECID:0 XactLockInfo: 0x0000000670326AC8

    2009-02-26 17:46:20.70 spid4s SPID: 87 ECID: 0 Statement Type: UPDATE Line #: 71

    2009-02-26 17:46:20.70 spid4s Input Buf: Language Event: exec CopyPlan 174,'LA 3 10 09','03/10/2009',-1,-1,0,'N'

    2009-02-26 17:46:20.70 spid4s Requested By:

    2009-02-26 17:46:20.70 spid4s ResType:LockOwner Stype:'OR'Xdes:0x00000007426F6B60 Mode: U SPID:85 BatchID:0 ECID:0 TaskProxy:(0x00000005CE006598) Value:0x4839780 Cost:(0/320028)

    2009-02-26 17:46:20.70 spid4s

    2009-02-26 17:46:20.70 spid4s Victim Resource Owner:

    2009-02-26 17:46:20.70 spid4s ResType:LockOwner Stype:'OR'Xdes:0x0000000670326A90 Mode: U SPID:87 BatchID:0 ECID:0 TaskProxy:(0x00000007185FC598) Value:0x7dc5640 Cost:(0/242436)

    2009-02-26 17:46:20.70 spid17s deadlock-list

    2009-02-26 17:46:20.70 spid17s deadlock victim=process3813198

    2009-02-26 17:46:20.70 spid17s process-list

    2009-02-26 17:46:20.70 spid17s process id=process3813198 taskpriority=0 logused=242436 waitresource=KEY: 19:72057594073645056 (a001ac05653e) waittime=654937 ownerId=3615482631 transactionname=INSERT lasttranstarted=2009-02-26T17:35:24.230 XDES=0x670326a90 lockMode=U schedulerid=2 kpid=12272 status=suspended spid=87 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-02-26T17:35:24.223 lastbatchcompleted=2009-02-26T17:35:24.223 clientapp=App.exe hostname=LT hostpid=652 loginname=Readuser isolationlevel=read committed (2) xactid=3615482631 currentdb=19 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056

    2009-02-26 17:46:20.70 spid17s executionStack

    2009-02-26 17:46:20.70 spid17s frame procname=PlanRoute_Audit line=71 stmtstart=7910 sqlhandle=0x030013000d8e1b402ea517014a9b00000000000000000000

    2009-02-26 17:46:20.70 spid17s Update PlanRoute set ModifiedDtm = Getdate(), ModifiedByUserTK=@OurTK

    2009-02-26 17:46:20.70 spid17s From Inserted i

    2009-02-26 17:46:20.70 spid17s where PlanRoute.[PlanTK]=i.[PlanTK] and

    2009-02-26 17:46:20.70 spid17s PlanRoute.[RouteTK]=i.[RouteTK]

    2009-02-26 17:46:20.70 spid17s frame procname=.CopyPlan line=70 stmtstart=5382 stmtend=7814 sqlhandle=0x030013008de3512e0dc51701ad9b00000100000000000000

    2009-02-26 17:46:20.70 spid17s Insert Into PlanRoute (plantk, routetk, ModifiedDtm, PickSeqNbr)

    2009-02-26 17:46:20.70 spid17s Select@newplantk, r.routetk, getdate(), RouteSeqNbr

    2009-02-26 17:46:20.70 spid17s FromRouteVision.Route r

    2009-02-26 17:46:20.70 spid17s Inner JoinPlan p On p.unitcd = r.unitcd --and p.depotcd = r.depotcd

    2009-02-26 17:46:20.70 spid17s Inner Joindw..Depot d On d.depotcd = r.depotcd

    2009-02-26 17:46:20.70 spid17s Andp.PlanTK = @newplantk

    2009-02-26 17:46:20.70 spid17s Andr.RouteStartDt <= @planstop

    2009-02-26 17:46:20.70 spid17s AndRouteVision.CalcEndDateForCompare(RouteEndDt,RouteEndDt,'06/01/2079') >= @planstart

    2009-02-26 17:46:20.70 spid17s Andr.GalleyRouteFlg <> 'Y'

    2009-02-26 17:46:20.70 spid17s Andd.DroppedDepotTitlesOnlyFlg <> 'Y'-- don't load depot dropped titles

    2009-02-26 17:46:20.70 spid17s -- include only routes with effective dates in range

    2009-02-26 17:46:20.70 spid17s And Exists

    2009-02-26 17:46:20.70 spid17s (

    2009-02-26 17:46:20.70 spid17s Select 1

    2009-02-26 17:46:20.70 spid17s From RouteVision.vw_ActiveAndPlannedRouteStops v

    2009-02-26 17:46:20.70 spid17s Where v.RouteTK = r.RouteTk

    2009-02-26 17:46:20.70 spid17s And ((DelMagsFlg = 'Y') and IsNull(DirectShipMagsFlg,'N')='N' and IsNull(ReplenishMagsFlg,'N')='N' and IsNull(SpeedimpexMagsFlg,'N')='N')

    2009-02-26 17:46:20.70 spid17s And RouteStopStartDt <= RouteVision.CalcEndDateForCompare(RouteStopEndDt,RouteEndDt,'06/01/

    2009-02-26 17:46:20.70 spid17s frame procname=adhoc line=1 sqlhandle=0x01001300e3820c1ad0bc205c070000000000000000000000

    2009-02-26 17:46:20.70 spid17s exec CopyPlan 174,'LA 3 10 09','03/10/2009',-1,-1,0,'N'

    2009-02-26 17:46:20.70 spid17s inputbuf

    2009-02-26 17:46:20.70 spid17s exec CopyPlan 174,'LA 3 10 09','03/10/2009',-1,-1,0,'N'

    2009-02-26 17:46:20.70 spid17s process id=process4e11588 taskpriority=0 logused=320028 waitresource=KEY: 19:72057594073645056 (8901f9c31fc2) waittime=3750 ownerId=3615338698 transactionname=user_transaction lasttranstarted=2009-02-26T17:34:15.807 XDES=0x7426f6b60 lockMode=U schedulerid=4 kpid=14432 status=suspended spid=85 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-02-26T17:34:00.023 lastbatchcompleted=2009-02-26T17:34:00.023 clientapp=App.exe hostname=C0C hostpid=3224 loginname=Readuser isolationlevel=read committed (2) xactid=3615338698 currentdb=19 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056

    2009-02-26 17:46:20.70 spid17s executionStack

    2009-02-26 17:46:20.70 spid17s frame procname=PlanRoute_Audit line=71 stmtstart=7910 sqlhandle=0x030013000d8e1b402ea517014a9b00000000000000000000

    2009-02-26 17:46:20.70 spid17s Update PlanRoute set ModifiedDtm = Getdate(), ModifiedByUserTK=@OurTK

    2009-02-26 17:46:20.70 spid17s From Inserted i

    2009-02-26 17:46:20.70 spid17s where PlanRoute.[PlanTK]=i.[PlanTK] and

    2009-02-26 17:46:20.70 spid17s PlanRoute.[RouteTK]=i.[RouteTK]

    2009-02-26 17:46:20.70 spid17s frame procname=PlanRouteEstimates line=132 stmtstart=7702 stmtend=11740 sqlhandle=0x03001300c8afdf6f52c11b01cd9a00000100000000000000

    2009-02-26 17:46:20.70 spid17s With CustRoutes

    2009-02-26 17:46:20.70 spid17s As(

    2009-02-26 17:46:20.70 spid17s Select@plantk as PlanTK,

    2009-02-26 17:46:20.70 spid17s cvr.CustCd,

    2009-02-26 17:46:20.70 spid17s rt.RouteTK,

    2009-02-26 17:46:20.70 spid17s rt.UPSShipperCd,

    2009-02-26 17:46:20.70 spid17s Sum( Coalesce( cvr.TotalQty / Nullif( pr.CopiesPerBundle, 0 ), 0 ) ) as CustBundleQty,

    2009-02-26 17:46:20.70 spid17s Sum( Coalesce( cvr.TotalQty % Nullif( pr.CopiesPerBundle, 0 ), cvr.TotalQty ) ) as CustLooseQty,

    2009-02-26 17:46:20.70 spid17s Case

    2009-02-26 17:46:20.70 spid17s When Sum( Coalesce( cvr.TotalQty % Nullif( pr.CopiesPerBundle, 0 ), cvr.TotalQty ) ) > 0

    2009-02-26 17:46:20.70 spid17s Then 1

    2009-02-26 17:46:20.70 spid17s Else 0

    2009-02-26 17:46:20.70 spid17s End + Coalesce( Sum( cvr.TotalQty / Nullif( pr.CopiesPerBundle, 0 ) ), 0 ) as CustPickQty,

    2009-02-26 17:46:20.70 spid17s dbo.fn_max_int(

    2009-02-26 17:46:20.70 spid17s Ceiling( Sum( cvr.TotalQty * pr.CopyWgt ) / Case when rt.UPSShipperCd <> 0 then @UPSWeight else @ToteWeight end ),

    2009-02-26 17:46:20.70 spid17s Ceiling( Sum( cvr.TotalQty * pr.Thickness * sc.HeightDcNbr * sc.WidthDcNbr ) / Case when rt.UPSShipperCd <> 0 then @UPSVolume else @ToteVolume end )

    2009-02-26 17:46:20.70 spid17s ) as CustContainerQty

    2009-02-26 17:46:20.70 spid17s From@PlanRoutes pr

    2009-02-26 17:46:20.70 spid17s JoinFulfillment.CustVersionAllocation cvr

    2009-02-26 17:46:20.70 spid17s Oncvr.VersionAllocationTk = pr.VersionAllocationTK

    2009-02-26 17:46:20.70 spid17s JoinRouteVision.RouteStop rs

    2009-02-26 17:46:20.70 spid17s On

    2009-02-26 17:46:20.70 spid17s frame procname=CopyPlan line=158 stmtstart=12284 stmtend=12466 sqlhandle=0x030013008de3512e0dc51701ad9b00000100000000000000

    2009-02-26 17:46:20.70 spid17s Exec PlanRouteEstimates @newplantk

    2009-02-26 17:46:20.70 spid17s -- user wants automatic load & balance

    2009-02-26 17:46:20.70 spid17s frame procname=adhoc line=1 sqlhandle=0x01001300723e852b80a08a40070000000000000000000000

    2009-02-26 17:46:20.70 spid17s exec cp_CopyPlan 173,'CLC 03 10 09','03/10/2009',-1,0,0,'N'

    2009-02-26 17:46:20.70 spid17s inputbuf

    2009-02-26 17:46:20.70 spid17s exec cp_CopyPlan 173,'CLC 03 10 09','03/10/2009',-1,0,0,'N'

    2009-02-26 17:46:20.70 spid17s resource-list

    2009-02-26 17:46:20.70 spid17s keylock hobtid=72057594073645056 dbid=19 objectname=PlanRoute indexname=PK_PlanRoute id=lock3d0fb00 mode=X associatedObjectId=72057594073645056

    2009-02-26 17:46:20.70 spid17s owner-list

    2009-02-26 17:46:20.70 spid17s owner id=process3813198 mode=X

    2009-02-26 17:46:20.70 spid17s waiter-list

    2009-02-26 17:46:20.70 spid17s waiter id=process4e11588 mode=U requestType=wait

    2009-02-26 17:46:20.70 spid17s keylock hobtid=72057594073645056 dbid=19 objectname=PlanRoute indexname=PK_PlanRoute id=lock55ed380 mode=X associatedObjectId=72057594073645056

    2009-02-26 17:46:20.70 spid17s owner-list

    2009-02-26 17:46:20.70 spid17s owner id=process4e11588 mode=X

    2009-02-26 17:46:20.70 spid17s waiter-list

    2009-02-26 17:46:20.70 spid17s waiter id=process3813198 mode=U requestType=wait

  • February 27, 2009 at 11:13 am

    #951307

    Please let me know if you require any thing else..

    i will go through avoiding deadlocks in BOL, thanks for that..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply