SQL Server死锁

2024-08-31 00:55:49

字体：大中小

来源：转载

供稿：网友

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=1SELECT * FROM [dbo].[customer] WHERE id=2SELECT * 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