Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4 5   вперед  Ctrl      все
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
aleks2
Guest
соединение = JOIN

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

Откуда:
Сообщений: 76
aleks2
iSteel
Что же за напасть то такая?

1. Хе-хе. Вы ишо верите в чудеса?
2. Тогда наука бессильна.
3. Сервер может изменить план выполнения в любой момент. Изменится и порядок наложения блокировок. Кластерный индекс тут ничего не гарантирует.
4. Так что при массированных удалениях только блокировка таблицы целиком гарантированно решает проблему.

with(xlock, tablock)


С xlock постоянно взаимоблокировки типа Key block. Не подходит такой метод.

В общем то решил проблему так - сделал простое удаление в триггере, а в параметры запуска сервера добавил такую штуку -T1224
автор
"Отключает укрупнение блокировок на основе количества блокировок. Однако слишком активное использование памяти может включить укрупнение блокировок. Компонент Компонент Database Engine укрупняет блокировки строк или страниц до блокировок таблиц (или секций), если объем памяти, используемый блокированными объектами, превышает одно из следующих условий."


В 7 потоков удаляю одновременно, никаких блокировок в течении часа.
На этот раз, надеюсь, это уже конечное решение проблемы. -)
16 окт 12, 17:05    [13328287]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6727
invm
Mnior
Только я так ине понял, проблема из-за порядка и эскалации?
Насколько я понял, да.
У вас в пример только из-за порядка, эсклация тут не причём.

Просто у TC на скринах Page Lock, а не Row Lock.
Вот поэтому я и подумал о злосчастной эскалации. Т.е. данные не пересекаются, а сервер норовит поднять до общих страниц. Но суть особо не меняется. Просто можно не только последовательностью лечить (к примеру).

iSteel
Однако, взаимоблокировки вернулись, без with(xlock)
Вы бы лучше сразу граф показали, чтоле.

Мне кажется мы вообще не о том говорим.
Там не может быть IX в принципе, такое только на VIEW бывает, на таблах же сразу U. И поэтому на таблах можно хинты особо не ставить, а на VIEW обязательно.

И ещё. Все эти тесты и бла-бла, если там происходит работа с одними и теми же данными. Это проблема - какого фига в параллели происходит обращение к одному и тому же объекту?!
16 окт 12, 17:09    [13328319]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Началось все с этого графа:
+ скриншот
Картинка с другого сайта.
16 окт 12, 17:58    [13328743]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Mnior
И ещё. Все эти тесты и бла-бла, если там происходит работа с одними и теми же данными. Это проблема - какого фига в параллели происходит обращение к одному и тому же объекту?!

Работа происходит с разными данными, в разрезе организаций. Данные никак не пересекаются, при удалении.


Вначале была проблема получается с тем, что не было режима снапшота на таблице. Сделал, эскалация блокировок ушла.
Затем выяснилось, что нельзя удалять записи, используя не индексированную временную таблицу. Решилось триггером.
А вот потом проблемы начались, когда пошло удаление большого числа записей, за транзакцию, тут помог флажок -T1224
16 окт 12, 18:05    [13328809]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Я имел ввиду снапшот на базе, конечно.

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

Откуда: Кишинёв
Сообщений: 6727
iSteel
Вначале была проблема получается с тем, что не было режима снапшота на таблице. Сделал, эскалация блокировок ушла.
Затем выяснилось, что нельзя удалять записи, используя не индексированную временную таблицу. Решилось триггером.
А вот потом проблемы начались, когда пошло удаление большого числа записей, за транзакцию, тут помог флажок -T1224
Матерь божья.
Может как-то без матюков меня?
16 окт 12, 19:07    [13329145]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
aleks2
Guest
Mnior
Матерь божья.

Да ладно те. Просто тредстартер мозгам предпочитает бубен.
Это не смертельно.
16 окт 12, 19:18    [13329180]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

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

Откуда: Кишинёв
Сообщений: 6727
iSteel
не так изложил

Т.е. типа проделанные вами действия это шутка такая.
16 окт 12, 21:32    [13329523]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Mnior
iSteel
не так изложил

Т.е. типа проделанные вами действия это шутка такая.

Не шутка, просто, может, как то коряво описал.
16 окт 12, 22:13    [13329614]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
iSteel,

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

Откуда:
Сообщений: 76
invm
iSteel,
Вместо глобального запрета эскалации блокировок, лучше пересоздайте для таблицы _CRgActP1636 все индексы, включая PK, с опцией ALLOW_PAGE_LOCKS = OFF.

Без запрета полезли опять взаимоблокировки.

Индекс такой:
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]


Взаимоблокировки такие:
+

Картинка с другого сайта.
17 окт 12, 08:40    [13330783]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

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

Откуда:
Сообщений: 76
Вот как было (из профайлера), без флажка Т1224:
+ скриншот

Картинка с другого сайта.
17 окт 12, 09:08    [13330968]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6727
iSteel
+ Взаимоблокировки такие:
Картинка с другого сайта.
Не верю! Глюк чтоле.
17 окт 12, 09:49    [13331191]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Mnior
iSteel
+
+ Взаимоблокировки такие:
Картинка с другого сайта.
Не верю! Глюк чтоле.

Может надо на 2012 переходить?
17 окт 12, 10:00    [13331259]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Mnior
Не верю! Глюк чтоле.

Сделал запрос, все честно:
+ вот
Картинка с другого сайта.
17 окт 12, 10:11    [13331356]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
iSteel
Может надо на 2012 переходить?
Последние SP+CU надо накатить. А то у вас до сих пор RTM.
17 окт 12, 10:19    [13331413]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
invm
iSteel
Может надо на 2012 переходить?
Последние SP+CU надо накатить. А то у вас до сих пор RTM.

Но сам мелкософт пишет, типа не используйте эту возможность, потом отключим: http://msdn.microsoft.com/ru-ru/library/ms186253.aspx

Попробую обновить sql. Отпишу попозже.
17 окт 12, 10:27    [13331465]     Ответить | Цитировать Сообщить модератору
 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
Хех, теперь и флажок не помогает.
Стабильно идут взаимоблокировки всегда, что были на скриншоте, чуть выше.

+ граф такой

<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>
17 окт 12, 12:35    [13332654]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
нязнайка
Guest
Скажите а зачем у вас тригере одни и те же данные удаляются ?

Удаление :

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 


В чем смысл вашего тригера ? Покажите может его весь код на данный момент ?

Потому что вот этот вообще содержит соединение с _CRg1596. Что за таблица ?

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;
17 окт 12, 13:47    [13333562]     Ответить | Цитировать Сообщить модератору
 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]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 [3] 4 5   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить