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

Откуда:
Сообщений: 49
Доброго времени суток!

Появилась большая проблема. Появилось огромное количество блокировок с типом LCK_M_X. Причем среднее время выполнения скачет от 30 сек. до нескольких минут, что очень сказывается на работе пользователей. И таких ситуаций уже становится достаточно много. Если раньше это было один-два раза в 3-4 дня, то на сегодня это уже ежедневная проблема. Оптимизировал несколько тяжелых по чтению запросов, добавил индексы по рекомендациям и скриптам Брентозара (разумеется не основываясь логикой, чем больше индексов тем лучше). Что примечательно, в таблице которая фиксирует такие блокировки, везде присутствуют 2-3 одинаковых запроса в столбце blocked, а именно одни и те же UPDATE. Проанализировал эти UPDATE и они не представляют особой опасности. Логического чтения 12-15, стоимость в районе 0,0000123 (условно, но примерно так и есть), поиск по индексу. Пробовал так же написать SELECT с условиями UPDATE, что бы он из буфера читал, но там в принципе нечему читать не то что с диска, даже с буфера

На что еще можно обратить внимание и что в данном случае по Вашему опыту, можно сделать. В инете все очень поверхностно и общими фразами.

Заранее благодарю!
29 апр 21, 11:47    [22316216]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
Владислав Колосов
Member

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

может кто-то запретил укрупнение блокировок на таблицу ALTER TABLE SET LOCK_ESCALATION = DISABLE.
29 апр 21, 12:42    [22316265]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
georgy_2012
Member

Откуда:
Сообщений: 49
Владислав Колосов,

Про это даже как-то не подумал, если я не ошибаюсь, то укрупнение дает возможность объединения всех блокировок в одну и блокирует же всю таблицу? Точнее на уровне таблицы

Сообщение было отредактировано: 29 апр 21, 12:44
29 апр 21, 12:52    [22316278]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
Владислав Колосов
Member

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

да, более 5000 блокировок укрупняются до таблицы.
29 апр 21, 13:05    [22316299]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
georgy_2012
Member

Откуда:
Сообщений: 49
Владислав Колосов,

А как это можно проверить?
29 апр 21, 13:17    [22316305]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
Владислав Колосов
Member

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

select tt.lock_escalation, schema_name(tt.schema_id) sname, tt.name tname from sys.tables tt
29 апр 21, 13:37    [22316327]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
invm
Member

Откуда: Москва
Сообщений: 9723
Владислав Колосов,

И как часто вы наблюдали укрупнение X при конкурентом доступе к таблице?
Как данное укрупнение позволит уменьшить общее время удержания X в системе?
29 апр 21, 13:43    [22316332]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
georgy_2012
Member

Откуда:
Сообщений: 49
Владислав Колосов,

Спасибо за скрпит, LOCK_ESCALATION = 0. То есть не отключено
29 апр 21, 13:44    [22316336]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
Владислав Колосов
Member

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

да, логично.
29 апр 21, 14:14    [22316361]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
georgy_2012
Member

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

Есть может какие еще варианты, куда нужно посмотреть?
29 апр 21, 14:14    [22316362]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
Владислав Колосов
Member

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

а что это за таблица, которая постоянно и параллельно обновляется?
29 апр 21, 14:24    [22316372]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
georgy_2012
Member

Откуда:
Сообщений: 49
Владислав Колосов,

Да, это динамическая таблица, которая постоянно изменяется/добавляются новые данные. SELECT из нее так же происходит, чуть ли не каждые 3 секунды.
29 апр 21, 14:27    [22316373]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
invm
Member

Откуда: Москва
Сообщений: 9723
georgy_2012,

Если действительно увеличилось время удержания X, то надо искать причины увеличения длительности транзакций, в рамках которых эти X были наложены.

Они могут быть разные.
Например, наличие в транзакции долгоиграющего запроса, который раньше не был долгоиграющим. Или долгие ожидания какого-либо ресурса.
29 апр 21, 14:32    [22316380]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
georgy_2012
Member

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

В части ожидания ресурса, вроде должны появляться запросы SOS_SHIELD (поправьте меня если я ошибаюсь), но таких я не встречал и по процессорным ресурсам, железка вполне себе тянет. Опять же я могу ошибаться в вышесказанном, но пока не встречал с таким проблем.

А вот по части долгоиграющего запроса, безусловно лезут запросы, которые раньше были мелкими и почти невесомыми по всем пунктам и как шарики начинают "надуваться", но за этим тоже слежу.
29 апр 21, 14:52    [22316402]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
georgy_2012
Member

Откуда:
Сообщений: 49
Может есть варианты, что можно сделать или куда посмотреть?
30 апр 21, 08:03    [22316655]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
-comm-
Member

Откуда:
Сообщений: 4
Причину блокировки установить удалось?
30 апр 21, 10:25    [22316716]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
invm
Member

Откуда: Москва
Сообщений: 9723
georgy_2012,

Аанализируйте содержимое sys.dm_exec_requests для сессий, удерживающих X

Если состояние сессии running/runnable, значит выполняется/стоит в очереди на выполнение долгоиграющий запрос.
Посмотреть что именно выполняется и на какой стадии можно с помощью sys.dm_exec_query_statistics_xml

Если состояние сессии suspended, значит имеет место ожидание какого-то ресурса. last_wait_type, wait_resource и blocking_session_id покажут какого именно и кто блокирует.
30 апр 21, 10:29    [22316723]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
georgy_2012
Member

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

Большое спасибо! Буду анализировать
30 апр 21, 10:39    [22316728]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
invm
Member

Откуда: Москва
Сообщений: 9723
georgy_2012,

Есть еще вариант - по каким-либо причинам перестали завершаться некоторые транзакции.
30 апр 21, 10:51    [22316736]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
georgy_2012
Member

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

А как это можно отследить/определить?
30 апр 21, 10:57    [22316738]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
invm
Member

Откуда: Москва
Сообщений: 9723
georgy_2012
А как это можно отследить/определить?
Сессия удерживает X и находится в состоянии sleeping/awaiting command
30 апр 21, 11:17    [22316765]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
georgy_2012
Member

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

Спасибо большое!
30 апр 21, 11:18    [22316767]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
-comm-
Member

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

В итоге нашлось?
30 апр 21, 23:19    [22317107]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8577
georgy_2012
Владислав Колосов,

Да, это динамическая таблица, которая постоянно изменяется/добавляются новые данные. SELECT из нее так же происходит, чуть ли не каждые 3 секунды.


Подумайте, как можно разделить процессы, чтобы эта таблица не была узким горлом. Например, выделить для каждого процесса отдельную таблицу или выполнять обновление фоновыми задачами при помощи очереди servicevroker. Не знаю условий использования сложно предложить что-то подходящее.
1 май 21, 14:22    [22317216]     Ответить | Цитировать Сообщить модератору
 Re: Долгие блокировки LCK_M_X  [new]
georgy_2012
Member

Откуда:
Сообщений: 49
-comm-,

Пока еще в процессе, так как увы есть куча других параллельных задач, но кое какая определенность появилась, а именно выползло еще несколько тяжелых по чтению запросов, тяжелых по записи таблиц, полагаю, что стоит теперь копаться в индексах (помимо основной оптимизации запросов).
4 май 21, 12:09    [22318192]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить