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 .
SELECT * FROM . WHERE id=1
SELECT * FROM . WHERE id=2
SELECT * FROM .(NOLOCK) WHERE id=2
[*] 分别执行后的结果（结果里空白的为阻塞状态）
[*]
[*]然后在事务session中执行rollback命令
[*]在普通SELECT session中运行下面语句
[*]
SELECT * FROM .
[*]
[*]可以看到，这里又变成了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
[*]使用基于行版本控制的隔离级别
页:
[1]