none
求教高手关于SQL死锁的问题. RRS feed

  • 问题

  • 在SQL死锁的时候,

    本人暂时想到的办法就是要么手动清除死锁的进程,但使用这个办法时,如果等待死锁进程的其它进程太多时,有可能在清除死锁进程后又出现重复死锁;因此估计是先清除所有等待死锁进程的其它进程,再清除死锁进程(为了铺抓死锁的准确性,我想在存储过程中进行记录死锁的进程,等待10秒,再铺抓一次死锁的进程,再等待10秒,再铺抓一次死锁的进程,最后把三次铺抓到死锁进程进行对比,三次都有出现的死锁进程才确定为清除的对象).

    第二个办法是把数据库用语句脱机,断开所有连接,再重新联机.

    以上是我想到的两个办法,但没实践过,也不确定各自的优缺点,有没有高手指点一下.

    至于第一个办法有没有高手写过类似的存储过程可以作为参考?

    谢谢!


    • 已编辑 hzpemu 2013年1月19日 8:31
    2013年1月19日 8:26

答案

  • 捕获一些死锁信息


    问题定位
    查看死锁信息,SQL提供了监视工具:两个跟踪标志:1204和1222以及SQL Trace

    跟踪标志1204和跟踪标志1222
    发生死锁时,跟踪标志1204和1222会向SQL错误日志返回捕获的信息
    跟踪标志1204会报告由死锁所涉及的每个节点设置格式的死锁信息
    他是SQL2005之前版本就有的功能。



    跟踪标志1222是SQL2005才开始有的新功能,会设置死锁信息的格式,顺序为先按进程,然后按资源。
    1222的结果不但基本包含了1204的所有信息,还包含许多1204所没有的信息。所以在SQL2005以后可以直接使用跟踪标志1222来跟踪死锁


    打开跟踪标志
    DBCC TRACEON(1222,-1)

    和1204输出结果不同,1222的输出结果分成3个部分,并且由于信息量比较多,长度要比
    1204的结果长很多.死锁的参与者不是用SPID来直接显示,而是用processXXXX的方法
    所以需要有些耐心来看懂。但是,包含的内容比1204的输出丰富很多


    1222包含3部分
    第一部分:
    死锁牺牲进程

    第二部分:process-list
    死锁发生的进程信息

    第三部分:resource-list
    发生死锁的资源信息



    死锁图形事件(locks-deadlock graph)
    在SQL Trace中也有表示死锁所涉及的任务和资源的图形描述的事件

    要分析死锁的时候,一般结合跟踪标志1222和SQL Trace。首先在sql errorlog里寻找跟踪标志1222的输出结果,
    根据输出的时间在跟踪里寻找相应的连接接着就可以详细分析死锁产生的原因了



    给我写信: QQ我:点击这里给我发消息

    • 已标记为答案 hzpemu 2013年2月4日 1:38
    2013年1月19日 14:36
  • ----------------------------解决办法---------------------------------------------------------------------
    --尽管死锁不能完全避免,但遵守特定的编码惯例可以将死锁发生的机会将至最低。
    --以下方法有助于将死锁减至最少:
    --按同一顺序访问对象
    --避免事务中的用户交互
    --保持事务简短并处于一个批处理中
    --使用较低的隔离级别
    --调整语句的执行计划,减少锁的申请数目

    更多详细信息,LZ可以上网找一下,这些都是解决死锁,分析死锁的常用步骤


    给我写信: QQ我:点击这里给我发消息

    • 已标记为答案 hzpemu 2013年2月4日 1:38
    2013年1月19日 14:37
  • 首先你要分清死锁跟阻塞的区别,它们2个是完全不同的概念。

    根据你的描述,你碰到的是阻塞而不是死锁。

    可以用下面的DMV查出阻塞者,然后查明阻塞的原因就可以了,一般都是占有某个资源太久造成的。

    [sys].[dm_os_waiting_tasks]



    Please click the Mark as Answer button if a post solves your problem!

    • 已标记为答案 hzpemu 2013年2月4日 1:38
    2013年1月20日 6:05
  • 第二个办法是把数据库用语句脱机,断开所有连接,再重新联机. 

    对于执行了一半的DML语句,SQL Server为了保持数据的一致性,在联机的时候会做REDO/UNDO,这个也需要耗费系统资源,而且不是解决问题的办法。另外同意楼上说的,你遇到的可能是BLOCKING,而不是死锁。死锁会有一个进程会被当做Victim Kill掉,不需要你手动KILL。

    • 已标记为答案 hzpemu 2013年2月4日 1:38
    2013年1月22日 5:35

全部回复

  • 若经常死锁,改进设计是正道,先天设计有缺陷

    多数情况下优化也能极大改善效果


    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com

    2013年1月19日 9:03
  • 分析死锁原因进而解决死锁才是最有效办法

    造成阻塞和死锁的3大原因:
    1、连接持有锁时间过长
    2、锁数目过多
    3、锁粒度过大

    SQL有一个死锁监视器

    为了解决死锁问题,SQLSERVER数据库引擎死锁监视器会定期检查陷入死锁的任务

    如果监视器检测到这种依赖循环关系,会选择其中一个任务作为牺牲品,然后终止其事务并提示错误。这时用户会遇到的死锁错误

    死锁检测
    死锁检测是由锁监视器这个系统线程执行的,他会定期搜索SQL里的所有任务。默认时间间隔是5秒

    检测到死锁后,SQL会选择其中一个线程作为死锁牺牲品来结束死锁。数据库引擎
    终止线程当前执行的批处理,回滚死锁牺牲品的事务,并将1205错误返回到应用程序。回滚死锁牺牲品会释放事务持有的所有锁。
    这使其他线程的事务解锁并继续运行。默认情况下,数据库引擎会选择运行回滚开销最小的事务的会话
    作为死锁牺牲品。


    给我写信: QQ我:点击这里给我发消息

    2013年1月19日 14:28
  • 捕获一些死锁信息


    问题定位
    查看死锁信息,SQL提供了监视工具:两个跟踪标志:1204和1222以及SQL Trace

    跟踪标志1204和跟踪标志1222
    发生死锁时,跟踪标志1204和1222会向SQL错误日志返回捕获的信息
    跟踪标志1204会报告由死锁所涉及的每个节点设置格式的死锁信息
    他是SQL2005之前版本就有的功能。



    跟踪标志1222是SQL2005才开始有的新功能,会设置死锁信息的格式,顺序为先按进程,然后按资源。
    1222的结果不但基本包含了1204的所有信息,还包含许多1204所没有的信息。所以在SQL2005以后可以直接使用跟踪标志1222来跟踪死锁


    打开跟踪标志
    DBCC TRACEON(1222,-1)

    和1204输出结果不同,1222的输出结果分成3个部分,并且由于信息量比较多,长度要比
    1204的结果长很多.死锁的参与者不是用SPID来直接显示,而是用processXXXX的方法
    所以需要有些耐心来看懂。但是,包含的内容比1204的输出丰富很多


    1222包含3部分
    第一部分:
    死锁牺牲进程

    第二部分:process-list
    死锁发生的进程信息

    第三部分:resource-list
    发生死锁的资源信息



    死锁图形事件(locks-deadlock graph)
    在SQL Trace中也有表示死锁所涉及的任务和资源的图形描述的事件

    要分析死锁的时候,一般结合跟踪标志1222和SQL Trace。首先在sql errorlog里寻找跟踪标志1222的输出结果,
    根据输出的时间在跟踪里寻找相应的连接接着就可以详细分析死锁产生的原因了



    给我写信: QQ我:点击这里给我发消息

    • 已标记为答案 hzpemu 2013年2月4日 1:38
    2013年1月19日 14:36
  • ----------------------------解决办法---------------------------------------------------------------------
    --尽管死锁不能完全避免,但遵守特定的编码惯例可以将死锁发生的机会将至最低。
    --以下方法有助于将死锁减至最少:
    --按同一顺序访问对象
    --避免事务中的用户交互
    --保持事务简短并处于一个批处理中
    --使用较低的隔离级别
    --调整语句的执行计划,减少锁的申请数目

    更多详细信息,LZ可以上网找一下,这些都是解决死锁,分析死锁的常用步骤


    给我写信: QQ我:点击这里给我发消息

    • 已标记为答案 hzpemu 2013年2月4日 1:38
    2013年1月19日 14:37
  • 首先你要分清死锁跟阻塞的区别,它们2个是完全不同的概念。

    根据你的描述,你碰到的是阻塞而不是死锁。

    可以用下面的DMV查出阻塞者,然后查明阻塞的原因就可以了,一般都是占有某个资源太久造成的。

    [sys].[dm_os_waiting_tasks]



    Please click the Mark as Answer button if a post solves your problem!

    • 已标记为答案 hzpemu 2013年2月4日 1:38
    2013年1月20日 6:05
  • 第二个办法是把数据库用语句脱机,断开所有连接,再重新联机. 

    对于执行了一半的DML语句,SQL Server为了保持数据的一致性,在联机的时候会做REDO/UNDO,这个也需要耗费系统资源,而且不是解决问题的办法。另外同意楼上说的,你遇到的可能是BLOCKING,而不是死锁。死锁会有一个进程会被当做Victim Kill掉,不需要你手动KILL。

    • 已标记为答案 hzpemu 2013年2月4日 1:38
    2013年1月22日 5:35