深入浅出SQL Server中的死锁(二)
持状态。简单来说,就是由于Update语句对基本表产生X锁，然后需要对表上的索引也进行更新，而表上的索引正好被另一个连接进行查找，加了S锁，此时又产生书签查找去基本表加了X锁的数据进行书签查找，此时形成死锁，这个概念可以从图8看到。
图8.由书签查找产生的死锁
这种死锁可以通过Include列来减少书签查找，从而减少这种类型死锁发生的概率。
由外键产生的死锁
这类死锁产生的原因来自外键约束。当主表(也就是主键是从表外键的那个表)更新数据时，需要查看从表，以确定从表的外键列满足外键约束。此时会在主表上加X锁，但这并不能阻止同一时间，另一个SPID向从表添加被修改的主表主键，为了解决这个问题，SQL Server在进行这类更新时，使用Range锁，这种锁是当隔离等级为序列化时才有的，因此在这时虽然隔离等级可能是默认的已提交读，但是行为却是序列化。这很可能就会导致死锁。
解决办法之一是向外键列添加索引，使得Range锁加在索引上，而不是表本身。从而降低了死锁发生的概率。
由于推进顺序不当产生的死锁
这也是图3中死锁的原因。在多个事务对资源的使用顺序不当，形成死锁环路而引发的。解决方法是尽量是资源的使用顺序一致。这也是死锁问题出现最多的一种情况。
如何减少死锁
上面简单讲述了SQL Server中产生死锁的一些情况。下面我们从更宽泛的角度来看如何减少死锁。
在操作系统中，进程并发减少死锁的原理同样可以套用到SQL Server中。在操作系统对于处理死锁的办法如下：
1) 预防死锁。
这是一种较简单和直观的事先预防的方法。方法是通过设置某些限制条件，去破坏产生死锁的四个必要条件中的一个或者几个，来预防发生死锁。预防死锁是一种较易实现的方法，已被广泛使用。但是由于所施加的限制条件往往太严格，可能会导致系统资源利用率和系统吞吐量降低。
2) 避免死锁。
该方法同样是属于事先预防的策略，但它并不须事先采取各种限制措施去破坏产生死锁的的四个必要条件，而是在资源的动态分配过程中，用某种方法去防止系统进入不安全状态，从而避免发生死锁。
3)检测死锁。
这种方法并不须事先采取任何限制性措施，也不必检查系统是否已经进入不安全区，此方法允许系统在运行过程中发生死锁。但可通过系统所设置的检测机构，及时地检测出死锁的发生，并精确地确定与死锁有关的进程和资源，然后采取适当措施，从系统中将已发生的死锁清除掉。
4)解除死锁。
这是与检测死锁相配套的一种措施。当检测到系统中已发生死锁时，须将进程从死锁状态中解脱出来。常用的实施方法是撤销或挂起一些进程，以便回收一些资源，再将这些资源分配给已处于阻塞状态的进程，使之转为就绪状态，以继续运行。死锁的检测和解除措施，有可能使系统获得较好的资源利用率和吞吐量，但在实现上难度也最大。
由上面4中处理死锁的办法看，其中检测死锁和解除死锁是Lock Monitor的事，作为DBA或数据库开发人员，处理死锁要放在预防和避免死锁上。
预防死锁
预防死锁就是破坏四个必要条件中的某一个和几个，使其不能形成死锁。有如下几种办法
1)破坏互斥条件
破坏互斥条件有比较严格的限制,在SQL Server中，如果业务逻辑上允许脏读，则可以通过将隔离等级改为未提交读或使用索引提示。这样使得读取不用加S锁，从而避免了和其它查询所加的与S锁不兼容的锁互斥，进而减少了死锁出现的概率。
2)破坏请求和等待条件
这点由于事务存在原子性，是不可破坏的，因为解决办法是尽量的减少事务的长度，事务内执行的越快越好。这也可以减少死锁出现的概率。
3)破坏不剥夺条件
由于事务的原子性和一致性，不剥夺条件同样不可破坏。但我们可以通过增加资源和减少资源占用两个角度来考虑。
增加资源：比如说通过建立非聚集索引，使得有了额外的资源，查询很多时候就不再索要锁基本表，转而锁非聚集索引，如果索引能够“覆盖(Cover)”查询，那更好不过。因此索引Include列不仅仅减少书签查找来提高性能，还能减少死锁。增加资源还可以通过SQL Server 2005之后的行版本控制进行，但这种方式并不推荐，在此不再详细讨论。
减少资源占用:比如说查询时，能用select col1,col2这种方式，就不要用select * .这有可能带来不必要的书签查找
避免死锁
避免死锁是在有限的资源下，使得主体争用资源不形成环路。比如说典型的银行家算法，就是在资源有限的情况下，在不造成现金流断裂的情况下，尽可能多的按一定顺序分配资源。
因此避免死锁的关键是“顺序”。在SQL Server中，尽量使查询对资源的使用顺序保持一致。比如图3就是一个典型的不按顺序请求资源而导致的死锁。假设图3的顺序改为图9所示顺序，那是形不成死锁的，转而，死锁会变为等待。
图9.按顺序，死锁转为等待
SQL Server中死锁的处理
那既然死锁无法避免，在出现死锁的时候要有一种处理机制。可以想象一下，如果你的程序是一个电子商务网站，由于死锁造成用户的生成的订单被RollBack…
因此死锁的处理在SQL Server可以放在两个层面进行
在SQL Server层面处理死锁
首先要知道，SQL Server中死锁的错误代码是1205，由于死锁是由阻塞引起的，而阻塞的时间往往都不长，索引可以通过重试几次来处理死锁，典型的代码如代码2所示。
--重试次数
DECLARE @retry INT
DECLARE @retry INT
SET @retry = 3
WHILE ( @retry > 0 )
BEGIN
BEGIN TRY
--这里是业务代码
BEGIN
BEGIN TRY
--这里是业务代码
--事务成功，将重试次数变为
SET @retry = 0
END TRY
SET @retry = 0
END TRY
BEGIN CATCH
--如果是死锁，则重试
IF ( ERROR_NUMBER() = 1205 )
SET @retry = @retry
ELSE
BEGIN
--如果是其它错误，记录到日志等..
END
--如果是死锁，则重试
IF ( ERROR_NUMBER() = 1205 )
SET @retry = @retry
ELSE
BEGIN
--如果是其它错误，记录到日志等..
END
END CATCH
END
END
代码2.在SQl Server层面处理死锁
在程序层处理死锁
和SQL Server中处理死锁的方式大同小异，也是通过错误代码进行判断，下面是C#处理死锁的方式如代码3所示。
int retry = 3;
while (retry > 0)
{
try
{
//执行sql语句的代码
//将重试次数变为0
retry = 0;
}
catch(SqlException e)
{
//如果是死锁的话,0.5S后重试
if(e.Number==1205)
{
System.Threading.Thread.Sleep(500);
retry--;
}
//其它错误....
else
{
throw;
}
}
}
while (retry > 0)
try
{
//执行sql语句的代码
//将重试次数变为0
retry = 0;
}
catch(SqlException e)
{
//如果是死锁的话,0.5S后重试
if(e.Number==1205)
{
System.Threading.Thread.Sleep(500);
retry--;
}
//其它错误....
else
{
throw;
}
}
}
代码3.死锁处理的C#代码
总结
本文讲述了死锁的概念，产生死锁的四个必要条件，死锁的处理方式和在SQL Server中如何检测