如何找出锁定我的桌子的东西? [英] 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 )?

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)?

我了解 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屋！