SQL Server死锁

SQL Server死锁

  • 多个事务之间互相等待对方的资源,导致这些事务永久等待
  • 注意是永久等待,而非长事务

死锁的4个条件

  • 互斥条件(Mutual exclusion):资源不能被共享,只能由一个进程使用。
  • 请求与保持条件(Hold and wait):已经得到资源的进程可以再次申请新的资源。
  • 非剥夺条件(No pre-emption):已经分配的资源不能从相应的进程中被强制地剥夺。
  • 循环等待条件(Circular wait):系统中若干进程组成环路,该环路中每个进程都在等待相邻进程正占用的资源。

事务隔离级别

  • READ UNCOMMITTED(SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED)
  • READ COMMITTED(SET TRANSACTION ISOLATION LEVEL READ COMMITTED)
  • REPEATABLE READ(SET TRANSACTION ISOLATION LEVEL REPEATABLE READ)
  • SERIALIZABLE(SET TRANSACTION ISOLATION LEVEL SERIALIZABLE)
  • SNAPSHOT(SET TRANSACTION ISOLATION LEVEL SNAPSHOT)

验证

  • 初始数据
  • 默认隔离等级下,更新事务对事务外SELECT语句的影响
    • 在sql server ssms中分别打开两个连接,分别模拟事务以及普通SELECT
    • 下面这个事务只有begin,没有COMMIT或者ROLLBACK,目的是模拟并发时多个session同时运行效果
    • 执行上面这个事务
    • 接着在另外一个session中分别查询下面这些select
    • /*example 1*/
      SELECT * FROM [dbo].[customer]
      SELECT * FROM [dbo].[customer] WHERE id=1
      SELECT * FROM [dbo].[customer] WHERE id=2
      SELECT * FROM [dbo].[customer](NOLOCK) WHERE id=2
      
    • 分别执行后的结果(结果里空白的为阻塞状态)

    • 然后在事务session中执行rollback命令
    • 在普通SELECT session中运行下面语句
    • SELECT * FROM [dbo].[customer]
    • 可以看到,这里又变成了aaa(在NOLOCK查询时这个值为Changed)
    • 结论
      • 在默认隔离级别下(READ COMMITTED),对于UPDATE过的记录,在事务范围外默认会被阻塞住
        • 同样适用于REPEATABLE READ以及SERIALIZABLE,但不适用于READ UNCOMMITTED
        • 同样适用于DELETE/INSERT记录
      • 如果在事务外部进行查询的查询条件不牵涉到事务内的更新记录,则外部查询不会被阻塞
        • 比如WHERE id=2会被阻塞
        • 比如WHERE id=3不会被阻塞
        • 比如WHERE id between 1 AND 3会被阻塞
      • 其他线下证明
        • 例子中是用了pkid来做where条件,但是用其他条件也同样适用
        • 例子中的pkid有index,但是没有index的字段同样也适用
  • SERIALIZABLE隔离等级下,对事务外的SELECT/UPDATE/DELETE影响
    • 初始数据为
    • 执行上面这个更改为serializable的事务
    • 然后在这个事务外部执行下面测试
    • 结论
      • 如果某事务是用了serializable隔离级别,则外部就无法insert任何记录到相应的表中
        • 无论insert的字段值是否落在where条件内或者外
      • 外部的UPDATE行为:同普通等级
        • 实际上DELETE行为也是
  • 默认隔离级别中插入的记录,对于外部查询的影响
    • 执行下面这些测试
    • 结论
      • 如果某事务是用了read committed隔离级别,则外部默认无法看到这些新增的记录
        • 同样适用于repeatable read以及serilizable隔离级别
      • 使用了NOLOCK关键字的查询语句能看到脏数据
  • 默认隔离级别下事务死锁例子
    • 初始数据
    • 我们在2个session中同时执行2个事务,如下
    • 结论
      • 互相等待资源的释放、同时又持有现有资源,就会造成死锁
      • 上面例子中被kill掉的事务,选择牺牲事务的算法,ms称:正常情况下,SQL Server会把它认为取消或回滚代价最小的连接作为默认的死锁牺牲品
        • 算了,不深入了
        • 知道还有个非正常情况 - 死锁优先级,后续
  • 事务优先级 - SET DEADLOCK_PRIORITY(http://msdn.microsoft.com/en-us/library/ms186736.aspx
    • 也可以如下
    • 结论
      • SET DEADLOCK_PRIORITY可以放在SESSION的任何地方,无论是事务外还是内、前还是后
        • sqlserver探测到死锁后,会根据session的deadlock优先级来kill
      • LOW/NORMAL/HIGH
        • 分别代表
          • -5/0/5
      • 也可以使用数字
        • -10直到10

避免死锁的建议

  • 把SELECT放在事务范围外
  • 将多个事务合并为一个事务
  • SELECT加With(NOLOCK)提示
  • 降低事务隔离级别, 比如READ UNCOMMITTED
  • 使用基于行版本控制的隔离级别

 

 

 

posted @ 2014-04-24 14:01 McKay 阅读(...) 评论(...) 编辑 收藏