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

Question

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.

I'd start by pulling plans from the cache (use the query handle you get from the deadlock XML) and check what to do from there. — GMassDBA, Commented Apr 24, 2020 at 10:24
Deadlock occurs when 2 different session are requesting lock on 2 different tables but in a different order. (Ex: Session 1 take lock on table 1 while session 2 take a lock on table 2. Then Session 1 want to take a lock on table 2 and session 2 needs to take a lock on table1). What's the T-SQL that the "fill in" process runs ? — Dominique Boucher, Commented Apr 24, 2020 at 12:59
so the logic is like this if one employee is not have data today in table attandance there is will be insert. but if already have data will be update field status — febry, Commented Apr 27, 2020 at 3:28

Dan Guzman · Accepted Answer · 2020-04-28 11:32:39Z

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);

so the logic is like this if one employee is not have data today in table attandance there is will be insert. but if already have data will be update field status — febry, Commented Apr 27, 2020 at 2:49
this is index on table attandance CREATE NONCLUSTERED INDEX [IX_TTADATTENDANCE] ON [dbo].[TTADATTENDANCE] ([emp_id] ASC,[company_id] ASC, [shiftstarttime] ASC) INCLUDE ( [attend_id], [shiftdaily_code], [shiftendtime], [attend_code], [starttime], [endtime], [actual_in], [actual_out], [daytype], [remark], [default_shift], [total_ot], [total_otindex], [auto_ovt], [actualworkmnt]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO — febry, Commented Apr 27, 2020 at 2:52
The first UPDATE query in the deadlock trace does not have Emp_Id in the WHERE clause so this index is not useful to that query. Do you have another index/constraint with Attend_ID as the first column? Also, what are the column types of Attend_ID and Emp_Id? — Dan Guzman, Commented Apr 27, 2020 at 9:51
no i don't today deadlock report got different query locking today i got this query on victim transaction (@P0 int,@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 int)UPDATE TTADATTENDANCE SET actualworkmnt = @P0 WHERE Attend_ID = @P1 AND Emp_ID = @P2 AND Company_ID = @P3 — febry, Commented Apr 28, 2020 at 6:05
Be aware the chosen victim is the one least costly to rollback, which may not necessarily be the problem query. — Dan Guzman, Commented Apr 28, 2020 at 11:33

Stack Exchange Network

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

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2014
deadlock
or ask your own question.

Hot Network Questions

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

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2014deadlock or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
sql-server-2014
deadlock
or ask your own question.