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

[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.

[iDesperado]

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

т.е. мне предлагается угадывать индекс то был или таблица, shared lock или exclusive. допустим известно что второй пытался row level лок поставить, но в чем причина дедлока мне угадывать ? с чем его блокировка оказалась не совместима ? с table lock первого ? page lock ? может как и у первого row lock. у вас там субд или конкурс угадайка ?
по факту в сообщении нет и половины необходимой инфы, что бы сделать хотя бы предварительные выводы.

[ALV00]

Для дедлоков есть простое решение: все агенты должны обращаться к ресурсам в одном и том же порядке. Если один тред обращается к переменным в порядке A,B, то другой тоже должен обращаться в порядке A,B. Если B,A то возможен дедлок.

В db2 есть монитор для дедлоков, который дает исчерпывающую информацию, хоть в таблицах, хоть в XML.

[zVlad]

Вынужден огорчить ребят которые страдают от index locks. В DB2 начиная с версии 4.1 (это очень давно было) индексы не локируются. Можете, например, здесь почитать:
http://www.anesi.com/v41201.htm

iDespirado, не истери. Просто подумай и все поймешь сам. Удачи. Я поехал в лес по грибы. Продолжим вечером, а может раньше если грибов не будет.

[Dmitry67]

Для дедлоков есть простое решение: все агенты должны обращаться к ресурсам в одном и том же порядке. Если один тред обращается к переменным в порядке A,B, то другой тоже должен обращаться в порядке A,B. Если B,A то возможен дедлок.

Как я уже говорил, это абсолютно верный и абсолютно бесполезный совет
Вы на воздушном шаре, сэр!

[Ir4]

ну вообще да - прогресс далеко ушел и совет по events тут вполне уместен. Когда я работала с MS SQL (давно) - делала почти то же самое "вручную" - сделала job+SP которая крутилась в цикле постоянно и проверяла дедлоки. Если находила - собирала всю возможную инфу - кто, когда, чем и что - и отправляла мне на мыло и селл.
Далее начинались разборки с программерами - кто чего не так написал. Они, конечно, в отказ. Пришлось написать быстренько процедуру, которая проверяла "закрытость" явно обьявленных транзакций - т.е. чтобы на каждый "begin" был свой "commit" или "rollback". Нашлось несколько таких процедур. Пофиксили, да и все.
А там. где причина была в lock order - пришлось самой искать решение и уже потом идти с готовым к программерам, т.к. сами они не всегда понимали о чем вообще речь.

[mskmel]

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

И как такое работает, когда 100-200 сессий меняющих данные в одной таблице, особенно в её горячем хвосте?

[Dmitry67]

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

И как такое работает, когда 100-200 сессий меняющих данные в одной таблице, особенно в её горячем хвосте?

В OLTP системах как правило эффективный доступ к документам по ID в лоб, так что такой проблемы нет

[zVlad]

....

В db2 есть монитор для дедлоков, который дает исчерпывающую информацию, хоть в таблицах, хоть в XML.

Вы про DB2 for LUW говорите, как я понимаю. В DB2 for zOS deadlocks выявляются автоматом (вопрос к Дмитрию и другим здесь специалистам по MS SQL - MS SQL способен выявлять deadlocks автоматически и решать проблему?) и один из участников убивается через 5 секунд (это время можно менять). Висячих deadlocks быть не может.

[iDesperado]

Вынужден огорчить ребят которые страдают от index locks. В DB2 начиная с версии 4.1 (это очень давно было) индексы не локируются.

блокируются. просто ради экономии памяти роль блоикровки индекса выполняет лок на данные. в 21 веке эта экономия отдает навозом. db2 читая страницы индекса вынужден проверять блокировки связанных строк данных, во времена db2 4.1 в 90х с 16мб памяти может это и было оправдано, но теперь этот подход - куча навоза

[zVlad]

Вынужден огорчить ребят которые страдают от index locks. В DB2 начиная с версии 4.1 (это очень давно было) индексы не локируются.

блокируются. просто ради экономии памяти роль блоикровки индекса выполняет лок на данные. в 21 веке эта экономия отдает навозом. db2 читая страницы индекса вынужден проверять блокировки связанных строк данных, во времена db2 4.1 в 90х с 16мб памяти может это и было оправдано, но теперь этот подход - куча навоза

Ты молодец, iDesperado, я тебя искренне уважаю. Ты читаешь предложенные ссылки, вникаешь... и находишь что сказать. Талант. Талантище!
Искомых грибов - опят не нашел, их нет еще, жаркая осень нынче в Торонто. Но маслят набрал. Были на одном, небольшом, пяточке в лесу, по которому я два часа носился по местам где в прошлом году собирал опята.
На входе в лес было (впервые за 17 лет в Канаде) предупреждение о клещах: blacklegged ticks. Но это меня не остановило. Жена вон жарит эти грибочки, сейчас еще накачу.

[zVlad]

Для дедлоков есть простое решение: все агенты должны обращаться к ресурсам в одном и том же порядке. Если один тред обращается к переменным в порядке A,B, то другой тоже должен обращаться в порядке A,B. Если B,A то возможен дедлок.

Как я уже говорил, это абсолютно верный и абсолютно бесполезный совет
Вы на воздушном шаре, сэр!

Дима, ты не прав. Над предложенным надо, как минимум, задуматься. Возможно, и весьма вероятно, что это имеет смысл и может сократить вероятность deadlocks.
Ведь я же целых четыре решения предложил и никто на опровергнул ни одного.

[ALV00]

блокируются. просто ради экономии памяти роль блоикровки индекса выполняет лок на данные. в 21 веке эта экономия отдает навозом. db2 читая страницы индекса вынужден проверять блокировки связанных строк данных, во времена db2 4.1 в 90х с 16мб памяти может это и было оправдано, но теперь этот подход - куча навоза

А если 20 индексов на таблице, они все блокируются? Это же может тормозить сильно.

Как я уже говорил, это абсолютно верный и абсолютно бесполезный совет
Вы на воздушном шаре, сэр!

Ну не совсем бесполезный, он мне помог сдать экзамен на джава программиста )

[Dmitry67]

Для дедлоков есть простое решение: все агенты должны обращаться к ресурсам в одном и том же порядке. Если один тред обращается к переменным в порядке A,B, то другой тоже должен обращаться в порядке A,B. Если B,A то возможен дедлок.

Как я уже говорил, это абсолютно верный и абсолютно бесполезный совет
Вы на воздушном шаре, сэр!

Дима, ты не прав. Над предложенным надо, как минимум, задуматься. Возможно, и весьма вероятно, что это имеет смысл и может сократить вероятность deadlocks.
Ведь я же целых четыре решения предложил и никто на опровергнул ни одного.

Поясню
Вы приходите в реальный проект. Там проблемы с дедлоками. Вы начинаете разбираеться. Там 1000000 строк кода на .net который что-то делает в нескольких продуктах, причем некоторые легаси а некоторые даже третьих фирм.

Разумеется, правильным порядком в транзакцию там и не пахнет- кто в лес, а кто по дрова. Что посоветуете? Переписать ВСЕ? Это похоже на совет мышам стать ежиками, как в известном анекдоте

[VladDod]

Поясню
Вы приходите в реальный проект. Там проблемы с дедлоками. Вы начинаете разбираеться. Там 1000000 строк кода на .net который что-то делает в нескольких продуктах, причем некоторые легаси а некоторые даже третьих фирм.

Разумеется, правильным порядком в транзакцию там и не пахнет- кто в лес, а кто по дрова. Что посоветуете? Переписать ВСЕ? Это похоже на совет мышам стать ежиками, как в известном анекдоте

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

[iDesperado]

А если 20 индексов на таблице, они все блокируются? Это же может тормозить сильно.

при чтении с индекса ты можешь положить 1 range lock, вместо мульенов локов на таблицу. уже обалденный выигрыш. плюс у мскл я помню полно оптимизаций на эту тему. там можно считать значение с индекса в тот момент когда строка залочена эксклюзивно и меняются значения полей, не входящие в индекс.

[Dmitry67]

Ну вы, ребята, даете. Читать сообщения на нормальном английском языке разучились со своими xml и графами.

Кстати, хотел вернуться к этой вкусной теме
Мейнфреймовцы имеют какой то особый стиль писать сообщения
Они ни фига не похожи на нормальный английский язык

Допустим я хотел бы сделать user friendly сообщение о check engine в автомобиле
У обычного программиста это выглядело бы так:

Check Engine on Odometer reading=34555mi
Error code: 108 (Spark damaged or other problems)
Cylinder 3.

У мейнфреймовца это выглядело бы так:

Code: Select all

-----
ERRCND=108 
SENSR 0x22 SENSRNAME=ODOMTR VALUE=34555 VALUE.UNIT=MI
CYL 0x0=STATUS OK CYL 0x1=STATUS OK 
CYL 0x2=ERR REASON 0x347373 REASNTYP=45
CYL 0x3=STATUS OK
DIAG MGR *****

[Dmitry67]

Для тех, кто хочет писать также, нехитрые советы
1. ONLY UPPERCASE !!!
2. Больше сокращений, не ERROR TYPE а ERRTYP
3. Выкидывайте больше гласных, REASON можно сократить до RSN. Если не будете сокращать, то кончатся все 80 позиций на перфокарте!
4. Больше умничания, больше hex, непонятных слов и сокращений. Главное - самая важная информация должна потонуть в этом месиве!
5. Выводите только код проблемы, и НИ В КОЕМ СЛУЧАЕ ее описание. Ничего, найдут в документации по коду.
6. Напоследок - шлифанте результат ascii art - немножно ----, *** итд по вкусу.
Et voila!

[ALV00]

У мейнфреймовца это выглядело бы так:

LOL. А на XML было бы так:

Code: Select all

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE HTML PUBLIC "-//VAZ MOTORS//DTD HTML 4.0 Transitional//EN" "http://www.blahlbah.org/dadidadah/govno/govno/%81%AB%EF%A4%EC%20%F1%A1%20%E2%A2%AE%EE%20%AC%A0%E2%EC.dtd" >
<?xml-stylesheet type="text/xsl" href="http://www.blahlbah.org/dadidadah/govno/govno/%8F%A8%A7%A4%A5%E6%20%8F%A8%A7%A4%A5%E6.xsl"?>
<vaz:document xmlns:vaz = "http://www.blahlbah.org/dadidadah/govno/govno/%8F%A8%A7%A4%A5%E6%20%8F%A8%A7%A4%A5%E6.xhtml">
<!-- here_we_go_to_the_f_cking_business_at_last  -->
<vaz:the_message>Check Engine</vaz:the_message>
<vaz:odometer>
<vaz:reading measure = "mi">34555</vaz:reading>
</vaz:odometer>
<vaz:error>
<vaz:code>108</vaz:code>
<vaz:error_message>Spark damaged or other problems</vaz:error_message>
<vaz:location type = "cylinder">3</vaz:location>
</vaz:error>
</vaz:document>

[iDesperado]

да, но xml рендерится в сексуальные картинки, читать то xml никто не просит

[alex_127]

Я автор большого куска этого xml. Сам придумывал, никто пальцем не указывал - так что плюхи мне если что. Xml был введен в 2002 году, тогда слово json не слышали. С предыдушим текстовым было много проблем - тяжело натравливать тулзы на него было.
Кстати, ничего дополнительно включать не надо для сбора таких событий - https://blog.sqlauthority.com/2017/01/0 ... ed-events/

А что касается как с ними бороться - это трудно. Highest cost support cases in average...