Better Solution for this deadlock?

Question

Post reply

Better Solution for this deadlock?

Sudiendra

SSCommitted

Points: 1839
More actions
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

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

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)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

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

Sudiendra SSCommitted Points: 1839 More actions · Answer 3

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 From RouteVision.Route r

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

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

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

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

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

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

2009-02-26 17:46:20.70 spid17s And d.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 Join Fulfillment.CustVersionAllocation cvr

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

2009-02-26 17:46:20.70 spid17s Join RouteVision.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

Sudiendra SSCommitted Points: 1839 More actions · Answer 4

Sudiendra

SSCommitted

Points: 1839

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