Взаимоблокировки, анализ и поиск путей решения. / Страница 3 / Microsoft SQL Server / Sql.ru

with(xlock, tablock)

CREATE CLUSTERED INDEX [_dta_index__CRgActP1636_c_8_2038922981__K1_K2_K3] ON [dbo].[_CRgActP1636] 
(
	[_RecorderTRef] ASC,
	[_RecorderRRef] ASC,
	[_LineNo] ASC
)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  = OFF) ON [PRIMARY]

<deadlock-list>
 <deadlock victim="processb76a2088">
  <process-list>
   <process id="processb76a2088" taskpriority="0" logused="3478840" waitresource="KEY: 8:72057608907063296 (bd9bfdc492a3)" waittime="8971" ownerId="130852" transactionname="user_transaction" lasttranstarted="2012-10-17T12:29:01.547" XDES="0xc24c4c60" lockMode="U" schedulerid="1" kpid="6476" status="suspended" spid="55" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-10-17T12:29:14.340" lastbatchcompleted="2012-10-17T12:29:14.337" clientapp="1CV82 Server" hostname="IAP-04-S1C02" hostpid="4428" loginname="1cuser" isolationlevel="read committed (2)" xactid="130852" currentdb="8" lockTimeout="20000" clientoption1="671219744" clientoption2="128056">
    <executionStack>
     <frame procname="zup.dbo.on_delete_P1636" line="10" stmtstart="924" stmtend="1280" sqlhandle="0x03000800b9062342d3adcb00eca000000000000000000000">
Delete from T1
   From _CRgActP1636 as T1
   inner join deleted as T3 on T1._RecorderTRef=T3._RecorderTRef and T1._RecorderRRef=T3._RecorderRRef AND T1._LineNo=T3._LineNo     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x020000005a47462e6d99e9e55cfe2d0ecdf5d3d7b9a02536">
DELETE FROM T2
FROM #tt69 T1 WITH(NOLOCK)
INNER JOIN _CRgActP1636 T2
ON T2._RecorderTRef = T1._RecorderTRef AND T2._RecorderRRef = T1._RecorderRRef AND T2._LineNo = T1._LineNo
WHERE T2._RecorderTRef = T1._RecorderTRef AND T2._RecorderRRef = T1._RecorderRRef AND T2._LineNo = T1._LineNo     </frame>
    </executionStack>
    <inputbuf>
DELETE FROM T2
FROM #tt69 T1 WITH(NOLOCK)
INNER JOIN _CRgActP1636 T2
ON T2._RecorderTRef = T1._RecorderTRef AND T2._RecorderRRef = T1._RecorderRRef AND T2._LineNo = T1._LineNo
WHERE T2._RecorderTRef = T1._RecorderTRef AND T2._RecorderRRef = T1._RecorderRRef AND T2._LineNo = T1._LineNo    </inputbuf>
   </process>
   <process id="process807a82c8" taskpriority="0" logused="4165232" waitresource="KEY: 8:72057608907063296 (fc7c5f9d42b1)" waittime="18136" ownerId="127487" transactionname="user_transaction" lasttranstarted="2012-10-17T12:28:59.753" XDES="0x800459c0" lockMode="U" schedulerid="2" kpid="6240" status="suspended" spid="58" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-10-17T12:29:05.157" lastbatchcompleted="2012-10-17T12:29:05.153" clientapp="1CV82 Server" hostname="IAP-04-S1C01" hostpid="3644" loginname="1cuser" isolationlevel="read committed (2)" xactid="127487" currentdb="8" lockTimeout="20000" clientoption1="671219744" clientoption2="128056">
    <executionStack>
     <frame procname="zup.dbo.on_delete_P1636" line="10" stmtstart="924" stmtend="1280" sqlhandle="0x03000800b9062342d3adcb00eca000000000000000000000">
Delete from T1
   From _CRgActP1636 as T1
   inner join deleted as T3 on T1._RecorderTRef=T3._RecorderTRef and T1._RecorderRRef=T3._RecorderRRef AND T1._LineNo=T3._LineNo     </frame>
     <frame procname="adhoc" line="1" sqlhandle="0x02000000aec2be0cb62263a39242fcfa0ef14a7a9a5cad6d">
DELETE FROM T2
FROM #tt173 T1 WITH(NOLOCK)
INNER JOIN _CRgActP1636 T2
ON T2._RecorderTRef = T1._RecorderTRef AND T2._RecorderRRef = T1._RecorderRRef AND T2._LineNo = T1._LineNo
WHERE T2._RecorderTRef = T1._RecorderTRef AND T2._RecorderRRef = T1._RecorderRRef AND T2._LineNo = T1._LineNo     </frame>
    </executionStack>
    <inputbuf>
DELETE FROM T2
FROM #tt173 T1 WITH(NOLOCK)
INNER JOIN _CRgActP1636 T2
ON T2._RecorderTRef = T1._RecorderTRef AND T2._RecorderRRef = T1._RecorderRRef AND T2._LineNo = T1._LineNo
WHERE T2._RecorderTRef = T1._RecorderTRef AND T2._RecorderRRef = T1._RecorderRRef AND T2._LineNo = T1._LineNo    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057608907063296" dbid="8" objectname="zup.dbo._CRgActP1636" indexname="_dta_index__CRgActP1636_c_8_2038922981__K1_K2_K3" id="lockc22bc400" mode="U" associatedObjectId="72057608907063296">
    <owner-list>
     <owner id="process807a82c8" mode="U"/>
    </owner-list>
    <waiter-list>
     <waiter id="processb76a2088" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057608907063296" dbid="8" objectname="zup.dbo._CRgActP1636" indexname="_dta_index__CRgActP1636_c_8_2038922981__K1_K2_K3" id="lockc13ab780" mode="X" associatedObjectId="72057608907063296">
    <owner-list>
     <owner id="processb76a2088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process807a82c8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

DELETE FROM T2
FROM #tt69 T1 WITH(NOLOCK)
INNER JOIN _CRgActP1636 T2
ON T2._RecorderTRef = T1._RecorderTRef AND T2._RecorderRRef = T1._RecorderRRef AND T2._LineNo = T1._LineNo
WHERE T2._RecorderTRef = T1._RecorderTRef AND T2._RecorderRRef = T1._RecorderRRef AND T2._LineNo = T1._LineNo 

Delete from T1
   From _CRgActP1636 as T1
   inner join deleted as T3 on T1._RecorderTRef=T3._RecorderTRef and T1._RecorderRRef=T3._RecorderRRef AND T1._LineNo=T3._LineNo 

create trigger [on_delete_P1636] on _CRgActP1636
instead of delete
as
begin
   Delete from T1 
   From _CRgActP1636 as T1
   inner join deleted as T3 on T1._RecorderTRef=T3._RecorderTRef and T1._RecorderRRef=T3._RecorderRRef AND T1._LineNo=T3._LineNo
   left join _CRg1596 as T2 on T1._RecorderTRef=T2._RecorderTRef and T1._RecorderRRef=T2._RecorderRRef AND T1._LineNo=T2._LineNo
   Where T1._Org=T2._Fld1626RRef
   
end;

Re: Взаимоблокировки, анализ и поиск путей решения. [new]
aleks2 Guest	соединение = JOIN Хе-хе.
16 окт 12, 16:37 [13328039] Ответить \| Цитировать Сообщить модератору

Re: Взаимоблокировки, анализ и поиск путей решения. [new]
iSteel Member Откуда: Сообщений: 76	Я имел ввиду снапшот на базе, конечно. Блин, почему редактировать текст нельзя...
16 окт 12, 18:06 [13328817] Ответить \| Цитировать Сообщить модератору

Re: Взаимоблокировки, анализ и поиск путей решения. [new]
iSteel Member Откуда: Сообщений: 76	Ну ладно вам. Я вовсе не профи, мне нужно было решить проблему. Если что не так изложил, извиняйте, как смог ;)
16 окт 12, 21:25 [13329513] Ответить \| Цитировать Сообщить модератору

Re: Взаимоблокировки, анализ и поиск путей решения. [new]
invm Member Откуда: Москва Сообщений: 9633	iSteel, Вместо глобального запрета эскалации блокировок, лучше пересоздайте для таблицы _CRgActP1636 все индексы, включая PK, с опцией ALLOW_PAGE_LOCKS = OFF.
16 окт 12, 23:09 [13329804] Ответить \| Цитировать Сообщить модератору

Re: Взаимоблокировки, анализ и поиск путей решения. [new]
iSteel Member Откуда: Сообщений: 76	Видимо дело в том что в рамках одной транзакции идет удаление более 5000 записей, в целом, из разных таблиц. А кол-во записей в этой конкретно таблице, всего около 130 000 записей, а удаление в одной транзакции порядка 300 строк. Видимо менеджер управления блокировками SQL решает выставить U блокировку на страницу сразу, вместо IX, т.к. идет много запросов на удаление на мелкую табличку.
17 окт 12, 08:49 [13330839] Ответить \| Цитировать Сообщить модератору

Добро пожаловать в форум, Guest >> Войти \| Регистрация \| Поиск \| Правила \|			В избранное \| Подписаться
Все форумы / Microsoft SQL Server

Re: Взаимоблокировки, анализ и поиск путей решения. [new]
iSteel Member Откуда: Сообщений: 76	В sp2 описываются какие то фиксы блокировок: http://support.microsoft.com/kb/2448971 Может это поможет. В любом случае, поставлю, а там посмотрим.
17 окт 12, 10:38 [13331575] Ответить \| Цитировать Сообщить модератору

Re: Взаимоблокировки, анализ и поиск путей решения. [new]
iSteel Member Откуда: Сообщений: 76	Поставил 2й SP, однако блокировки упорно лезут, без флажка запуска 1224. Версия теперь такая: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
17 окт 12, 12:03 [13332312] Ответить \| Цитировать Сообщить модератору

Re: Взаимоблокировки, анализ и поиск путей решения. [new]
iSteel Member Откуда: Сообщений: 76	После установки SP2 ситуация такая: 1. С флажком Т1224, простым индексом, без with (xlock), взаимоблокировки есть - Page Lock 2. С флажком Т1224, простым индексом, с with (xlock), взаимоблокировок нету! 2. С флажком Т1224, кластерным индексом, без with (xlock), взаимоблокировки есть - Page Lock, если указать в индексе "Page_lock=OFF", то будет Key Lock. 3. С флажком Т1224, кластерным индексом, с with (xlock), взаимоблокировок нету! 4. Без флажка Т1224, кластерным индексом, с with (xlock), взаимоблокировок нету! Скорее всего я выберу 4й вариант.
17 окт 12, 14:15 [13333918] Ответить \| Цитировать Сообщить модератору