0

Earlier we used to have reader vs writer and writer vs reader deadlock, which is resolved by setting isolation level at application configuration level from "Repeatable Read" to "Read Committed" which is also default isolation level of SQL server. Even though I had set isolation level as Snapshot(Read Committed Snapshot) however that was overridden by application and hence we were suffering like anything. You may refer to my earlier question and answer provided.

After changing above, our issue is solved up to 95% and only few cases are remaining wherein writer is blocking another writer and since its for same object, they end up in deadlock. Basis my knowledge(Whatever I have gained so far), writer vs writer can't be solved even if we change isolation level to Snapshot or RCSI as delete/update will eventually turn lock into key/exclusive lock for maintaining data consistency.

Please find below the deadlock XDL:

<deadlock><victim-list><victimProcess id="process2f6d83848"/></victim-list><process-list><process id="process2f6d83848" taskpriority="0" logused="3016" waitresource="KEY: 11:72057594180993024 (d005aa042de1)" waittime="2024" ownerId="507214355" transactionname="implicit_transaction" lasttranstarted="2019-04-30T12:16:15.163" XDES="0x160bf0d90" lockMode="S" schedulerid="3" kpid="8176" status="suspended" spid="108" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-30T12:16:16.167" lastbatchcompleted="2019-04-30T12:16:16.167" lastattention="1900-01-01T00:00:00.167" clientapp="jTDS" hostname="Server_Name02" hostpid="123" loginname="DB_User_Name" isolationlevel="read committed (2)" xactid="507214355" currentdb="11" currentdbname="DB_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="202" stmtend="334" sqlhandle="0x02000000fff9ec078b33c8d7ab0a8244de78996c4df2befc0000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@P0 bigint,@P1 int)/* delete company_name */ delete from Table_Name where id= @P0  and [VERSION]= @P1    </inputbuf></process><process id="process2efac7c28" taskpriority="0" logused="3480" waitresource="KEY: 11:72057594180993024 (6e26e74c5ba9)" waittime="2035" ownerId="507214354" transactionname="implicit_transaction" lasttranstarted="2019-04-30T12:16:15.160" XDES="0x1e3356b80" lockMode="S" schedulerid="4" kpid="2964" status="suspended" spid="77" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2019-04-30T12:16:16.013" lastbatchcompleted="2019-04-30T12:16:16.013" lastattention="1900-01-01T00:00:00.013" clientapp="jTDS" hostname="Server_Name01" hostpid="123" loginname="DB_User_Name" isolationlevel="read committed (2)" xactid="507214354" currentdb="11" currentdbname="DB_Name" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"><executionStack><frame procname="adhoc" line="1" stmtstart="202" stmtend="334" sqlhandle="0x02000000fff9ec078b33c8d7ab0a8244de78996c4df2befc0000000000000000000000000000000000000000">
unknown    </frame></executionStack><inputbuf>
(@P0 bigint,@P1 int)/* delete company_name */ delete from Table_Name where id= @P0  and [VERSION]= @P1    </inputbuf></process></process-list><resource-list><keylock hobtid="72057594180993024" dbid="11" objectname="DB_Name.Schema_name.Table_Name_2" indexname="Table_Name_PK" id="lock2f03baa00" mode="X" associatedObjectId="72057594180993024"><owner-list><owner id="process2efac7c28" mode="X"/></owner-list><waiter-list><waiter id="process2f6d83848" mode="S" requestType="wait"/></waiter-list></keylock><keylock hobtid="72057594180993024" dbid="11" objectname="DB_Name.Schema_name.Table_Name_2" indexname="Table_Name_PK" id="lock2f2ca4780" mode="X" associatedObjectId="72057594180993024"><owner-list><owner id="process2f6d83848" mode="X"/></owner-list><waiter-list><waiter id="process2efac7c28" mode="S" requestType="wait"/></waiter-list></keylock></resource-list></deadlock>

Below is deadlock representation from Sentry One plan explorer:

Deadlock Graph

In both the delete statement where is on id column which is identity columns and defined as primary key clustered index.

I have advised vendor to remove these delete statements and purge them on a weekly basis over the weekend late in the night.

Disabling implicit tran is not feasible as it requires code change and needs to be tested.

Queries are triggered from two different server as network load balancing is activated and at any given time, request can be dynamically allocated to the available application server. It seems, at any give point of time only one server can be handed upon request and also to note that one request can't be handled to two servers concurrently as per definition from NLB mentioned:

https://docs.aws.amazon.com/elasticloadbalancing/latest/network/introduction.html

Also to add that, this is one specific scenario wherein request has come from two different application server however in other scenarios, deadlock has taken place even when two request(delete operation) has come from same server.

Is there any way, we can resolve above deadlock by changing anything at database level or some other possibilities.

Any help in this regard will be highly appreciated.

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)

  • Two deletes shouldn't be deadlocking on their own - the deletes must be part of a larger transaction? Does the vendor code issue a series of identity values to delete in a single transaction? Why does it have to do this from different servers concurrently? Sounds like an ideal case for a queue. Or soft deletes that are deferred to hard deletes until later (which you could potentially handle in an instead of delete trigger). But if a vendor is doing less-than-smart things, the easiest (and sometimes only) solution is going to be to smarten the vendor. – Aaron Bertrand Apr 30 at 11:43
  • @AaronBertrand If they were part of one single transaction, will that not be part of single SPID? Yes, I am looking for smarter solution from experts here. – Learning_DBAdmin Apr 30 at 11:46
  • No, ServerName_01 sends a single transaction where it tries to delete 5, then 7, then 8. ServerName_02 sends a single transaction where it tries to delete 2, then 8, then 5. And potentially those transactions involve statements against other tables, too. The picture of the deadlock doesn't tell enough (the .xdl will tell us more, but still not enough to understand everything the app code is trying to do). – Aaron Bertrand Apr 30 at 11:46
  • @AaronBertrand Agreed on this point. Now the question is - how to solve this? – Learning_DBAdmin Apr 30 at 11:48
  • I've given a couple of off-the-cuff suggestions, but again, I don't know enough about the app or even this deadlock to solve it for you. Really you should be taking this back to the vendor and telling them to fix the way they're deleting this data. – Aaron Bertrand Apr 30 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.