Deadlock

• Hi_Venkat

  SSC Veteran

  Points: 207

  More actions

  April 15, 2014 at 10:08 am

  #292449

  How to resolve this deadlock?

  Its an object lock. Both are different object but partition number shows same that is 9.

  Can anyone help on this? below is the deadlock graph XML.

  <deadlock-list>

  <deadlock victim="process6227b88">

  <process-list>

  <process id="process6227b88" taskpriority="0" logused="0" waitresource="OBJECT: 11:724535781:9 " waittime="4968" ownerId="2945978554" transactionname="sqlsource_transform" lasttranstarted="2014-04-15T04:42:29.990" XDES="0x800b1d20" lockMode="Sch-S" schedulerid="10" kpid="7476" status="suspended" spid="276" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2014-04-15T04:42:28.187" lastbatchcompleted="2014-04-15T04:42:28.187" clientapp="OSQL-32" hostname="WTPCPCL9LWSQL10" hostpid="6368" loginname="LKWD\svc_sql2k" isolationlevel="read committed (2)" xactid="2945978554" currentdb="5" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

  <executionStack>

  <frame procname="CFGE.dbo.DMN_JPMAMAPLAuditData" line="158" stmtstart="14282" stmtend="17446" sqlhandle="0x03000500495d5e414eb793006da200000100000000000000">

  select i.accountid, case when action = 0 then 'BUY '

  when action = 1 then 'SELL '

  when action = 2 then 'B/S '

  when action = 3 then 'HOLD ' end + 'TICK EQ ' + s.ticker as rt

  into #PRSTR

  from Lockwood.dbo.restriction r

  inner join Lockwood.dbo.account a on r.acuniqueid = a.acuniqueid

  inner join Lockwood.dbo.accountid i on a.acuniqueid = i.acuniqueid

  inner join Lockwood.dbo.relatedparty_account rpa on a.acuniqueid = rpa.acuniqueid

  inner join Lockwood.dbo.relatedparty rp on rpa.rpuniqueid = rp.rpuniqueid and rp.role = 1 and rp.nickname = 'CSP'

  inner join Lockwood.dbo.security s on r.seuniqueid = s.seuniqueid

  union all

  select i.accountid, 'RESTRC HAS ' + isnull(map.jpm, g2.appvar)

  from Lockwood.dbo.restriction r

  inner join Lockwood.dbo.account a on r.acuniqueid = a.acuniqueid

  inner join Lockwood.dbo.accountid i on a.acuniqueid = i.acuniqueid

  inner join Lockwood.dbo.relatedparty_accoun </frame>

  <frame procname="adhoc" line="1" stmtstart="104" sqlhandle="0x01000500360ad70080392dff220000000000000000000000">

  DMN_JPMAMAPLAuditData @installMode = 0, @xsetID = @xsetID, @parmString = @parmString, @genID = @genID </frame>

  <frame procname="mssqlsystemresource.sys.sp_executesql" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000">

  sp_executesql </frame>

  <frame procname="CFGE.dbo.ExecuteCFGE" line="46" stmtstart="3092" stmtend="3398" sqlhandle="0x0300050067c18a77e5e15001e0a200000100000000000000">

  exec sp_executesql @sql, N'@xsetID int, @parmString varchar(8000), @genid int', @xsetID = @xsetID, @parmString = @parmString, @genID = @genID </frame>

  <frame procname="adhoc" line="5" stmtstart="336" stmtend="450" sqlhandle="0x02000000b7f7103265b0dcc3de425402088771b4f9d17b9c">

  exec @ret = CFGE.dbo.executeCFGE 'JPMAM_AuditExceptions'; </frame>

  </executionStack>

  <inputbuf>

  -- jobuid = 428241

  set nocount on;

  declare @ret int;

  update ImportAdapterLKWD.dbo.execbatchsql set spid = @@spid, execstart = getdate() where ebsuniqueid = 428241;

  exec @ret = CFGE.dbo.executeCFGE 'JPMAM_AuditExceptions';

  update ImportAdapterLKWD.dbo.execbatchsql set execfinish = getdate(), resultcode = @ret where ebsuniqueid = 428241;

  </inputbuf>

  </process>

  <process id="process14631dc8" taskpriority="0" logused="8888" waitresource="OBJECT: 11:2041058307:9 " waittime="4964" ownerId="2945980022" transactionname="DROPOBJ" lasttranstarted="2014-04-15T04:42:31.470" XDES="0x20fb249950" lockMode="Sch-M" schedulerid="23" kpid="12536" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2014-04-15T04:28:01.540" lastbatchcompleted="2014-04-15T04:28:01.537" clientapp="OSQL-32" hostname="WTPCPCL9LWSQL10" hostpid="9956" loginname="LKWD\svc_sql2k" isolationlevel="read committed (2)" xactid="2945980022" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

  <executionStack>

  <frame procname="adhoc" line="1" sqlhandle="0x01000b00b2fb13144019dfc72c0000000000000000000000">

  drop table __positionMoveToCustbalance_85587 </frame>

  <frame procname="mssqlsystemresource.sys.sp_executesql" line="1" sqlhandle="0x0400ff7fbe80662601000000000000000000000000000000">

  sp_executesql </frame>

  <frame procname="lockwood.dbo.positionMoveToCustposition" line="145" stmtstart="16766" stmtend="16862" sqlhandle="0x03000b00c6a0c60c7aaf1700c1a200000100000000000000">

  exec sp_executesql @sql </frame>

  <frame procname="adhoc" line="5" stmtstart="336" stmtend="430" sqlhandle="0x02000000f1c61d275f3a7dcaa8ee23f710fe767458b45845">

  exec @ret = positionMoveToCustposition '85587'; </frame>

  </executionStack>

  <inputbuf>

  -- jobuid = 428237

  set nocount on;

  declare @ret int;

  update ImportAdapterLKWD.dbo.execbatchsql set spid = @@spid, execstart = getdate() where ebsuniqueid = 428237;

  exec @ret = positionMoveToCustposition '85587';

  update ImportAdapterLKWD.dbo.execbatchsql set execfinish = getdate(), resultcode = @ret where ebsuniqueid = 428237;

  </inputbuf>

  </process>

  </process-list>

  <resource-list>

  <objectlock lockPartition="9" objid="724535781" subresource="FULL" dbid="11" objectname="724535781" id="lock1b1d4b8f80" mode="Sch-M" associatedObjectId="724535781">

  <owner-list>

  <owner id="process14631dc8" mode="Sch-M"/>

  </owner-list>

  <waiter-list>

  <waiter id="process6227b88" mode="Sch-S" requestType="wait"/>

  </waiter-list>

  </objectlock>

  <objectlock lockPartition="9" objid="2041058307" subresource="FULL" dbid="11" objectname="lockwood.dbo.account" id="lock1be01e0f80" mode="Sch-S" associatedObjectId="2041058307">

  <owner-list>

  <owner id="process6227b88" mode="Sch-S"/>

  </owner-list>

  <waiter-list>

  <waiter id="process14631dc8" mode="Sch-M" requestType="wait"/>

  </waiter-list>

  </objectlock>

  </resource-list>

  </deadlock>

  </deadlock-list>

  Thanks in Advance!