Блокировка в SQL Server является обычным явлением в параллельных системах. Она обеспечивает согласованность данных, предотвращая одновременную запись несколькими процессами в одни и те же данные. Однако чрезмерная блокировка может привести к замедлению системы или даже полной остановке. Чтобы предотвратить такие проблемы, важно отслеживать и оповещать о блокировках.

В этой статье мы рассмотрим основной метод обнаружения блокировок и настройки оповещений на основе заданного порога. Давайте начнем с понимания того, как идентифицировать заблокированные сеансы в SQL Server.

Динамическое представление управления sys.dm_exec_requests (DMV) является ценным ресурсом для мониторинга блокировок. Путем запроса этого представления мы можем идентифицировать заблокированные сеансы, ища ненулевое значение в столбце blocking_session_id. Кроме того, мы можем получить информацию, такую как тип ожидания и продолжительность блокировки.

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

Теперь давайте перейдем к примеру кода, который демонстрирует, как реализовать базовый механизм обнаружения блокировок и оповещения:

DECLARE @blockedsessions_1 INT = 0;
DECLARE @blockedsessions_2 INT = 0;
DECLARE @blockedsessions_3 INT = 0;
DECLARE @blockedsessions_4 INT = 0;

SELECT @blockedsessions_1 = COUNT(*)
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
WAITFOR DELAY '00:00:10'

SELECT @blockedsessions_2 = COUNT(*)
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
WAITFOR DELAY '00:00:10'

SELECT @blockedsessions_3 = COUNT(*)
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
WAITFOR DELAY '00:00:10'

SELECT @blockedsessions_4 = COUNT(*)
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0

IF(@blockedsessions_1 <> 0 AND @blockedsessions_2 <> 0 AND @blockedsessions_3 <> 0 AND @blockedsessions_4 <> 0)
BEGIN
    SELECT 'Обнаружена блокировка!'
    /*
    DECLARE @subject VARCHAR(200) = 'Оповещение о блокировке - ' + @@SERVERNAME
    DECLARE @email_body VARCHAR(200) = 'Обнаружена блокировка на ' + @@SERVERNAME + ' - немедленно проведите расследование.'
    EXEC msdb.dbo.sp_send_dbmail  
     @profile_name = 'DBA',  
     @recipients = 'team@company.com',
     @subject = @subject,
     @body = @email_body
    */
END

Этот фрагмент кода объявляет четыре целочисленные переменные и заполняет их через интервалы в десять секунд с результатами COUNT(*) из представления управления sys.dm_exec_requests. Если все четыре переменные имеют ненулевые значения, указывающие на обнаружение блокировки на каждом интервале, срабатывает оповещение. В этом примере оповещение представляет собой простое выражение SELECT, но вы можете настроить его для отправки электронной почты или выполнения любого другого желаемого действия.

Чтобы непрерывно отслеживать блокировки, вы можете запланировать этот скрипт как задание агента для запуска каждые 30 секунд.

Важно отметить, что предоставленный пример является очень простым механизмом оповещения. Он проверяет только блокировку на уровне экземпляра и не предоставляет подробной информации о заблокированных сеансах. Для более сложных систем вы можете рассмотреть расширение функциональности механизма оповещения. Некоторые возможные улучшения включают:

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

Эти улучшения могут быть реализованы в соответствии с конкретными требованиями вашей системы.

В заключение, мониторинг и оповещение о блокировках в SQL Server являются важными для поддержания производительности системы и согласованности данных. Путем использования представления управления sys.dm_exec_requests и реализации базового механизма обнаружения блокировок и оповещения вы можете оперативно выявлять и устранять проблемы с блокировками в вашей среде SQL Server.