[Daily]Sql Server 解决死锁

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

http://blog.csdn.net/hb_gx/article/details/1759199

公司的系统上线以后出了很多问题，其中一个就是 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

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

 

------------------------------------------------------------------------------------------

SQL Server 2005中解决死锁问题

http://blog.csdn.net/arrow_gx/article/details/2483155
数据库操作的死锁是不可避免的，本文并不打算讨论死锁如何产生，重点在于解决死锁，通过SQL Server 2005, 现在似乎有了一种新的解决办法。   

将下面的SQL语句放在两个不同的连接里面，并且在5秒内同时执行，将会发生死锁。   


use Northwind  
begin tran  
　　insert into Orders(CustomerId) values(@#ALFKI@#)  
　　waitfor delay @#00:00:05@#  
　　select * from Orders where CustomerId = @#ALFKI@#  
commit  
print @#end tran@#   

　　   
SQL Server对付死锁的办法是牺牲掉其中的一个，抛出异常，并且回滚事务。在SQL Server 2000，语句一旦发生异常，T-SQL将不会继续运行，上面被牺牲的连接中， print @#end tran@#语句将不会被运行，所以我们很难在SQL Server 2000的T-SQL中对死锁进行进一步的处理。   

现在不同了，SQL Server 2005可以在T-SQL中对异常进行捕获，这样就给我们提供了一条处理死锁的途径：   

下面利用的try ... catch来解决死锁。   


SET XACT_ABORT ON  
declare @r int  
set @r = 1  
while @r  <= 3  
begin  
　　begin tran  
　　  
　　begin try　　　  
　　　　insert into Orders(CustomerId) values(@#ALFKI@#)  
　　　　waitfor delay @#00:00:05@#  
　　　　select * from Orders where CustomerId = @#ALFKI@#  
　　　　  
　　　　commit  
　　　　break  
　　end try  
　　　　  
　　begin catch  
　　　　rollback  
　　　　waitfor delay @#00:00:03@#  
　　　　set @r = @r + 1  
　　　　continue  
　　end catch  
end   

　　   
解决方法当然就是重试，但捕获错误是前提。rollback后面的waitfor不  

解决方法当然就是重试，但捕获错误是前提。rollback后面的waitfor不可少，发生冲突后需要等待一段时间，@retry数目可以调整以应付不同的要求。   

但是现在又面临一个新的问题: 错误被掩盖了，一但问题发生并且超过3次，异常却不会被抛出。SQL Server 2005 有一个RaiseError语句，可以抛出异常，但却不能直接抛出原来的异常，所以需要重新定义发生的错误，现在，解决方案变成了这样:   


declare @r int  
set @r = 1  
while @r  <= 3  
begin  
　　begin tran  
　　  
　　begin try　　　  
　　　　insert into Orders(CustomerId) values(@#ALFKI@#)  
　　　　waitfor delay @#00:00:05@#  
　　　　select * from Orders where CustomerId = @#ALFKI@#  
　　　　  
　　　　commit  
　　　　break  
　　end try  
　　　　  
　　begin catch  
　　　　rollback  
　　　　waitfor delay @#00:00:03@#  
　　　　set @r = @r + 1  
　　　　continue  
　　end catch  
end  
if ERROR_NUMBER()  <> 0  
begin  
　　declare @ErrorMessage nvarchar(4000);  
　　declare @ErrorSeverity int;  
　　declare @ErrorState int;  
　　select  
　　　　@ErrorMessage = ERROR_MESSAGE(),  
　　　　@ErrorSeverity = ERROR_SEVERITY(),  
　　　　@ErrorState = ERROR_STATE();  
　　raiserror (@ErrorMessage,  
　　　　　　　 @ErrorSeverity,  
　　　　　　　 @ErrorState  
　　　　　　　 );  
end   

　　   
我希望将来SQL Server 2005能够直接抛出原有异常，比如提供一个无参数的RaiseError。   

因此方案有点臃肿，但将死锁问题封装到T-SQL中有助于明确职责，提高高层系统的清晰度。现在，对于DataAccess的代码，或许再也不需要考虑死锁问题了。

通过 Wiz 发布

转载于:https://www.cnblogs.com/lost2x/archive/2011/11/09/2242170.html