Поиск по сайту:

Найти и удалить блокировки в Microsoft SQL Server

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

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

USE tesdb1
BEGIN TRANSACTION
DELETE TOP(1) FROM tblStudents

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

SELECT * FROM tblStudents

Запрос заморозится в состоянии «Выполнение запроса», пока не истечет время ожидания. Это связано с тем, что запрос SELECT пытается получить доступ к данным в таблице, заблокированной SQL Server.

В Microsoft 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

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

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

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

SPID процесса дает вам код T-SQL последнего SQL-запроса, выполненного этим процессом или транзакцией:

DBCC INPUTBUFFER(59)

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

KILL number_of_session
GO

В моем случае это будет:

KILL 59

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

CREATE PROCEDURE GetCurrentQueryCode
@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 GetCurrentQueryCode 51

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

select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)

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

  • Щелкните сервер правой кнопкой мыши, откройте Монитор активности и разверните Процессы. Вы увидите список запросов, ожидающих освобождения ресурсов, со статусом SUSPENDED.

  • Выберите базу данных и перейдите в раздел Отчеты -> Все блокирующие транзакции. Он также показывает список заблокированных запросов и SPID источника блокировки.

Статьи по данной тематике