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

[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. Есть еще вариант решения (полагаю и не один, но один знаю точно). Расскажу после прогулки.

[ie]

Влад и Дима, допустим мы нашли таблицу и пару 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 и повторяет попытку.

ну то есть если у вас сложная система, с множеством програмистов, решения практически нет?

[zVlad]

....

ну то есть если у вас сложная система, с множеством програмистов, решения практически нет?

Решение проблемы #4: Залокав r1 и обнаружих потребность в ресурсе r2 программа отпускает ресурс r1 и локает ресурсы r1 и r2 одновременно.

Наверняка есть и дриугие подходы. Я никогда не боролся с проблемой deadlock в своих программах поскольку мои программы однопользовательские.

Прикрываются мифической сложностью те, по моему глубокому убеждению, кто не умеет писать эти самые сложные системы, т.е. не умеет управлять проблемами сложных систем. Пишет как получится, а потом блеет о сложности систем. Не обижайтесь, это я не о Вас лично, а так вообще.

[ie]

....

ну то есть если у вас сложная система, с множеством програмистов, решения практически нет?

Решение проблемы #4: Залокав r1 и обнаружих потребность в ресурсе r2 программа отпускает ресурс r1 и локает ресурсы r1 и r2 одновременно.

Наверняка есть и дриугие подходы. Я никогда не боролся с проблемой deadlock в своих программах поскольку мои программы однопользовательские.

Прикрываются мифической сложностью те, по моему глубокому убеждению, кто не умеет писать эти самые сложные системы, т.е. не умеет управлять проблемами сложных систем. Пишет как получится, а потом блеет о сложности систем. Не обижайтесь, это я не о Вас лично, а так вообще.

как вы можете рассуждать о том что кто-то "прикрывается" если у вас нет опыта написания многопользовательских программ?

[zVlad]

....

ну то есть если у вас сложная система, с множеством програмистов, решения практически нет?

Решение проблемы #4: Залокав r1 и обнаружих потребность в ресурсе r2 программа отпускает ресурс r1 и локает ресурсы r1 и r2 одновременно.

Наверняка есть и дриугие подходы. Я никогда не боролся с проблемой deadlock в своих программах поскольку мои программы однопользовательские.

Прикрываются мифической сложностью те, по моему глубокому убеждению, кто не умеет писать эти самые сложные системы, т.е. не умеет управлять проблемами сложных систем. Пишет как получится, а потом блеет о сложности систем. Не обижайтесь, это я не о Вас лично, а так вообще.

как вы можете рассуждать о том что кто-то "прикрывается" если у вас нет опыта написания многопользовательских программ?

Рассуждать всегда можно. Тем более если есть что предложить, как это я показал. Мои программы возможно и будут работать в многопользовательском варианте - я не проверял, поскольку писал их для себя, для DBA и zOS system programmer, который умеет програмировать и делает это чтобы сократить ручную работу или там где обьем работы не посволяет обойтись ручным управлением.

[Dmitry67]

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

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

Как ни странно я в общем совершенно согласен с zVlad. Действительно, правильный (и одинаковый) порядок модификации таблиц, Однако это примерно как сказать 'если все будут ездить по правилам, то аварий не будет'. Правильно, но бесполезно. Реально объектно ориентированные системы делают все в удобном им порядке, а если есть ORM, то вообще туши свет.

Однако есть еще один путь - очень часто процессы пересекаются по данным не напрямую, а косвенно. Поясню, что я имею в виду. Пусть вы выполняете

update TAB set N=N+1 where A=111 and B=222

Причем индекс есть только по A. Тогда SQL читает (если это эффективно) данные по индексу A и далее фильтрует по условию B=222. При этом данные с B<>222 также оказываются прочитанными и заблокированными. Это не баг, это важно для consistency. Соотвественно, вы можете словить блокировку или дедлок по данным которые даже не меняли (B<>222). Индекс по A,B накладывает RANGE LOCK по A,B сразу и не касается данных которе вы не трогаете

Иногда, при низкой селективности индекса SQL может предпочесть SCAN. Это может оказаться быстрее но... повысит вероятность блокировки. В этом случае может иметь смысл немного поступиться произодительностью, зафорсив использование индекса.

Можно только добавить, что в MS SQL есть еще возможность поиграться snapshot isolation level.