January 22, 2019 at 4:00 am
how to reduce this deadlock.. it happen very frequently. deadlock_xml
<deadlock> <victim-list> <victimProcess id="processd4fe015848" /> </victim-list> <process-list> <process id="processd4fe015848" taskpriority="0" logused="1219420" waitresource="PAGE: 7:17:384615 " waittime="3550" ownerId="42364946100" transactionname="UPDATE" lasttranstarted="2018-12-31T16:30:37.147" XDES="0xd4f7c13a50" lockMode="IX" schedulerid="1" kpid="17264" status="suspended" spid="123" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-31T16:30:00.253" lastbatchcompleted="2018-12-31T16:30:00.253" lastattention="1900-01-01T00:00:00.253" clientapp="SQLAgent - TSQL JobStep (Job 0x845F2BC7C95158469B934457A3F40B0E : Step 1)" hostname="server-1" hostpid="3236" loginname="NT SERVICE\SQLSERVERAGENT" isolationlevel="read committed (2)" xactid="42364946100" currentdb="7" currentdbname="ecommerce" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056"> <executionStack> <frame procname="ecommerce.dbo.sp_ecommerce_maintenance_pricing_amazon" line="71" stmtstart="3222" stmtend="3548" sqlhandle="0x030007004af05643ace4bb0021a9000001000000000000000000000000000000000000000000000000000000">UPDATE dSET price = t.priceFROM product_details dJOIN (SELECT *FROM #tmp AS T1WHERE IdentityID BETWEEN @StartID and @EndID) t ON d.id = t.i </frame> <frame procname="adhoc" line="10" stmtstart="426" stmtend="514" sqlhandle="0x01000700e8b7a908603e2ce81701000000000000000000000000000000000000000000000000000000000000">exec sp_ecommerce_maintenance_pricing_amazo </frame> </executionStack> <inputbuf> exec sp_ecommerce_maintenance_pricing_amazon; </inputbuf> </process> <process id="processd50e057848" taskpriority="0" logused="8871956" waitresource="KEY: 7:72057983830851584 (1d7e35ee8c1d)" waittime="3528" ownerId="42364945781" transactionname="UPDATE" lasttranstarted="2018-12-31T16:30:36.900" XDES="0x11863c46040" lockMode="U" schedulerid="8" kpid="13720" status="suspended" spid="102" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2018-12-31T16:30:30.300" lastbatchcompleted="2018-12-31T16:30:30.300" lastattention="1900-01-01T00:00:00.300" clientapp="SQLAgent - TSQL JobStep (Job 0x769B7731D4C93E42A5C2D0804B6FB1DE : Step 1)" hostname="server-1" hostpid="3236" loginname="NT SERVICE\SQLSERVERAGENT" isolationlevel="read committed (2)" xactid="42364945781" currentdb="7" currentdbname="ecommerce" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056"> <executionStack> <frame procname="ecommerce.dbo.usp_ecommerceFileBulkProcess_Inventory_Batch_Process" line="362" stmtstart="43748" stmtend="44460" sqlhandle="0x03000700684e776dacbebd00a6a9000001000000000000000000000000000000000000000000000000000000">UPDATE T1 SET last_inventory_update = GETDATE() FROM product_details AS T1 JOIN [ecommerce_BulkProcess].[dbo].staging_Inventory_Batch_Process AS T2 ON T1.id = T2.id WHERE isnull(vendor_id, 0) IN (@vendorid, 0) AND BatchUID = @batchGUI </frame> <frame procname="ecommerce_BulkProcess.dbo.usp__ecommerce_BulkProcess_Inventory_Batch_Process" line="91" stmtstart="10074" stmtend="10702" sqlhandle="0x03000f00f358af6290c913011ca9000001000000000000000000000000000000000000000000000000000000">EXEC ecommerce.dbo.[usp_ecommerceFileBulkProcess_Inventory_Batch_Process] @batchGUID = @pBatchGUID, @vendorid = @vendorid, @removemissing = @removemissing, @processQueueID = @processQueueI </frame> <frame procname="adhoc" line="1" stmtend="110" sqlhandle="0x01000f00d936563380bca144e300000000000000000000000000000000000000000000000000000000000000">EXEC usp__ecommerce_BulkProcess_Inventory_Batch_Proces </frame> </executionStack> <inputbuf>EXEC usp__ecommerce_BulkProcess_Inventory_Batch_Process </inputbuf> </process> </process-list> <resource-list> <pagelock fileid="17" pageid="384615" dbid="7" subresource="FULL" objectname="ecommerce.dbo.product_details" id="lockd1298eaf00" mode="U" associatedObjectId="72058013127409664"> <owner-list> <owner id="processd50e057848" mode="U" /> </owner-list> <waiter-list> <waiter id="processd4fe015848" mode="IX" requestType="wait" /> </waiter-list> </pagelock> <keylock hobtid="72057983830851584" dbid="7" objectname="ecommerce.dbo.product_details" indexname="cx_product_details_id_pid" id="lock100d50dc180" mode="X" associatedObjectId="72057983830851584"> <owner-list> <owner id="processd4fe015848" mode="X" /> </owner-list> <waiter-list> <waiter id="processd50e057848" mode="U" requestType="wait" /> </waiter-list> </keylock> </resource-list></deadlock>
January 22, 2019 at 12:16 pm
Schedule these two procedures to run at different non-overlapping times:
sp_ecommerce_maintenance_pricing_amazon
usp__ecommerce_BulkProcess_Inventory_Batch_Process
January 22, 2019 at 9:36 pm
not possible , thats business requirement
sp_ecommerce_maintenance_pricing_amazon -- every 15min job
usp__ecommerce_BulkProcess_Inventory_Batch_Process -- every 1min job
January 24, 2019 at 7:16 pm
How long the jobs take if there is no deadlock?
_____________
Code for TallyGenerator
January 24, 2019 at 7:26 pm
Modify this statement:
UPDATE dS
ET price = t.price
FROM product_details d
JOIN (SELECT *FROM #tmp AS T1WHERE IdentityID BETWEEN @StartID and @EndID) t ON d.id = t.i
By adding
WHERE d.price t.price or d.price is null
There is no slightest point to update a value with exactly the same one.
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply