Sql Server Deadlock issue with 3 processes

Question

I am new to sql server. I am facing deadlock issue in sql server. In deadlock graph it is showing due to 3 processes, deadlock happened. I am not able to understand the deadlock graph with 3 processes. Below is my deadlock graph.

Below is deadlock xml :

<deadlock>
    <victim-list>
        <victimProcess id="process2db5c28" />
    </victim-list>
    <process-list>
        <process id="process2db5c28" taskpriority="0" logused="0" waitresource="KEY: 10:72057597892755456 (63eb251d860a)" waittime="11658" ownerId="5805847" transactionname="user_transaction" lasttranstarted="2019-02-10T18:12:06.793" XDES="0x336846d90" lockMode="S" schedulerid="4" kpid="7860" status="suspended" spid="327" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-02-10T18:12:07.080" lastbatchcompleted="2019-02-10T18:12:07.073" lastattention="1900-01-01T00:00:00.073" clientapp=".Net SqlClient Data Provider" hostname="Test" hostpid="7716" loginname="test" isolationlevel="read committed (2)" xactid="5805847" currentdb="10" currentdbname="test_Event_6701" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="adhoc" line="1" stmtstart="320" stmtend="2784" sqlhandle="0x02000000c6cb1d04abdbec564d5b74a8bedb0851eaa773280000000000000000000000000000000000000000">
unknown    </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 bigint,@p1 datetime,@p2 datetime,@p3 bit,@p4 nvarchar(4000),@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 nvarchar(4000),@p8 nvarchar(4000),@p9 nvarchar(4000))SELECT this_.Request_Id as Request1_190_0_, this_.Request_CreateDate as Request2_190_0_, this_.Request_IsSuccessful as Request3_190_0_, this_.Request_UpdateExistingPositionProduct as Request4_190_0_, this_.Request_UpdateCustomValidationCaching as Request5_190_0_, this_.Request_IsAnyHpcTaskInProgress as Request6_190_0_, this_.Request_ChangeTypes as Request7_190_0_, this_.IsSystemRequest as IsSystem8_190_0_, this_.SubmittedOn as Submitte9_190_0_, this_.EffectiveDate as Effecti10_190_0_, this_.ActionDate as ActionDate190_0_, this_.ActionType as ActionType190_0_, this_.RequestStatus as Request13_190_0_, this_.AuthorPositionId as AuthorP14_190_0_, this_.Title as Title190_0_, this_.LastComment as LastCom16_190_0_, this_.RequestCode as Request17_190_0_, this_.AuthorPersonnelId as AuthorP18_190_0_, this_.IsSyncedToReporting as IsSynce19_190_0_, this_.Personne   </inputbuf>
        </process>
        <process id="process2dceca8" taskpriority="0" logused="256" waitresource="KEY: 10:72057597892755456 (63eb251d860a)" waittime="10583" ownerId="5802704" transactionname="user_transaction" lasttranstarted="2019-02-10T18:12:06.277" XDES="0x378b2b770" lockMode="S" schedulerid="1" kpid="7732" status="suspended" spid="297" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-02-10T18:12:08.147" lastbatchcompleted="2019-02-10T18:12:08.107" lastattention="1900-01-01T00:00:00.107" clientapp=".Net SqlClient Data Provider" hostname="Test" hostpid="7716" loginname="test" isolationlevel="read committed (2)" xactid="5802704" currentdb="10" currentdbname="test_Event_6701" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="adhoc" line="1" stmtend="826" sqlhandle="0x02000000c41a7b04756cb87d16131d008eb58c6f909659cd0000000000000000000000000000000000000000">
unknown    </frame>
                <frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql    </frame>
                <frame procname="test_Event_6701.dbo.UpdateAttributeValidationCache" line="47" stmtstart="4398" stmtend="4500" sqlhandle="0x03000a0018bc1b7e4fb42301f0a9000001000000000000000000000000000000000000000000000000000000">
INSERT INTO #RequestRelation exec sp_executesql @sq    </frame>
                <frame procname="adhoc" line="1" stmtstart="242" sqlhandle="0x01000a00f5251b0990bf48b00500000000000000000000000000000000000000000000000000000000000000">
EXEC UpdateAttributeValidationCache  @validationIds=@p0,@OrgUnitCacheHi=@p1,@RequestOrgUnitCacheHi=@p2, @RequestId=@p3, @AuthorPositionId=@p4,@eventStartDate=@p5,@eventEndDate=@p6    </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 [dbo].[Int_List] READONLY,@p1 bigint,@p2 bigint,@p3 bigint,@p4 nvarchar(4000),@p5 nvarchar(4000),@p6 nvarchar(4000))EXEC UpdateAttributeValidationCache  @validationIds=@p0,@OrgUnitCacheHi=@p1,@RequestOrgUnitCacheHi=@p2, @RequestId=@p3, @AuthorPositionId=@p4,@eventStartDate=@p5,@eventEndDate=@p6   </inputbuf>
        </process>
        <process id="process2c13848" taskpriority="0" logused="260956" waitresource="KEY: 10:72057597892755456 (ea7380e6da4f)" waittime="8719" ownerId="5769072" transactionname="user_transaction" lasttranstarted="2019-02-10T18:12:00.300" XDES="0x671eac3b0" lockMode="S" schedulerid="1" kpid="7140" status="suspended" spid="306" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2019-02-10T18:12:10.020" lastbatchcompleted="2019-02-10T18:12:10.020" lastattention="1900-01-01T00:00:00.020" clientapp=".Net SqlClient Data Provider" hostname="Test" hostpid="7716" loginname="test" isolationlevel="read committed (2)" xactid="5769072" currentdb="10" currentdbname="test_Event_6701" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
            <executionStack>
                <frame procname="adhoc" line="1" stmtend="498" sqlhandle="0x020000001ffb721bb57eb5027444ed4d9184e816c11833120000000000000000000000000000000000000000">
unknown    </frame>
                <frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x0400ff7f427f99d9010000000000000000000000000000000000000000000000000000000000000000000000">
sp_executesql    </frame>
                <frame procname="test_Event_6701.dbo.RunCustomerTypeRoleValidation" line="60" stmtstart="6434" stmtend="6536" sqlhandle="0x03000a008878d96e1cb42301f0a9000001000000000000000000000000000000000000000000000000000000">
INSERT INTO #RequestRelation exec sp_executesql @sq    </frame>
                <frame procname="adhoc" line="1" stmtstart="240" sqlhandle="0x01000a00a033940a409fb5b40500000000000000000000000000000000000000000000000000000000000000">
EXEC RunCustomerTypeRoleValidation  @ObjectTypeId=@p0, @RequestId=@p1,@batchId=@p2,@AuthorPositionId=@p3,@EffectiveDate=@p4,@EndDate=@p5,@isBatchChange=@p6    </frame>
                <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
            </executionStack>
            <inputbuf>
(@p0 nvarchar(4000),@p1 bigint,@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000),@p6 smallint)EXEC RunCustomerTypeRoleValidation  @ObjectTypeId=@p0, @RequestId=@p1,@batchId=@p2,@AuthorPositionId=@p3,@EffectiveDate=@p4,@EndDate=@p5,@isBatchChange=@p6   </inputbuf>
        </process>
    </process-list>
    <resource-list>
        <keylock hobtid="72057597892755456" dbid="10" objectname="test_Event_6701.dbo.Requests" indexname="PK_Requests" id="lock4b1a2c480" mode="X" associatedObjectId="72057597892755456">
            <owner-list>
                <owner id="process2c13848" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process2db5c28" mode="S" requestType="wait" />
            </waiter-list>
        </keylock>
        <keylock hobtid="72057597892755456" dbid="10" objectname="test_Event_6701.dbo.Requests" indexname="PK_Requests" id="lock4b1a2c480" mode="X" associatedObjectId="72057597892755456">
            <owner-list>
                <owner id="process2db5c28" mode="S" requestType="wait" />
            </owner-list>
            <waiter-list>
                <waiter id="process2dceca8" mode="S" requestType="wait" />
            </waiter-list>
        </keylock>
        <keylock hobtid="72057597892755456" dbid="10" objectname="test_Event_6701.dbo.Requests" indexname="PK_Requests" id="lock64a436900" mode="X" associatedObjectId="72057597892755456">
            <owner-list>
                <owner id="process2dceca8" mode="X" />
            </owner-list>
            <waiter-list>
                <waiter id="process2c13848" mode="S" requestType="wait" />
            </waiter-list>
        </keylock>
    </resource-list>
</deadlock>

Please someone help me understanding of this deadlock issue and how can I resolve it.

Thanks in Advance!

Answer 1

It looks like a typical Reader-Writer deadlock. It states that there is a deadlock between a statement that is reading from dbo.Requests table and another statement that is performing some form of data modification on the same table. Easy fix for this form of deadlock is to use a row-version based isolation level, either READ COMMITTED SNAPSHOT or SNAPSHOT

You can read more about this Transaction Locking and Row Versioning Guide

You can also try using WITH(SERIALIZABLE) when selecting any record from dbo.Requests table like following

select * from dbo.Requests WITH(SERIALIZABLE) where id=@id