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（
  • 也可以如下
  • 结论
  • SET DEADLOCK_PRIORITY可以放在SESSION的任何地方，无论是事务外还是内、前还是后
  • sqlserver探测到死锁后，会根据session的deadlock优先级来kill
  • LOW/NORMAL/HIGH
  • 分别代表
  • -5/0/5
  • 也可以使用数字
  • -10直到10

避免死锁的建议

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