Deadlock where victim has two shared locks and blocker two exclusive

Question

Deadlock where victim has two shared locks and blocker two exclusive

Hall of Fame

Points: 3560

August 16, 2021 at 8:52 am

#3920280

I have a deadlock where the victim has two shared locks and the blocker two exclusive locks. Can someone explain this to me?

deadlock

Here is the XDL

<deadlock-list>
 <deadlock victim="process1a3a413fc28">
  <process-list>
   <process id="process1a3a413fc28" taskpriority="0" logused="0" waitresource="KEY: 33:72060041080537088 (d362a1319904)" waittime="2986" ownerId="24994798678" transactionname="SELECT" lasttranstarted="2021-07-08T15:41:22.043" XDES="0x1a5f3ec4188" lockMode="S" schedulerid="5" kpid="1084" status="suspended" spid="557" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2021-07-08T15:41:22.043" lastbatchcompleted="2021-07-08T15:41:22.033" lastattention="2021-07-08T15:41:21.983" clientapp="GEAsyncService" hostname="TALPRODIIS102" hostpid="22080" loginname="dbssys" isolationlevel="read committed (2)" xactid="24994798678" currentdb="33" currentdbname="Talent102h" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="2" stmtend="2770" sqlhandle="0x02000000d87f59249c36ad40cb1c368b1f25b834b9b06c1f0000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
 SELECT   wnr.wns_id employeeID  ,dvb.dvb_id contractid  ,dvb.srt_dvb contracttype  ,dvb.dat_ind StartDateCon  ,dvb.dat_uit EndDateCon  ,con.con_id SubContractID  ,con.dat_ind StartDateSubCon  ,con.dat_uit EndDateSubCon , wnr.wng_id employeegroup , empposition.orgunitID orgunitID  FROM wg1  INNER JOIN wnr  ON (wg1.bed_id = 10 AND wg1.bed_id = wnr.bed_id)  INNER JOIN dvb  ON (dvb.bed_id=wnr.bed_id  AND dvb.wns_id=wnr.wns_id)  INNER JOIN con  ON (con.bed_id=dvb.bed_id  AND con.wns_id=dvb.wns_id  AND con.dvb_id  = dvb.dvb_id)  LEFT OUTER JOIN wng  ON (wng.bed_id=wg1.bed_id  AND wng.wng_id = wnr.wng_id)  LEFT OUTER JOIN empposition  ON (empposition.companyID = con.bed_id  AND empposition.employeeID = con.wns_id  AND empposition.contractID = con.dvb_id  AND empposition.subcontractID = con.con_id  AND ((  con.dat_uit &lt; &apos;20210708 0:0:0&apos; AND con.dat_uit is not null  AND empposition.datefrom &lt;= con.dat_uit  AND (empposition.dateto &gt;= con.dat_uit OR empposition.dateto is null)  )  OR (  con.dat_ind &gt; &apos;20210708 0:0:0&apos;     </inputbuf>
   </process>
   <process id="process1b203a0d088" taskpriority="0" logused="43868" waitresource="KEY: 33:72057594113687552 (64713ff52522)" waittime="2902" ownerId="24994780380" transactionname="user_transaction" lasttranstarted="2021-07-08T15:41:21.537" XDES="0x1b101820428" lockMode="X" schedulerid="5" kpid="6524" status="suspended" spid="727" sbid="3" ecid="0" priority="0" trancount="2" lastbatchstarted="2021-07-08T15:41:22.127" lastbatchcompleted="2021-07-08T15:41:22.127" lastattention="2021-07-08T15:41:22.127" clientapp="EmpData" hostname="TALPRODIIS102" hostpid="20620" loginname="dbspay" isolationlevel="read committed (2)" xactid="24994780380" currentdb="33" currentdbname="Talent102h" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="40" stmtend="186" sqlhandle="0x020000004f8cc91967c7272d2fc1ba503a8b17575749656a0000000000000000000000000000000000000000">
unknown     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@k_cao nvarchar(4))UPDATE dvb SET k_cao=@k_cao WHERE bed_id=10 AND wns_id=300328 AND dvb_id=1    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72060041080537088" dbid="33" objectname="Talent102h.dbo.con" indexname="con_idx" id="lock1ac9b375d00" mode="X" associatedObjectId="72060041080537088">
    <owner-list>
     <owner id="process1b203a0d088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process1a3a413fc28" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594113687552" dbid="33" objectname="Talent102h.dbo.dvb" indexname="dvb_idx" id="lock1a2c7dd7680" mode="S" associatedObjectId="72057594113687552">
    <owner-list>
     <owner id="process1a3a413fc28" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="process1b203a0d088" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

There are only two processes involved in the deadlock.

Make sure you have an index on

dvd ( bed_id, wns_id, dvb_id )

Actually, all these tables should almost certainly be clustered first on bed_id, but that's a different discussion:

con, dvd, wg1, wng

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".