如何找出是什么锁定了我的表?
我有一个 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.
相关文章