sqlserver2005 查看死锁和kill死鎖

### SQL Server 2005 中查看与处理死锁 #### 概述 在SQL Server 2005中，当两个或多个事务试图锁定同一资源时，可能会发生死锁情况，导致这些事务无限期地等待对方释放锁，从而无法继续执行。这种情况下，必须采取措施来解决死锁问题，以便恢复正常的数据操作。本文将详细介绍如何使用自定义存储过程来检测死锁以及如何手动杀死导致死锁的进程。 #### 查找死锁 为了有效地解决死锁问题，首先需要能够准确地识别哪些进程参与了死锁，并了解它们正在执行的具体SQL语句。SQL Server提供了几个内置的系统存储过程，如`sp_who`和`sp_lock`，用于查看活动的会话和锁定情况，但这些方法可能不够直观且信息有限。下面介绍一个更有效的存储过程，可以帮助我们快速找出导致死锁的进程及其执行的SQL语句。 #### 自定义存储过程sp_who_lock ```sql CREATE PROCEDURE sp_who_lock AS BEGIN DECLARE @spid INT, @bl INT, @intTransactionCountOnEntry INT, @intRowcount INT, @intCountProperties INT, @intCounter INT; CREATE TABLE #tmp_lock_who ( id INT IDENTITY (1,1), spid SMALLINT, bl SMALLINT ); IF @@ERROR <> 0 RETURN @@ERROR; INSERT INTO #tmp_lock_who (spid, bl) SELECT 0, blocked FROM sysprocesses WHERE blocked > 0 AND NOT EXISTS ( SELECT * FROM (SELECT * FROM sysprocesses WHERE blocked > 0) AS b WHERE a.blocked = b.spid ) UNION SELECT spid, blocked FROM sysprocesses WHERE blocked > 0; IF @@ERROR <> 0 RETURN @@ERROR; -- 找到临时表的记录数 SELECT @intCountProperties = COUNT(*), @intCounter = 1 FROM #tmp_lock_who; IF @@ERROR <> 0 RETURN @@ERROR; IF @intCountProperties = 0 SELECT '现在没有阻塞和死锁信息' AS message; -- 循环开始 WHILE @intCounter <= @intCountProperties BEGIN -- 取第一条记录 SELECT @spid = spid, @bl = bl FROM #tmp_lock_who WHERE Id = @intCounter; BEGIN IF @spid = 0 SELECT '引起数据库死锁的是:' + CAST(@bl AS VARCHAR(10)) + ' 进程号, 其执行的SQL语法如下' ELSE SELECT '进程号SPID：' + CAST(@spid AS VARCHAR(10)) + ' 被 进程号SPID：' + CAST(@bl AS VARCHAR(10)) + ' 阻塞, 其当前进程执行的SQL语法如下', DBCC INPUTBUFFER(@bl); END; -- 循环指针下移 SET @intCounter = @intCounter + 1; END; DROP TABLE #tmp_lock_who; RETURN 0; END; ``` 该存储过程首先创建了一个临时表`#tmp_lock_who`，用于存储所有被阻塞的进程信息。接着，它通过查询`sysprocesses`视图并进行一系列的条件判断来填充该临时表。存储过程遍历临时表中的每条记录，显示阻塞的进程以及它们正在执行的SQL语句。 #### 杀死进程 一旦识别出导致死锁的进程，下一步是终止这些进程。虽然重启服务是一种简单的方法，但它通常不是最优的选择，因为这会导致所有连接断开。下面介绍一个更为灵活的方法，即创建一个存储过程来手动杀死指定的进程。 #### 自定义存储过程 p_killspid ```sql CREATE PROCEDURE p_killspid @dbname VARCHAR(200) -- 要关闭进程的数据库名 AS BEGIN DECLARE @sql NVARCHAR(500), @spid NVARCHAR(20); DECLARE #tb CURSOR FOR SELECT spid = CAST(spid AS VARCHAR(20)) FROM master..sysprocesses WHERE dbid = DB_ID(@dbname); OPEN #tb; FETCH NEXT FROM #tb INTO @spid; WHILE @@FETCH_STATUS = 0 BEGIN EXEC ('KILL ' + @spid); FETCH NEXT FROM #tb INTO @spid; END; CLOSE #tb; DEALLOCATE #tb; END; ``` 这个存储过程`p_killspid`接收一个参数`@dbname`，表示要处理哪个数据库中的进程。它通过游标遍历`sysprocesses`视图，并对每个进程执行`KILL`命令来终止它们。 #### 结论 通过以上自定义存储过程，不仅可以轻松地检测到SQL Server 2005中的死锁情况，还可以手动终止导致死锁的进程，从而有效避免了长时间的数据锁定，确保了数据库系统的稳定性和可用性。在实际应用中，建议定期检查是否存在死锁现象，并根据实际情况灵活使用这些存储过程。