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
  
• 使用基于行版本控制的隔离级别