April 11, 2015 at 2:20 am
Hi,
We are using SQL Server 2008 R2 Version (10.50.4000) and getting a deadlock. The deadlock seems to be on lock partition and there are 2 inserts into the same table. Can someone help me out on understanding on whats causing the deadlock?
<deadlock-list>
<deadlock victim="process12e3a988">
<process-list>
<process id="process12e3a988" taskpriority="0" logused="20552" waitresource="OBJECT: 5:921770341:21 " waittime="1899" ownerId="205286093" transactionname="implicit_transaction" lasttranstarted="2015-04-08T12:45:47.743" XDES="0x3f52d1950" lockMode="X" schedulerid="54" kpid="15344" status="suspended" spid="109" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-04-08T12:45:49.243" lastbatchcompleted="2015-04-08T12:45:49.240" clientapp="Microsoft SQL Server JDBC Driver" hostname="mislnxappp01" hostpid="0" loginname="NBU" isolationlevel="read committed (2)" xactid="205286093" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="6486" sqlhandle="0x0200000010fc192991e1dba1b987841c2d3271334d081b8c"> INSERT INTO Policy(PASBilledToDate,PASPaidToDate,PASPaymentDueDate,ReplacementTC,RoutingNo,ShortName,SpecialHandlingTC,SponsoringPlanCode,SponsoringPlanSeparationDate,SponsoringPlanTC,StampDutyAmt,StatementBasisTC,StatusChangeDate,StatusReasonTC,TaxJurisdictionTC,TaxNationTC,TaxStatusTC,TermDate,Territory,TotalCommissionRetainedAmt,TotalCOIAmt,WeightedAvgInterestRatePct,CarrierPendingAmount,CarrierTerminatedAmount,OtherCarrierActiveAmount,OtherCarrierPendingAmount,OtherCarrierTotalAmount,TIAApplicableIndicator,TIAAmount,TIAEffectiveDate,TIACancellationDate,TIACancellationReason,CarrierActiveAmount,AccountNo,AdministeringCarrierCode,AdvancingRejectedFlag,AnnualIndexTC,AnnualPaymentAmt,AssumingCompanyCaseNo,BankAccountTC,BankBranchName,BankName,BeneficiaryFlag,BilledToDate,BillingNo,BillingResumeDate,BillingStopDate,CarrierAdminSystemName,CarrierCode,CarrierCommissionCode,CertificateDeliveryMethod,CommissionAnnualFlag,CommissionExtension,CommissionOptionSelectedTC,CommissionRollOverPct,ContestabilityEndDate,Con </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame>
</executionStack>
<inputbuf> (@P0 date,@P1 date,@P2 date,@P3 varchar(8000),@P4 varchar(8000),@P5 varchar(8000),@P6 varchar(8000),@P7 varchar(8000),@P8 varchar(8000),@P9 varchar(8000),@P10 varchar(8000),@P11 varchar(8000),@P12 date,@P13 varchar(8000),@P14 varchar(8000),@P15 varchar(8000),@P16 varchar(8000),@P17 date,@P18 varchar(8000),@P19 varchar(8000),@P20 varchar(8000),@P21 varchar(8000),@P22 varchar(8000),@P23 varchar(8000),@P24 varchar(8000),@P25 varchar(8000),@P26 varchar(8000),@P27 varchar(8000),@P28 decimal(38,9),@P29 date,@P30 date,@P31 varchar(8000),@P32 varchar(8000),@P33 varchar(8000),@P34 varchar(8000),@P35 varchar(8000),@P36 varchar(8000),@P37 decimal(38,2),@P38 varchar(8000),@P39 varchar(8000),@P40 varchar(8000),@P41 varchar(8000),@P42 varchar(8000),@P43 date,@P44 varchar(8000),@P45 varchar(8000),@P46 varchar(8000),@P47 varchar(8000),@P48 varchar(8000),@P49 varchar(8000),@P50 varchar(8000),@P51 varchar(8000),@P52 varchar(8000),@P53 varchar(8000),@P54 varchar(8000),@P55 varchar(8000),@P56 varchar(8000),@P57 varchar(8000),@P </inputbuf>
</process>
<process id="process5c3b4c8" taskpriority="0" logused="322984" waitresource="OBJECT: 5:921770341:0 " waittime="1865" ownerId="205288085" transactionname="implicit_transaction" lasttranstarted="2015-04-08T12:45:48.507" XDES="0x4b28373c0" lockMode="X" schedulerid="22" kpid="36248" status="suspended" spid="75" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2015-04-08T12:45:49.287" lastbatchcompleted="2015-04-08T12:45:49.283" lastattention="2015-04-08T12:02:24.790" clientapp="Microsoft SQL Server JDBC Driver" hostname="mislnxappp01" hostpid="0" loginname="NBU" isolationlevel="read committed (2)" xactid="205288085" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="6460" sqlhandle="0x02000000ddf57d004ddedfcd5e0963746f1131b5fd3125eb"> INSERT INTO Policy(PASBilledToDate,PASPaidToDate,PASPaymentDueDate,ReplacementTC,RoutingNo,ShortName,SpecialHandlingTC,SponsoringPlanCode,SponsoringPlanSeparationDate,SponsoringPlanTC,StampDutyAmt,StatementBasisTC,StatusChangeDate,StatusReasonTC,TaxJurisdictionTC,TaxNationTC,TaxStatusTC,TermDate,Territory,TotalCommissionRetainedAmt,TotalCOIAmt,WeightedAvgInterestRatePct,CarrierPendingAmount,CarrierTerminatedAmount,OtherCarrierActiveAmount,OtherCarrierPendingAmount,OtherCarrierTotalAmount,TIAApplicableIndicator,TIAAmount,TIAEffectiveDate,TIACancellationDate,TIACancellationReason,CarrierActiveAmount,AccountNo,AdministeringCarrierCode,AdvancingRejectedFlag,AnnualIndexTC,AnnualPaymentAmt,AssumingCompanyCaseNo,BankAccountTC,BankBranchName,BankName,BeneficiaryFlag,BilledToDate,BillingNo,BillingResumeDate,BillingStopDate,CarrierAdminSystemName,CarrierCode,CarrierCommissionCode,CertificateDeliveryMethod,CommissionAnnualFlag,CommissionExtension,CommissionOptionSelectedTC,CommissionRollOverPct,ContestabilityEndDate,Con </frame>
<frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000"> unknown </frame>
</executionStack>
<inputbuf> (@P0 date,@P1 date,@P2 date,@P3 varchar(8000),@P4 varchar(8000),@P5 varchar(8000),@P6 varchar(8000),@P7 varchar(8000),@P8 varchar(8000),@P9 varchar(8000),@P10 varchar(8000),@P11 varchar(8000),@P12 date,@P13 varchar(8000),@P14 varchar(8000),@P15 varchar(8000),@P16 varchar(8000),@P17 date,@P18 varchar(8000),@P19 varchar(8000),@P20 varchar(8000),@P21 varchar(8000),@P22 varchar(8000),@P23 varchar(8000),@P24 varchar(8000),@P25 varchar(8000),@P26 varchar(8000),@P27 varchar(8000),@P28 decimal(38,9),@P29 date,@P30 date,@P31 varchar(8000),@P32 varchar(8000),@P33 varchar(8000),@P34 varchar(8000),@P35 varchar(8000),@P36 varchar(8000),@P37 decimal(38,9),@P38 varchar(8000),@P39 varchar(8000),@P40 varchar(8000),@P41 varchar(8000),@P42 varchar(8000),@P43 date,@P44 varchar(8000),@P45 varchar(8000),@P46 varchar(8000),@P47 varchar(8000),@P48 varchar(8000),@P49 varchar(8000),@P50 varchar(8000),@P51 varchar(8000),@P52 varchar(8000),@P53 varchar(8000),@P54 varchar(8000),@P55 varchar(8000),@P56 varchar(8000),@P57 varchar(8000),@P </inputbuf>
</process>
</process-list>
<resource-list>
<objectlock lockPartition="21" objid="921770341" subresource="FULL" dbid="5" objectname="NBU.dbo.Policy" id="lock216c44300" mode="IX" associatedObjectId="921770341">
<owner-list>
<owner id="process5c3b4c8" mode="IX"/>
</owner-list>
<waiter-list>
<waiter id="process12e3a988" mode="X" requestType="wait"/>
</waiter-list>
</objectlock>
<objectlock lockPartition="0" objid="921770341" subresource="FULL" dbid="5" objectname="NBU.dbo.Policy" id="lock5936e9900" mode="X" associatedObjectId="921770341">
<owner-list>
<owner id="process12e3a988" mode="X"/>
</owner-list>
<waiter-list>
<waiter id="process5c3b4c8" mode="X" requestType="wait"/>
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
</deadlock-list>
April 11, 2015 at 4:29 am
There's not enough here. These are part of a greater process. That other process has other statements. You need to determine the order in which these statements are occurring. This looks like a classic deadlock caused by two different processes accessing objects in differing orders. Probably part of the process reads from the same table that it's inserting into. Figure that out and you can determine how to go about fixing the deadlock.
Also, remember that deadlocks are fundamentally performance issues. If everything runs very fast, you'll never see a deadlock. From the look we have at this query, it might need to be tuned. Actually, from the look at the query, with all the VARCHAR(8000) over and over, I'm concerned that maybe your database needs to be redesigned a little bit.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply