SQL 如何找出锁定我的表的原因？

How to find out what is locking my tables?

提问by just.another.programmer

I have a SQL table that all of a sudden cannot return data unless I include with (nolock)on the end, which indicates some kind of lock left on my table.

我有一个 SQL 表，它突然无法返回数据，除非我最后包含with (nolock)它，这表明我的表上留下了某种锁。

I've experimented a bit with sys.dm_tran_locksto identify that there are in fact a number of locks on the table, but how do I identify whatis locking them (ie the request element of the sys.dm_tran_locks)?

我已经尝试了一下与sys.dm_tran_locks以确定有其实一些在桌子上的锁，但如何辨别什么是锁定它们（即请求元素sys.dm_tran_locks）？

EDIT: I know about sp_lockfor pre SQL 2005, but now that that sp is deprecated, AFAIK the right way to do this is with sys.dm_tran_locks. I'm using SQL Server 2008 R2.

编辑：我知道SQL 2005 之前的sp_lock，但现在sp_lock已被弃用，AFAIK 正确的方法是使用sys.dm_tran_locks。我正在使用 SQL Server 2008 R2。

回答by Nicholas Carey

Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS):

查看以下系统存储过程，您可以在 SQLServer Management Studio (SSMS) 中运行这些过程：

• sp_who
• sp_lock

• sp_who
• sp_lock

Also, in SSMS, you can view locks and processes in different ways:

此外，在 SSMS 中，您可以通过不同方式查看锁和进程：

Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu when you right-click on a server node.

不同版本的 SSMS 将活动监视器放在不同的位置。例如，当您右键单击服务器节点时，SSMS 2008 和 2012 会将其显示在上下文菜单中。

回答by Chris F Carroll

For getting straight to "who is blocked/blocking" I combined/abbreviated sp_who and sp_lock into a single query which gives a nice overview of who has what object locked to what level.

为了直接了解“谁被阻止/阻止”，我将 sp_who 和 sp_lock 组合/缩写为一个查询，这很好地概述了谁将什么对象锁定到什么级别。

--Create Procedure WhoLock
--AS
set nocount on
if object_id('tempdb..#locksummary') is not null Drop table #locksummary
if object_id('tempdb..#lock') is not null Drop table #lock
create table #lock (    spid int,    dbid int,    objId int,    indId int,    Type char(4),    resource nchar(32),    Mode char(8),    status char(6))
Insert into #lock exec sp_lock
if object_id('tempdb..#who') is not null Drop table #who
create table #who (     spid int, ecid int, status char(30),
            loginame char(128), hostname char(128),
            blk char(5), dbname char(128), cmd char(16)
            --
            , request_id INT --Needed for SQL 2008 onwards
            --
         )
Insert into #who exec sp_who
Print '-----------------------------------------'
Print 'Lock Summary for ' + @@servername  + ' (excluding tempdb):'
Print '-----------------------------------------' + Char(10)
Select     left(loginame, 28) as loginame, 
    left(db_name(dbid),128) as DB,
    left(object_name(objID),30) as object,
    max(mode) as [ToLevel],
    Count(*) as [How Many],
    Max(Case When mode= 'X' Then cmd Else null End) as [Xclusive lock for command],
    l.spid, hostname
into #LockSummary
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and l.status='GRANT'
group by dbID, objID, l.spid, hostname, loginame

Select * from #LockSummary order by [ToLevel] Desc, [How Many] Desc, loginame, DB, object

Print '--------'
Print 'Who is blocking:'
Print '--------' + char(10)
SELECT p.spid
,convert(char(12), d.name) db_name
, program_name
, p.loginame
, convert(char(12), hostname) hostname
, cmd
, p.status
, p.blocked
, login_time
, last_batch
, p.spid
FROM      master..sysprocesses p
JOIN      master..sysdatabases d ON p.dbid =  d.dbid
WHERE     EXISTS (  SELECT 1
          FROM      master..sysprocesses p2
          WHERE     p2.blocked = p.spid )

Print '--------'
Print 'Details:'
Print '--------' + char(10)
Select     left(loginame, 30) as loginame,  l.spid,
    left(db_name(dbid),15) as DB,
    left(object_name(objID),40) as object,
    mode ,
    blk,
    l.status
from #lock l join #who w on l.spid= w.spid
where dbID != db_id('tempdb') and blk <>0
Order by mode desc, blk, loginame, dbID, objID, l.status

(For what the lock level abbreviations mean, see e.g. https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx)

（有关锁定级别缩写的含义，请参见例如https://technet.microsoft.com/en-us/library/ms175519%28v=sql.105%29.aspx）

Copied from: sp_WhoLock – a T-SQL stored proc combining sp_who and sp_lock...

复制自：sp_WhoLock——一个结合了 sp_who 和 sp_lock 的 T-SQL 存储过程...

NB the [Xclusive lock for command] column can be misleading -- it shows the current command for that spid; but the X lock could have been triggered by an earlier command in the transaction.

注意 [Xclusive lock for command] 列可能会产生误导——它显示了该 spid 的当前命令；但是 X 锁可能是由事务中较早的命令触发的。

回答by Marcello Miorelli

I have a stored procedure that I have put together, that deals not only with locks and blocking, but also to see what is running in a server. I have put it in master. I will share it with you, the code is below:

我有一个存储过程，我把它放在一起，它不仅处理锁和阻塞，而且还查看服务器中运行的内容。我已经把它放在主人那里了。分享给大家，代码如下：

USE [master]
go


CREATE PROCEDURE [dbo].[sp_radhe] 

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


-- the current_processes
-- marcelo miorelli 
-- CCHQ 
-- 04 MAR 2013 Wednesday

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, '') AS login_name
,COALESCE(es.host_name,'') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
CASE es.transaction_isolation_level
    WHEN 0 THEN 'Unspecified'
    WHEN 1 THEN 'Read Uncommitted'
    WHEN 2 THEN 'Read Committed'
    WHEN 3 THEN 'Repeatable'
    WHEN 4 THEN 'Serializable'
    WHEN 5 THEN 'Snapshot'
END
,COALESCE(es.cpu_time,0) 
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0) 
    + COALESCE(es.writes,0) 
    + COALESCE(er.reads,0) 
+ COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
where es.is_user_process = 1 
  and es.session_id <> @@spid
  and es.status = 'running'
ORDER BY es.session_id

end 

GO

this procedure has done very good for me in the last couple of years. to run it just type sp_radhe

在过去的几年里，这个程序对我来说非常好。运行它只需输入 sp_radhe

Regarding putting sp_radhe in the master database

关于将sp_radhe放入master数据库

I use the following code and make it a system stored procedure

我使用以下代码并使其成为系统存储过程

exec sys.sp_MS_marksystemobject 'sp_radhe'

as you can see on the link below

正如您在下面的链接中看到的

Creating Your Own SQL Server System Stored Procedures

创建您自己的 SQL Server 系统存储过程

Regarding the transaction isolation level

关于事务隔离级别

Questions About T-SQL Transaction Isolation Levels You Were Too Shy to Ask

关于 T-SQL 事务隔离级别的问题你不敢问

Jonathan Kehayias

乔纳森·凯哈亚斯

Once you change the transaction isolation level it only changes when the scope exits at the end of the procedure or a return call, or if you change it explicitly again using SET TRANSACTION ISOLATION LEVEL.

In addition the TRANSACTION ISOLATION LEVEL is only scoped to the stored procedure, so you can have multiple nested stored procedures that execute at their own specific isolation levels.

一旦您更改了事务隔离级别，它只会在范围在过程结束或返回调用时退出时才会更改，或者如果您使用 SET TRANSACTION ISOLATION LEVEL 再次显式更改它。

此外，TRANSACTION ISOLATION LEVEL 仅限于存储过程，因此您可以有多个嵌套的存储过程，它们在它们自己的特定隔离级别上执行。

回答by Li0liQ

exec sp_lock

This query should give you existing locks.

这个查询应该给你现有的锁。

exec sp_who SPID -- will give you some info

Having spids, you could check activity monitor(processes tab) to find out what processes are locking the tables ("details" for more info and "kill process" to kill it).

有了 spid，您可以检查活动监视器（进程选项卡）以找出哪些进程正在锁定表（“详细信息”了解更多信息，“杀死进程”以杀死它）。

回答by radar

You can also use sp_who2which gives more information

您还可以使用sp_who2which 提供更多信息

Here is some info http://dbadiaries.com/using-sp_who2-to-help-with-sql-server-troubleshooting

这是一些信息http://dbadiaries.com/using-sp_who2-to-help-with-sql-server-troubleshooting

回答by Weihui Guo

This should give you all the details of the existing locks.

这应该为您提供现有锁的所有详细信息。

DECLARE @tblVariable TABLE(SPID INT, Status VARCHAR(200), [Login] VARCHAR(200), HostName VARCHAR(200), 
    BlkBy VARCHAR(200), DBName VARCHAR(200), Command VARCHAR(200), CPUTime INT, 
    DiskIO INT, LastBatch VARCHAR(200), ProgramName VARCHAR(200), _SPID INT, 
    RequestID INT)

INSERT INTO @tblVariable
EXEC Master.dbo.sp_who2

SELECT v.*, t.TEXT 
FROM @tblVariable v
INNER JOIN sys.sysprocesses sp ON sp.spid = v.SPID
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS t
ORDER BY BlkBy DESC, CPUTime DESC

You can then kill, with caution, the SPID that blocks your table.

然后，您可以谨慎地终止阻止您的表的 SPID。

kill 104 -- Your SPID

回答by Marcelo Farinelli

Plot twist!

剧情转折！

You can have orphaned distributed transactions holding exclusive locks and you will not see them if your script assumes there is a session associated with the transaction (there isn't!). Run the script below to identify these transactions:

您可以拥有持有独占锁的孤立分布式事务，如果您的脚本假定存在与事务关联的会话（没有！），您将看不到它们。运行下面的脚本来识别这些交易：

;WITH ORPHANED_TRAN AS (
SELECT
    dat.name,
    dat.transaction_uow,
    ddt.database_transaction_begin_time,
    ddt.database_transaction_log_bytes_reserved,
    ddt.database_transaction_log_bytes_used
FROM
    sys.dm_tran_database_transactions ddt,
    sys.dm_tran_active_transactions dat,
    sys.dm_tran_locks dtl
WHERE
    ddt.transaction_id = dat.transaction_id AND
    dat.transaction_id = dtl.request_owner_id AND
    dtl.request_session_id = -2 AND
    dtl.request_mode = 'X'
)
SELECT DISTINCT * FROM ORPHANED_TRAN

Once you have identified the transaction, use the transaction_uow column to find it in MSDTC and decide whether to abort or commit it. If the transaction is marked as In Doubt (with a question mark next to it) you will probably want to abort it.

确定事务后，使用 transaction_uow 列在 MSDTC 中查找它并决定是中止还是提交它。如果交易被标记为不确定（旁边有一个问号），您可能想要中止它。

You can also kill the Unit Of Work (UOW) by specifying the transaction_uow in the KILL command:

您还可以通过在 KILL 命令中指定 transaction_uow 来终止工作单元 (UOW)：

KILL '<transaction_uow>'

References:

参考：

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2017#arguments

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2017#arguments

https://www.mssqltips.com/sqlservertip/4142/how-to-kill-a-blocking-negative-spid-in-sql-server/

https://www.mssqltips.com/sqlservertip/4142/how-to-kill-a-blocking-negative-spid-in-sql-server/

回答by Luc Bos

A colleague and I have created a tool just for this. It's a visual representation of all the locks that your sessions produce. Give it a try (http://www.sqllockfinder.com), it's open source (https://github.com/LucBos/SqlLockFinder)

我和一位同事为此专门创建了一个工具。它是会话产生的所有锁的可视化表示。试一试（http://www.sqllockfinder.com），它是开源的（https://github.com/LucBos/SqlLockFinder）

回答by gotqn

As per the official docs the sp_lockis mark as deprecated:

根据官方文档，sp_lock被标记为已弃用：

This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

此功能处于维护模式，可能会在 Microsoft SQL Server 的未来版本中删除。避免在新的开发工作中使用此功能，并计划修改当前使用此功能的应用程序。

and it is recommended to use sys.dm_tran_locksinstead. This dynamic management object returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

建议改用sys.dm_tran_locks。此动态管理对象返回有关当前活动的锁管理器资源的信息。每一行代表对锁管理器的当前活动请求，请求已授予或正在等待授予的锁。

It generally returns more details in more user friendly syntax then sp_lockdoes.

它通常会以更用户友好的语法返回更多详细信息sp_lock。

The whoisactiveroutine written by Adam Machanicis very good to check the current activity in your environment and see what types of waits/locks are slowing your queries. You can very easily find what is blocking your queries and tons of other handy information.

Adam Machanic编写的whoisactive例程非常适合检查您环境中的当前活动，并查看哪些类型的等待/锁定会减慢您的查询速度。您可以很容易地找到阻止您查询的内容和大量其他方便的信息。

---

---

For example, let's say we have the following queries running in the default SQL Server Isolation Level - Read Committed. Each query is executing in separate query window:

例如，假设我们在默认 SQL Server 隔离级别 - 已提交读中运行以下查询。每个查询都在单独的查询窗口中执行：

-- creating sample data
CREATE TABLE [dbo].[DataSource]
(
    [RowID] INT PRIMARY KEY
   ,[RowValue] VARCHAR(12)
);

INSERT INTO [dbo].[DataSource]([RowID], [RowValue])
VALUES (1,  'samle data');

-- query window 1
BEGIN TRANSACTION;

    UPDATE [dbo].[DataSource]
    SET [RowValue] = 'new data'
    WHERE [RowID] = 1;

--COMMIT TRANSACTION;

-- query window 2
SELECT *
FROM [dbo].[DataSource];

Then execute the sp_whoisactive(only part of the columns are displayed):

然后执行sp_whoisactive（只显示部分列）：

You can easily seen the session which is blocking the SELECTstatement and even its T-SQL code. The routine has a lot of parameters, so you can check the docsfor more details.

你可以很容易地看到阻塞SELECT语句的会话，甚至是它的 T-SQL 代码。该例程有很多参数，因此您可以查看文档以获取更多详细信息。

If we query the sys.dm_tran_locksview we can see that one of the session is waiting for a share lock of a resource, that has exclusive lock by other session:

如果我们查询sys.dm_tran_locks视图，我们可以看到其中一个会话正在等待资源的共享锁，该资源具有其他会话的排他锁：