Deadlock Graph and Interpretation, solution to avoid The 2019 Stack Overflow Developer Survey Results Are InSET NOCOUNT Error in handling SQL call after upgradeSQL Server Deadlock Graph - Table, Page or Row Lock?Shared and IX locks causing deadlock (Sql server)Why are there Victimless Entries on the Deadlock Graph?Why isn't the objectname and indexname populated in deadlock graph?Understanding Deadlock graph on one tableWeird deadlock graphHow do I understand this DeadLock Graph?SQL Server deadlock graph explainedExplain SQL Server deadlock graph modesHelp in understanding how to avoid a page-level deadlock

Is bread bad for ducks?

Is it legal to have the "// (c) 2019 John Smith" header in all files when there are hundreds of contributors?

Where to refill my bottle in India?

What does "rabbited" mean/imply in this sentence?

New order #4: World

Is "plugging out" electronic devices an American expression?

Can I buy Keisei train tickets with international debit card?

Poison Arrows Piercing damage reduced to 0, do you still get poisoned?

It's possible to achieve negative score?

How can I create a character who can assume the widest possible range of creature sizes?

What is GPS' 19 year rollover and does it present a cybersecurity issue?

Supports in 3d printing

Python comments not highlighted with lstlisting

How can I fix this gap between bookcases I made?

Can I write a for loop that iterates over both collections and arrays?

Inline version of a function returns different value then non-inline version

What tool would a Roman-age civilisation use to reduce/breakup silver and other metals?

Do Shazam and Man of Steel exist in same universe?

Is it possible for the two major parties in the UK to form a coalition with each other instead of a much smaller party?

Does it makes sense to buy a new cycle to learn riding?

What do hard-Brexiteers want with respect to the Irish border?

What could be the right powersource for 15 seconds lifespan disposable giant chainsaw?

Can one use the reaction spell from the War Caster feat to cast Bigby's Hand?

Why the maximum length of openwrt’s root password is 8?



Deadlock Graph and Interpretation, solution to avoid



The 2019 Stack Overflow Developer Survey Results Are InSET NOCOUNT Error in handling SQL call after upgradeSQL Server Deadlock Graph - Table, Page or Row Lock?Shared and IX locks causing deadlock (Sql server)Why are there Victimless Entries on the Deadlock Graph?Why isn't the objectname and indexname populated in deadlock graph?Understanding Deadlock graph on one tableWeird deadlock graphHow do I understand this DeadLock Graph?SQL Server deadlock graph explainedExplain SQL Server deadlock graph modesHelp in understanding how to avoid a page-level deadlock



.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








2















I am supporting vendor based application, which is filled with blocking and deadlock. Deadlocks occur mostly involving two or three processes however I noticed yesterday, it was involving 9 SPIDs.



Can somebody please help me in understanding this deadlock graph and solution on how to avoid this.



<deadlock><victim-list><victimProcess id="process2ff017c28"/><victimProcess id="process2f1538108"/><victimProcess id="process2f618d088"/><victimProcess id="process2f6d828c8"/><victimProcess id="process2f6d83848"/><victimProcess id="process2da9b5468"/><victimProcess id="process2efac7468"/><victimProcess id="process2efac7848"/></victim-list><process-list><process id="process2ff017c28" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4412" ownerId="284194209" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:29:07.323" XDES="0x2a785f800" lockMode="IX" schedulerid="1" kpid="9052" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.733" lastbatchcompleted="2019-04-08T15:30:58.733" lastattention="1900-01-01T00:00:00.733" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284194209" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f1538108" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4411" ownerId="284107628" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:24:11.843" XDES="0xdacf54e0" lockMode="IX" schedulerid="1" kpid="7272" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.737" lastbatchcompleted="2019-04-08T15:30:58.727" lastattention="1900-01-01T00:00:00.727" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284107628" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f618d088" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4620" ownerId="284193487" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:28:59.513" XDES="0x20df303b0" lockMode="IX" schedulerid="2" kpid="2088" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.527" lastbatchcompleted="2019-04-08T15:30:58.527" lastattention="1900-01-01T00:00:00.527" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284193487" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f6d828c8" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4302" ownerId="284194269" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:29:07.743" XDES="0x25090c6d0" lockMode="IX" schedulerid="3" kpid="3140" status="suspended" spid="105" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.843" lastbatchcompleted="2019-04-08T15:30:58.843" lastattention="1900-01-01T00:00:00.843" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284194269" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f6d83848" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4319" ownerId="284178892" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:38.450" XDES="0xb83c63b0" lockMode="IX" schedulerid="3" kpid="7372" status="suspended" spid="81" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.827" lastbatchcompleted="2019-04-08T15:30:58.827" lastattention="1900-01-01T00:00:00.827" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284178892" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2da9b5468" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4300" ownerId="284174799" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:25:26.760" XDES="0x20d4683b0" lockMode="IX" schedulerid="3" kpid="5664" status="suspended" spid="97" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.847" lastbatchcompleted="2019-04-08T15:30:58.847" lastattention="1900-01-01T00:00:00.847" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284174799" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7468" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4394" ownerId="284192570" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:28:49.180" XDES="0x184e789f0" lockMode="IX" schedulerid="4" kpid="5348" status="suspended" spid="98" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.753" lastbatchcompleted="2019-04-08T15:30:58.753" lastattention="1900-01-01T00:00:00.753" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284192570" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7848" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4409" ownerId="284178168" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:23.063" XDES="0x2420aab80" lockMode="IX" schedulerid="4" kpid="4572" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.740" lastbatchcompleted="2019-04-08T15:30:58.737" lastattention="1900-01-01T00:00:00.737" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284178168" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7c28" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4413" ownerId="284177493" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:12.160" XDES="0xab9103b0" lockMode="IX" schedulerid="4" kpid="2448" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.733" lastbatchcompleted="2019-04-08T15:30:58.733" lastattention="1900-01-01T00:00:00.733" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284177493" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process></process-list><resource-list><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2ff017c28" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f1538108" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f618d088" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f618d088" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f618d088" mode="IX" requestType="convert"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f6d828c8" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f618d088" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f618d088" mode="IX" requestType="convert"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f6d83848" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7848" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7848" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2da9b5468" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7848" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7848" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7468" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7468" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7848" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7468" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7c28" mode="IX" requestType="convert"/></waiter-list></objectlock></resource-list></deadlock>


Isolation level is set as Read Committed Snapshot.



When I opened this deadlock graph in Sentry One plan Explorer, it was scary.



Deadlock Graph Sentry Plan Explorer



Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)










share|improve this question






















  • Can you include an execution plan please? It would be useful to see exactly what objects SQL is locking the data on (IE is it using a non-clustered index, a heap etc?)

    – George.Palacios
    2 hours ago






  • 1





    It's also worth saying that repeatable read could potentially be causing this - was each spid called with a different @P1 value do you know? The fact that the lock seems to be being taken out at the table level rather than the page level is likely the root cause.

    – George.Palacios
    2 hours ago

















2















I am supporting vendor based application, which is filled with blocking and deadlock. Deadlocks occur mostly involving two or three processes however I noticed yesterday, it was involving 9 SPIDs.



Can somebody please help me in understanding this deadlock graph and solution on how to avoid this.



<deadlock><victim-list><victimProcess id="process2ff017c28"/><victimProcess id="process2f1538108"/><victimProcess id="process2f618d088"/><victimProcess id="process2f6d828c8"/><victimProcess id="process2f6d83848"/><victimProcess id="process2da9b5468"/><victimProcess id="process2efac7468"/><victimProcess id="process2efac7848"/></victim-list><process-list><process id="process2ff017c28" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4412" ownerId="284194209" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:29:07.323" XDES="0x2a785f800" lockMode="IX" schedulerid="1" kpid="9052" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.733" lastbatchcompleted="2019-04-08T15:30:58.733" lastattention="1900-01-01T00:00:00.733" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284194209" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f1538108" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4411" ownerId="284107628" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:24:11.843" XDES="0xdacf54e0" lockMode="IX" schedulerid="1" kpid="7272" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.737" lastbatchcompleted="2019-04-08T15:30:58.727" lastattention="1900-01-01T00:00:00.727" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284107628" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f618d088" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4620" ownerId="284193487" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:28:59.513" XDES="0x20df303b0" lockMode="IX" schedulerid="2" kpid="2088" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.527" lastbatchcompleted="2019-04-08T15:30:58.527" lastattention="1900-01-01T00:00:00.527" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284193487" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f6d828c8" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4302" ownerId="284194269" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:29:07.743" XDES="0x25090c6d0" lockMode="IX" schedulerid="3" kpid="3140" status="suspended" spid="105" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.843" lastbatchcompleted="2019-04-08T15:30:58.843" lastattention="1900-01-01T00:00:00.843" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284194269" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f6d83848" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4319" ownerId="284178892" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:38.450" XDES="0xb83c63b0" lockMode="IX" schedulerid="3" kpid="7372" status="suspended" spid="81" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.827" lastbatchcompleted="2019-04-08T15:30:58.827" lastattention="1900-01-01T00:00:00.827" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284178892" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2da9b5468" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4300" ownerId="284174799" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:25:26.760" XDES="0x20d4683b0" lockMode="IX" schedulerid="3" kpid="5664" status="suspended" spid="97" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.847" lastbatchcompleted="2019-04-08T15:30:58.847" lastattention="1900-01-01T00:00:00.847" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284174799" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7468" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4394" ownerId="284192570" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:28:49.180" XDES="0x184e789f0" lockMode="IX" schedulerid="4" kpid="5348" status="suspended" spid="98" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.753" lastbatchcompleted="2019-04-08T15:30:58.753" lastattention="1900-01-01T00:00:00.753" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284192570" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7848" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4409" ownerId="284178168" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:23.063" XDES="0x2420aab80" lockMode="IX" schedulerid="4" kpid="4572" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.740" lastbatchcompleted="2019-04-08T15:30:58.737" lastattention="1900-01-01T00:00:00.737" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284178168" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7c28" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4413" ownerId="284177493" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:12.160" XDES="0xab9103b0" lockMode="IX" schedulerid="4" kpid="2448" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.733" lastbatchcompleted="2019-04-08T15:30:58.733" lastattention="1900-01-01T00:00:00.733" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284177493" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process></process-list><resource-list><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2ff017c28" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f1538108" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f618d088" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f618d088" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f618d088" mode="IX" requestType="convert"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f6d828c8" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f618d088" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f618d088" mode="IX" requestType="convert"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f6d83848" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7848" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7848" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2da9b5468" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7848" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7848" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7468" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7468" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7848" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7468" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7c28" mode="IX" requestType="convert"/></waiter-list></objectlock></resource-list></deadlock>


Isolation level is set as Read Committed Snapshot.



When I opened this deadlock graph in Sentry One plan Explorer, it was scary.



Deadlock Graph Sentry Plan Explorer



Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)










share|improve this question






















  • Can you include an execution plan please? It would be useful to see exactly what objects SQL is locking the data on (IE is it using a non-clustered index, a heap etc?)

    – George.Palacios
    2 hours ago






  • 1





    It's also worth saying that repeatable read could potentially be causing this - was each spid called with a different @P1 value do you know? The fact that the lock seems to be being taken out at the table level rather than the page level is likely the root cause.

    – George.Palacios
    2 hours ago













2












2








2








I am supporting vendor based application, which is filled with blocking and deadlock. Deadlocks occur mostly involving two or three processes however I noticed yesterday, it was involving 9 SPIDs.



Can somebody please help me in understanding this deadlock graph and solution on how to avoid this.



<deadlock><victim-list><victimProcess id="process2ff017c28"/><victimProcess id="process2f1538108"/><victimProcess id="process2f618d088"/><victimProcess id="process2f6d828c8"/><victimProcess id="process2f6d83848"/><victimProcess id="process2da9b5468"/><victimProcess id="process2efac7468"/><victimProcess id="process2efac7848"/></victim-list><process-list><process id="process2ff017c28" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4412" ownerId="284194209" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:29:07.323" XDES="0x2a785f800" lockMode="IX" schedulerid="1" kpid="9052" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.733" lastbatchcompleted="2019-04-08T15:30:58.733" lastattention="1900-01-01T00:00:00.733" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284194209" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f1538108" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4411" ownerId="284107628" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:24:11.843" XDES="0xdacf54e0" lockMode="IX" schedulerid="1" kpid="7272" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.737" lastbatchcompleted="2019-04-08T15:30:58.727" lastattention="1900-01-01T00:00:00.727" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284107628" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f618d088" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4620" ownerId="284193487" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:28:59.513" XDES="0x20df303b0" lockMode="IX" schedulerid="2" kpid="2088" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.527" lastbatchcompleted="2019-04-08T15:30:58.527" lastattention="1900-01-01T00:00:00.527" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284193487" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f6d828c8" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4302" ownerId="284194269" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:29:07.743" XDES="0x25090c6d0" lockMode="IX" schedulerid="3" kpid="3140" status="suspended" spid="105" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.843" lastbatchcompleted="2019-04-08T15:30:58.843" lastattention="1900-01-01T00:00:00.843" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284194269" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f6d83848" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4319" ownerId="284178892" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:38.450" XDES="0xb83c63b0" lockMode="IX" schedulerid="3" kpid="7372" status="suspended" spid="81" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.827" lastbatchcompleted="2019-04-08T15:30:58.827" lastattention="1900-01-01T00:00:00.827" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284178892" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2da9b5468" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4300" ownerId="284174799" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:25:26.760" XDES="0x20d4683b0" lockMode="IX" schedulerid="3" kpid="5664" status="suspended" spid="97" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.847" lastbatchcompleted="2019-04-08T15:30:58.847" lastattention="1900-01-01T00:00:00.847" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284174799" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7468" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4394" ownerId="284192570" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:28:49.180" XDES="0x184e789f0" lockMode="IX" schedulerid="4" kpid="5348" status="suspended" spid="98" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.753" lastbatchcompleted="2019-04-08T15:30:58.753" lastattention="1900-01-01T00:00:00.753" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284192570" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7848" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4409" ownerId="284178168" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:23.063" XDES="0x2420aab80" lockMode="IX" schedulerid="4" kpid="4572" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.740" lastbatchcompleted="2019-04-08T15:30:58.737" lastattention="1900-01-01T00:00:00.737" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284178168" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7c28" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4413" ownerId="284177493" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:12.160" XDES="0xab9103b0" lockMode="IX" schedulerid="4" kpid="2448" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.733" lastbatchcompleted="2019-04-08T15:30:58.733" lastattention="1900-01-01T00:00:00.733" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284177493" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process></process-list><resource-list><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2ff017c28" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f1538108" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f618d088" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f618d088" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f618d088" mode="IX" requestType="convert"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f6d828c8" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f618d088" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f618d088" mode="IX" requestType="convert"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f6d83848" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7848" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7848" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2da9b5468" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7848" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7848" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7468" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7468" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7848" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7468" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7c28" mode="IX" requestType="convert"/></waiter-list></objectlock></resource-list></deadlock>


Isolation level is set as Read Committed Snapshot.



When I opened this deadlock graph in Sentry One plan Explorer, it was scary.



Deadlock Graph Sentry Plan Explorer



Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)










share|improve this question














I am supporting vendor based application, which is filled with blocking and deadlock. Deadlocks occur mostly involving two or three processes however I noticed yesterday, it was involving 9 SPIDs.



Can somebody please help me in understanding this deadlock graph and solution on how to avoid this.



<deadlock><victim-list><victimProcess id="process2ff017c28"/><victimProcess id="process2f1538108"/><victimProcess id="process2f618d088"/><victimProcess id="process2f6d828c8"/><victimProcess id="process2f6d83848"/><victimProcess id="process2da9b5468"/><victimProcess id="process2efac7468"/><victimProcess id="process2efac7848"/></victim-list><process-list><process id="process2ff017c28" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4412" ownerId="284194209" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:29:07.323" XDES="0x2a785f800" lockMode="IX" schedulerid="1" kpid="9052" status="suspended" spid="63" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.733" lastbatchcompleted="2019-04-08T15:30:58.733" lastattention="1900-01-01T00:00:00.733" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284194209" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f1538108" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4411" ownerId="284107628" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:24:11.843" XDES="0xdacf54e0" lockMode="IX" schedulerid="1" kpid="7272" status="suspended" spid="73" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.737" lastbatchcompleted="2019-04-08T15:30:58.727" lastattention="1900-01-01T00:00:00.727" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284107628" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f618d088" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4620" ownerId="284193487" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:28:59.513" XDES="0x20df303b0" lockMode="IX" schedulerid="2" kpid="2088" status="suspended" spid="79" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.527" lastbatchcompleted="2019-04-08T15:30:58.527" lastattention="1900-01-01T00:00:00.527" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284193487" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f6d828c8" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4302" ownerId="284194269" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:29:07.743" XDES="0x25090c6d0" lockMode="IX" schedulerid="3" kpid="3140" status="suspended" spid="105" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.843" lastbatchcompleted="2019-04-08T15:30:58.843" lastattention="1900-01-01T00:00:00.843" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284194269" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2f6d83848" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4319" ownerId="284178892" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:38.450" XDES="0xb83c63b0" lockMode="IX" schedulerid="3" kpid="7372" status="suspended" spid="81" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.827" lastbatchcompleted="2019-04-08T15:30:58.827" lastattention="1900-01-01T00:00:00.827" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284178892" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2da9b5468" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4300" ownerId="284174799" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:25:26.760" XDES="0x20d4683b0" lockMode="IX" schedulerid="3" kpid="5664" status="suspended" spid="97" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.847" lastbatchcompleted="2019-04-08T15:30:58.847" lastattention="1900-01-01T00:00:00.847" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284174799" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7468" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4394" ownerId="284192570" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:28:49.180" XDES="0x184e789f0" lockMode="IX" schedulerid="4" kpid="5348" status="suspended" spid="98" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.753" lastbatchcompleted="2019-04-08T15:30:58.753" lastattention="1900-01-01T00:00:00.753" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284192570" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7848" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4409" ownerId="284178168" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:23.063" XDES="0x2420aab80" lockMode="IX" schedulerid="4" kpid="4572" status="suspended" spid="68" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.740" lastbatchcompleted="2019-04-08T15:30:58.737" lastattention="1900-01-01T00:00:00.737" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284178168" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process><process id="process2efac7c28" taskpriority="0" logused="0" waitresource="OBJECT: 11:1142295129:0 " waittime="4413" ownerId="284177493" transactionname="implicit_transaction" lasttranstarted="2019-04-08T15:26:12.160" XDES="0xab9103b0" lockMode="IX" schedulerid="4" kpid="2448" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-08T15:30:58.733" lastbatchcompleted="2019-04-08T15:30:58.733" lastattention="1900-01-01T00:00:00.733" clientapp="jTDS" hostname="APPNAME" hostpid="123" loginname="IRB_APP_USER" isolationlevel="repeatable read (3)" xactid="284177493" currentdb="11" currentdbname="Database_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="46" stmtend="180" sqlhandle="0x02000000b6b6e728e6bf289c908196a1f4e56b8892a5eab10000000000000000000000000000000000000000">
unknown </frame></executionStack><inputbuf>
(@P0 bigint,@P1 bigint)update Table_Name set STATUS_ID= @P0 where id= @P1 </inputbuf></process></process-list><resource-list><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2ff017c28" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f1538108" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2f6d828c8" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f618d088" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f618d088" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f618d088" mode="IX" requestType="convert"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f6d828c8" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f618d088" mode="S"/><owner id="process2da9b5468" mode="S"/><owner id="process2f618d088" mode="IX" requestType="convert"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2da9b5468" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2f6d83848" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7848" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7848" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2da9b5468" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7848" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7848" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7468" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7468" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7848" mode="IX" requestType="convert"/></waiter-list></objectlock><objectlock lockPartition="0" objid="1142295129" subresource="FULL" dbid="11" objectname="Database_Name.Schema_Name.Table_Name" id="lock2149d8800" mode="S" associatedObjectId="1142295129"><owner-list><owner id="process2ff017c28" mode="S"/><owner id="process2f6d83848" mode="S"/><owner id="process2efac7468" mode="S"/><owner id="process2ff017c28" mode="IX" requestType="convert"/><owner id="process2efac7468" mode="IX" requestType="convert"/><owner id="process2f6d83848" mode="IX" requestType="convert"/></owner-list><waiter-list><waiter id="process2efac7c28" mode="IX" requestType="convert"/></waiter-list></objectlock></resource-list></deadlock>


Isolation level is set as Read Committed Snapshot.



When I opened this deadlock graph in Sentry One plan Explorer, it was scary.



Deadlock Graph Sentry Plan Explorer



Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 14393: ) (Hypervisor)







sql-server deadlock






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked 2 hours ago









Learning_DBAdminLearning_DBAdmin

504214




504214












  • Can you include an execution plan please? It would be useful to see exactly what objects SQL is locking the data on (IE is it using a non-clustered index, a heap etc?)

    – George.Palacios
    2 hours ago






  • 1





    It's also worth saying that repeatable read could potentially be causing this - was each spid called with a different @P1 value do you know? The fact that the lock seems to be being taken out at the table level rather than the page level is likely the root cause.

    – George.Palacios
    2 hours ago

















  • Can you include an execution plan please? It would be useful to see exactly what objects SQL is locking the data on (IE is it using a non-clustered index, a heap etc?)

    – George.Palacios
    2 hours ago






  • 1





    It's also worth saying that repeatable read could potentially be causing this - was each spid called with a different @P1 value do you know? The fact that the lock seems to be being taken out at the table level rather than the page level is likely the root cause.

    – George.Palacios
    2 hours ago
















Can you include an execution plan please? It would be useful to see exactly what objects SQL is locking the data on (IE is it using a non-clustered index, a heap etc?)

– George.Palacios
2 hours ago





Can you include an execution plan please? It would be useful to see exactly what objects SQL is locking the data on (IE is it using a non-clustered index, a heap etc?)

– George.Palacios
2 hours ago




1




1





It's also worth saying that repeatable read could potentially be causing this - was each spid called with a different @P1 value do you know? The fact that the lock seems to be being taken out at the table level rather than the page level is likely the root cause.

– George.Palacios
2 hours ago





It's also worth saying that repeatable read could potentially be causing this - was each spid called with a different @P1 value do you know? The fact that the lock seems to be being taken out at the table level rather than the page level is likely the root cause.

– George.Palacios
2 hours ago










3 Answers
3






active

oldest

votes


















5














Another problem in your XML graph is that you appear to have "implicit_transactions" turned on:




transactionname="implicit_transaction"




This can also lead to blocking if the application doesn't explicitly close transactions. This can be set at the connection level, or by calling SET IMPLICIT TRANSACTIONS ON explicitly.



Check out these times:



screenshot of Plan Explorer deadlock graph



You have transactions that started at 15:25:26 that are still running at 15:30:58. Five minutes is usually way too long to be holding locks - it's a recipe for blocking and deadlocks. This seems like potentially an accidental open lingering transaction.



If you have any control over the application's connection string, verify that they are not turning this setting on. Also, double check that this isn't set at the server level via sp_configure 'user options (see here for more detail).






share|improve this answer






























    5














    This definitely seems to be a bad combination of isolation level, lock escalation, and many sessions each issuing multiple queries inside the same transaction.



    If you expand the waiter/owner list you see they are all trying to access the same resource (presumably the entire table):




    enter image description here




    Also a slightly less scary way to see this is to optimize layout and use force directed:




    enter image description here




    If you replay the deadlock you will see all of these different sessions holding their transactions open for relatively long periods of time (5+ minutes, which is crazy, though the animation really hides that), and issuing multiple queries intermittently. You need to drastically reduce the amount of time you spend inside those transactions (or get rid of them altogether), collapse multiple queries for different parameter values into a single, point-in-time query, build better indexes so that escalation doesn't happen, stop forcing repeatable read, created a clustered index (if I'm reading the resource descriptor right, that's a heap!?), or all of the above.






    share|improve this answer






























      5














      Your default isolation level might be Read Committed Snapshot,
      but the isolation level set by your application = repeatable read (3)



      For all the update statements in the deadlock, these are the isolationlevels:



      isolationlevel="repeatable read (3)"


      This is done at the connection level with this command:



      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ



      the Repeatable Read SQL Server isolation level prevents dirty reads
      and not-repeatable reads. It is achieved by placing shared locks on
      all data that is read by each statement in a transaction and all the
      locks are held until the transaction completes. As a result other
      transactions are unable to modify the data that has been read by the
      current transaction. However, it does not prevent other transactions
      from inserting new rows into the tables which have been selected in
      the current transaction
      A part of the explanation on repeatable read here




      Transactions blocking eachother, with lock escalation under the repeatable read isolation level can cause blocking and as a byproduct of blocking, deadlocks.



      The blocked queries where waiting on the resource:



      waitresource="OBJECT: 11:1142295129:0 "


      Where the several processes have Shared locks on this entire object:



      <owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/>


      and these same processes have several intent exclusive locks on these objects



       <owner id="process2f6d828c8" mode="IX" requestType="convert"/>


      Whereas the requestType="convert" explains that the processes are trying to convert the shared lock into an IX lock.



      And as @David Browne - Microsoft explained:




      In Repeatable Read the object-level S locks that conflict with the
      pending IX locks could have been acquired on an earlier query in the
      transaction.




      Meaning that a reasonable explanation is that all these transactions are trying to convert their own shared lock (e.g. because of a previous select) into an Exclusive lock (Update statement). As a result of being in the repeatable read isolation level they do not want to give up their locks, resulting in deadlocks.



      What can you do



      You should contact the application team / vendor and see if this a necessary evil, because blocking will be more prevalent.



      Next to checking up on the isolation level, you should look into speeding up the queries inside the transactions by adding indexes.






      share|improve this answer




















      • 1





        In Repeatable Read the object-level S locks that conflict with the pending IX locks could have been acquired on an earlier query in the transaction.

        – David Browne - Microsoft
        2 hours ago











      • @DavidBrowne-Microsoft You are correct, makes sense when seeing the requestType="convert" popping up for all these IX locks. Added it, thanks!

        – Randi Vertongen
        1 hour ago











      Your Answer








      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "182"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      imageUploader:
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      ,
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );













      draft saved

      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234303%2fdeadlock-graph-and-interpretation-solution-to-avoid%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      3 Answers
      3






      active

      oldest

      votes








      3 Answers
      3






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      5














      Another problem in your XML graph is that you appear to have "implicit_transactions" turned on:




      transactionname="implicit_transaction"




      This can also lead to blocking if the application doesn't explicitly close transactions. This can be set at the connection level, or by calling SET IMPLICIT TRANSACTIONS ON explicitly.



      Check out these times:



      screenshot of Plan Explorer deadlock graph



      You have transactions that started at 15:25:26 that are still running at 15:30:58. Five minutes is usually way too long to be holding locks - it's a recipe for blocking and deadlocks. This seems like potentially an accidental open lingering transaction.



      If you have any control over the application's connection string, verify that they are not turning this setting on. Also, double check that this isn't set at the server level via sp_configure 'user options (see here for more detail).






      share|improve this answer



























        5














        Another problem in your XML graph is that you appear to have "implicit_transactions" turned on:




        transactionname="implicit_transaction"




        This can also lead to blocking if the application doesn't explicitly close transactions. This can be set at the connection level, or by calling SET IMPLICIT TRANSACTIONS ON explicitly.



        Check out these times:



        screenshot of Plan Explorer deadlock graph



        You have transactions that started at 15:25:26 that are still running at 15:30:58. Five minutes is usually way too long to be holding locks - it's a recipe for blocking and deadlocks. This seems like potentially an accidental open lingering transaction.



        If you have any control over the application's connection string, verify that they are not turning this setting on. Also, double check that this isn't set at the server level via sp_configure 'user options (see here for more detail).






        share|improve this answer

























          5












          5








          5







          Another problem in your XML graph is that you appear to have "implicit_transactions" turned on:




          transactionname="implicit_transaction"




          This can also lead to blocking if the application doesn't explicitly close transactions. This can be set at the connection level, or by calling SET IMPLICIT TRANSACTIONS ON explicitly.



          Check out these times:



          screenshot of Plan Explorer deadlock graph



          You have transactions that started at 15:25:26 that are still running at 15:30:58. Five minutes is usually way too long to be holding locks - it's a recipe for blocking and deadlocks. This seems like potentially an accidental open lingering transaction.



          If you have any control over the application's connection string, verify that they are not turning this setting on. Also, double check that this isn't set at the server level via sp_configure 'user options (see here for more detail).






          share|improve this answer













          Another problem in your XML graph is that you appear to have "implicit_transactions" turned on:




          transactionname="implicit_transaction"




          This can also lead to blocking if the application doesn't explicitly close transactions. This can be set at the connection level, or by calling SET IMPLICIT TRANSACTIONS ON explicitly.



          Check out these times:



          screenshot of Plan Explorer deadlock graph



          You have transactions that started at 15:25:26 that are still running at 15:30:58. Five minutes is usually way too long to be holding locks - it's a recipe for blocking and deadlocks. This seems like potentially an accidental open lingering transaction.



          If you have any control over the application's connection string, verify that they are not turning this setting on. Also, double check that this isn't set at the server level via sp_configure 'user options (see here for more detail).







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered 2 hours ago









          Josh DarnellJosh Darnell

          7,94522243




          7,94522243























              5














              This definitely seems to be a bad combination of isolation level, lock escalation, and many sessions each issuing multiple queries inside the same transaction.



              If you expand the waiter/owner list you see they are all trying to access the same resource (presumably the entire table):




              enter image description here




              Also a slightly less scary way to see this is to optimize layout and use force directed:




              enter image description here




              If you replay the deadlock you will see all of these different sessions holding their transactions open for relatively long periods of time (5+ minutes, which is crazy, though the animation really hides that), and issuing multiple queries intermittently. You need to drastically reduce the amount of time you spend inside those transactions (or get rid of them altogether), collapse multiple queries for different parameter values into a single, point-in-time query, build better indexes so that escalation doesn't happen, stop forcing repeatable read, created a clustered index (if I'm reading the resource descriptor right, that's a heap!?), or all of the above.






              share|improve this answer



























                5














                This definitely seems to be a bad combination of isolation level, lock escalation, and many sessions each issuing multiple queries inside the same transaction.



                If you expand the waiter/owner list you see they are all trying to access the same resource (presumably the entire table):




                enter image description here




                Also a slightly less scary way to see this is to optimize layout and use force directed:




                enter image description here




                If you replay the deadlock you will see all of these different sessions holding their transactions open for relatively long periods of time (5+ minutes, which is crazy, though the animation really hides that), and issuing multiple queries intermittently. You need to drastically reduce the amount of time you spend inside those transactions (or get rid of them altogether), collapse multiple queries for different parameter values into a single, point-in-time query, build better indexes so that escalation doesn't happen, stop forcing repeatable read, created a clustered index (if I'm reading the resource descriptor right, that's a heap!?), or all of the above.






                share|improve this answer

























                  5












                  5








                  5







                  This definitely seems to be a bad combination of isolation level, lock escalation, and many sessions each issuing multiple queries inside the same transaction.



                  If you expand the waiter/owner list you see they are all trying to access the same resource (presumably the entire table):




                  enter image description here




                  Also a slightly less scary way to see this is to optimize layout and use force directed:




                  enter image description here




                  If you replay the deadlock you will see all of these different sessions holding their transactions open for relatively long periods of time (5+ minutes, which is crazy, though the animation really hides that), and issuing multiple queries intermittently. You need to drastically reduce the amount of time you spend inside those transactions (or get rid of them altogether), collapse multiple queries for different parameter values into a single, point-in-time query, build better indexes so that escalation doesn't happen, stop forcing repeatable read, created a clustered index (if I'm reading the resource descriptor right, that's a heap!?), or all of the above.






                  share|improve this answer













                  This definitely seems to be a bad combination of isolation level, lock escalation, and many sessions each issuing multiple queries inside the same transaction.



                  If you expand the waiter/owner list you see they are all trying to access the same resource (presumably the entire table):




                  enter image description here




                  Also a slightly less scary way to see this is to optimize layout and use force directed:




                  enter image description here




                  If you replay the deadlock you will see all of these different sessions holding their transactions open for relatively long periods of time (5+ minutes, which is crazy, though the animation really hides that), and issuing multiple queries intermittently. You need to drastically reduce the amount of time you spend inside those transactions (or get rid of them altogether), collapse multiple queries for different parameter values into a single, point-in-time query, build better indexes so that escalation doesn't happen, stop forcing repeatable read, created a clustered index (if I'm reading the resource descriptor right, that's a heap!?), or all of the above.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered 2 hours ago









                  Aaron BertrandAaron Bertrand

                  154k18298493




                  154k18298493





















                      5














                      Your default isolation level might be Read Committed Snapshot,
                      but the isolation level set by your application = repeatable read (3)



                      For all the update statements in the deadlock, these are the isolationlevels:



                      isolationlevel="repeatable read (3)"


                      This is done at the connection level with this command:



                      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ



                      the Repeatable Read SQL Server isolation level prevents dirty reads
                      and not-repeatable reads. It is achieved by placing shared locks on
                      all data that is read by each statement in a transaction and all the
                      locks are held until the transaction completes. As a result other
                      transactions are unable to modify the data that has been read by the
                      current transaction. However, it does not prevent other transactions
                      from inserting new rows into the tables which have been selected in
                      the current transaction
                      A part of the explanation on repeatable read here




                      Transactions blocking eachother, with lock escalation under the repeatable read isolation level can cause blocking and as a byproduct of blocking, deadlocks.



                      The blocked queries where waiting on the resource:



                      waitresource="OBJECT: 11:1142295129:0 "


                      Where the several processes have Shared locks on this entire object:



                      <owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/>


                      and these same processes have several intent exclusive locks on these objects



                       <owner id="process2f6d828c8" mode="IX" requestType="convert"/>


                      Whereas the requestType="convert" explains that the processes are trying to convert the shared lock into an IX lock.



                      And as @David Browne - Microsoft explained:




                      In Repeatable Read the object-level S locks that conflict with the
                      pending IX locks could have been acquired on an earlier query in the
                      transaction.




                      Meaning that a reasonable explanation is that all these transactions are trying to convert their own shared lock (e.g. because of a previous select) into an Exclusive lock (Update statement). As a result of being in the repeatable read isolation level they do not want to give up their locks, resulting in deadlocks.



                      What can you do



                      You should contact the application team / vendor and see if this a necessary evil, because blocking will be more prevalent.



                      Next to checking up on the isolation level, you should look into speeding up the queries inside the transactions by adding indexes.






                      share|improve this answer




















                      • 1





                        In Repeatable Read the object-level S locks that conflict with the pending IX locks could have been acquired on an earlier query in the transaction.

                        – David Browne - Microsoft
                        2 hours ago











                      • @DavidBrowne-Microsoft You are correct, makes sense when seeing the requestType="convert" popping up for all these IX locks. Added it, thanks!

                        – Randi Vertongen
                        1 hour ago















                      5














                      Your default isolation level might be Read Committed Snapshot,
                      but the isolation level set by your application = repeatable read (3)



                      For all the update statements in the deadlock, these are the isolationlevels:



                      isolationlevel="repeatable read (3)"


                      This is done at the connection level with this command:



                      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ



                      the Repeatable Read SQL Server isolation level prevents dirty reads
                      and not-repeatable reads. It is achieved by placing shared locks on
                      all data that is read by each statement in a transaction and all the
                      locks are held until the transaction completes. As a result other
                      transactions are unable to modify the data that has been read by the
                      current transaction. However, it does not prevent other transactions
                      from inserting new rows into the tables which have been selected in
                      the current transaction
                      A part of the explanation on repeatable read here




                      Transactions blocking eachother, with lock escalation under the repeatable read isolation level can cause blocking and as a byproduct of blocking, deadlocks.



                      The blocked queries where waiting on the resource:



                      waitresource="OBJECT: 11:1142295129:0 "


                      Where the several processes have Shared locks on this entire object:



                      <owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/>


                      and these same processes have several intent exclusive locks on these objects



                       <owner id="process2f6d828c8" mode="IX" requestType="convert"/>


                      Whereas the requestType="convert" explains that the processes are trying to convert the shared lock into an IX lock.



                      And as @David Browne - Microsoft explained:




                      In Repeatable Read the object-level S locks that conflict with the
                      pending IX locks could have been acquired on an earlier query in the
                      transaction.




                      Meaning that a reasonable explanation is that all these transactions are trying to convert their own shared lock (e.g. because of a previous select) into an Exclusive lock (Update statement). As a result of being in the repeatable read isolation level they do not want to give up their locks, resulting in deadlocks.



                      What can you do



                      You should contact the application team / vendor and see if this a necessary evil, because blocking will be more prevalent.



                      Next to checking up on the isolation level, you should look into speeding up the queries inside the transactions by adding indexes.






                      share|improve this answer




















                      • 1





                        In Repeatable Read the object-level S locks that conflict with the pending IX locks could have been acquired on an earlier query in the transaction.

                        – David Browne - Microsoft
                        2 hours ago











                      • @DavidBrowne-Microsoft You are correct, makes sense when seeing the requestType="convert" popping up for all these IX locks. Added it, thanks!

                        – Randi Vertongen
                        1 hour ago













                      5












                      5








                      5







                      Your default isolation level might be Read Committed Snapshot,
                      but the isolation level set by your application = repeatable read (3)



                      For all the update statements in the deadlock, these are the isolationlevels:



                      isolationlevel="repeatable read (3)"


                      This is done at the connection level with this command:



                      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ



                      the Repeatable Read SQL Server isolation level prevents dirty reads
                      and not-repeatable reads. It is achieved by placing shared locks on
                      all data that is read by each statement in a transaction and all the
                      locks are held until the transaction completes. As a result other
                      transactions are unable to modify the data that has been read by the
                      current transaction. However, it does not prevent other transactions
                      from inserting new rows into the tables which have been selected in
                      the current transaction
                      A part of the explanation on repeatable read here




                      Transactions blocking eachother, with lock escalation under the repeatable read isolation level can cause blocking and as a byproduct of blocking, deadlocks.



                      The blocked queries where waiting on the resource:



                      waitresource="OBJECT: 11:1142295129:0 "


                      Where the several processes have Shared locks on this entire object:



                      <owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/>


                      and these same processes have several intent exclusive locks on these objects



                       <owner id="process2f6d828c8" mode="IX" requestType="convert"/>


                      Whereas the requestType="convert" explains that the processes are trying to convert the shared lock into an IX lock.



                      And as @David Browne - Microsoft explained:




                      In Repeatable Read the object-level S locks that conflict with the
                      pending IX locks could have been acquired on an earlier query in the
                      transaction.




                      Meaning that a reasonable explanation is that all these transactions are trying to convert their own shared lock (e.g. because of a previous select) into an Exclusive lock (Update statement). As a result of being in the repeatable read isolation level they do not want to give up their locks, resulting in deadlocks.



                      What can you do



                      You should contact the application team / vendor and see if this a necessary evil, because blocking will be more prevalent.



                      Next to checking up on the isolation level, you should look into speeding up the queries inside the transactions by adding indexes.






                      share|improve this answer















                      Your default isolation level might be Read Committed Snapshot,
                      but the isolation level set by your application = repeatable read (3)



                      For all the update statements in the deadlock, these are the isolationlevels:



                      isolationlevel="repeatable read (3)"


                      This is done at the connection level with this command:



                      SET TRANSACTION ISOLATION LEVEL REPEATABLE READ



                      the Repeatable Read SQL Server isolation level prevents dirty reads
                      and not-repeatable reads. It is achieved by placing shared locks on
                      all data that is read by each statement in a transaction and all the
                      locks are held until the transaction completes. As a result other
                      transactions are unable to modify the data that has been read by the
                      current transaction. However, it does not prevent other transactions
                      from inserting new rows into the tables which have been selected in
                      the current transaction
                      A part of the explanation on repeatable read here




                      Transactions blocking eachother, with lock escalation under the repeatable read isolation level can cause blocking and as a byproduct of blocking, deadlocks.



                      The blocked queries where waiting on the resource:



                      waitresource="OBJECT: 11:1142295129:0 "


                      Where the several processes have Shared locks on this entire object:



                      <owner id="process2ff017c28" mode="S"/><owner id="process2f6d828c8" mode="S"/><owner id="process2da9b5468" mode="S"/>


                      and these same processes have several intent exclusive locks on these objects



                       <owner id="process2f6d828c8" mode="IX" requestType="convert"/>


                      Whereas the requestType="convert" explains that the processes are trying to convert the shared lock into an IX lock.



                      And as @David Browne - Microsoft explained:




                      In Repeatable Read the object-level S locks that conflict with the
                      pending IX locks could have been acquired on an earlier query in the
                      transaction.




                      Meaning that a reasonable explanation is that all these transactions are trying to convert their own shared lock (e.g. because of a previous select) into an Exclusive lock (Update statement). As a result of being in the repeatable read isolation level they do not want to give up their locks, resulting in deadlocks.



                      What can you do



                      You should contact the application team / vendor and see if this a necessary evil, because blocking will be more prevalent.



                      Next to checking up on the isolation level, you should look into speeding up the queries inside the transactions by adding indexes.







                      share|improve this answer














                      share|improve this answer



                      share|improve this answer








                      edited 1 hour ago

























                      answered 2 hours ago









                      Randi VertongenRandi Vertongen

                      4,6261924




                      4,6261924







                      • 1





                        In Repeatable Read the object-level S locks that conflict with the pending IX locks could have been acquired on an earlier query in the transaction.

                        – David Browne - Microsoft
                        2 hours ago











                      • @DavidBrowne-Microsoft You are correct, makes sense when seeing the requestType="convert" popping up for all these IX locks. Added it, thanks!

                        – Randi Vertongen
                        1 hour ago












                      • 1





                        In Repeatable Read the object-level S locks that conflict with the pending IX locks could have been acquired on an earlier query in the transaction.

                        – David Browne - Microsoft
                        2 hours ago











                      • @DavidBrowne-Microsoft You are correct, makes sense when seeing the requestType="convert" popping up for all these IX locks. Added it, thanks!

                        – Randi Vertongen
                        1 hour ago







                      1




                      1





                      In Repeatable Read the object-level S locks that conflict with the pending IX locks could have been acquired on an earlier query in the transaction.

                      – David Browne - Microsoft
                      2 hours ago





                      In Repeatable Read the object-level S locks that conflict with the pending IX locks could have been acquired on an earlier query in the transaction.

                      – David Browne - Microsoft
                      2 hours ago













                      @DavidBrowne-Microsoft You are correct, makes sense when seeing the requestType="convert" popping up for all these IX locks. Added it, thanks!

                      – Randi Vertongen
                      1 hour ago





                      @DavidBrowne-Microsoft You are correct, makes sense when seeing the requestType="convert" popping up for all these IX locks. Added it, thanks!

                      – Randi Vertongen
                      1 hour ago

















                      draft saved

                      draft discarded
















































                      Thanks for contributing an answer to Database Administrators Stack Exchange!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid


                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.

                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234303%2fdeadlock-graph-and-interpretation-solution-to-avoid%23new-answer', 'question_page');

                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      How should I use the fbox command correctly to avoid producing a Bad Box message?How to put a long piece of text in a box?How to specify height and width of fboxIs there an arrayrulecolor-like command to change the rule color of fbox?What is the command to highlight bad boxes in pdf?Why does fbox sometimes place the box *over* the graphic image?how to put the text in the boxHow to create command for a box where text inside the box can automatically adjust?how can I make an fbox like command with certain color, shape and width of border?how to use fbox in align modeFbox increase the spacing between the box and it content (inner margin)how to change the box height of an equationWhat is the use of the hbox in a newcommand command?

                      Doxepinum Nexus interni Notae | Tabula navigationis3158DB01142WHOa682390"Structural Analysis of the Histamine H1 Receptor""Transdermal and Topical Drug Administration in the Treatment of Pain""Antidepressants as antipruritic agents: A review"

                      inputenc: Unicode character … not set up for use with LaTeX The Next CEO of Stack OverflowEntering Unicode characters in LaTeXHow to solve the `Package inputenc Error: Unicode char not set up for use with LaTeX` problem?solve “Unicode char is not set up for use with LaTeX” without special handling of every new interesting UTF-8 characterPackage inputenc Error: Unicode character ² (U+B2)(inputenc) not set up for use with LaTeX. acroI2C[I²C]package inputenc error unicode char (u + 190) not set up for use with latexPackage inputenc Error: Unicode char u8:′ not set up for use with LaTeX. 3′inputenc Error: Unicode char u8: not set up for use with LaTeX with G-BriefPackage Inputenc Error: Unicode char u8: not set up for use with LaTeXPackage inputenc Error: Unicode char ́ (U+301)(inputenc) not set up for use with LaTeX. includePackage inputenc Error: Unicode char ̂ (U+302)(inputenc) not set up for use with LaTeX. … $widehatleft (OA,AA' right )$Package inputenc Error: Unicode char â„¡ (U+2121)(inputenc) not set up for use with LaTeX. printbibliography[heading=bibintoc]Package inputenc Error: Unicode char − (U+2212)(inputenc) not set up for use with LaTeXPackage inputenc Error: Unicode character α (U+3B1) not set up for use with LaTeXPackage inputenc Error: Unicode characterError: ! Package inputenc Error: Unicode char ⊘ (U+2298)(inputenc) not set up for use with LaTeX