查找並刪除 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

已阻止列顯示阻止資源的進程的進程 ID。它還顯示此查詢的等待時間（等待時間以毫秒為單位）。如果需要，這可以用於搜索最早或最新的鎖。

在某些情況下，鎖可能是由整個進程樹引起的。要查找源鎖進程，請對 SPID 使用以下查詢，直到找到阻塞=0 的進程，這是持有鎖的初始進程。

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

進程 SPID 為您提供該進程或事務執行的最後一個 SQL 查詢的 T-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

參見：如何在 Windows Server 2012 R2 上安裝 SQL Server 2016 CTP 3.0

還可以從加鎖進程的sql_handle中獲取查詢代碼。例如

select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)

您可以使用 Microsoft SQL Server Management Studio 在 MS SQL Server 中搜索鎖。您可以使用以下方法之一：