Как найти причину Deadlock в MS SQL

[K000k]

Помогите плз, не могу найти где искать источник Deadlock
https://yadi.sk/i/oD25kUrf3Ngj6g

[Dmitry67]

Получите deadlock graph, он в XML

[zVlad]

Вам обоим надо поставить двойку. Один задает вопрос ответ на который дается в каждом учебнике по реляционным базам данных и системам с блокировками ресурсов вообще. А второй пытается помочь этому.
Возможно также что вопрос сформирован не корректно. Автор, наверное, спрашивает о том как узнать какие процессы создали deadlock и на каком ресурсе.

[Dmitry67]

Вам обоим надо поставить двойку. Один задает вопрос ответ на который дается в каждом учебнике по реляционным базам данных и системам с блокировками ресурсов вообще. А второй пытается помочь этому.
Возможно также что вопрос сформирован не корректно. Автор, наверное, спрашивает о том как узнать какие процессы создали deadlock и на каком ресурсе.

О, подтянулись крупные специалисты по мертвым технологиям

Возвращаясь к теме
Именно на этот вопрос и ответит XML

ПРимер:

Code: Select all

- <EVENT_INSTANCE>
  <EventType>DEADLOCK_GRAPH</EventType> 
  <PostTime>2017-10-18T08:35:58.083</PostTime> 
  <SPID>20</SPID> 
- <TextData>
- <deadlock-list>
- <deadlock victim="process756bc8">
- <process-list>
- <process id="process756bc8" taskpriority="0" logused="0" waitresource="PAGE: 11:1:388407" waittime="22077" ownerId="2431733424" transactionname="INSERT" lasttranstarted="2017-10-18T08:35:35.980" XDES="0x1321e9b60" lockMode="S" schedulerid="8" kpid="13456" status="suspended" spid="133" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2017-10-18T08:35:35.980" lastbatchcompleted="2017-10-18T08:35:35.980" clientapp=".Net SqlClient Data Provider" hostname="хрю" hostpid="10624" loginname="хрю" isolationlevel="read committed (2)" xactid="2431733424" currentdb="11" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
- <executionStack>
  <frame procname="хрю.dbo.хрю" line="62" stmtstart="5552" stmtend="7852" sqlhandle="0x03000b003733966c817213000da800000100000000000000">insert @Result(EventID,RefEntityTypeID,RefClientID,SecID,LegalEntityID,CoraxEventID,CounterpartyID,RefEventActionID,RefEventStatusID,RefEventSourceID,CreateDate,ChangeDate,ChangedBy,Priority,EntityName,IsTicket) SELECT top(@ChunkRows4UpdateSecurity) e.EventID, e.RefEntityTypeID, e.RefClientID, s.SecID as SecID, null as LegalEntityID, e.CoraxEventID, e.CounterpartyID, e.RefEventActionID, e.RefEventStatusID, e.RefEventSourceID, e.CreateDate, e.ChangeDate, e.ChangedBy, e.Priority, s.Name as EntityName, CAST(CASE WHEN t.EventID IS NULL THEN 0 ELSE 1 END as bit) AS IsTicket FROM dbo.[Event] e INNER JOIN dbo.Sec s WITH(NOLOCK) on s.SecID = e.SecID LEFT OUTER JOIN SecStub t on t.EventID = e.EventID WHERE (t.EventID is null or (t.Vendor is null and t.RefTicketStatusID = 1)) AND e.RefEntityTypeID = 1 AND e.RefEventActionID = 2 AND e.RefEventStatusID = 1 and (e.RequestID is null or exists(select 1 from dbo.EventProcessingHistory eph where eph.R</frame> 
  </executionStack>
  <inputbuf>Proc [Database Id = 11 Object Id = 1821782839]</inputbuf> 
  </process>
- <process id="process708988" taskpriority="0" logused="110260" waitresource="PAGE: 11:1:95564" waittime="740" ownerId="2431729700" transactionname="user_transaction" lasttranstarted="2017-10-18T08:35:14.200" XDES="0x2f75e1970" lockMode="IX" schedulerid="5" kpid="6576" status="suspended" spid="92" sbid="0" ecid="0" priority="0" trancount="4" lastbatchstarted="2017-10-18T08:35:14.200" lastbatchcompleted="2017-10-18T08:35:14.200" clientapp=".Net SqlClient Data Provider" hostname="хрю" hostpid="10624" loginname="хрю" isolationlevel="read committed (2)" xactid="2431729700" currentdb="11" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
- <executionStack>
  <frame procname="SMGC.dbo.usp_populateEventByLegalEntityGC2GC" line="275" stmtstart="16924" stmtend="19732" sqlhandle="0x03000b003bd22a5a967313000da800000100000000000000">INSERT INTO dbo.Event ( RefEntityTypeID, SecID, CoraxEventID, CounterpartyID, LegalEntityID, RefEventActionID, RefEventStatusID, RefEventSourceID, ChangedBy, RefClientID, Priority, RequestID ) SELECT DISTINCT e.RefEntityTypeID, NULL, NULL, NULL, e.LegalEntityID, e.RefEventActionID, e.RefEventStatusID, e.RefEventSourceID, e.ChangedBy, e.RefClientID, 7, @RequestID FROM #EventLE e inner join dbo.LegalEntity le on le.LegalEntityID=e.LegalEntityID left join dbo.LegalEntityAuxCustomValues acvle on acvle.LegalEntityID=le.SecLeID_Parent LEFT JOIN dbo.Event AS t2 --WITH(NOLOCK) ON e.RefEntityTypeID= t2.RefEntityTypeID AND e.LegalEntityID=t2.LegalEntityID AND e.RefEventActionID= t2.RefEventActionID AND e.RefEventSourceID = t2.RefEventSourceID AND (@RequestID =t2.RequestID OR (@RequestID IS NULL AND t2.RequestID IS NULL AND e.RefEventStatusID = t2.RefEventStatusID)) WHERE (t2.LegalEntityID I</frame> 
  <frame procname="хрю" line="294" stmtstart="19566" stmtend="20454" sqlhandle="0x03000b001e931b26977313000da800000100000000000000">exec [dbo].[хрю] @AutoPost=@AutoPost, @RefClientID=@RefClientID, @RefSecTypeList=@RefSecTypeList, @RefInvestmentTypeList=@RefInvestmentTypeList, @SecIDList=@SecIDList, @SourceOnly=@SourceOnly, @NoCheckSourcePriority=@NoCheckSourcePriority, @AttributeID=@AttributeID, @RequestID=@RequestID, @IncludeInactive=@IncludeInactive, @RefEventSourceID=@RefEventSourceID, @ChangedBy=@ChangedBy</frame> 
  </executionStack>
  <inputbuf>Proc [Database Id = 11 Object Id = 639341342]</inputbuf> 
  </process>
  </process-list>
- <resource-list>
- <pagelock fileid="1" pageid="388407" dbid="11" objectname="хрю" id="lock112e31c00" mode="IX" associatedObjectId="72057602522611712">
- <owner-list>
  <owner id="process708988" mode="IX" /> 
  </owner-list>
- <waiter-list>
  <waiter id="process756bc8" mode="S" requestType="wait" /> 
  </waiter-list>
  </pagelock>
- <pagelock fileid="1" pageid="95564" dbid="11" objectname="хрю" id="lock332561580" mode="S" associatedObjectId="72057602522611712">
- <owner-list>
  <owner id="process756bc8" mode="S" /> 
  </owner-list>
- <waiter-list>
  <waiter id="process708988" mode="IX" requestType="wait" /> 
  </waiter-list>
  </pagelock>
  </resource-list>
  </deadlock>
  </deadlock-list>
  </TextData>
  <TransactionID /> 
  <LoginName>sa</LoginName> 
  <StartTime>2017-10-18T08:35:58.080</StartTime> 
  <ServerName>хрю</ServerName> 
  <LoginSid>AQ==</LoginSid> 
  <EventSequence>35828382</EventSequence> 
  <IsSystem>1</IsSystem> 
  <SessionLoginName /> 
  </EVENT_INSTANCE>

[zVlad]

....

О, подтянулись крупные специалисты по мертвым технологиям

...

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

[zVlad]

...
Возвращаясь к теме
Именно на этот вопрос и ответит XML

А вот как это выглядит в DB2 for zOS (в системном журнале zOS):

Code: Select all

DSNT375I  -DB09 PLAN=P9GPASS WITH 135
        CORRELATION-ID=POOLPD240005
        CONNECTION-ID=AS7DNG1
        LUW-ID=NET.DB09LU.D34E3B9D560D=111241
        THREAD-INFO=KHANASHR:*:*:*
        IS DEADLOCKED WITH PLAN=P9GPASS WITH
        CORRELATION-ID=POOLPD200013
        CONNECTION-ID=AS7PNG2
        LUW-ID=NET.DB09LU.D34E3B9D1FF5=111236
        THREAD-INFO=WILLIAK:*:*:*
        ON MEMBER DB09
DSNT501I  -DB09 DSNILMCL RESOURCE UNAVAILABLE 136
           CORRELATION-ID=POOLPD240005
           CONNECTION-ID=AS7DNG1
           LUW-ID=NET.DB09LU.D34E3B9D560D=111241
           REASON 00C90088
           TYPE 00000304
           NAME AS09PDB .TISCASUM.X'001232' '.X'02'

Первое сообщение DSNT375I о том кто и кем был deadlock-ed. Второе, DSNT501T показывает имя ресурса, номер страницы и номер записи на ней.

[iDesperado]

И все ? А граф дб2 экспортнуть может?

[zVlad]

И все ? А граф дб2 экспортнуть может?

О чем граф? Что еще нужно знать о deadlock кроме того что представленно в моем примере чтобы начать исправлять программу допускающую deadlock?

[iDesperado]

О чем граф? Что еще нужно знать о deadlock кроме того что представленно в моем примере чтобы начать исправлять программу допускающую deadlock?

нужна базовая информация + граф. нужно имя и тип объекта (индекс, таблица, etc), тип блокировки, какойнить идентификатор типа rowid. в твоем примере нифига нет, ни имени таблицы, ни размер блокировки (row lock, page lock, table lock?). может в коде все хорошо, просто это тупой дб2 с крошечной памятью проэскалировал блокировку до page lock и тем самым вырубил транзакцию которая вообще не должна бы пересечься.

[zVlad]

О чем граф? Что еще нужно знать о deadlock кроме того что представленно в моем примере чтобы начать исправлять программу допускающую deadlock?

нужна базовая информация + граф. нужно имя и тип объекта (индекс, таблица, etc), тип блокировки, какойнить идентификатор типа rowid. в твоем примере нифига нет, ни имени таблицы, ни размер блокировки (row lock, page lock, table lock?). может в коде все хорошо, просто это тупой дб2 с крошечной памятью проэскалировал блокировку до page lock и тем самым вырубил транзакцию которая вообще не должна бы пересечься.

Все что ты перечислил все в моем примере есть.
Тебе тоже надо бы почитать учебники. Причина у deadlock одна, и устраняется она переписыванием программ.
Граф для этой ситуации ничем не помогает. Это все и без графа понять легко.

[iDesperado]

Все что ты перечислил все в моем примере есть.
Тебе тоже надо бы почитать учебники. Причина у deadlock одна, и устраняется она переписыванием программ.
Граф для этой ситуации ничем не помогает. Это все и без графа понять легко.

Влад, уж не тебе оценивать мои знания. ты же это сам хорошо знаешь. еще раз, дб2 блокировочник, причем страдает эскалациями, причина может как в логике процедур, так и в ущербности движка дб2, который вынужден временами применять эскалацию блокировок. эскалация блокировок одна из причин дедлоков в дб2
https://www.ibm.com/support/knowledgece ... ation.html

Влад, ну признай, что никогда не работал с db2.

[zVlad]

Все что ты перечислил все в моем примере есть.
Тебе тоже надо бы почитать учебники. Причина у deadlock одна, и устраняется она переписыванием программ.
Граф для этой ситуации ничем не помогает. Это все и без графа понять легко.

Влад, уж не тебе оценивать мои знания. ты же это сам хорошо знаешь. еще раз, дб2 блокировочник, причем страдает эскалациями, причина может как в логике процедур, так и в ущербности движка дб2, который вынужден временами применять эскалацию блокировок. эскалация блокировок одна из причин дедлоков в дб2
https://www.ibm.com/support/knowledgece ... ation.html

Влад, ну признай, что никогда не работал с db2.

Вообще то я привел пример диагностических сообщений чтобы показать как в DB2 диагностика связанная с deadlock выглядит.
Ты хочешь поговорить о влиянии эскалации на частоту deadlocks? Давай поговорим об этом. Но ты и из предложенной тобой ссылки понимаешь что DSNI031I позволяет исключить или подтвердить что конкретный deadlock мог быть спровоцирован конкретной эскалацией.
Т.е. говорить то здесь особо не о чем.
Единственное что могло бы быть интересным и полезным это поговорить о том как избежать deadlock в программах. Наверное это не тривиальная тема коль скоро проблема продолжает присутствовать во всех системах с блокировками, не только в базах данных.

P.S. Да конечно же, ты прав, я никогда не работал с DB2. Я вообще компьютеров в жизни не видал.

[ie]

Получите deadlock graph, он в XML

так для этого надо трэйсер иметь постоянно включенным, так?

[iDesperado]

Вообще то я привел пример диагностических сообщений чтобы показать как в DB2 диагностика связанная с deadlock выглядит.
Ты хочешь поговорить о влиянии эскалации на частоту deadlocks? Давай поговорим об этом. Но ты и из предложенной тобой ссылки понимаешь что DSNI031I позволяет исключить или подтвердить что конкретный deadlock мог быть спровоцирован конкретной эскалацией.

нет, не понимаю. я не вижу в сообщении ничего. я не вижу что было заблокировано - индекс, таблица или что еще. я не вижу что с чем столкнулось, был ли там row lock или page lock, может что еще. там ничего нет и понять дело в эскалации или логике по такому обрывку не возможно.

[Dmitry67]

Получите deadlock graph, он в XML

так для этого надо трэйсер иметь постоянно включенным, так?

Не обязательно
Можно подписаться на extended events и триггер будет получать инфу и сохранять куда хотите.

[ie]

Получите deadlock graph, он в XML

так для этого надо трэйсер иметь постоянно включенным, так?

Не обязательно
Можно подписаться на extended events и триггер будет получать инфу и сохранять куда хотите.

extended events? отстал я от жизни..
мне надо будет написть тригер на таблицу(ы)?

[Dmitry67]

нет, триггеры в SQL могут работать и на логины, и на DDL, и на это

Вот пример login trigger https://msdn.microsoft.com/en-us/librar ... .105).aspx
А вот про deadlocks: https://www.brentozar.com/archive/2014/ ... esnt-hard/

[ie]

нет, триггеры в SQL могут работать и на логины, и на DDL, и на это

Вот пример login trigger https://msdn.microsoft.com/en-us/librar ... .105).aspx
А вот про deadlocks: https://www.brentozar.com/archive/2014/ ... esnt-hard/

нифигасе... а я тут по старинке киркой да лапатой все делаю.

[Dmitry67]

нет, триггеры в SQL могут работать и на логины, и на DDL, и на это

Вот пример login trigger https://msdn.microsoft.com/en-us/librar ... .105).aspx
А вот про deadlocks: https://www.brentozar.com/archive/2014/ ... esnt-hard/

нифигасе... а я тут по старинке киркой да лапатой все делаю.

Вспоминается анекдот: какой такой механизм, все вручную (c)

[zVlad]

Вообще то я привел пример диагностических сообщений чтобы показать как в DB2 диагностика связанная с deadlock выглядит.
Ты хочешь поговорить о влиянии эскалации на частоту deadlocks? Давай поговорим об этом. Но ты и из предложенной тобой ссылки понимаешь что DSNI031I позволяет исключить или подтвердить что конкретный deadlock мог быть спровоцирован конкретной эскалацией.

нет, не понимаю. я не вижу в сообщении ничего. я не вижу что было заблокировано - индекс, таблица или что еще. я не вижу что с чем столкнулось, был ли там row lock или page lock, может что еще. там ничего нет и понять дело в эскалации или логике по такому обрывку не возможно.

Все там есть. Эскалацию я не проверял, не смотрел. Почему - объяснение выше, тема не о DB2 и не об эскалации. Главная же причина - алгоритм блокирования ресурсов в программах. Эскалация может только повлиять на частоту дедлоков, но не на их появление.

[zVlad]

нет, триггеры в SQL могут работать и на логины, и на DDL, и на это

Вот пример login trigger https://msdn.microsoft.com/en-us/librar ... .105).aspx
А вот про deadlocks: https://www.brentozar.com/archive/2014/ ... esnt-hard/

В DB2 for zOS для получения диагностики, в частности, по deadlock ничего делать не требуется. Кроме сообщений в системный журнал (показаны выше), DB2 формирует записи в трассировку, которую можно обрабатывать программно для получения желаемых отчетов.

[Dmitry67]

нет, триггеры в SQL могут работать и на логины, и на DDL, и на это

Вот пример login trigger https://msdn.microsoft.com/en-us/librar ... .105).aspx
А вот про deadlocks: https://www.brentozar.com/archive/2014/ ... esnt-hard/

В DB2 for zOS для получения диагностики, в частности, по deadlock ничего делать не требуется. Кроме сообщений в системный журнал (показаны выше), DB2 формирует записи в трассировку, которую можно обрабатывать программно для получения желаемых отчетов.

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

Также не видно, кто из этих процессов был выбран жертвой

[zVlad]

нет, триггеры в SQL могут работать и на логины, и на DDL, и на это

Вот пример login trigger https://msdn.microsoft.com/en-us/librar ... .105).aspx
А вот про deadlocks: https://www.brentozar.com/archive/2014/ ... esnt-hard/

В DB2 for zOS для получения диагностики, в частности, по deadlock ничего делать не требуется. Кроме сообщений в системный журнал (показаны выше), DB2 формирует записи в трассировку, которую можно обрабатывать программно для получения желаемых отчетов.

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

Также не видно, кто из этих процессов был выбран жертвой

Ну вы, ребята, даете. Читать сообщения на нормальном английском языке разучились со своими xml и графами.
В первом сообщении дает два набора данных о столкнувшихся юзерах соединеных "IS DEADLOCKED WITH..", в переводе на русский первый был выбран жертвой дэдлока вторым.
Второе сообщение начинается словами "RESOURCE UNAVAILABLE", и в конце сообще с ключевым словом "NAME =" идет имя ресурса. В имени есть два шестнадцетиричных значения, первое это страница, второе это номер записи на ней. Поскольку есть номер записи то это row level locking.
Другие ключевые слова означают:

CORRELATION-ID имя транзакции и еще кое что что может привести к имени программы
CONNECTION-ID имя сервера приложений
LUW-ID сетевая информации о рабочей станции с которой была транзакция запрошена
REASON причина недоступности ресурса, в данном случае deadlock
TYPE тип ресурса, в данном случае табличное пространство

P.S. THREAD-INFO в данном случае userID конечных пользователей.

[ie]

Влад и Дима, допустим мы нашли таблицу и пару stored-proc, которые создают дэдлок.
допустим это случается once in a blue moon, тоесть программы и db работают в основном нармально.

теперь что мы будем делать? хотелось бы узнать ваш подход к решению проблемы.

ну понятно что можно валить все на разработчиков фронт-энд которые пишут кривой sql.
но с другой стороны в 99% случаев их sql работает... такшта... кто виновать? и что делать?

[zVlad]

Влад и Дима, допустим мы нашли таблицу и пару stored-proc, которые создают дэдлок.
допустим это случается once in a blue moon, тоесть программы и db работают в основном нармально.

теперь что мы будем делать? хотелось бы узнать ваш подход к решению проблемы.

ну понятно что можно валить все на разработчиков фронт-энд которые пишут кривой sql.
но с другой стороны в 99% случаев их sql работает... такшта... кто виновать? и что делать?

Хороший и даже ключевой в этой ситуации вопрос. Я в течении последних 17+ лет работы пытался донести до наших програмистов идею как с этим бороться несчетное количество раз. Последний раз это было совсем недавно, не более недели назад.

Вспомним, для начала, что ж такое deadlock. Один пользователь локает ресурс r1 и держит, второй локает r2 и держит. Первый хочет залокать r2 - ждет, a второй просит lock на r1. Второго система выкидывает, давая первому закончить хотя бы его транзакцию.

Вариант решения проблемы #1: локать r1, и r2 одновременно, сразу. Но, как я понимаю, не все алгоритмы и логика выполнения могут определить сразу что понадобится ресурс r2.
Вариант решения проблемы #2: локать все таблицы нужные транзакции полностью и сразу. Тут есть неочевидные плюсы и минусы. Это может работать но явно снизут уровень параллельногоо выполнения множества транзакции. Из плюсов - меньше возни с locks, т.е. меньше накладных расходов. Идеальный, тем не менее, вариант в случае одного CPU "бесконечной" мощности, т.е. такой что любая транзакция выполняется "мгновенно".
Третий (наиболее реалистичный, простой, понятный) вариант #3: Програма распознает что оказалась жертвой deadlock и повторяет попытку.

P.S. Есть еще вариант решения (полагаю и не один, но один знаю точно). Расскажу после прогулки.