• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    SQL2008中SQL应用之- 死锁(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查询窗口执行:

    复制代码 代码如下:

    use AdventureWorks
    go
    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    WHILE 1=1
    BEGIN
    BEGIN TRAN
    UPDATE Purchasing.Vendor
    SET CreditRating = 1
    WHERE VendorID = 90
    UPDATE Purchasing.Vendor
    SET CreditRating = 2
    WHERE VendorID = 91
    COMMIT TRAN
    END

    在第二个查询窗口执行:
    复制代码 代码如下:

    use AdventureWorks
    go

    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    WHILE 1=1
    BEGIN
    BEGIN TRAN
    UPDATE Purchasing.Vendor
    SET CreditRating = 2
    WHERE VendorID = 91
    UPDATE Purchasing.Vendor
    SET CreditRating = 1
    WHERE VendorID = 90
    COMMIT TRAN
    END

    等待几秒后,其中一个查询窗口会提示:
    复制代码 代码如下:

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

    此时,查看,SQL Server Management Studio的SQL 日志,发现死锁事件没有被记录。
    打开第三个查询窗口,执行:
    复制代码 代码如下:

    DBCC TRACEON (1222, -1)
    GO
    DBCC TRACESTATUS

    为了模拟另一个死锁,将重启动“胜利”的那个连接查询(没有被杀死的那个),然后重启死锁丢失的会话,几秒后又出现另一个死锁了。
    死锁发生后,停止另一个执行的查询。现在,SQL Server Management Studio的SQL 日志中包含了死锁事件的详细错误信息。包括相关的数据库和对象、锁定模式以及死锁中的SQL语句。

    在检查完毕后,关闭跟踪标志位:
    复制代码 代码如下:

    DBCC TRACEON (1222, -1)
    GO
    DBCC TRACESTATUS

    解析: 

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

    三、设置死锁优先级

    我们也可以使用SET DEADLOCK_PRIORITY命令来增加一个查询会话被选为死锁牺牲品的可能性。此命令的语法如下:
    SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | numeric-priority> | @deadlock_var | @deadlock_intvar }
    numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }

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

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

    复制代码 代码如下:

    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    SET DEADLOCK_PRIORITY LOW
    BEGIN TRAN

    UPDATE Purchasing.Vendor
    SET CreditRating = 1
    WHERE VendorID = 2
    UPDATE Purchasing.Vendor
    SET CreditRating = 2
    WHERE VendorID = 1
    COMMIT TRAN

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

    关于死锁的其他资源,可能会有补充:

    happyhippy的SQL Server死锁总结,也总结的不错。  http://www.cnblogs.com/happyhippy/archive/2008/11/14/1333922.html

    您可能感兴趣的文章:
    • SQLServer 中的死锁说明
    • SqlServer表死锁的解决方法分享
    • sql server 2000阻塞和死锁问题的查看与解决方法
    • 查找sqlserver查询死锁源头的方法 sqlserver死锁监控
    • C#中lock死锁实例教程
    • SQL2005查看死锁存储过程sp_who_lock
    上一篇:SQL Server 2008中SQL之WaitFor使用介绍
    下一篇:SQLServer 2008中的代码安全(一) 存储过程加密与安全上下文
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    SQL2008中SQL应用之- 死锁(Deadlocking) SQL2008,中,SQL,应,用之,死锁,