Блокировка SQL Server - в режиме блокировки S и IX

Question

Недавно мы столкнулись с проблемами взаимоблокировки, когда все больше пользователей используют систему, ниже приведены подробные сведения о журнале. Благодарим вас за то, что кто-то может предоставить инструкции по ее решению:

Как S или же IX режим блокировки вызывает тупик?

Я думаю, вероятно, чтобы улучшить время запроса для оператора выбора, чтобы он мог снять блокировку быстрее.

<TextData>
  <deadlock-list>
 <deadlock victim="process6956d8">
  <process-list>
   <process id="process6956d8" taskpriority="0" logused="0" waitresource="PAGE: 8:1:6923" waittime="1840" ownerId="67336413" transactionname="implicit_transaction" lasttranstarted="2013-07-08T16:22:34.897" XDES="0x124272700" lockMode="S" schedulerid="2" kpid="5472" status="suspended" spid="64" sbid="0" ecid="0" priority="0" transcount="1" lastbatchstarted="2013-07-08T16:22:34.900" lastbatchcompleted="2013-07-08T16:22:34.900" clientapp="Microsoft SQL Server JDBC Driver" hostname="VIRTIPH-MAS001" hostpid="0" loginname="prtcmt_user" isolationlevel="read committed (2)" xactid="67336413" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" sqlhandle="0x02000000ec69351d69911b1227018da1e1e67e60256cd8be">
select top 290 workplan0_.id as id112_, workplan0_.request_mode as request2_112_, workplan0_.cmt_status_id as cmt3_112_, workplan0_.requestType_rd_id as requestT4_112_, workplan0_.requested_dt as requested5_112_, workplan0_.generic_desc as generic6_112_, workplan0_.brand_desc as brand7_112_, workplan0_.strength as strength112_, workplan0_.prod_form_name as prod9_112_, workplan0_.pack_desc as pack10_112_, workplan0_.inv_uom_desc as inv11_112_, workplan0_.pack_uom_desc as pack12_112_, workplan0_.requestType_name as request13_112_, workplan0_.site_name as site14_112_, workplan0_.priority as priority112_, workplan0_.source as source112_, workplan0_.cmt_status_code as cmt17_112_, workplan0_.prev_cmt_status_code as prev18_112_, workplan0_.swmd_prod_id as swmd19_112_, workplan0_.hppl_lookslike_id as hppl20_112_, workplan0_.amt_id as amt21_112_, workplan0_.locked_dt as locked22_112_ from ipharm_workPlanView workplan0_ where workplan0_.requestType_rd_id=6 and workplan0_.cmt_status_id=33 order by workplan0_.generic_des     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
select top 290 workplan0_.id as id112_, workplan0_.request_mode as request2_112_, workplan0_.cmt_status_id as cmt3_112_, workplan0_.requestType_rd_id as requestT4_112_, workplan0_.requested_dt as requested5_112_, workplan0_.generic_desc as generic6_112_, workplan0_.brand_desc as brand7_112_, workplan0_.strength as strength112_, workplan0_.prod_form_name as prod9_112_, workplan0_.pack_desc as pack10_112_, workplan0_.inv_uom_desc as inv11_112_, workplan0_.pack_uom_desc as pack12_112_, workplan0_.requestType_name as request13_112_, workplan0_.site_name as site14_112_, workplan0_.priority as priority112_, workplan0_.source as source112_, workplan0_.cmt_status_code as cmt17_112_, workplan0_.prev_cmt_status_code as prev18_112_, workplan0_.swmd_prod_id as swmd19_112_, workplan0_.hppl_lookslike_id as hppl20_112_, workplan0_.amt_id as amt21_112_, workplan0_.locked_dt as locked22_112_ from ipharm_workPlanView workplan0_ where workplan0_.requestType_rd_id=6 and workplan0_.cmt_status_id=33 order by workplan0_.generic_de    </inputbuf>
   </process>
   <process id="processc7c5ec58" taskpriority="0" logused="9904" waitresource="PAGE: 8:1:6922" waittime="936" ownerId="67336189" transactionname="implicit_transaction" lasttranstarted="2013-07-08T16:22:33.930" XDES="0x123635380" lockMode="IX" schedulerid="1" kpid="5232" status="suspended" spid="61" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2013-07-08T16:22:35.930" lastbatchcompleted="2013-07-08T16:22:35.927" clientapp="Microsoft SQL Server JDBC Driver" hostname="VIRTIPH-MAS001" hostpid="0" loginname="prtcmt_user" isolationlevel="read committed (2)" xactid="67336189" currentdb="8" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058">
    <executionStack>
     <frame procname="adhoc" line="1" stmtstart="890" sqlhandle="0x02000000a89737254800790c60bb4d0fa72b9640012d8592">
update requestedProduct set version=@P0, create_user=@P1, create_dt=@P2, last_update_user=@P3, last_update_dt=@P4, requested_by=@P5, requested_dt=@P6, requested_byName=@P7, requested_byTitle=@P8, status_id=@P9, status_by=@P10, status_date=@P11, cmt_status_id=@P12, prev_cmt_status_id=@P13, prod_id=@P14, source_prod_id=@P15, hppl_lookslike_id=@P16, amt_id=@P17, fred_code=@P18, source=@P19, swmd_prod_id=@P20, locked_dt=@P21, locked_by=@P22, request_mode=@P23, original_request_mode=@P24, site_rd_id=@P25, priority_rd_id=@P26, submitted_priority_rd_id=@P27, requestType_rd_id=@P28, comment=@P29 where id=@P30 and version=@P31     </frame>
     <frame procname="unknown" line="1" sqlhandle="0x000000000000000000000000000000000000000000000000">
unknown     </frame>
    </executionStack>
    <inputbuf>
(@P0 int,@P1 nvarchar(4000),@P2 datetime,@P3 nvarchar(4000),@P4 datetime,@P5 bigint,@P6 datetime,@P7 nvarchar(4000),@P8 nvarchar(4000),@P9 bigint,@P10 bigint,@P11 datetime,@P12 bigint,@P13 bigint,@P14 int,@P15 int,@P16 nvarchar(4000),@P17 nvarchar(4000),@P18 nvarchar(4000),@P19 nvarchar(4000),@P20 varchar(8000),@P21 datetime,@P22 bigint,@P23 int,@P24 int,@P25 bigint,@P26 bigint,@P27 bigint,@P28 bigint,@P29 varchar(8000),@P30 bigint,@P31 int)update requestedProduct set version=@P0, create_user=@P1, create_dt=@P2, last_update_user=@P3, last_update_dt=@P4, requested_by=@P5, requested_dt=@P6, requested_byName=@P7, requested_byTitle=@P8, status_id=@P9, status_by=@P10, status_date=@P11, cmt_status_id=@P12, prev_cmt_status_id=@P13, prod_id=@P14, source_prod_id=@P15, hppl_lookslike_id=@P16, amt_id=@P17, fred_code=@P18, source=@P19, swmd_prod_id=@P20, locked_dt=@P21, locked_by=@P22, request_mode=@P23, original_request_mode=@P24, site_rd_id=@P25, priority_rd_id=@P26, submitted_priority_rd_id=@P27, requestType_r    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <pagelock fileid="1" pageid="6923" dbid="8" objectname="MYDB.dbo.requestedProduct" id="lockedec4680" mode="IX" associatedObjectId="72057594227261440">
    <owner-list>
     <owner id="processc7c5ec58" mode="IX" />
    </owner-list>
    <waiter-list>
     <waiter id="process6956d8" mode="S" requestType="wait" />
    </waiter-list>
   </pagelock>
   <pagelock fileid="1" pageid="6922" dbid="8" objectname="MYDB.dbo.requestedProduct" id="lockfa82e880" mode="SIU" associatedObjectId="72057594227261440">
    <owner-list>
     <owner id="process6956d8" mode="S" />
    </owner-list>
    <waiter-list>
     <waiter id="processc7c5ec58" mode="IX" requestType="convert" />
    </waiter-list>
   </pagelock>
  </resource-list>
 </deadlock>
</deadlock-list>
</TextData>)

Answer 1

Можете ли вы опубликовать DDL представления от process6956d8?

Взаимоблокировки являются хитрыми без всей модели данных, но из этой статьи вы можете найти некоторые шаги, чтобы выяснить, что вы можете попытаться избежать их: http://support.microsoft.com/kb/832524

Я бы начал с поиска индексов, если вы обновляете поле в кластерном индексе в requestedProductЭто может быть причиной или, по крайней мере, способом избежать этого.