Sql Server 2005 中解决死锁的一种方式

来自：http://www.cnblogs.com/qanholas/archive/2011/07/01/2095498.html

公司的系统上线以后出了很多问题，其中一个就是 deadlock 的出现，这个问题我以前一直不是很重视，因为我知道在Sql2005 中死锁是会自动解决的，一旦监视器检测到这种循环依赖关系，将自动的选择其中一个任务作为牺牲品，然后终止其事务并提示错误。这样，其他任务就可以完成其事务。不过这样一来其中一个肯定是要重新做了，这对用户来说有时候会觉得莫名其妙，总认为我们的系统有问题。于是我查看了一下联机帮助，在网上搜索了一番，终于用 Sql2005 的新特性 TRY/CATCH  解决了这样一个让用户不满意的事情。

在我们操作数据库的时候，特别是在高并发的事务中，死锁的出现不可避免。在网上我也看到很多高人写的解决死锁的例子，用 Sql2000 我不是很熟悉，也没什么发言权的，不过 Sql2005 我还算用的比较熟一点，因此就在这里模仿MSDN 的上的例子也写一个。我写出来的目的也是想加深点印象，已巩固学到的新知识，这也算是种锻炼了，如果能对其他人有点帮助那是最好不过了。当然我也知道自己的语文不好，希望大家见谅，我只是一个初中生，能走到今天这一步真的不容易了，希望大家多多批评，我也是在不断的批评中慢慢成长的。废话不说看下面的例子。个人比较喜欢用 AdventureWorks 数据库，这是 Sql2005 里面的学习库，不用白不用。

（参考MSDN：http://www.microsoft.com/china/msdn/library/data/sqlserver/05TSQLEnhance.mspx?mfr=true）

首先模仿一个死锁出来，开一个查询窗口，执行下面语句将ID是25的职员生日改成75年。

BEGIN TRANSACTION

UPDATE HumanResources.Employee SET BirthDate = ' 1975-08-25 '
WHERE EmployeeID = 25

WAITFOR DELAY ' 00:00:10 '

SELECT * FROM HumanResources.Employee

COMMIT

10秒中内在第二个查询窗口执行下面的语句，将ID是5的职员生日改成8月25

BEGIN TRANSACTION

UPDATE HumanResources.Employee SET BirthDate = ' 1979-08-25 '
WHERE EmployeeID = 5

SELECT * FROM HumanResources.Employee

COMMIT

很明显上面两个查询执行以后会产生一个死锁，这时其中一个查询回滚并且抛出一个错误，如果在下面还有其他语句需要继续执行，那么下面的语句都会停下来。错误如下：

消息 1205 ，级别 13 ，状态 51 ，第 6 行
Transaction (Process ID 75 ) was deadlocked on lock | communication buffer resources with

  another process and has been chosen as the deadlock victim. Rerun the transaction .

我们注意观看错误的代号是 1205 ，这个就是死锁的错误号。在 Sql2005 中可以用 TRY/CATCH  捕捉几乎所有的错误，现在我们来用 TRY/CATCH  捕捉死锁。

BEGIN TRANSACTION
BEGIN try 

  UPDATE HumanResources.Employee SET BirthDate = ' 1975-08-25 '
   WHERE EmployeeID = 25
  WAITFOR DELAY ' 00:00:10 '
  SELECT * FROM HumanResources.Employee

  COMMIT
END try

BEGIN catch
  IF (ERROR_NUMBER() = 1205 ))--如果是死锁回滚后继续执行下面的语句
  BEGIN
    ROLLBACK
  END
  ELSE   --如果不是死锁抛出异常，停止下面的操作
  BEGIN
    ROLLBACK
    DECLARE @error nvarchar ( 100 )
    SET @error = ERROR_MESSAGE()
    RAISERROR ( @error , 16 , 1 )
  END
END catch

上面的方面虽然把死锁给抓住了，下面的语句可以继续执行，但是如果上面这段语句必须那么就试试下面的方法，多重试几次好了，相信总有一次能成功吧！利用 TRY/CATCH 语句进行重试。

DECLARE @i tinyint
SET @i = 1
WHILE @i <= 3
BEGIN
  BEGIN TRANSACTION
BEGIN try 

  UPDATE HumanResources.Employee SET BirthDate = ' 1975-08-25 '
   WHERE EmployeeID = 25
  WAITFOR DELAY ' 00:00:10 '
  SELECT * FROM HumanResources.Employee

  COMMIT
  BREAK   -- 如果执行成功没有发生死锁则跳出循环
END try

BEGIN catch
  IF (ERROR_NUMBER() = 1205 ) -- 如果是死锁回滚后继续执行下面的语句
  BEGIN
    ROLLBACK
    SET @i = @i + 1   -- 如果发生死锁继续执行一次
    CONTINUE
  END
  ELSE     -- 如果不是死锁抛出异常，停止下面的操作
  BEGIN
    ROLLBACK
    DECLARE @error nvarchar ( 100 )
    SET @error = ERROR_MESSAGE()
    RAISERROR ( @error , 16 , 1 )
  END
END catch
END

好了，现在死锁应该不会再带来什么麻烦了吧。