Categories: SQL Server

Поиск блокировок в MS SQL Server

Блокировки в SQL Server позволяют обеспечивать целостность данных при одновременном изменении несколькими пользователя. SQL Server блокирует объекты в таблице при начале транзакции и снимает блокировку при ее завершении. В этой статье мы научимся искать блокировки в базе данных MS SQL Server и удалять их.

Можно сымитировать блокировку одной из таблиц с помощью незакрытой транзакции (которая не завершена через rollback или commit). Например, выполните такой SQL запрос:

USE tesdb1 BEGIN TRANSACTION DELETE TOP(1) FROM tblStudents

SQL Server перед внесением изменений сначала заблокирует таблицу. Попробуйте открыть SQL Server Management Studio и выполнить простой SQL запрос на выборку:

SELECT * FROM tblStudents

Запрос зависнет в состоянии ( Executing query ) пока не отвалится по таймауту. Дело в том, что запрос SELECT пытается обратиться к данным в таблице, которая заблокирована SQL Server-ом.

В SQL Server можно настроить блокировку на уровне строки или на уровне всей таблицы.

Чтобы вывести список заблокированных запросов в MSSQL Server, выполните команду:

select cmd,* from sys.sysprocesses where blocked > 0

Либо вывести список блокировок для конкретной базы данных:

 SELECT * FROM master.dbo.sysprocesses 
 WHERE 
 dbid = DB_ID('testdb12') and blocked <> 0 
 order by blocked

В колонке Blocked указан идентификатор процесса PID процесса, который заблокировал ресурсы. Здесь же видно и время ожидания для данного запроса (waittime в милисекундах). Можно использовать это поле для поиска наиболее старых блокировок.

В некоторых случаях блокировка может быть вызвана целым деревом процессов. Чтобы найти процесс-первоисточник блокировки нужно использовать следующий запрос для по SPID до тех пор, пока не найдете процесс со значением blocked=0 (это и будет процесс источник блокировки).

select * FROM master.dbo.sysprocesses where 1=1 --and blocked <> 0 and spid = 59

По SPID процесса можно получить код последнего SQL запроса, выполнено в рамках данного процесса (транзакции):

DBCC INPUTBUFFER(59)

Для принудительного завершения процесса и снятия блокировки, выполните команду:

KILL номер_сессии GO

Например, в моем случае это:

KILL 59

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

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

Теперь для вывод кода SQL запроса, который заблокировал таблицу, нужно указать только его SPID:
Exec PrintCurrentCode 51

Также код запроса можно получить по sql_handle процесса блокировки. Например:

select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)

Для поиска блокировок в MS SQL Server можно использовать Microsoft SQL Server Management Studio. Вы можете использовать один из следующих методов:

Щелкните правой кнопкой по северу, запустите Activity Monitor и разверните Processes. Список запросов, ожидающих освобождения ресурсов указан со статусом SUSPENDED.
Выберите базу данных -> Reports -> All Blocking Transactions. Здесь также видно список заблокированных запросов и SPID источника блокировки.