Поиск блокировок в MS 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-ом.

завис запрос 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 в милисекундах). Можно использовать это поле для поиска наиболее старых блокировок.

вывести список заблокированных процессов в Microsoft SQL Server

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

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

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

DBCC INPUTBUFFER(59)

Вывести SQL код запроса, который заблокировал ресурсы

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

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

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

KILL 59

sql kill - принудительно завершить зависший процесс в sql server

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

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 server

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

select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)

текст SQL запроса по sql_handle

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

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

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