Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 Ожидание блокировки локальной временной таблицы  [new]
Олегон
Member

Откуда: Казахстан, Алматы
Сообщений: 80
Добрый день!

Microsoft SQL Server 2014 (SP2-CU10-GDR) (KB4052725) - 12.0.5571.0 (X64) Jan 10 2018 15:52:08 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Второй раз уже наблюдаю ситуацию. Зависает сессия с ожиданием LCK_M_SCH_M. В сессии работает хранимая процедура, в которой неоднократно выполняется запуск другой хранимой процедуры, внутри которой создаются и используются локальные временные таблицы (if object_id ... is not null drop table ... create table ...).
syslockinfo показывает (см. вложение) ожидание на одну из этих локальных временных таблиц. Не могу понять, что происходит.
Что означает, что есть блокировка (granted) на эту таблицу с transaction_id = 5448341393, а с другим transaction_id = 5448332983 блокировка Waiting?
В какую сторону смотреть, чтоб разобраться?

Предыдущий раз разрешился только перезапуском службы. KILL сессии никаких результатов не дал - так и весел несколько часов с 0% выполнения, хотя по сессии не было какой-то огромной транзакции, которая кучу бы чего изменила.

К сообщению приложен файл (syslockinfo.txt - 6Kb) cкачать
1 окт 18, 06:39    [21690695]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
Олегон,

гугл говорит, что LCK_M_SCH_M ожидание на блокировку изменения схемы. Все блокировки это вопрос уровня изоляции. Начните с выяснения какой уровень изоляции у вас установлен для выполнения вашей исходной ХП.
1 окт 18, 07:33    [21690705]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Олегон
Member

Откуда: Казахстан, Алматы
Сообщений: 80
Дополню. В логах SQL-сервера в обоих случаях:

* Unresolved deadlock
Deadlock monitor failed to resolve one or more deadlocks.
Server may require restart to recover from this condition.
1 окт 18, 07:33    [21690706]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
Олегон
Дополню. В логах SQL-сервера в обоих случаях:

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


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

Учитывая
Олегон
хранимая процедура, в которой неоднократно выполняется запуск другой хранимой процедуры, внутри которой создаются и используются локальные временные таблицы

тут надо смотреть на алгоритм ваших ХП и фиксить логику, которая приводит к таким дедлокам.
Это боевой сервер? Вы можете обратиться к разработчику вашей базы за помощью?
1 окт 18, 07:42    [21690710]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Олегон,

Ну на tempdb достаточно старая проблема, сколько файлов у базы?
Тут больше про последнее, но уктально в общем для всех
https://support.microsoft.com/az-latn-az/help/4131193/performance-issues-occur-in-form-of-pagelatch-ex-and-pagelatch-sh-wait
https://blogs.msdn.microsoft.com/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my/
1 окт 18, 08:50    [21690729]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
PizzaPizza
Олегон,

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

да что вы говорите.
и на каком же уровне изоляции SCH-M кого-то не остановит?
1 окт 18, 09:11    [21690742]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Олегон
Что означает, что есть блокировка (granted) на эту таблицу с transaction_id = 5448341393, а с другим transaction_id = 5448332983 блокировка Waiting?

5448341393 читает, 5448332983 пытается изменить саму таблицу, например, колонку добавляет.
разумеется, ждет, пока первая транзакция отпустит таблицу, нельзя же ее модифицировать,
пока есть читатели
1 окт 18, 09:17    [21690745]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Yasha123
PizzaPizza
Олегон,

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

да что вы говорите.
и на каком же уровне изоляции SCH-M кого-то не остановит?

гугл ему это не сказал :)
1 окт 18, 09:21    [21690749]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
Yasha123
PizzaPizza
Олегон,

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

да что вы говорите.
и на каком же уровне изоляции SCH-M кого-то не остановит?


Вопрос не в остановит-неостановит, а в понимании спрашивающим с чем он столкнулся. Если человек видит блокировку и, допустим, не знаком с изоляциями, то совет тут только один - обратиться к разработчику.
Не будете же вы, в самом деле, советовать на боевом и неизвестном вам сервере что то делать с неизвестной вам логикой.
1 окт 18, 09:58    [21690782]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Олегон
Member

Откуда: Казахстан, Алматы
Сообщений: 80
TaPaK
Ну на tempdb достаточно старая проблема, сколько файлов у базы?

Файлов 16. Ядер CPU: 32
Планируем увеличить, прибавив 4 файла, т.к. стали наблюдать PAGELATCH_UP при нагрузке.

Yasha123
5448341393 читает, 5448332983 пытается изменить саму таблицу, например, колонку добавляет.
разумеется, ждет, пока первая транзакция отпустит таблицу, нельзя же ее модифицировать,
пока есть читатели


Как это может происходить в одной и той же сессии?
1 окт 18, 09:59    [21690783]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Олегон,

автор
Планируем увеличить, прибавив 4 файла, т.к. стали наблюдать PAGELATCH_UP при нагрузке.

ну UP это не к IO относится. Так то общая рекомендация на CPU = количество файлов, но и если не ошибаюсь то были и о том что много тоже не очень хорошо. Посмотрите что в ожиданиях, если там что то типа sysobjvalues то тут точно файлами не решить
1 окт 18, 10:06    [21690786]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Олегон
Как это может происходить в одной и той же сессии?

я же не вижу ваш код.
попробуйте пошариться в system_health,
может, там есть инфо о дедлоке,
хотя бы будет текст конфликтующих процессов.
+
WITH SystemHealth
AS 
(
SELECT CAST(target_data as xml) AS TargetData
FROM sys.dm_xe_session_targets st
       JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
WHERE name = 'system_health'
AND st.target_name = 'ring_buffer'
)
 
SELECT XEventData.XEvent.query('(data/value/deadlock)[1]') AS DeadLockGraph
FROM SystemHealth
       CROSS APPLY TargetData.nodes('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name','varchar(4000)') = 'xml_deadlock_report';

с другой стороны, раз дедлок не резолвился,
может и графа никакого нет.
---
в логе можно было посмотреть, что за транзакции
(sys.fn_dblog())
теперь уже наверное лог перезатеря,
а вот system_health может еще и нет.
ну или выставьте TF 1222, пусть графы в еррорлог валятся
1 окт 18, 10:26    [21690801]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
PizzaPizza
Yasha123
пропущено...

да что вы говорите.
и на каком же уровне изоляции SCH-M кого-то не остановит?


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

достали уже неучи лезть с ценными советами.
еще раз, дарагуля, ответь на конкретный воппрос:
какой же надо выставить уровень изоляции процедуре ТС,
чтобы получить SCH-M на таблицу при имеющихся читателях таблицы?
1 окт 18, 10:30    [21690802]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Олегон
Member

Откуда: Казахстан, Алматы
Сообщений: 80
Yasha123
с другой стороны, раз дедлок не резолвился,
может и графа никакого нет.


Графа нет - вместо него сообщение об ошибке. Флаг включён.
Процесс всё ещё висит (я так понимаю, в заблокированном глючном состоянии, раз его не смог сервер прибить).
sys.fn_dblog() пока не даёт посмотреть - отваливается по тайм-ауту.
1 окт 18, 10:43    [21690812]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Олегон,

процесс ждёт чего?
1 окт 18, 10:45    [21690814]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Олегон
Member

Откуда: Казахстан, Алматы
Сообщений: 80
Я в самом начале написал. Внутри одной и той же сессии взаимоблокировка. Резолв Deadlock'а не среботал - вывалился в ошибку (запись в журнале).
Теперь сессия вот в таком состоянии (см. скриншот).

К сообщению приложен файл. Размер - 11Kb
1 окт 18, 10:52    [21690821]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
Yasha123,

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

FYI "Unresolved deadlock" имеет не так много упоминаний и они все легко гуглятся. Пробуйте своими мощными экстрасенсорными способностями помочь ТС с такого уровня проблемами... Дорогуша.
1 окт 18, 10:57    [21690827]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
PizzaPizza
Yasha123,

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

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

я так понимаю, это вещает клон Andy или его ближайший соратник по партии?
какого же тогда хрена doppia Pizza спрашивала
"Начните с выяснения какой уровень изоляции у вас установлен для выполнения вашей исходной ХП."
ведь это и есть это самое:
"Правда это выглядит странно - задавать бессмысленный вопрос нерелевантный контексту?"
не форум, натуральное болото
1 окт 18, 11:20    [21690860]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Олегон,

в общем я по прежнему склонен думать что это таже проблема что и описанная в ссылкае, в вашем случае как раз слудющий CU её и правит
https://support.microsoft.com/en-us/help/4090320/significantly-increased-pagelatch-ex-contentions-in-sys-sysobjvalues.

А так, судя по всему рестарт как все спать уйдут
1 окт 18, 11:22    [21690866]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
PizzaPizza
Yasha123,

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

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

Козловсике вернулись?
1 окт 18, 11:22    [21690867]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
Олегон,

пробуйте sys.fn_dblog() с nolock,
и не весь, с фильтром по ид транзакции.

в одной сессии наверное может быть,
если вторая транзакция системная.

не могу придумать, зачем может понадобиться sch-m.
наверное, чтобы дроп сделать.
вы зачем внутри процедуры дропаете то, что само должно умереть?
1 окт 18, 11:30    [21690884]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Олегон
Member

Откуда: Казахстан, Алматы
Сообщений: 80
TaPaK
Олегон,

в общем я по прежнему склонен думать что это таже проблема что и описанная в ссылкае, в вашем случае как раз слудющий CU её и правит
https://support.microsoft.com/en-us/help/4090320/significantly-increased-pagelatch-ex-contentions-in-sys-sysobjvalues.

А так, судя по всему рестарт как все спать уйдут


Ясно. Спасибо за мысль. В планах на этот месяц была установка CU13.
Рестартанём, конечно. В предыдущий случай так и сделали. Не хочется таким образом решать, т.к. не понятна причина появления такой ситуации, т.к. изменений в конфигурации или в коде не было.
1 окт 18, 11:31    [21690886]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Yasha123
Member

Откуда:
Сообщений: 1833
+
TaPaK
Козловсике вернулись?

бесит то, что в кои-то веки что-то интересное нарисовалось,
и то в топике сразу же срет чей-то клон.
чего потом удивляться, что сюда людям заглядывать неохота
1 окт 18, 11:32    [21690890]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
Олегон
Member

Откуда: Казахстан, Алматы
Сообщений: 80
Yasha123
Олегон,
не могу придумать, зачем может понадобиться sch-m.
наверное, чтобы дроп сделать.
вы зачем внутри процедуры дропаете то, что само должно умереть?


Привычка, наверное... При разработке, отладки удобно.
1 окт 18, 11:42    [21690913]     Ответить | Цитировать Сообщить модератору
 Re: Ожидание блокировки локальной временной таблицы  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Олегон
Yasha123
Олегон,
не могу придумать, зачем может понадобиться sch-m.
наверное, чтобы дроп сделать.
вы зачем внутри процедуры дропаете то, что само должно умереть?


Привычка, наверное... При разработке, отладки удобно.

ну для понимания, ремоендация ms по этому

автор
Do not explicitly drop temp tables at the end of a stored procedure, they will get cleaned up when the session that created them ends.
Do not alter temp tables after they have been created.
Do not truncate temp tables
Move index creation statements on temp tables to the new inline index creation syntax that was introduced in SQL Server 2014.
1 окт 18, 11:46    [21690925]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить