DeadLock

Question

Post reply

DeadLock

ESAT ERKEC

SSCertifiable

Points: 5661
More actions
April 26, 2012 at 9:34 am

#257116

I have a table and it is using for totals of materials and there is a deadlock problem when ı capture the deadlock ı find a index and it has a lot of inculude column is it efect the performance of insert and update or can it be cause deadlock
I002_08_STINVTOT_I2 this the name of index
<resource-list>
<keylock hobtid="72058041283117056" dbid="7" objectname="dbo.LG_002_08_STINVTOT" indexname="I002_08_STINVTOT_I2" id="lock37c5b81280"
mode="X" associatedObjectId="72058041283117056">
<owner-list>
<owner id="process15e77708" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process17e814c8" mode="S"
requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72058041283117056" dbid="7" objectname="LG_002_08_STINVTOT" indexname="I002_08_STINVTOT_I2"
id="lock13cb817e80" mode="X" associatedObjectId="72058041283117056">
<owner-list>
<owner id="process17e814c8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process15e77708" mode="S" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>

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

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

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 1

ESAT ERKEC

SSCertifiable

Points: 5661

April 27, 2012 at 12:15 am

#1480195

Any idea?

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

Can you post the entire deadlock graph please? Just knowing the resources involved isn't very useful.

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

ESAT ERKEC SSCertifiable Points: 5661 More actions · Answer 3

Deadlock Graph

<deadlock-list>

<process-list>

<process id="process15e94988" taskpriority="0" logused="77928" waitresource="KEY: 7:72058041283117056 (60b438c2a4e5)" waittime="4667" ownerId="13857101193"

transactionname="user_transaction" lasttranstarted="2012-04-26T16:13:11.180" XDES="0x80133950" lockMode="S" schedulerid="47" kpid="13548"

status="suspended" spid="283" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2012-04-26T16:13:14.550" lastbatchcompleted="2012-04-26T16:13:14.547"

clientapp="" hostname="" hostpid="32016" loginname="" isolationlevel="read committed (2)" xactid="13857101193"

currentdb="7" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128568">

IF NOT EXISTS(SELECT LOGICALREF FROM LG_002_08_STINVTOT

WHERE (STOCKREF = @par_ItemRef) AND

(INVENNO = @par_InvenNo) AND

(DATE_ = @par_TransDate)) </frame>

EXECUTE LG_STKCHECKINVTOT_002_08 @par_ItemRef, @par_VariantRef, -1, @par_TransDate </frame>

EXECUTE LG_STKHANDLEINOUT_002_08 @coef, @itemRef, @variantRef, @invoiceRef,

@diffPrice, @diffRepPrice,

@distCost, @distAddExp,

@amount, @unit1CFact,

@unit2CFact, @price,

@vatPer, @vatIInvFlag,

@trCode, @billedFlag,

@ordAmount, @calcSign,

@ordUnit1CFact, @ordUnit2CFact,

@reportRate,

@sourceType,

@sourceIndex,

@sourceWSRef,

@ordInvenNu </frame>

EXECUTE LG_STKHANDLENUMBERS_002_08 @new_ItemRef, @new_VariantRef, @new_TransDate,

@new_TrCode, 1, @new_CancFlag,

@new_LineType, @new_BilledFlag,

@new_DecPrDiff, @new_LProdStat,

@new_SourceType, @new_DestType,

@new_SourceWSRef, @new_DestWSRef,

@new_SourceIndex, @new_DestIndex,

@new_Amount, @new_Price,

@new_Unit1CFact, @new_Unit2CFact,

@new_VatPer, @new_DistCost,

@new_DistAddExp, @new_DiffPrice,

@new_DiffRepPrice, @new_ReportRate,

@new_VatIncFlag, @new_InvoiceRef,

@new_OrdTransRef, @new_DistOrdLineRef, </frame>

INSERT INTO "DBNAME".."LG_002_08_STLINE" ("STOCKREF","LINETYPE","PREVLINEREF","PREVLINENO","DETLINE","TRCODE","DATE_","FTIME","GLOBTRANS","CALCTYPE",

"PRODORDERREF","SOURCETYPE","SOURCEINDEX","SOURCECOSTGRP","SOURCEWSREF","SOURCEPOLNREF","DESTTYPE","DESTINDEX","DESTCOSTGRP","DESTWSREF",

"DESTPOLNREF","FACTORYNR","IOCODE","STFICHEREF","STFICHELNNO","INVOICEREF","INVOICELNNO","CLIENTREF","ORDTRANSREF","ORDFICHEREF","CENTERREF",

"ACCOUNTREF","VATACCREF","VATCENTERREF","PRACCREF","PRCENTERREF","PRVATACCREF","PRVATCENREF","PROMREF","PAYDEFREF","SPECODE","DELVRYCODE","AMOUNT",

"PRICE","TOTAL","PRCURR","PRPRICE","TRCURR","TRRATE","REPORTRATE","DISTCOST","DISTDISC","DISTEXP","DISTPROM","DISCPER","LINEEXP","UOMREF","USREF","UINFO1",

"UINFO2","UINFO3","UINFO4","UINFO5","UINFO6","UINFO7","UINFO8","PLNAMOUNT","VATINC","VAT","VATAMNT","VATMATRAH","BILLEDITEM","BILLED","CPSTFLAG","RETCOSTTYPE",

"SOURCELINK","RETCOST","RETCOSTCURR","OUTCOST","OUTCOSTCURR","RETAMOUNT","FAREGREF","FAATTRIB","CANCELLED","LINENET","DISTADDEXP","FADACC </frame>

unknown </frame>

</executionStack>

(@P1 int,@P2 smallint,@P3 int,@P4 smallint,@P5 smallint,@P6 smallint,@P7 datetime,@P8 int,@P9 smallint,@P10 smallint,@P11 int,@P12 smallint,@P13 smallint,@P14 smallint,@P15 int,@P16 int,@P17 smallint,@P18 smallint,@P19 smallint,@P20 int,@P21 int,@P22 smallint,@P23 smallint,@P24 int,@P25 smallint,@P26 int,@P27 smallint,@P28 int,@P29 int,@P30 int,@P31 int,@P32 int,@P33 int,@P34 int,@P35 int,@P36 int,@P37 int,@P38 int,@P39 int,@P40 int,@P41 varchar(1),@P42 varchar(1),@P43 float,@P44 float,@P45 float,@P46 smallint,@P47 float,@P48 smallint,@P49 float,@P50 float,@P51 float,@P52 float,@P53 float,@P54 float,@P55 float,@P56 varchar(1),@P57 int,@P58 int,@P59 float,@P60 float,@P61 float,@P62 float,@P63 float,@P64 float,@P65 float,@P66 float,@P67 float,@P68 smallint,@P69 float,@P70 float,@P71 float,@P72 int,@P73 smallint,@P74 smallint,@P75 smallint,@P76 int,@P77 float,@P78 float,@P79 float,@P80 float,@P81 float,@P82 int,@P83 smallint,@P84 smallint,@P85 float,@P86 float,@P87 int,@P88 int,@P89 int,@P90 int,@P91 float,@P92 </inputbuf>

</process>

<process id="process17e30748" taskpriority="0" logused="430204" waitresource="KEY: 7:72058041283117056 (ab932f489f92)" waittime="3990"

ownerId="13857098934" transactionname="user_transaction"

lasttranstarted="2012-04-26T16:13:10.837" XDES="0x14ce651950"

lockMode="S" schedulerid="53" kpid="11760" status="suspended" spid="320" sbid="0" ecid="0" priority="0" trancount="1"

lastbatchstarted="2012-04-26T16:13:15.227" lastbatchcompleted="2012-04-26T16:13:15.220" clientapp="" hostname="" hostpid="27360"

loginname="LOGO" isolationlevel="read committed (2)" xactid="13857098934" currentdb="7" lockTimeout="4294967295" clientoption1="671220000" clientoption2="128568">

IF NOT EXISTS(SELECT LOGICALREF FROM LG_002_08_STINVTOT

WHERE (STOCKREF = @par_ItemRef) AND

(INVENNO = @par_InvenNo) AND

(DATE_ = @par_TransDate)) </frame>

EXECUTE LG_STKCHECKINVTOT_002_08 @par_ItemRef, @par_VariantRef, -1, @par_TransDate </frame>

EXECUTE LG_STKHANDLEINOUT_002_08 @coef, @itemRef, @variantRef, @invoiceRef,

@diffPrice, @diffRepPrice,

@distCost, @distAddExp,

@amount, @unit1CFact,

@unit2CFact, @price,

@vatPer, @vatIInvFlag,

@trCode, @billedFlag,

@ordAmount, @calcSign,

@ordUnit1CFact, @ordUnit2CFact,

@reportRate,

@sourceType,

@sourceIndex,

@sourceWSRef,

@ordInvenNu </frame>

EXECUTE LG_STKHANDLENUMBERS_002_08 @old_ItemRef, @old_VariantRef, @old_TransDate,

@old_TrCode, -1, @old_CancFlag,

@old_LineType, @old_BilledFlag,

@old_DecPrDiff, @old_LProdStat,

@old_SourceType, @old_DestType,

@old_SourceWSRef, @old_DestWSRef,

@old_SourceIndex, @old_DestIndex,

@old_Amount, @old_Price,

@old_Unit1CFact, @old_Unit2CFact,

@old_VatPer, @old_DistCost,

@old_DistAddExp, @old_DiffPrice,

@old_DiffRepPrice, @old_ReportRate,

@old_VatIncFlag, @old_InvoiceRef, </frame>

UPDATE "DBNAME".."LG_002_08_STLINE" SET "STOCKREF"=@P1,"LINETYPE"=@P2,"PREVLINEREF"=@P3,"PREVLINENO"=@P4,"DETLINE"=@P5,"TRCODE"=@P6,"DATE_"=@P7,"FTIME"=@P8,"GLOBTRANS"=@P9,"CALCTYPE"=@P10,"PRODORDERREF"=@P11,"SOURCETYPE"=@P12,"SOURCEINDEX"=@P13,"SOURCECOSTGRP"=@P14,"SOURCEWSREF"=@P15,"SOURCEPOLNREF"=@P16,"DESTTYPE"=@P17,"DESTINDEX"=@P18,"DESTCOSTGRP"=@P19,"DESTWSREF"=@P20,"DESTPOLNREF"=@P21,"FACTORYNR"=@P22,"IOCODE"=@P23,"STFICHEREF"=@P24,"STFICHELNNO"=@P25,"INVOICEREF"=@P26,"INVOICELNNO"=@P27,"CLIENTREF"=@P28,"ORDTRANSREF"=@P29,"ORDFICHEREF"=@P30,"CENTERREF"=@P31,"ACCOUNTREF"=@P32,"VATACCREF"=@P33,"VATCENTERREF"=@P34,"PRACCREF"=@P35,"PRCENTERREF"=@P36,"PRVATACCREF"=@P37,"PRVATCENREF"=@P38,"PROMREF"=@P39,"PAYDEFREF"=@P40,"SPECODE"=@P41,"DELVRYCODE"=@P42,"AMOUNT"=@P43,"PRICE"=@P44,"TOTAL"=@P45,"PRCURR"=@P46,"PRPRICE"=@P47,"TRCURR"=@P48,"TRRATE"=@P49,"REPORTRATE"=@P50,"DISTCOST"=@P51,"DISTDISC"=@P52,"DISTEXP"=@P53,"DISTPROM"=@P54,"DISCPER"=@P55,"LINEEXP"=@P56,"UOMREF"=@P57,"USREF"=@P58,"UINFO1"=@P59,"UINFO2"=@P6 </frame>

unknown </frame>

</executionStack>

(@P1 int,@P2 smallint,@P3 int,@P4 smallint,@P5 smallint,@P6 smallint,@P7 datetime,@P8 int,@P9 smallint,@P10 smallint,@P11 int,@P12 smallint,@P13 smallint,@P14 smallint,@P15 int,@P16 int,@P17 smallint,@P18 smallint,@P19 smallint,@P20 int,@P21 int,@P22 smallint,@P23 smallint,@P24 int,@P25 smallint,@P26 int,@P27 smallint,@P28 int,@P29 int,@P30 int,@P31 int,@P32 int,@P33 int,@P34 int,@P35 int,@P36 int,@P37 int,@P38 int,@P39 int,@P40 int,@P41 varchar(1),@P42 varchar(1),@P43 float,@P44 float,@P45 float,@P46 smallint,@P47 float,@P48 smallint,@P49 float,@P50 float,@P51 float,@P52 float,@P53 float,@P54 float,@P55 float,@P56 varchar(1),@P57 int,@P58 int,@P59 float,@P60 float,@P61 float,@P62 float,@P63 float,@P64 float,@P65 float,@P66 float,@P67 float,@P68 smallint,@P69 float,@P70 float,@P71 float,@P72 int,@P73 smallint,@P74 smallint,@P75 smallint,@P76 int,@P77 float,@P78 float,@P79 float,@P80 float,@P81 float,@P82 int,@P83 smallint,@P84 smallint,@P85 float,@P86 float,@P87 int,@P88 int,@P89 int,@P90 int,@P91 float,@P92 </inputbuf>

</process>

</process-list>

<resource-list>

<keylock hobtid="72058041283117056" dbid="7" objectname="DBNAME.dbo.LG_002_08_STINVTOT" indexname="I002_08_STINVTOT_I2" id="lock3be7a4400" mode="X"

associatedObjectId="72058041283117056">

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

<keylock hobtid="72058041283117056" dbid="7" objectname="DBNAME.dbo.LG_002_08_STINVTOT" indexname="I002_08_STINVTOT_I2" id="lock190f9eaa80"

mode="X" associatedObjectId="72058041283117056">

<owner-list>

</owner-list>

<waiter-list>

</waiter-list>

</keylock>

</resource-list>

</deadlock>

</deadlock-list>

</TextData>