Deadlocks on same PK for 2 update queries
-
Hi,
I'm a bit stumped here. I have 2 queries running from an application causing deadlocks. The table has 4.5 million rows, PK is SEQ and these are the following queries.
<deadlock>
<victim-list>
<victimProcess id="process2e9523c38" />
</victim-list>
<process-list>
<process id="process2e9523c38" taskpriority="0" logused="952" waitresource="KEY: 8:72057594092716032 (32de254ed832)" waittime="3113" ownerId="887063654" transactionname="implicit_transaction" lasttranstarted="2017-03-10T09:40:36.247" XDES="0x50fd703a8" lockMode="X" schedulerid="21" kpid="14672" status="suspended" spid="75" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-03-10T09:40:36.250" lastbatchcompleted="2017-03-10T09:40:36.247" lastattention="1900-01-01T00:00:00.247" clientapp="jTDS" hostname="XXXXXXXX4" hostpid="123" loginname="c2" isolationlevel="read committed (2)" xactid="887063654" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="94" sqlhandle="0x0200000059ada205f2fb6216402f13aaee17fc147a0a5a690000000000000000000000000000000000000000">
UPDATE [awb_move] WITH(rowlock) set [exp_pieces] = @1,[exp_weight] = @2,[volume] = @3 WHERE [seq]=@4 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x0200000000548b12341b9539be2100abdaff11e389de60530000000000000000000000000000000000000000">
Update awb_move with (rowlock) set exp_pieces = 22, exp_weight = 149.0, volume = 1.380 where seq = 50126824 </frame>
</executionStack>
<inputbuf>
Update awb_move with (rowlock) set exp_pieces = 22, exp_weight = 149.0, volume = 1.380 where seq = 50126824 </inputbuf>
</process>
<process id="process3c313d868" taskpriority="0" logused="3888" waitresource="KEY: 8:72057594092716032 (e1db1f6aa7f9)" waittime="3107" ownerId="887063008" transactionname="implicit_transaction" lasttranstarted="2017-03-10T09:40:36.203" XDES="0x45b9983a8" lockMode="U" schedulerid="22" kpid="7324" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2017-03-10T09:40:36.253" lastbatchcompleted="2017-03-10T09:40:36.253" lastattention="1900-01-01T00:00:00.253" clientapp="jTDS" hostname="XXXXXXXXXV0354" hostpid="123" loginname="c2" isolationlevel="read committed (2)" xactid="887063008" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="64" sqlhandle="0x02000000c0bb111f4c3a386be38e3ec1e878fe13de00eb360000000000000000000000000000000000000000">
UPDATE [awb_move] WITH(rowlock) set [import_status] = @1,[next_move_seq] = @2 WHERE [seq]=@3 </frame>
<frame procname="adhoc" line="1" sqlhandle="0x020000004a61802555f4bba25fd81d831359209cc4550bac0000000000000000000000000000000000000000">
update awb_move with (rowlock) set import_status = 'CKD', next_move_seq = 50126824 where seq in (50142309) </frame>
</executionStack>
<inputbuf>
update awb_move with (rowlock) set import_status = 'CKD', next_move_seq = 50126824 where seq in (50142309) </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057594092716032" dbid="8" objectname="AWB_Move" indexname="PK__AWB_Move__4AB81AF0" id="lock418175e00" mode="X" associatedObjectId="72057594092716032">
<owner-list>
<owner id="process3c313d868" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process2e9523c38" mode="X" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057594092716032" dbid="8" objectname=".AWB_Move" indexname="PK__AWB_Move__4AB81AF0" id="lock47319ae80" mode="X" associatedObjectId="72057594092716032">
<owner-list>
<owner id="process2e9523c38" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process3c313d868" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>the execution plans are
I changed the IN to = for testing but no difference and i commented out 'next_move_seq' in the top query and this changes the execution plan to the lower one. There are no constraints or triggers on this column.
Maybe i'm missing something obvious here but could anyone advise
1) why the deadlocks are happening
2) why the 1st execution plan is so much more expensive and what's causing the additional tasks? -
Based on the pictures, not really. For real assistance we would need the execution plans as .sqlplan files, the deadlock graph as an .xml file (may need to upload as a .txt file), the DDL for the table(s) including index definitions.
Not sure if you can post this information or not.
-
Hi, here are the files.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply