SQL2005中的事务与锁定（七）
------------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-10-21
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
-- 转载请注明出处，更多请关注：http://blog.csdn.net/happyflystone
-- 关键字： 锁升级、禁止升级 行锁与页锁 动态管理锁定 死锁
------------------------------------------------------------------------
上一篇对锁定资源进行了深入，并对实体类型、锁的本质、生命周期等相关知识进行了讨论，这一篇我们再深入对敏感话题锁的升级及死锁进行梳理。我们经常看见有人发帖数据库死锁了，那死锁有哪些类型，如果查看？如何处理？。。。。。。。
6、锁升级 禁止升级
锁定粒度是一个查询或更新所锁定的最小数据，粒度不同数据库的性能和并发能力是此消彼长的，怎么来理解呢？锁定的粒度越小并发的用户数越多，这是显而易的，如果这时发生一种情况，根据业务规律要锁定大量的记录行来进行更新，在保持并发用户的前提下，我们锁定的记录的行锁或键锁就很多，我们知道锁定不是免费的午餐，是要付出代价的，管理的锁定多越多系统资源开销就越大。还记得我们在前面介绍过锁块吧，锁块是一个64/128(128是64位操作系统)字节的内存块，另外对每一个申请或正持有锁块的进程还要准备一个32/64(64是64位操作系统)字节的内存块来描述这些进程，在这儿我们确定一个前提：不管锁定粒度的大小，每一个锁定都占用几乎同样的系统开销。好，比如我们要进行10W行数据更新，为了并发我们都采用行锁来锁定，按照锁块的定义那么我们就得需要64B * 100000+N*32B= 6400000B +32NB(理论更新我们取N=1相对于6400000可以忽略)> 6.4M的RAM来管理这些行锁，假设并发进程(当然是不同资源上的)数量是X，那么当前数据库就得要X*6.4M的RAM用于管理锁定，显然这种对RAM需求的上升是系统无法忍受，不可能无限制的满足的这种增长，那么SQLSERVER得用一种办法来防止系统使用太多的内存来追踪锁定并且提高锁定的效率。这个任务交给了锁管理器，它负责平衡资源的使用(当然还负责从特定操作的开始到结束保持连续、逻辑完整性)，这时管理器就采取锁定升级这一明智造择，从行锁或键锁或页锁升级为表级锁定，比较6.4M和96B，显然获取一个表级锁定比持有许多行或键锁更有意义。
锁升级的意义是显而易见，使得锁定开销下降并避免系统资源耗尽。在结构引擎里我们提及锁管理器，系统分配给锁管理器的内存是有限的，锁的升级保证了锁定占用内存维持一个合理的限度。
锁升级发生的时机：
1、 在一个对象上一个查询或更新持有锁的数量超过阀值。SQL2005缺省是5000个锁(记得SQL6.0只有200个，但是我们要记住SQL6.0只有页面锁定哦)。
2、 锁资源占用的内存超过AWE或常规内存的40%，40%是一个约数。
时机一满足SQLSERVER就会尝试锁升级，当然升级不一定会成功，当失败后在同一个对象上的锁资源再次上升到一定程度时升级会再次发生，如果升级成功SQLSERVER会释放对象上先前获得的行、键、分页锁定。升级失败发生当另外一个进程对表有行或页有排它锁定时。
锁升级潜在的危险：
1、 锁升级的结果一定是一个完全表级锁定，也就是不可能出现行锁升级为页锁的，最细的行级锁升级的直接结果一定是表锁定。
2、 锁升级可能造成意外的阻塞(这个应该是很好理解的)
3、 锁升级成功后无法降级
禁止升级 我们知道锁升级是有潜在的危险，并且这种升级的结果是不可能现降级除非事务结束。所以升级不是对所有的应用都是一件好事，MS提供了两个开关项：1211和1224，我们可以通过设置跟踪标识来禁止升级。
7、行锁、页锁
7.0之前的版本锁定的最小粒度就是页锁，提醒大家一下那时的页面最小单位是2K，如果细心部署一定程度上是可以满足够大的呑吐量和可以接受的响应时间。然后7.0后把分页从2KB提升为8KB时(为什么要提升呢？嘿嘿，留一个疑问给大家)，这种页面锁定对并发能力是一种挑战，也就是锁定的范围是7.0之前的4倍，这时并发及响应时间都成一个问题。SQL2005完全实现行级锁定，显然这对并发响应是可喜的，可是正如我在锁升级里给大家算的一笔帐，在有限可利用的锁定资源前提下，大量行级锁定的代价还是让人无法接受的，特别在极限的状态下。
我们知道锁定操作是一个密集型操作，一个锁定不仅要看到内存的损耗，还要看到SQLSERVER管理这些锁定对其本身来也是一种负荷。虽然SQL内部使用闩或自旋锁来降低这种负荷，但我们很容易可以想像管理一个分页锁定比管理N个行级锁定(假设页面内有N行记录)更轻松、更有效率。
比较行锁和页锁，行锁降低了并发冲突但是资源的损耗也是显然的，页锁减少必须存在锁的数量及管理这些锁定的资源损耗但是以并发能下降为代价的。到底哪个更合适，恐怕不是一句两句能说完的，因为针对不同应用、不同行业、不同并发模型、不同隔离两都各有各的优势。
在SQLSERVER2005可以用sp_indexoption来控制索引的锁定单位。关于这个设置我们可以看看联机帮助，但是一定要注意它只针对索引所以对堆表无法控制分页锁定。
8、动态管理锁定
SQL造择锁定类型、粒度是基于行数、可能扫描的页面数、分页上的行数，隔离级别、进行的何种操作、可使用的系统资源等因素的影响 ，根据这些影响因素SQLSERVER选择一种合适的锁定模式这个过程称动态锁定策略(我发现策略在MS很流行)，数据库引擎(还有印象我有引擎结构中介绍的存储引擎吧)动态的管理粒度和锁定模式，控制锁定与系统资源的最佳成本效率。一个范围内的锁定所要使用的系统资源肯定小，但是系统的并发性也就降低，如果选择小范围内的锁定，那管理锁定所使用的系统资源上升，然而并发性能却得到了淋漓发挥。
一般情况我们可使用系统缺省设置(行级锁定是系统缺省的)，让系统决定是否要进行锁定的升级。这样一来简化我们对库锁定的管理，系统根据实际情况平衡负载。
9、死锁
首先，我们得清楚死锁与等待是两回事。等待是当前进程所需要的资源让另一个进程排它了，只要另外一个进程释放，当时进程就可以继续执行(当然如果另外这个进程已经死锁那会进入无限期等待，但是这种情况一般不会发生，因为SQLSERVER会干预死锁的。另外我们还有一个锁定超时设置 ，这方面大家可以看联机丛书)。而死锁是发生在两个进程间，在没有人为干预两个锁定的进程是都无法继续工作的一种困境。另外一个显著的地方就是死锁一旦发生，SQLSERVER就会干预进来，我们所能感知比如接收到1205号错误，健壮的应用系统会人工干预1205错误，恰当的重新提交批处理，当1205错误发生没有终止的进程获得相应的资源并处理自己的事务直至释放资源，其实这种人为的干预潜在的又为死锁提供一个外在环境。当然我们前面写的一个过程也可以查询到相应的锁定信息。
接着，死锁是无法完全避免的。在一个并发的多用户系统，锁定、线程、内存、并行查询、MARS中死锁的发生是正常的、可以预见的，也是必然的。在我们能力范围内只能尽可能的在应用端或服务器上恰当的处理死锁，使得这种无法完全避免的事件给系统带来的影响降到最低。也就是我们应该明白：死锁是无法完全避免，但是我可以降低发生的次数。
第三，死锁是一种末日，没有人为干预时永远退不出这种状态。一个并发的多用户系统这种竞争资源的可能性是很大的，一有竞争就会有“矛盾”发生，双方等待对方释放自己所需要的资源，必然成了无限期等待，这种等待就是我们所说的死锁。我们通过上面的介绍知道这时SQLSERVER锁管理器会干预这个过程，试想如果没有SQLSERVER锁管理器的干预那么两个进程一根筯的结果就是无限期等待，对于应用系统来说就是一个末日。SQLSERVER2005更是提供了丰富的锁有关元数据，可以很方便的侦察出锁定信息，SQLSERVER锁管理器干预的结果就是根据牺牲品的优先等级及回滚代价，把优先级低和代价最小的进程当作牺牲品，杀掉这个进程并抛出1205错误。
第四，死锁大体分为三类：cycle死锁、conversion死锁、应用级死锁及不明死锁。
Cycle死锁：是进程双方持有的排它性资源是另外一方想要的资源。比如说进程A拥有TA的表级排它锁这时它又想申请TB的排它锁，同时进程B先拥有TB的排它锁定也想申请TA的表级排它锁定，这是进程A想要TB的排它锁但是已经让进程B锁定，进程B想要的TA锁定也已经让进程B锁定，这时死锁发生，下面我们来模拟一下：
create table ta(id int,col varchar(10))
create table tb(id int,col varchar(10))
go
查询一：
BEGIN TRAN
UPDATE TB SET COL = 'A'
WAITFOR DELAY '00:00:05'
UPDATE TA SET COL = 'B'
--COMMIT TRAN
查询二：
BEGIN TRAN
UPDATE TA SET COL = 'A'
WAITFOR DELAY '00:00:05'
EXEC SP_US_LOCKINFO –-在死锁前获取锁的信息
UPDATE TB SET COL = 'B'
--COMMIT TRAN
执行顺序先运行行查询一立即切换查询二执行，得死锁前的锁定信息，我们列出部分锁定信息：
由上图我们可以看得出，一开始查询一和查询二分别获得了TB TA上的锁定，行号为20和25的记录我们可以看到分别获得了排它锁定（我仅说明RID,在表和页上也会相应的锁定），因为查询一行执行，那么5S后，它想更新TA，这时阻塞发生，我们从记录26可以看到SPID＝53的进程想要获取表TA上行的更新锁定被SPID＝52的进程阻塞，而进入等待状，注意这时不是死锁哦，是等待哦，紧接查询二5S过了