Блокировки в Microsoft SQL Server обеспечивают целостность данных при одновременных изменениях, но могут вызывать зависания запросов. В этой статье расскажем, как найти блокировки, определить их источник и устранить с помощью SQL-запросов, PowerShell и SQL Server Management Studio (SSMS).
Имитация блокировки
Чтобы воспроизвести блокировку, выполните незавершённую транзакцию:
USE tesdb1
BEGIN TRANSACTION
DELETE TOP(1) FROM tblStudents
Таблица tblStudents будет заблокирована. Попробуйте выполнить:
SELECT * FROM tblStudents
Запрос зависнет в состоянии Executing query до таймаута из-за блокировки.
Поиск заблокированных процессов
1. Список всех заблокированных процессов:
SELECT cmd, * FROM sys.sysprocesses WHERE blocked > 0
2. Блокировки в конкретной базе (например, testdb12):
SELECT * FROM master.dbo.sysprocesses
WHERE dbid = DB_ID('testdb12') AND blocked <> 0
ORDER BY blocked
– Колонка blocked показывает SPID процесса, вызвавшего блокировку.
– Колонка waittime (в миллисекундах) указывает время ожидания.
3. Поиск источника блокировки:
Найдите процесс с blocked = 0, который является источником:
SELECT * FROM master.dbo.sysprocesses WHERE spid = 59
Повторяйте, пока не найдёте процесс с blocked = 0.
Анализ заблокированного запроса
1. Получите SQL-код запроса по SPID:
DBCC INPUTBUFFER(59)
2. Создайте процедуру для вывода кода запроса:
CREATE PROCEDURE PrintCurrentCode
@SPID int
AS
DECLARE @sql_handle binary(20), @stmt_start int, @stmt_end int
SELECT @sql_handle = sql_handle, @stmt_start = stmt_start/2, @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID AND ecid = 0
DECLARE @line nvarchar(4000)
SET @line = (SELECT SUBSTRING([text], COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end WHEN -1 THEN DATALENGTH([text]) ELSE (@stmt_end - @stmt_start) END) FROM ::fn_get_sql(@sql_handle))
PRINT @line
Выполните:
EXEC PrintCurrentCode 51
3. Получите текст запроса по sql_handle:
SELECT * FROM sys.dm_exec_sql_text(0x0100050069139B0650B35EA64702000000000000)
Завершение блокировки
Завершите процесс, вызывающий блокировку:
KILL 59
GO
Поиск блокировок через SSMS
1. Activity Monitor:
– В SSMS щёлкните правой кнопкой по серверу → Activity Monitor.
– В разделе Processes найдите процессы со статусом SUSPENDED.
2. Отчёт по блокировкам:
– Выберите базу → Reports → All Blocking Transactions.
– Просмотрите список заблокированных запросов и их SPID.
Оптимизация для частых блокировок
Для анализа ресурсоёмких запросов:
1. Используйте хранимую процедуру PrintCurrentCode для мониторинга.
2. Настройте уровень блокировки:
– На уровне строки: ROWLOCK.
– На уровне таблицы: TABLOCK.
Пример:
SELECT * FROM tblStudents WITH (ROWLOCK)
3. Проверяйте индексы и оптимизируйте запросы:
SELECT * FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
Устранение неполадок
Запросы зависают:
– Проверьте незавершённые транзакции (BEGIN TRANSACTION без COMMIT/ROLLBACK).
– Используйте KILL для завершения.
Частые блокировки:
– Оптимизируйте запросы, добавьте индексы.
– Используйте READ COMMITTED SNAPSHOT:
ALTER DATABASE tesdb1 SET READ_COMMITTED_SNAPSHOT ON
Ошибка таймаута:
– Увеличьте время ожидания:
SET LOCK_TIMEOUT 5000
Рекомендации
Мониторинг: Настройте оповещения через SQL Server Agent для длительных блокировок:
EXEC msdb.dbo.sp_add_alert @name=N'Long Blocking', @message_id=1222
Автоматизация: Создайте скрипт для периодической проверки:
SELECT * FROM sys.sysprocesses WHERE blocked > 0
IF @@ROWCOUNT > 0
BEGIN
INSERT INTO LogTable (LogTime, BlockedSPID) SELECT GETDATE(), spid FROM sys.sysprocesses WHERE blocked > 0
END
Резервное копирование: Регулярно создавайте резервные копии базы:
BACKUP DATABASE tesdb1 TO DISK = 'C:\Backup\tesdb1.bak'
Логи: Проверяйте логи SQL Server в SSMS (Management → SQL Server Logs).
Поиск и устранение блокировок в Microsoft SQL Server возможны через sys.sysprocesses, DBCC INPUTBUFFER, SSMS Activity Monitor и отчёты. Завершение процессов с помощью KILL и оптимизация запросов минимизируют проблемы. Настройка мониторинга и использование READ COMMITTED SNAPSHOT повышают производительность и стабильность базы данных.
