查找并删除 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 查询，直到找到带有 blocked=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

还可以从加锁进程的sql_handle中获取查询代码。例如

select * from sys.dm_exec_sql_text (0x0100050069139B0650B35EA64702000000000000)

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

• 右键单击服务器，打开活动监视器并展开进程。您将看到等待资源释放且状态为“暂停”的请求列表。

  

• 选择一个数据库并导航至报告 -> 所有阻止事务。它还显示锁定查询的列表和锁定源的 SPID。