SQL2005中的事务与锁定（七）

2019-11-03 08:36:10

字体：大中小

来源：转载

供稿：网友

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过了开始执行更新TB上的记录行，显然这个时候TB给进程53排它锁定，这是死锁发生，因为进程52等待53释放TB上的锁定，进程53在等待52释放资源，进入了抱死状态，这就是cycle锁定。这时我们会SQLSERVER参与了干预，查询二抛出错误并回滚事务：

消息1205，级别13，状态45，第8 行

事务(进程ID 52)与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。

为什么说它回滚呢，这是死锁发生时SQLSERVER自动处理的，不信你最后提交查询二的commit语句，你会发现报如下错（3902）：

消息3902，级别16，状态1，第1 行

COMMIT TRANSACTION 请求没有对应的BEGIN TRANSACTION。

conversion死锁：转换死锁发生在不同进程在查询相同的数据后准备尝试更新刚才查询的数据时，这时大家都持相同数据的共享锁定并都准备升级为更新锁，但是都因为对方不释放共享锁定而无法获取更新锁定，这是死锁发生，我们称这个为转换死锁。好下面我们模拟一下conversion死锁。

(约定：运行查询一后在3S内运行查询二)

查询一：

select @@spid –-53

set transaction isolation level repeatable read

begin tran

select * from ta

waitfor delay '00:00:03'



update ta

    set col = 'B'

where id = 1

exec sp_us_lockinfo -–结果见图conversion1

commit tran

查询二：

select @@spid –-52

set transaction isolation level repeatable read

begin tran

    select * from ta



    waitfor delay '00:00:05'

    exec sp_us_lockinfo  --结果见图conversion2

    update ta

       set col = 'B'

    where id = 1 –-系统检测到死锁，抛出1205错误

commit tran

图conversion1：

图conversion2：

查询二死锁1205信息：

消息1205，级别13，状态45，第7 行

事务(进程ID 52)与另一个进程被死锁在锁资源上，并且已被选作死锁牺牲品。请重新运行该事务。

应用级死锁及不明死锁：这类死锁有点特殊，除了绑定外我们可能通过动态管理视图或函数是无法侦测到锁定信息，显然锁管理器可能也无法干预它。我们所能看到可能是一个长期占用的资源锁定，而这种锁定潜在造成更多死锁发生。比如说同一个应用程序的多线程间、应用程序调用外部程序等如果最终再与数据库发生联系，那显然还是数据库两个进程间的死锁，如果与非数据库联系，那么这种依赖于其它程序的状态锁定只能是无限期的等待，除非人为干预SQLSERVER是干预不了的。

我们把SQLSERVER锁管理器无法侦测的死锁称不明死锁，这类死锁可以借助SSIS来模拟，比如我们要完成一项任务就是把未导出的数据通过SSIS生成文件，并把已经导出的数据做上标识。我们用一个过程模拟插入数据，并在事务提交前启动SSIS包完成数据导出并修改标识，这时我们如果想要在一个事务里完成所有工作那是不可能的，会造成事务一直运行，并且我们锁定管理器也无法侦测。

第五，锁管理器：在SQLSERVER中一有独立线程周期性的检查系统的死锁，当死锁发生时，死锁的检查周期缩小到毫秒级，直到死锁的频率降低再次恢复到默认的周期。如果侦测到锁定，管理器会权衡回滚的代价，并依据是否已经标识为回滚引起不明状态及牺牲品的优先级别选择牺牲品，杀掉进程并发送1205错误，这也就意思牺牲品所占的资源全部释放，这样其它相关的进程可以继续运行。

最后提一上我们先前提到的闩及自旋锁定，它们是预防死锁而不是解决死锁，这两种轻量级的锁定之所以能预防死锁是因为MS严谨的控制达到不需要管理死锁。

综上所述，死锁是无法完全避免的，对于SQLSERVER所能侦测的死锁还是比较容易处理的，恰当的做好出错后的处理使得对死锁相关的用户进程的影响降到最低。所谓的愉当就是接受到1205错误时应用程序应能够再次提交处理或提醒1205错误的用户进行相应处理。我们还可以做的一件事就是尽量回避死锁，回避死锁可以从下面几点出发：

l         事务尽可能的短，锁定时间就会短；

l         应用程序做好死锁发生后处理；

l         认识索引的重要性；

l         保证业务规则及执行顺序的合理性、可实现性；

l         根据业务规则选择合适隔离等级；

l         事务有始有终(dbcc opentran & set xaxt_abort on);

l         避免人为干扰事务的执行(不要在事务中人机交互过程)；

l         慎用锁定提示(Lock Hint)来改变锁定的粒度；

l         正确认识使用绑定；

锁定机制是一个很复杂的过程，它保证了并发下资源的正确、有序使用，在了解锁定的机制后对跟踪解决死锁是有相当的帮助。下面我们再梳理一下2005的行版本控制。

上一篇：如何在SQL Server 2005中为安装程序增加计数器注册表项值

下一篇：SQL Server2005 表分区三步曲