Олегон,

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

ОлегонДополню. В логах SQL-сервера в обоих случаях:

* Unresolved deadlock
Deadlock monitor failed to resolve one or more deadlocks.
Server may require restart to recover from this condition.

Ну это все, что вам нужно знать, собственно. Deadlock.

Учитывая
Олегонхранимая процедура, в которой неоднократно выполняется запуск другой хранимой процедуры, внутри которой создаются и используются локальные временные таблицы
тут надо смотреть на алгоритм ваших ХП и фиксить логику, которая приводит к таким дедлокам.
Это боевой сервер? Вы можете обратиться к разработчику вашей базы за помощью?

Yasha123PizzaPizzaОлегон,

гугл говорит, что LCK_M_SCH_M ожидание на блокировку изменения схемы. Все блокировки это вопрос уровня изоляции. Начните с выяснения какой уровень изоляции у вас установлен для выполнения вашей исходной ХП.
да что вы говорите.
и на каком же уровне изоляции SCH-M кого-то не остановит?

Вопрос не в остановит-неостановит, а в понимании спрашивающим с чем он столкнулся. Если человек видит блокировку и, допустим, не знаком с изоляциями, то совет тут только один - обратиться к разработчику.
Не будете же вы, в самом деле, советовать на боевом и неизвестном вам сервере что то делать с неизвестной вам логикой.

Yasha123,

Ответ простой: уровень изоляции 42 надо поставить.
И сразу вам вопрос: как можно откатить транзакцию используя партицирование?
Правда это выглядит странно - задавать бессмысленный вопрос нерелевантный контексту?

FYI " Unresolved deadlock" имеет не так много упоминаний и они все легко гуглятся. Пробуйте своими мощными экстрасенсорными способностями помочь ТС с такого уровня проблемами... Дорогуша.

Yasha123какого же тогда хрена

Andy_OLAPтут серьезные разработчики решают серьезные вопросы, а Вам все Козловы видятся в каждом.

О да. Серьезные разработчики, которые решают проблемы кода путем плясок с конфигурацией сервера. А чо, у нас дедлок и локмонитор крашится, давай еще накинем ядер или памяти. На первое время поможет (ну да, поможет).

Да и эти серьезные разработчики читать не очень любят - ТС в цикле работает с #таблицами, и ему совет - а вы их не убивайте. Конечно серьезные разработчики экстрасенсы и "знают", что логика ТС позволяет оставлять эти таблицы в памяти на всю сессию, ну и "всем очевидно", что сбор мусора дешевле силами самого сервера.

0wlодин на голубом глазу изменением уровня изоляции лечит блокировку схемы
Еще раз - вопрос был понимании работает ли ТС с этой базой или "знакомый попросил посмотреть". Преклоняюсь перед местными жителями, которые с порога, видимо наложением куда то рук, видят конфигурацию сервера, тексты процедур, планы выполнения и плюс к этому сразу понимают пишет ли это DBA, девелопер или секретарь.

И кстати да, MaxDOP вполне может помочь. Но понравится ли ТС увеличение времени исполнения? Кого это волнует! Больше советов!

invmИ как знание перечисленного поможет в диагностике причин проблемы?
Никак не поможет. Это простой наводящий вопрос имеющий целью получить чуть больше информации о проблеме. В частности работает ли ТС непосредственно с кодом или же код не доступен и ТС пытается решить проблему конфигурацией сервера.

invmПоясните каким образом, при ожидании на Sch-M, уровень изоляции блокируемой или блокирующей сессии будет на это ожидание влиять?
Никаким. Это было обобщение, грубоватое, да. См. выше.

PizzaPizzaИ кстати да, MaxDOP вполне может помочь.
invmЗа счет чего, при ожидании на Sch-M?

Так как мы все тут тыкаем пальцами в небо я тоже могу предположить окружение. Допустим у ТС запускается множество сессий в них множество ХП со своими вызовами ХП и еще и операции на схему и это все в параллель, тогда, при возникновении ошибки/дедлока у сервера начинаются проблемы с очередями/буферами/планировщиками приводящие к краху/ошибкам локмонитора и (дальше полная спекуляция, что может произойти если локмонитор не справился) невозможности выполнения/отката транзакций сессии и она виснет.
Уменьшение параллелизма должно уменьшить количество объектов/блокировок для менеджмента и, наверное, количество дедлоков в принципе. Вопрос в том, что никто не знает есть ли конкретный запрос, на который поможет поставить MaxDOP и как это повлияет на производительность. А по моему, тут надо в код смотреть и, по возможности, минимизировать/изолировать как то возможные проблемы блокировок. Хотя конечно все зависит от проекта, и где то проще пойти по экстенсивному пути ради производительности.

invmТеперь наложите на вашу гипотезу тот факт, что у ТС проблема в пределах одной сессии.

Я не вижу проблемы с тем, что это одна сессия. Известно, что проблема не очень воспроизводимая, так что вполне мне кажется возможным, что зависшая сессия даёт выполниться другим. Возможно даже, что одновременно существует только одна такая сессия в принципе, согласно условию задачи... Возможно она, эта сессия, запускается в какой то пик нагрузки и сама тяжёлая и при сочетании каких либо факторов даёт проблему.
Все это конечно писанина по воде, с моей точки зрения - без отладочной информации и логов найти затык и (если это баг севера) придумать обходное решение нельзя.

У вас есть ответ? Расскажите. Мне интересно, я никогда не сталкивался с такими нагрузками как у тс и с удовольствием новую информацию съем.

invmPizzaPizzaЯ не вижу проблемы с тем, что это одна сессия.Вот и объясните как, в пределах одной сессии, получить ожидание на Sch-M при maxdop > 1 и не получить с maxdop = 1. Учитывая, что на уровне пакета выполнение инструкций всегда последовательное.

Ожидание в любом случае будет, но вопрос то не в ожидании, а в том, к чему оно приводит иногда.
Опять допустим: Может в пределах одной сессии запущено несколько экземпляров ХП? В этом случае будут ли выполняться одновременно и конкурировать за ресурсы? Если ХП выполняются одновременно и каждая в параллель, то возможно ли, что некоторое сочетание шагов выполнения этих ХП создаёт слишком много дедлоков на малое количество ресурсов и локмонитор тупо не справляется?
Если это все предположить возможным, то тупое уменьшение количества тасков даст серверу разобраться с сессией.
Это все конечно если допустить, что в коде ХП нет логики, которая дополнительно провоцирует массовые дедлоки.

Вообще когда идёт речь про локи/вейты, то я бы сразу смотрел в lock counters. Видимо все участники дискуссии ознакомлены с этими данными...

invmPizzaPizzaОжидание в любом случае будет, но вопрос то не в ожидании, а в том, к чему оно приводит иногда.
Опять допустим: Может в пределах одной сессии запущено несколько экземпляров ХП? В этом случае будут ли выполняться одновременно и конкурировать за ресурсы?Вам было бы неплохо, прежде чем выдвигать различные теории, изучить матчасть.

Согласен. Для этого форум очень хорошо подходит.
Понимаю, что допущение невозможное об одновременности. Но оно не исключающее. Вариант с конкурирующими сессиями за общие ресурсы остаётся. Вейт на блокировку локальной временной таблицы это просто операция, которую видно последней и на которой одна из сессий встаёт. Что происходит перед этим неизвестно. Какие операции выполнялись, какие ресурсы запрашивались блокировки накладывались... Вообще какую вам этот вейт даёт диагностическую информацию, кроме того, что он там есть помимо возможных сотен других операций?
А вот unresolved deadlock имхо более понятен в плане куда смотреть.

felix_ff
простите господи - это ересь.

лок монитор нормально со всем справляется. графа у ТС нет потому что две транзакции в рамках одной сессии, он просто не сможет выбрать жертву для abort-операции, потому что жертвой в данном случае будет выступать та же самая сессия.


А что, локмонитор откатывает сессию, а не транзакцию? Где об этом прочитать можно? Это документированное поведение, что когда "он не сможет выбрать жертву" он кидает в лог unresolved deadlock?