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

[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, вместо мульенов локов на таблицу. уже обалденный выигрыш. плюс у мскл я помню полно оптимизаций на эту тему. там можно считать значение с индекса в тот момент когда строка залочена эксклюзивно и меняются значения полей, не входящие в индекс.