SQL Server 【提高】 死锁
死锁
在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁
死锁发生的原因
-
系统资源不足
-
进程运行推进的顺序不合适
-
资源分配不当等
产生死锁的必要条件
-
互斥条件：一个资源每次只能被一个进程使用
-
请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
-
不剥夺条件：进程已获得的资源，在末使用完之前，不能强行剥夺
-
循环等待条件：若干进程之间形成一种头尾相接的循环等待资源关系
这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁
死锁类型
-
循环死锁：两个进程请求不同资源上的锁，每一个进程都需要对方持有的该资源上的锁，这时将发生循环死锁
-
转换死锁：两个或多个进程都在事务中持有同一资源上的共享锁，并且都想把它升级为独占锁，但是，谁也没法升级直到其他的进程释放共享锁
如何查看死锁
-
执行系统存储过程
EXEC sp_who EXEC sp_lock
-
通过 Sql Profile查看死锁信息
如何处理死锁
-
SQL Server 自动处理
SQL Server在后台进程中实现死锁检测称为死锁监控（Deadlock Monitor）。这个后台进程每5秒钟运行一次，为死锁检查当前锁定情况。在最坏的情况中，因此一个死锁不应该超过5秒。这个查询会回滚并收到1205错误号。
-
KILL会话
-
设定锁请求超时
默认情况下，数据库没有锁定超时期限。也就是说一个会话在申请新的资源时，如果这个资源已经被其它进程锁定，那么本会话会一直处于等待状态。这样无疑是有问题的。我们可以通过SQL命令来设定锁请求超时。也可以访问全局变量 @@LOCK_TIMEOUT 来查看这个值。
SET LOCK_TIMEOUT 20000; --单位是毫秒
当请求锁超过设定时间时，SQL Server将返回错误。我们的程序可以根据返回的错误来进行响应的处理，避免长时间的用户等待。
如何避免死锁
预防死锁就是破坏四个必要条件中的某一个和几个，使其不能形成死锁。有如下几种办法：
-
破坏互斥条件
破坏互斥条件有比较严格的限制,在SQL Server中，如果业务逻辑上允许脏读，则可以通过将隔离等级改为未提交读或使用索引提示。这样使得读取不用加S锁，从而避免了和其它查询所加的与S锁不兼容的锁互斥，进而减少了死锁出现的概率。
-
破坏请求和等待条件
这点由于事务存在原子性，是不可破坏的，因为解决办法是尽量的减少事务的长度，事务内执行的越快越好。这也可以减少死锁出现的概率。
-
破坏不剥夺条件
由于事务的原子性和一致性，不剥夺条件同样不可破坏。但我们可以通过增加资源和减少资源占用两个角度来考虑。
-
增加资源：比如说通过建立非聚集索引，使得有了额外的资源，查询很多时候就不再索要锁基本表，转而锁非聚集索引，如果索引能够"覆盖(Cover)"查询，那更好不过。因此索引Include列不仅仅减少书签查找来提高性能，还能减少死锁。
-
减少资源占用：比如说查询时，能用select col1,col2这种方式，就不要用select * .这有可能带来不必要的书签查找
-
最大限度减少死锁的方法
- 尽量使用低隔离级别： 确定事务是否能在更低的隔离级别上运行，执行提交读取允许事务读取另一个事务已读取（未修改）的数据，而不必等待第一个事务完成。使用较低的隔离级别（例如提交读取）而不使用较高的隔离级别（例如可串行读）可以缩短持有共享锁的时间，从而降低了锁定争夺
- 按同一顺序访问对象： 按同一顺序访问对象也就是：第一个事务提交或回滚后，第二个事务继续进行，这样不会发生死锁
- 保持事务简短并在一个批处理中： 在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长，其持有排它锁或更新锁的时间也就越长，从而堵塞了其它活动并可能导致死锁。 保持事务在一个批处理中，可以最小化事务的网络通信往返量，减少完成事务可能的延迟并释放锁
