如何检查表上持有哪些锁 [英] How to check which locks are held on a table

问题描述

我们如何根据查询批处理检查对哪些行应用了哪些数据库锁?

How can we check which database locks are applied on which rows against a query batch?

实时突出显示表行级锁定的任何工具?

Any tool that highlights table row level locking in real time?

数据库:SQL Server 2005

DB: SQL Server 2005

推荐答案

要添加到其他响应中，sp_lock 还可用于转储所有正在运行的进程的完整锁信息.输出可能会让人不知所措，但如果您想确切知道锁定的内容，那么运行它是很有价值的.我通常将它与 sp_who2 一起使用以快速解决锁定问题.

To add to the other responses, sp_lock can also be used to dump full lock information on all running processes. The output can be overwhelming, but if you want to know exactly what is locked, it's a valuable one to run. I usually use it along with sp_who2 to quickly zero in on locking problems.

在线提供多个不同版本的更友好"sp_lock 过程，具体取决于相关 SQL Server 的版本.

There are multiple different versions of "friendlier" sp_lock procedures available online, depending on the version of SQL Server in question.

在您的情况下，对于 SQL Server 2005，sp_lock 仍然可用，但已弃用，因此现在建议使用 sys.dm_tran_locks 查看此类内容.您可以找到如何推出自己的"sp_lock 函数的示例 此处.

In your case, for SQL Server 2005, sp_lock is still available, but deprecated, so it's now recommended to use the sys.dm_tran_locks view for this kind of thing. You can find an example of how to "roll your own" sp_lock function here.

这篇关于如何检查表上持有哪些锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！