Sql-server – Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim (Msg 1205) when update

deadlocksql serversql server 2014

I have some issue when i test 100 user fill in attendance with same time.
i got error message like this

Transaction (Process ID) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim (Msg 1205)

so i see the report system health i got this xml report

    <deadlock>
 <victim-list>
  <victimProcess id="process3ae2a5468" />
 </victim-list>
 <process-list>
  <process id="process3ae2a5468" taskpriority="0" logused="0" waitresource="PAGE: 10:1:69191 " waittime="322" ownerId="713318192" transactionname="UPDATE" lasttranstarted="2020-04-23T17:58:35.933" XDES="0x4787c8d90" lockMode="U" schedulerid="4" kpid="14732" status="suspended" spid="323" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-04-23T17:58:35.930" lastbatchcompleted="2020-04-23T17:58:35.930" lastattention="1900-01-01T00:00:00.930" clientapp="Microsoft JDBC Driver for SQL Server" hostname="bmtprdcfchcms02" hostpid="0" loginname="sfxxxx_xxxxxxx_admin" isolationlevel="read committed (2)" xactid="713318192" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="119864">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="226" stmtend="786" sqlhandle="0x020000004f18fd1a8c0547c9fbb4d50189cef73faec0899e0000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P0 datetime2,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 datetime2,@P4 nvarchar(4000),@P5 nvarchar(4000),@P6 int)UPDATE TTADATTENDANCE SET 


                        endtime = @P0,
                        actual_out = 118,

                    photo_end = @P1,
                    geoloc_end = @P2,
                    ip_endtime = '172.18.25.26',

                modified_date = @P3,
                modified_by = @P4
            WHERE Attend_ID = @P5
            and company_id = @P6                                                        </inputbuf>
  </process>
  <process id="process3afdf5088" taskpriority="0" logused="920" waitresource="PAGE: 10:1:9236 " waittime="10809" ownerId="713321433" transactionname="UPDATE" lasttranstarted="2020-04-23T17:58:39.900" XDES="0x6521278e0" lockMode="U" schedulerid="4" kpid="12144" status="suspended" spid="288" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2020-04-23T17:58:39.893" lastbatchcompleted="2020-04-23T17:58:39.893" lastattention="1900-01-01T00:00:00.893" clientapp="Microsoft JDBC Driver for SQL Server" hostname="bmtprdcfchscm01" hostpid="0" isolationlevel="read committed (2)" xactid="713321433" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="119864">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="148" stmtend="452" sqlhandle="0x020000006e2d14307f557de0a5a1086f35638330fbc6a8530000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P0 nvarchar(4000),@P1 int,@P2 int,@P3 nvarchar(4000),@P4 nvarchar(4000))UPDATE TTADATTENDANCE 
                SET attend_code = @P0
                ,actual_lti = @P1
                ,actual_eao = @P2
                WHERE Attend_ID = @P3
                AND Emp_ID = @P4                                           </inputbuf>
  </process>
  <process id="process578c8e8c8" taskpriority="0" logused="10000" waittime="108" schedulerid="2" kpid="15644" status="suspended" spid="288" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-04-23T17:58:39.893" lastbatchcompleted="2020-04-23T17:58:39.893" lastattention="1900-01-01T00:00:00.893" clientapp="Microsoft JDBC Driver for SQL Server" hostname="bmtprdcfchscm01" hostpid="0" loginname="sfbiznet_bankmantap_admin" isolationlevel="read committed (2)" xactid="713321433" currentdb="10" lockTimeout="4294967295" clientoption1="671088672" clientoption2="119864">
   <executionStack>
    <frame procname="adhoc" line="1" stmtstart="148" stmtend="452" sqlhandle="0x020000006e2d14307f557de0a5a1086f35638330fbc6a8530000000000000000000000000000000000000000">
unknown    </frame>
    <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown    </frame>
   </executionStack>
   <inputbuf>
(@P0 nvarchar(4000),@P1 int,@P2 int,@P3 nvarchar(4000),@P4 nvarchar(4000))UPDATE TTADATTENDANCE 
                SET attend_code = @P0
                ,actual_lti = @P1
                ,actual_eao = @P2
                WHERE Attend_ID = @P3
                AND Emp_ID = @P4                                           </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <pagelock fileid="1" pageid="69191" dbid="10" subresource="FULL" objectname="dbSF_BizNet_BANKMANTAP.dbo.TTADATTENDANCE" id="lock6251ca580" mode="U" associatedObjectId="72057594308132864">
   <owner-list>
    <owner id="process578c8e8c8" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process3ae2a5468" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="1" pageid="9236" dbid="10" subresource="FULL" objectname="dbSF_BizNet_BANKMANTAP.dbo.TTADATTENDANCE" id="lock550beb580" mode="U" associatedObjectId="72057594308132864">
   <owner-list>
    <owner id="process3ae2a5468" mode="U" />
   </owner-list>
   <waiter-list>
    <waiter id="process3afdf5088" mode="U" requestType="wait" />
   </waiter-list>
  </pagelock>
  <exchangeEvent id="Pipe3ec380380" WaitType="e_waitPipeGetRow" nodeId="6">
   <owner-list>
    <owner id="process3afdf5088" />
   </owner-list>
   <waiter-list>
    <waiter id="process578c8e8c8" />
   </waiter-list>
  </exchangeEvent>
 </resource-list>
</deadlock>

for where condition is already different (unique) every each user.
so what can i do to solved this because when i give 10ms gap for each user i don't get error message.

Best Answer

The many nvarchar(4000) parameters in the query suggest the application is not specifying correct parameter data types for string values. This may be due to .NET application code using AddWithValue or ORM framework configurations using Unicode as the default for string types. This will prevent indexes on legacy SQL collation varchar columns from being used efficiently and lead to deadlocks and excessive resource utilization overall.

Make sure the application-specified parameter data types match those of the referenced columns. Also, the specified parameter length for varchar/nvarchar should be the same as the referenced column max length to avoid procedure cache bloat. These practices will help ensure optimal queries and avoid deadlocks.

One also must have useful indexes to avoid scans and associated deadlocks. Together with sargable expressions to use indexes efficiently (such as matching parameter data types as described above). You mentioned in comments you have an index with these keys: 

CREATE NONCLUSTERED INDEX [IX_TTADATTENDANCE] ON [dbo].[TTADATTENDANCE]
    ([emp_id] ASC,[company_id] ASC, [shiftstarttime] ASC)

However, the above index is not useful for this UPDATE query involved in the deadlock because the emp_id is not specified:

UPDATE TTADATTENDANCE SET 
    endtime = @P0,
    actual_out = 118,
    photo_end = @P1,
    geoloc_end = @P2,
    ip_endtime = '172.18.25.26',
    modified_date = @P3,
    modified_by = @P4
WHERE Attend_ID = @P5
and company_id = @P6

I expect the below index will help avoid the scan and reduce deadlocks. Note that this index is UNIQUE since you mentioned in comments the condition is unique. It is a best practice is to explicitly specify UNIQUE when index keys are indeed unique since that gives SQL Server important cardinality information to generate better execution plans. 

CREATE UNIQUE NONCLUSTERED INDEX [IX_TTADATTENDANCE_company_id] ON [dbo].[TTADATTENDANCE]
(company_id, AttendID);
Related Question