如何找出是什么锁定了我的表? [英] How to find out what is locking my tables?

问题描述

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

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.

我已经对 sys.dm_tran_locks 来识别表上实际上有许多锁，但是我如何识别什么 正在锁定它们(即 sys.dm_tran_locks)?

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

我知道 sp_lock 用于 SQL 2005 之前的版本，但现在该 sp 已被弃用，AFAIK 正确的方法是使用 sys.dm_tran_locks.我正在使用 SQL Server 2008 R2.

I know about sp_lock for 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.

推荐答案

看看下面的系统存储过程，你可以在SQLServer Management Studio (SSMS)中运行:

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

• sp_who
• sp_lock

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

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

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

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.

这篇关于如何找出是什么锁定了我的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！