SQL2005查看死锁存储过程sp_who_lock

当多个事务互相等待对方释放资源时，就会发生死锁。在SQL Server 2005中，可以使用存储过程sp_who_lock查看正在发生死锁的会话和相关信息。下面是使用sp_who_lock的完整攻略。

步骤一：创建存储过程sp_who_lock

在SQL Server Management Studio（SSMS）中，使用以下SQL语句创建存储过程sp_who_lock。

CREATE PROCEDURE sp_who_lock
AS
SELECT request_session_id AS spid, OBJECT_NAME(resource_associated_entity_id) AS resource, 
    request_mode AS mode, resource_description AS description
FROM sys.dm_tran_locks
WHERE resource_type <> 'DATABASE'

此存储过程是从系统视图dm_tran_locks中检索正在请求锁定和正在等待资源的会话的信息。

步骤二：运行存储过程sp_who_lock

在SSMS中，使用以下SQL语句运行sp_who_lock。

EXEC sp_who_lock

此命令将返回会话ID、锁定资源、请求模式和资源描述。

示例一：查看发生死锁的会话

假设两个会话都正在访问相同的表并尝试更新相同的行。在一个会话中，使用以下SQL语句模拟死锁：

BEGIN TRANSACTION
UPDATE mytable SET value = 'new value' WHERE id = 1

然后，在另一个会话中使用以下SQL语句尝试更新相同的行：

BEGIN TRANSACTION
UPDATE mytable SET value = 'new value 2' WHERE id = 1

此时，两个会话都在等待对方释放资源，就会发生死锁。此时，使用存储过程sp_who_lock查看正在发生死锁的会话。运行以下SQL语句：

EXEC sp_who_lock

可以看到有两个会话正在持有资源和等待资源，这就是死锁的表现。在这种情况下，需要终止其中一个会话，以解决死锁问题。

示例二：查看某一表上的锁定情况

在某些情况下，需要查看特定表上的锁定情况。在这种情况下，使用以下SQL语句运行存储过程sp_who_lock：

EXEC sp_who_lock 'mytable'

此命令将返回表mytable上的所有锁定资源的会话信息。

总之，通过创建和运行存储过程sp_who_lock，可以查看发生死锁的会话以及特定表上的锁定情况，以便优化和调试数据库。