在SQL Server 2005中编写sp_lock系统存储过程

2026-04-30 13:04 栏目：常见问题 查看( )

做为系统存储过程，sp_lock可以用来了解服务器的运行情况，通过查看系统的锁定信息诊断SQL Server可能出现的问题。不过系统存储过程sp_lock本身存在一些缺陷。对于数据库管理新手来说，其返回的结果不够直白，花费了大量的工作来显示系统中哪个会话造成了最多锁定，却并没有提供多少关于这些对象或会话的相应详细信息。虽然我们可以创建自定义的脚本来查看这些信息，但是返回的结果往往过于复杂，而充其量能返回一些质量低下的信息。也有其他的一些系统表可以用来查看锁定信息，例如syslockinfo，但信息的细节同样不够明了。此外，sp_lock和syslockinfo还有一个更大的问题，那就是他们都是“不建议使用的特性”，所以将来的SQL Server版本中可能不再包含这些特性。SQL Server 2005提供的新的动态管理视图包含了大量锁定细节，并使我们能够将锁定信息关联起来，看起来可以更一目了然。

sys.dm_tran_locks

新的动态视图sys.dm_tran_locks能够返回系统中当前活动的锁管理器资源信息。这个视图返回的信息类型和sp_lock一样，但提供了更多细节。关键是这是一个视图，允许数据库管理员轻松的将其连接到其他表。

自定义sp_lock例子

USE MASTER

接下来，更新表中某事务的记录并使该事务保持开放状态。在更新过程中，该事务将持有对这些记录的锁定。然后添加WITH(HOLDLOCK)锁提示，确保该数据库的事务隔离水平不会影响到我们的测试。

BEGIN TRANSACTION UPDATE TOP(2000) LockMyData WITH(HOLDLOCK) SET 
NumberField = NumberField + IDCol

另起一个独立的查询会话，执行我们之前创建的sp_Lock_Detail存储过程。我们将在与之前发布的UPDATE语句相同的数据库环境下执行该存储过程。

EXECUTE sp_Lock_Detail

我们的返回结果超过了2000行，表明发布的UPDATE指令使用了行级锁(KEY)来发布该指令。

该存储过程生成了很多有用的数据。我们可以立刻就能看到谁发布了这些指令、哪些程序被用来发布这些指令、锁的类型等等信息。假如我们在原始UPDATE指令运行的时候运行这条指令，那么我们就能够看到引起锁定的确切指令了。

总结

系统存储过程是很有用的工具，使我们能够在一个数据库中编写存储过程，然后在其他数据库环境下运行该过程。本文的sp_Lock_Detail只是一个例子，告诉我们如何编写自定义的系统存储过程来监控自己的数据库。系统存储过程的用途还有很多，包括获取存储在数据库中所有表的大小分配，同时生成用来存储SQL语句以节省编写代码的时间等等。有兴趣的读者可以自己尝试，相信您一定会有所收获的。

IT头条