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

Откуда:
Сообщений: 76
нязнайка
Скажите а зачем у вас тригере одни и те же данные удаляются ?
В чем смысл вашего тригера ? Покажите может его весь код на данный момент ?

Смысл триггера - подменить запрос удаления, т.к. его я не могу править, его генерит 1С. А 1С удаляет с помощью временной таблицы, которая не проиндексирована, в итоге получается разный порядок удаления записей.

Тот что выше был триггер, это на тот момент, когда я думал решить проблему с помощью секционирования.
Сейчас триггер такой:
Create trigger [dbo].[on_delete_P1636] on [dbo].[_CRgActP1636]
instead of delete
as
begin
   Delete from T1 with(xlock)
   From _CRgActP1636 as T1
   inner join deleted as T3 on T1._RecorderTRef=T3._RecorderTRef and T1._RecorderRRef=T3._RecorderRRef AND T1._LineNo=T3._LineNo
   
end;


И тут вся загвоздка оказалась использовать или нет " with(xlock)", в итоге опытным путем выяснил, что все таки надо.

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

_CRg1596 - это таблица основная, что то типа заголовка. А таблица _CRgActP1636 дополняет ее, по ключевым полям.
17 окт 12, 14:25    [13334047]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Все таки взаимоблокировки вернулись)))
Варианты исчерпаны у меня. Не понятна закономерность. До этого час почти, все было нормально и опять...

+ вот такие

<deadlock-list>
 <deadlock victim="process8b1ea088">
  <process-list>
   <process id="process8b1ea088" taskpriority="0" logused="186396" waitresource="KEY: 8:72057608907522048 (9502de195276)" waittime="8466" ownerId="157828" transactionname="user_transaction" lasttranstarted="2012-10-17T15:15:50.023" XDES="0xae1fae80" lockMode="X" schedulerid="1" kpid="4992" status="suspended" spid="57" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-10-17T15:15:53.080" lastbatchcompleted="2012-10-17T15:15:53.080" clientapp="1CV82 Server" hostname="IAP-03-SDB11" hostpid="163816" loginname="1cuser" isolationlevel="read committed (2)" xactid="157828" currentdb="8" lockTimeout="20000" clientoption1="671219744" clientoption2="128056">
    <executionStack>
     <frame procname="zup.dbo.on_delete_P1636" line="5" stmtstart="194" stmtend="574" sqlhandle="0x03000800b9062342cc7bf900eda000000000000000000000">
         Delete from T1 with(xlock)
            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="0x020000005a441b350e8be24812584c6f3b16433aa224b2d2">
         DELETE FROM T2
         FROM #tt60 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 #tt60 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="process8261abc8" taskpriority="0" logused="442644" waitresource="KEY: 8:72057608907522048 (02b8147d5875)" waittime="7479" ownerId="153823" transactionname="user_transaction" lasttranstarted="2012-10-17T15:15:47.097" XDES="0xdb7c5600" lockMode="X" schedulerid="2" kpid="6480" status="suspended" spid="51" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2012-10-17T15:15:52.740" lastbatchcompleted="2012-10-17T15:15:52.733" clientapp="1CV82 Server" hostname="IAP-03-SDB11" hostpid="163816" loginname="1cuser" isolationlevel="read committed (2)" xactid="153823" currentdb="8" lockTimeout="20000" clientoption1="671219744" clientoption2="128056">
    <executionStack>
     <frame procname="zup.dbo.on_delete_P1636" line="5" stmtstart="194" stmtend="574" sqlhandle="0x03000800b9062342cc7bf900eda000000000000000000000">
         Delete from T1 with(xlock)
            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="0x02000000bee5d70b717981af432a1550258f0553c9ad644f">
         DELETE FROM T2
         FROM #tt85 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 #tt85 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="72057608907522048" dbid="8" objectname="zup.dbo._CRgActP1636" indexname="_dta_index__CRgActP1636_c_8_2038922981__K1_K2_K3" id="lockab0c2b80" mode="X" associatedObjectId="72057608907522048">
    <owner-list>
     <owner id="process8261abc8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process8b1ea088" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057608907522048" dbid="8" objectname="zup.dbo._CRgActP1636" indexname="_dta_index__CRgActP1636_c_8_2038922981__K1_K2_K3" id="lockbcaf6180" mode="X" associatedObjectId="72057608907522048">
    <owner-list>
     <owner id="process8b1ea088" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process8261abc8" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

17 окт 12, 15:20    [13334649]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
нязнайка
Guest
Проблема в сортировке все-таки.
Если смотреть план запроса : сканирование внутренней таблицы deleted, то у него нету опции Ordered или нет.
Данные нужно упорядочить, как вам уже советовали.


Попробуйте внутри тригера создать временную переменную или таблицу с нужным кластерным индексом.
Deadlock-и должны уйти.
17 окт 12, 15:37    [13334789]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
invm
Member

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

Если судить вот по этому: 13315387
(_RecorderTRef, _RecorderRRef, _LineNo) -- неключевой набор полей. А удаление у вас в разрезе _Org. Вот и получается, что разные соединения могут удалять одни и те же строки.
17 окт 12, 15:45    [13334879]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
нязнайка
Проблема в сортировке все-таки.
Если смотреть план запроса : сканирование внутренней таблицы deleted, то у него нету опции Ordered или нет.
Данные нужно упорядочить, как вам уже советовали.


Попробуйте внутри тригера создать временную переменную или таблицу с нужным кластерным индексом.
Deadlock-и должны уйти.


Есть там опция Ordered:
+ скриншот
Картинка с другого сайта.


Но попробую сделать так.
17 окт 12, 15:47    [13334906]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
invm
iSteel,

Если судить вот по этому: 13315387
(_RecorderTRef, _RecorderRRef, _LineNo) -- неключевой набор полей. А удаление у вас в разрезе _Org. Вот и получается, что разные соединения могут удалять одни и те же строки.


Прошу не смотреть там, т.к. там речь идет о секционировании таблицы, то был как вариант, я тогда думал о том, чтобы физически разложить данные в разные файлы, чтобы уйти от page lock.
_Org - реквизит был искусственно добавлен, для физического разделения. Сейчас его нету.

Вот как таблица выглядит:
CREATE TABLE [dbo].[_CRgActP1636](
	[_RecorderTRef] [binary](4) NOT NULL,
	[_RecorderRRef] [binary](16) NOT NULL,
	[_LineNo] [numeric](9, 0) NOT NULL,
	[_APDateFrom] [datetime] NOT NULL,
	[_APDateTill] [datetime] NOT NULL
) ON [PRIMARY]
17 окт 12, 15:51    [13334931]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
нязнайка
Guest
автор
Есть там опция Ordered


Прошу прощения, действительно опция есть но только в instead of тригере.
Я изначально посмотрел в after (там спец. оператор inserted/deleted scan), а у него нет такой опции.

Получается ваша таблица на данный момент - куча.
Попробуйте создать хотя неуникальный, но кластерный индекс по этим колонкам как в _dta_index__CRgActP1636_c_8_2038922981__K1_K2_K3.
17 окт 12, 16:07    [13335067]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Кластерный индекс я уже пробовал, никак не меняется ситуация либо page lock(если ставить галку Page_Lock в опциях индекса), либо Key lock.
17 окт 12, 16:18    [13335184]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
invm
Вот и получается, что разные соединения могут удалять одни и те же строки.

Кстати пробовал такой вариант, думал может одинаковые строки цепляет, однако нет.
Вычислил 2 документа на которых была взаимоблокировка, в триггере написал вставку в новую таблицу, которую потом посмотрел, что же он там удаляет, так вот данные там разные. Но взаимоблокировки были только, если постоянно их переписывать из 1С, в 2 потока. В окнах sql 2 сеанса мирно держали блокировки и никому не мешали.
17 окт 12, 16:32    [13335349]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Кстати есть еще 1 момент, в течении транзакции удаление в таблице происходит дважды, с разным набором строк.
Может дело в этом?
17 окт 12, 16:33    [13335361]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Решил выяснить природу эскалации блокировок, если зациклить удаление одинаковых записей, в одной и той же таблице, то происходит эскалация до Х.
Затем начал удалять разные данные по одной организации, со временем начали появляться те же самые эскалации до Х.
Это все без флажка -T1224
17 окт 12, 16:50    [13335521]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
нязнайка
Guest
Кстати есть еще 1 момент, в течении транзакции удаление в таблице происходит дважды, с разным набором строк.
Может дело в этом?


Да, покажите весь код, всю транзакцию.
Если у вас 2 обновления в разных местах транзакции, но ключи обновления не пересекаются и есть хороший индекс, то деадлока может и не быть.

Теперь обратите внимание на заполнение таблицы deleted.

Посмотреть блокировки, что остались после заполнения deleted можно если добавить вначале тригера :

SELECT *
FROM sys.dm_tran_locks dtl
WHERE dtl.request_session_id = @@SPID


Ни одной блокировки ключа нету, есть только UI на странице.
Поэтому instead of тригер - не помощник в этом деле. Нужно код менять.

Такая конструкция #tt641 T11 WITH(NOLOCK) подталкивает оптимизатор к unordered scan.
17 окт 12, 16:57    [13335585]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Выложил трассу, в самом конце есть момент дедлока, там 2 сессии всего.
http://files.mail.ru/TGTG1R
17 окт 12, 17:29    [13335859]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
iSteel
Такое ощущение, что перезаполняется какой то буфер блокировок и происходит эскалация уровня блокировки.


Это запросто может быть. Решение --- увеличить кол-во блокировок в конфигурации сервера.

Написано тут.

http://msdn.microsoft.com/en-us/library/ms190253.aspx
17 окт 12, 17:42    [13335952]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
iSteel
Есть проблема с взаимоблокировками в sql. База 1С УПП 8.2 Проблема при массовом проведении документов по зарплате, по разным организациям.


Да, если приложение не ваше, как тут имеет место быть, то дедлоки вы не уберёте никак, можно только конфигурировать
сервер на большее кол-во локов, большее кол-во памяти для них.
17 окт 12, 17:44    [13335968]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
MasterZiv
Да, если приложение не ваше, как тут имеет место быть, то дедлоки вы не уберёте никак, можно только конфигурировать
сервер на большее кол-во локов, большее кол-во памяти для них.

Большую часть блокировок уже убрали благодаря этому топику, спасибо участникам ;)
Речь теперь идет о крупных документах, ранее же, даже на мелких, были проблемы.
17 окт 12, 19:59    [13336522]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
MasterZiv
Это запросто может быть. Решение --- увеличить кол-во блокировок в конфигурации сервера.

Вроде как флажок T1224 не смотрит туда вообще, однако проблема имеет место быть.
17 окт 12, 20:00    [13336529]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Продолжил исследование взаимоблокировок.
Решил проверить, а не появляются ли избыточные блокировки, которые не принадлежат моему сеансу, когда удаление происходит в 1 сеанс. И о чудо, появляются! Т.е. когда заблокировано много записей, возникает X,U блокировка.

Сделал такой триггер:
+ sql
USE [1c_zup_kon_test_3]
GO
/****** Object:  Trigger [dbo].[on_delete_P1636]    Script Date: 10/18/2012 08:48:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[on_delete_P1636] on [dbo].[_CRgActP1636]
instead of delete
as
begin
  Delete from T1 with(xlock)
   From _CRgActP1636 as T1
   inner join deleted as T3 on T1._RecorderTRef=T3._RecorderTRef and T1._RecorderRRef=T3._RecorderRRef AND T1._LineNo=T3._LineNo

  insert into _CRgActP1636_statistic(_SPID,_kol_my_locks,_kol_my_locks_x,_kol_other_locks,_kol_other_locks_xu)
  SELECT @@SPID as _SPID,
		sum(case 
			when dtl.request_session_id = @@SPID then 1
			else 0
			end) as kol_my_locks,
		sum(case 
			when dtl.request_session_id = @@SPID and dtl.request_mode='X' then 1
			else 0
			end) as kol_my_locks_x,
		sum(case 
			when dtl.request_session_id <> @@SPID then 1
			else 0
			end) as kol_other_locks,
		sum(case 
			when dtl.request_session_id <> @@SPID and (dtl.request_mode='X' or dtl.request_mode='U') then 1
			else 0
			end) as kol_other_locks
	FROM sys.dm_tran_locks dtl
	WHERE resource_database_id=DB_ID();
   
end;


Вот что лежало в таблице:
+ скриншот

Картинка с другого сайта.

В скриншоте видно, что после блокирования более 10000, sql сервер держит дополнительную блокировки типа X,U. У меня она держалась 16 секунд.

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

Откуда:
Сообщений: 76
Хех, как выяснилось то была блокировка FILE0 -)
Что интересно периодически возникают блокировки всех записей типа Х. Это что же за ерунда такая?

+ скриншот

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

Откуда:
Сообщений: 76
Хех, теперь даже в 7 потоков не могу поймать дедлок. Что же за мистика такая, при чем данные за тот же период обрабатываются.

Вывел топ, по количеству блокировок, доходит до 45000 блокировок в сеансе.
+ скриншот

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

Откуда:
Сообщений: 76
Похоже удалось поймать причину взаимоблокировок.
SQL сервер при нехватке ресурсов отправляет всех во взаимоблокировку, сейчас я в этом убедился.
Разместил базу на SSD, а tempdb на SAS оставил, в итоге взаимоблокировки полезли, т.к. SAS загрузился на 100% легко и пошли очереди на нем в счетчиках windows.

Т.е. не только какие то ошибки настройки sql сервера или не оптимальный код, влияют на взаимоблокировки.
19 окт 12, 16:24    [13347859]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
aleks2
Guest
Продолжайте наблюдение.
19 окт 12, 19:14    [13348599]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Почему то, периодически, идет укрупнение блокировок. При этом в профайлере нет события "Escalation".
Что это значит?

Например, удаление 574 строк вызвало блокировку 20 000 записей. Затем, чуть позже, идет удаление 72 строк, а блокируется уже 119 667 записей. И конечно же потом пошли взаимоблокировки.

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

Откуда:
Сообщений: 76
Пробую теперь с таким триггером:
  Delete from T1 
   From deleted as T3
   left join _CRgActP1636 as T1 on T1._RecorderTRef=T3._RecorderTRef and T1._RecorderRRef=T3._RecorderRRef AND T1._LineNo=T3._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



В течении получаса внезапных блокировок по 100 000 записей нету. Посмотрим, что будет дальше.
22 окт 12, 13:50    [13356428]     Ответить | Цитировать Сообщить модератору
 Re: Взаимоблокировки, анализ и поиск путей решения.  [new]
iSteel
Member

Откуда:
Сообщений: 76
Все работает замечательно. Теперь нет никаких глюков(лишних блокировок).
Видимо inner join работает иначе, в случае удаления строк, с использованием временной таблицы.
22 окт 12, 17:15    [13357921]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 3 [4] 5   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить