SQL Server 2008中SQL应用之一“死锁(Deadlocking)”

当一个用户会话（会话1）已经落定了一个资源，而另一个会话（会话2）想要修改该资源，并且会话2也锁定了会话1想要修改的资源时，就会出现“死锁”(deadlocking)。在另一方释放资源前，会话1和会话2都不可能继续。所以，SQL Server会选择死锁中的一个会话作为“死锁牺牲品”。

网站建设哪家好，找创新互联！专注于网页设计、网站建设、微信开发、小程序开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了邯郸免费建站欢迎大家使用！

注意：死锁牺牲品的会话会被杀死，事务会被回滚。

注意：死锁与正常的阻塞是两个经常被混淆的概念。

发生死锁的一些原因：

1、应用程序以不同的次序访问表。例如会话1先更新了客户然后更新了订单，而会话2先更新了订单然后更新了客户。这就增加了死锁的可能性。

2、应用程序使用了长时间的事务，在一个事务中更新很多行或很多表。这样增加了行的“表面积”，从而导致死锁冲突。

3、在一些情况下，SQL Server发出了一些行锁，之后它又决定将其升级为表锁。如果这些行在相同的数据页面中，并且两个会话希望同时在相同的页面升级锁粒度，就会产生死锁。

一、使用 SQL Server Profiler 分析死锁

http://msdn.microsoft.com/zh-cn/library/ms188246.aspx

二、使用跟踪标志位找出死锁

本文主要介绍使用DBCC TRACEON、DBCC TRACEOFF和DBCC TRACESTATUS命令来确保死锁被正确记录到SQL Server Management Studio SQL日志中。这些命令用来启用、关闭、和检查跟踪标志位的状态。

■DBCC TRACEON，启用跟踪标志位。用法：DBCC TRACEON ( trace# [ ,...n ][ , -1 ] ) [ WITH NO_INFOMSGS ]

详细参看 MSDN:http://msdn.microsoft.com/zh-cn/library/ms187329.aspx

■DBCC TRACESTATUS，检查跟踪标志位状态。用法：DBCC TRACESTATUS ( [ [ trace# [ ,...n ] ] [ , ] [ -1 ] ] ) [ WITH NO_INFOMSGS ]

详细参看 MSDN:http://msdn.microsoft.com/zh-cn/library/ms187809.aspx

■DBCC TRACEOFF，关闭跟踪标志位。用法：DBCC TRACEOFF (trace# [ ,...n ] [ , -1 ] ) [ WITH NO_INFOMSGS ]

详细参看 MSDN:http://msdn.microsoft.com/en-us/library/ms174401.aspx

下面我们模拟一个死锁：

在***个SQL查询窗口执行：

 
 
 
 
      
  
  
  
  1. use AdventureWorks  
    2.   
  
  
  
  2. go  
    3.   
  
  
  
  3. SET NOCOUNT ON 
    4.   
  
  
  
  4. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
    5.   
  
  
  
  5. WHILE 1=1  
    6.   
  
  
  
  6. BEGIN 
    7.   
  
  
  
  7. BEGIN TRAN  
    8.   
  
  
  
  8. UPDATE Purchasing.Vendor  
    9.   
  
  
  
  9. SET CreditRating = 1  
    10.   
  
  
  
  10. WHERE VendorID = 90  
    11.   
  
  
  
  11. UPDATE Purchasing.Vendor  
    12.   
  
  
  
  12. SET CreditRating = 2  
    13.   
  
  
  
  13. WHERE VendorID = 91  
    14.   
  
  
  
  14. COMMIT TRAN  
    15.   
  
  
  
  15. END 
    16.

在第二个查询窗口执行：

 
 
 
 
      
  
  
  
  1. use AdventureWorks  
    2.   
  
  
  
  2. go  
    3.   
  
  
  
  3. SET NOCOUNT ON 
    4.   
  
  
  
  4. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
    5.   
  
  
  
  5. WHILE 1=1  
    6.   
  
  
  
  6. BEGIN 
    7.   
  
  
  
  7. BEGIN TRAN  
    8.   
  
  
  
  8. UPDATE Purchasing.Vendor  
    9.   
  
  
  
  9. SET CreditRating = 2  
    10.   
  
  
  
  10. WHERE VendorID = 91  
    11.   
  
  
  
  11. UPDATE Purchasing.Vendor  
    12.   
  
  
  
  12. SET CreditRating = 1  
    13.   
  
  
  
  13. WHERE VendorID = 90  
    14.   
  
  
  
  14. COMMIT TRAN  
    15.   
  
  
  
  15. END 
    16.

等待几秒后，其中一个查询窗口会提示：

 
 
 
 
      
  
  
  
  1. /*
    2.   
  
  
  
  2. Msg 1205, Level 13, State 51, Line 9
    3.   
  
  
  
  3. Transaction (Process ID 52) was deadlocked on lock resources with another process 
    4.   
  
  
  
  4. and has been chosen as the deadlock victim.Rerun the transaction.
    5.   
  
  
  
  5. */ 
    6.

此时，查看，SQL Server Management Studio的SQL 日志，发现死锁事件没有被记录。

打开第三个查询窗口，执行：

 
 
 
 
      
  
  
  
  1. DBCC TRACEON (1222, -1)  
    2.   
  
  
  
  2. GO  
    3.   
  
  
  
  3. DBCC TRACESTATUS 
    4.

为了模拟另一个死锁，将重启动“胜利”的那个连接查询（没有被杀死的那个），然后重启死锁丢失的会话，几秒后又出现另一个死锁了。

死锁发生后，停止另一个执行的查询。现在，SQL Server Management Studio的SQL 日志中包含了死锁事件的详细错误信息。包括相关的数据库和对象、锁定模式以及死锁中的SQL语句。

在检查完毕后，关闭跟踪标志位：

 
 
 
 
      
  
  
  
  1. DBCC TRACEON (1222, -1)  
    2.   
  
  
  
  2. GO  
    3.   
  
  
  
  3. DBCC TRACESTATUS 
    4.

解析：

在本例中，我们使用跟踪标志位1222。跟踪标志位1222能把详细的死锁信息返回到SQL日志中，标志位－1表示跟踪标志位1222应该对所有SQL Server连接在全局中启用。


三、设置死锁优先级

我们也可以使用SET DEADLOCK_PRIORITY命令来增加一个查询会话被选为死锁牺牲品的可能性。此命令的语法如下：

SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | | @deadlock_var | @deadlock_intvar }
::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }

http://msdn.microsoft.com/en-us/library/ms186736.aspx

例如，上例中，***个查询窗口如果使用以下的死锁优先级命令，几乎可以肯定会被选为死锁牺牲品。（正常情况下，SQL Server会把它认为取消或回滚代价最小的连接作为默认的死锁牺牲品）：

 
 
 
 
      
  
  
  
  1. SET NOCOUNT ON 
    2.   
  
  
  
  2. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
    3.   
  
  
  
  3. SET DEADLOCK_PRIORITY LOW  
    4.   
  
  
  
  4. BEGIN TRAN  
    5.   
  
  
  
  5. UPDATE Purchasing.Vendor  
    6.   
  
  
  
  6. SET CreditRating = 1  
    7.   
  
  
  
  7. WHERE VendorID = 2  
    8.   
  
  
  
  8. UPDATE Purchasing.Vendor  
    9.   
  
  
  
  9. SET CreditRating = 2  
    10.   
  
  
  
  10. WHERE VendorID = 1  
    11.   
  
  
  
  11. COMMIT TRAN 
    12.

解析：可以将优先级设为High或Normal,High表示除非另一个会话有相同的优先级，否则它不会被选为牺牲品。Normal是默认行为，如果另一个会话是High,它可能会被选中。如果另一个是Low,则它可以安全地不被选中。如果两个会话有相同的优先级，则回滚代价最小的事务会被选中。

关于死锁的其他资源，可能会有补充：

happyhippy的SQL Server死锁总结，也总结的不错。 

链接为：http://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html

原文链接：http://www.cnblogs.com/downmoon/archive/2011/02/27/1966337.html

【编辑推荐】

  1. SQL Server 2000中正确的检测死锁与结束死锁
  2. SQL Server锁在使用时如何避免死锁的6步骤
  3. SQL Server死锁问题的解决与分析
  4. SQL Server锁使用注意事项之如何避免死锁
  5. 详解SQL Server中的死锁 妙用查询优化器

分享名称：SQL Server 2008中SQL应用之一“死锁(Deadlocking)”
网站地址：http://www.shufengxianlan.com/qtweb/news31/183131.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

解决方案知识

分类信息网站

企业网站建设　　 　3D立体画　　 　短视频拍摄　　 　IDC机房托管　　 　成都网站设计　　 　泸州叙永网站建设　　 　成都雪糕加盟　　 　网站设计制作报价　　 　租用服务器　　 　帝美豪门窗　　 　led背发光字　　 　乐山做网站　　 　成都宣传画册设计　　 　成都网站设计　　 　上市集团网站建设　　 　成都定制网站建设　　 　新网创想　　 　西部信息机房　　 　自贡主机托管　　 　重庆电商网站建设