SQL2005中的事务与锁定(一到九整合版)

最新推荐文章于 2020-03-16 23:24:27 发布

htl258_Tony

最新推荐文章于 2020-03-16 23:24:27 发布

阅读量1w

点赞数

分类专栏：事务与锁文章标签： sql sqlserver null 数据库 sql server insert

事务与锁专栏收录该内容

11 篇文章 0 订阅

订阅专栏

出处：http://blog.csdn.net/happyflystone

终于定下心来写这个事务与锁定的文章，下笔后才发现真的很难写，交易中事务与锁定这个话题过于复杂，甚至有些还摸不着（就是通过DMV或DMF或其它工具你可能很难看到它的踪影），让人有点让人望而止步，但是既然说了要写，似乎不继续下去不是我的风格。在接下来的几篇文章（其实我也不知道要几篇）里我就事务与锁定这个话题写写，由SQL2005的并发模型引入事务，在事务的概念里展开锁定，本着先概念后实例的原则，和大家一起来学习，有不当之处希望大家指正。

一、并发及并发控制模型

对于这个我在<< SQL2005数据库引擎结构>>一文有所提及，你可以通过如下链接进行访问：SQL2005数据库引擎结构（三）并有一起的意思，显然就是多个的意思啦，光书面来理解并发就是多个东西同时发生，在数据库并发就是多个进程同时取、存数据库里数据的能力。着眼我们开发的系统，当然是激动态的并互不打架的并发用户进程越多并发能力就越强大啦，你想想看好多的网上购物系统，如果没有并发处理的能力，那么在上面登记的用户信息、商品有库存信息及用户帐户信息很难保证正确性和一致性，比如一个物品本身库存只有100个，结果如果100人同时在线进行预定，库存就有可能搞一个100－1的效果出来。

很显然对上述的例子我们希望一个进程在修改库存数据时必须阻止其它进程读或修改数据，或是正在读的用户进程限制其它活动的用户进程进行读或修改的操作，这样一来势必造成系统的并发性能下降，但是如果不采用这种办法又无法保证数据正确性和一致性。那怎么解决这个问题呢，办法只有通过不同的并发模式来管理这些并发事件。我们下面来理解并发控制的模式、并发下可能发生的非一致数据行为，即并发副作用，并由模式及数据行为引入事务及相关的5个隔离等级等概念，进而来理解不同隔离等级下并发实现的机理，显然我们自己也就可以回答上面这个问题了。

并发控制模式：一般并发控制模式有两种：积极并发（又称乐观并发）和消极并发（又称悲观并发）。积极并发是SQL2005才引入的新模式，在2005以前的版本其实只有唯一的并发模式即：消极并发。那什么是消极并发呢？消极并发就是SQLSERVER默认行为是进程以获取锁的方式来阻止其它进程对正在使用的数据进行修改的能力。对于数据库来说对数据修改的操作进程肯定很多，这些进程肯定都会去影响其它进程读取数据的能力，反之，对数据进行读时加上锁也一定会影响其它进程修改数据的能力，简而言之，就是读取与修改数据之间是冲突的、互相阻塞的。乐观并发是SQL2005利用一个行版本控制器的新技术来处理上述的冲突。行版本控制器在当前进程读取数据时生成旧版本的数据，使得其它请求读的进程能看到当前进程一开始读取时的数据状态，并且不受当前进程或其它进程对数据进行修改的影响。简而言之读与修改之间是不冲突的，但是修改与修改之间还是冲突的。

对于这两种并发模式两个进程同时请求数据修改必然会冲突的，除此以外的差别在于一个是在冲突发生前进行控制，另一个在冲突发生了进行协调处理。这好比生活一样，两种方式就是两种不同的人生，一种消极怠工一种积极向上。

二、并发下可能发生的并发副作用：丢失更新、脏读、不可重复读、幻影。

为了把这些可能发生的并发副作用说清楚，我们先“布置”一个场景：这是一个卖工艺石头的小商店，平时在前场完成交易，客户凭单据到后场领取石头，AMM和BMM是营业员，她们平时掌握库存数是通过大厅里的一块LED显示牌得之，并且在各自完成一笔交易后修改LED显示，以保证数据的实时性。在这个场景下我们来观察可能发生的行为：

1、丢失更新：

丢失更新估计是所有数据库用户都不想发生的情况，什么是丢失更新呢？丢失更新是当2个或两个以上的用户进程同时读取同样的数据后又企图修改原来的数据时就会发生。好在上述场景下，大厅LED显示牌显示当前库存1000，这时同时有两个客户上门了，AMM和BMM满面春风接待，比如AMM卖出1个，BMM呢卖出了10个，AMM处理完业务后赶紧把LED显示数修改为1000　－　1　＝　999个，几乎同一时间BMM处理完自己的业务后习惯性的把LED显示数修改为1000　－　10　＝　990　个，这时老板从后场过来，看着LED有点不爽，大吼一声：现在还有多少存货呀？，AMM说我卖了1个，BMM说我是10个，不过两个人都傻眼了，LED显示怎么是990呢？原来BMM在更改时把AMM做的更改搞丢了，这就是丢失更新。显然对老板和营业员来说都是必须回避不能发生的事。

2、脏读

很显然，在上面的例子里因为AMM和BMM事先因为不知道对方已经修改了柜台存货，所以才造成了存货数目显示错误，出了问题我们要想办法解决问题，英明的老板说了，你们随便哪个在谈一笔生意时先把客户意向购卖石头数扣掉，如果最后客户不要你再改回头，两个MM对老板的英明决定表示等赞同，可是问题还是发生了，怎么回事呢，还是假设柜台存货1000个石头，AMM有一笔生意正在谈着，顾客意向要600块石头，AMM赶紧把LED显示修改为400。这时BMM也很兴奋因为她已经谈成一笔700块石头的生意，所以呢BMM抬头一看，好嘛，还有400块可卖，完了BMM的生意做不成了，只好向客户表达歉意。BMM只能让老板进货，可是老板一看LED显示还有1000块怎么你的700块生意做不成了呢？哦，因为最后AMM的600块生意没做成。嘿嘿，也就是BMM错误的读取了AMM修改的数据，完成了一次“脏读”操作。脏读也就是一个用户进程读取了另一个用户进程修改过但没有正式提交的数据，这时导致了数据不一样的情形发生了。因为A用户进程是无法确认另一个B用户进程在自己提交数据前是否修改过数据，这是数据库系统默认情况下必须回避的。

3、不可重复读

不可重复读又称不一致分析，不过，个人以为似乎不一致分析更让人好理解一点，但是大部分地方称不可重复读。不可重复读是指一个用户进程两次读取数据得到不同样的数据。比如那个英明的老板吧，他知道要盘点，掌握库存的变化，忙得満头大汗，终于计出库存数来，比如说1000吧，或是当他跑到大厅一看LED显示牌却只有了900，显然这一次的检查库存的过程中两次得到库存数不一样，原因就是AMM在老板从后场走到前场的过程中做了一担生意，卖出100块。嘿嘿，老板气又不是不气又不是，这AMM真可爱，做生意挺两下呀！显然在一个用户进程两次读取数据间隔内另一个用户进程修改了数据，这就是不可重复读。

4、幻影

幻影，嘿嘿，我们不是经常无视BS自己的人吗？你无视他并不代表他不BS你吧，这个BS你的人就成了幻影，嘿嘿开个玩笑。这种情况多数在查询带谓词时结果集内部分数据变化的时候发生，如果谓词限定下在一个交易里两次同一查询的结果集不同，那些不同的行或行集就是幻影。比方说英明的老板到大厅走走，顺便请大家吃饭，数数人数，BMM，。。。。一路数过去，发现有10人，呵呵，正好一桌，，通知好她们后老板回办会室拿人民币，回到前场看见AMM，再一数11人，晕，刚才怎么看到AMM　？AMM也知道了老板请客没数到他，很是生气，这时AMM就成了幻影。

以上是四种并发副作用只是一个交易事务里或事务间可能发生的异常的非一致的数据行为（记好并发副作用和不一致的数据行为术语，这在以后会经常提及），其实还是有好多的行为是我们所期望的，那么我们期望的行为是什么呢，下面我们在事务里来介绍。我们可以通过隔离级别来设定一个合适级别以决定上述上种数据行为哪些是允许的。那什么是交易事务，什么又是隔离等级呢？

三、事务

事务是数据库一笔交易的基本单元，存于两种并发模型中。又分为显式事务和隐式事务。显式事务是显式的开始一个事务并显式的滚回或提交事务，除了显式的事务还有隐式的了，隐式事务是数据库自己根据情况完成的事务处理，如单独的select、update、delete、select语句。

作为一个事务，它能保证数据库的数据一致性及重做。提到事务不得不提及事务的ACID属性：原子性、一致性、隔离性及持久性。不管是显式还是隐式的，都必须维持这四个属性。

原子性：一个事务是一个整体，要不全部提交，要不全部中止。意思就是要不全部成功提交到数据，要不全部回滚恢复事务开始前的状态。比方我们做一个入库操作，在这个事务里，审核入库单和修改库存作为一个整体，要不单据变成审核过同时库存增加相应的值，要不就是单据未审核同时库存不变。

一致性：一致性要求事务保证数据在逻辑上正确，处理的结果不是一个不确定的状态，什么是不确定状态呢，比如说我们完成一个库存减少的操作，如果没有一个出货单据那么这个库存的当前修改就是一个不确定状态，因为你无法知道减少的东东到哪儿去了。

隔离性：这个隔离和锁定有关，以后在说锁的过程中会提到这些，你先记住这个就行。

持久性：持久很显然是要求正确提交的修改必须保证长久存在，不会因为关机或掉电把这笔交易丢掉。进行中的事务发生故障那事务就完全撤销，像没有发生一样，如果事务提交的确认已经反馈给应用程序发生故障，那么这些日志利用先写技术，在启动恢复阶段自动完成相应的动作保证事务的持久性。（这个在前面的引擎组件有过介绍哦。）

四、隔离等级

首先来说说隔离，隔离是一个事务必须与其他事务所进行的资源或数据更改相隔开，显然隔离等级就是相隔的程度了吧。在说隔离级别不得不提及锁的概念，但是在本单不提及锁，在以后听章节里再作说明，大家只要有个印象就行。在这儿我们必须明白两件事：

1，隔离级别不会影响进程获得数据修改的排它锁，并且这个锁会保存到事务结束。相对于读进程来说，隔离级别就是对读操作的一个保护级别，保护读操作受其它事务影响的程序。

2，较低的隔离级别可以增强许多用户同时访问数据的能力，但也增加了用户可能遇到的并发副作用（例如脏读或丢失更新）的数量。相反，较高的隔离级别减少了用户可能遇到的并发副作用，却需要太多的系统资源及一个事务阻塞其他事务的可能性。

应平衡应用程序的完整性要求与相应隔离级别的系统开销，在此基础上选择相应的隔离级别。最高隔离级别（可序列化）保证事务在每次重复读取操作时都能准确检索到相同的数据，但需要通过执行某种级别的锁定来完成此操作，而锁定可能会影响其他用户进程。最低隔离级别（未提交读）可以检索其他事务已经修改但未提交的数据。在未提交读中，所有并发副作用都可能发生，但因为没有读锁定或修改阻塞读取，所以开销最少。

不同的隔离级别决定我们有哪些数据副作用可以发生，而并发模型决定不同隔离等级下如何来限制这些数据行为或如何协调这数据行为。好，那我们来关注一下不同隔离等级下如何限制这些行为的发生。

未提交读（uncommitted Read）：字面理解一下，修改了的未提交数据可以读取。准确点：一个用户进程可以读取另一个用户进程修改却未提交的数据。SQL SERVER对这个等级下的读操作不需要获得任何锁就可以读取数据，因为不需要锁所以不会和其它任何进程互相阻塞，自然而然能读取其它进程修改了的却未提交数据。显然这不是我们理想的一种模式，但是它却有了高并发性，因为读操作没有锁不会影响其它进程的读或写操作。在这种级别下，除了丢失更新（上一讲中的数据可能发生的行为）外，其它行为都有可能发生，冒着数据不一致的风险来避免修改的进程阻塞读取的进程，事务的一致性肯定是得不到保障，显然这是消极并发模式下的回避阻塞频繁的一种解决方案。未提交读那肯定是不适合于股票、金融系统的，但是在一些趋势分析的系统里，要求的只是一种走向，准确性可以不是那么严格时，这个级别因并发性能超强成为首选。

已提交读(Read Committed)：它和未提交读相反，已提交读级别保证一个进程不可能读到另一个进程修改但未提交的数据。这个级别是引擎默认的级别，也是2005乐观并发模式下支持的级别，也就是说已提交读可是乐观的也可以是悲观的，那究竟当前库是属于哪个并发模型下的已提交读呢，这取决于一个READ_COMMITED_SNAPSHOT数据库配置项，并且缺省是悲观并发控制的。这个配置项决定已提交读级别下事务使用锁定还是行版本控制，并很显然行版本控制是乐观并发模式，锁定是悲观并发模式。我们来点角本看看：

--设置已提交读隔离使用行版本控制

ALTER DATABASE testcsdn SET READ_COMMITTED_SNAPSHOT ON

--查看当前已提交读隔离并发模型

select name,database_id,is_read_committed_snapshot_on from sys.databases

name database_id is_read_committed_snapshot_on

-------------------- ----------- -----------------------------

master 1 0

tempdb 2 0

model 3 0

msdb 4 0

ReportServer$SQL2005 5 0

ReportServer$SQL2005TempDB 6 0

TestCsdn 7 1 --current

(7 行受影响)

--设置已提交读隔离使用锁定

ALTER DATABASE testcsdn SET READ_COMMITTED_SNAPSHOT OFF

--查看已提交读隔离并发模型

select name,database_id,is_read_committed_snapshot_on from sys.databases

name database_id is_read_committed_snapshot_on

-------------------- ----------- -----------------------------

master 1 0

tempdb 2 0

model 3 0

msdb 4 0

ReportServer$SQL2005 5 0

ReportServer$SQL2005TempDB 6 0

TestCsdn 7 0 --curret

(7 行受影响)

已提交读在逻辑上保证了不会读到不实际存在的数据。悲观并发下的已提交读，当进程要修改数据时会在数据行上申请排它锁，其它进程（无论是读还是写）必须等到排它锁释放才可以使用这些数据。如果进程仅是读取数据时会使用共享锁，其它进程虽然可以读取数据但是无法更新数据，必须等到共离锁释放（共享锁在数据处理完即释放，比如行共享锁在当前数据行数据处理完就自动释放，不会在整个事务内保留发。）。乐观并发的已提交读，也确保不会读到未提交的数据，不是通过锁定的方式来实现，而是通过行版本控制器生成行的提前交的数据版本，被修改的数据虽然仍然锁定，但是其它进程可以可以读取更新前版本数据。

可重复读（Repeatable Read）：这也是一个悲观并发的级别。可重复读比已提交读要求更严格，在已提交读的基础上增加了一个限制：获取的共享锁保留到事务结束。在这个限制下，进程在一个事务里两交次读取的数据一致，也就是不会读取到其它进程修改了数据。在这儿我们提到共享锁会保留到事务结束，那得申明一下无论哪种级别及并发模型，排它锁是一定要保留到事务结束的。在可重复读级别共享锁同样也会保留到事务结束。那么这种对数据安全的保证是通过增加共享保留的开销为代价的，也就是只要开始一个事务，其它用户进程是不可能修改数据的，显而易见的系统的并发性和性能必然下降。这似乎是我们想像中的一种级别，虽然这个级别暂时无法回避幻影读，而且我们也默许并发及性能下降，那只有对程序员对事务的控制有严格的要求：事务要短并尽量不要人为因素的干扰，减少潜在的锁竞争。

快照(SnapShot)：乐观并发级别。这是2005新增加的一个隔离级别。快照级别与使用乐观并发的已提交读差不多，差别在于行版控制器里的数据版本有多早，这个在以后讲锁时再说。这个级别保证了一个事务读取的数据是事务开始时就在数据库逻辑上确认并符合一致性的数据。读操作不会要求共享锁定，如果要求的数据已经排它，就会通过行版本控制器读取最近的符合一致性的数据。

可串行化：是目前最严谨、最健壮的一个级别，属于悲观并发。它防止幻影的发生，回避了以前所有意外行为的发生。可串行化意味着系统按进程进入队列的顺序依次、序列化的执行的结果与事务同时运行得到一致的结果。这个最健壮的级别显然共享锁也是随事务开始随事务结束，并通过锁定部分不存在的数据（即索引键范围锁定）来回避幻影的发生。

在前面的两篇里我从纯理论上把事务相关的知识作了一个梳理，有人看了一定觉得无味了吧，好这一篇我们加入一点T-SQL语句把前面所说有东东关联起来，我们人为产生锁定来理解不同的意外数据行为在不同隔离等级下的表现，顺便再重温一下意外数据行及隔离等级，让大家对交易事务有一个直观的认识。

在进行实例前不得不先介绍一点锁的知识，注意这儿只是简单的说一下，不作深入讨论。我们根据用户访问资源的行为先归纳出几种锁，这几种锁在下面的实例里会出现，它们为：共享锁定、排它锁定、更新锁定及用意向这个限定词限定的三种锁(意向共享、意向排它、意向更新)，当当然还有其它的模式，我们在下一篇再说。意向锁的存在是解决死锁的发生，保证进程在申请锁定前确定当前数据是否存在不兼容性的锁定。

先对上面提到的锁作一个简单的描述，更详细的下面再说。

共享锁定发生在查询记录时，直观就是我们select啦，但是并不是只有select才有共享锁定。一个查询记录的语句必须在没有与共享锁定互斥锁定存在或等待互斥锁定结束后，才能设置共享锁定并提取数据(互斥不互斥就是锁的兼容性，这在以后再说明)。

排它锁定发生在对数据增加、删除、修改时，事务开始以后语句申请并设置排它锁定(前提是没有其它互斥锁定存在)，以明确告知其它进程对当前数据不可以查询或修改，等待事务结束后其它进程才可以查询或修改。

更新锁定是一个介于共享与排它之间的中继锁定，比如我们带where条件的update语句，在查询要更新的记录时是设置共享锁定，当要更新数据时这时锁定必须由共享锁定升级成更新锁定继而升级为排它锁定，当排它锁定设置成功才可以进行数据修改操作。显然也是要要求在锁升级的过程中没有互斥锁定的存在。简单的理解更新锁定是一个中继闸一样，把升级成排它锁定进程“序列化”，以解决死锁。最后重点说明一下，数据更新阶段是要对数据排它锁定不是更新锁定，不要被字面意思训导哦。

最后说一下在上述锁定模式下的互斥，共享锁定只与排它锁定互斥，更新锁定只与共享锁定不互斥。

在进行具体实例前我们一定要有一个工具来对我们实例过程进行监控，好，下面我写了一个过程，在需要时直接调用就行，过程如下：

Create Proc sp_us_lockinfo

---------------------------------------------------------------------

-- Author : HappyFlyStone

-- Date : 2009-10-03 15:30:00

-- BLOG : http://blog.csdn.net/happyflystone

-- 申明：请保留作者信息，转载注明出处

---------------------------------------------------------------------

BEGIN

SELECT

DB_NAME(t1.resource_database_id) AS [数据库名],

t1.resource_type AS [资源类型],

-- t1.request_type AS [请求类型],

t1.request_status AS [请求状态],

-- t1.resource_description AS [资源说明],

CASE t1.request_owner_type WHEN 'TRANSACTION' THEN '事务所有'

WHEN 'CURSOR' THEN '游标所有'

WHEN 'SESSION' THEN '用户会话所有'

WHEN 'SHARED_TRANSACTION_WORKSPACE' THEN '事务工作区的共享所有'

WHEN 'EXCLUSIVE_TRANSACTION_WORKSPACE' THEN '事务工作区的独占所有'

ELSE ''

END AS [拥有请求的实体类型],

CASE WHEN T1.resource_type = 'OBJECT'

THEN OBJECT_NAME(T1.resource_ASsociated_entity_id)

ELSE T1.resource_type+':'+ISNULL(LTRIM(T1.resource_ASsociated_entity_id),'')

END AS [锁定的对象],

t4.[name] AS [索引],

t1.request_mode AS [锁定类型],

t1.request_session_id AS [当前spid],

t2.blocking_session_id AS [锁定spid],

-- t3.snapshot_isolation_state AS [快照隔离状态],

t3.snapshot_isolation_state_desc AS [快照隔离状态描述],

t3.is_read_committed_snapshot_on AS [已提交读快照隔离]

FROM

sys.dm_tran_locks AS t1

left join

sys.dm_os_waiting_tasks AS t2

t1.lock_owner_address = t2.resource_address

left join

sys.databases AS t3

ON t1.resource_database_id = t3.database_id

left join

(

SELECT rsc_text,rsc_indid,rsc_objid,b.[name]

FROM

sys.syslockinfo a

JOIN

sys.indexes b

ON a.rsc_indid = b.index_id and b.object_id = a.rsc_objid) t4

ON t1.resource_description = t4.rsc_text

END

调用示例：exec sp_us_lockinfo

exec sp_us_lockinfo

drop proc sp_us_lockinfo

最后介绍一个隔离等级设置命令：

SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED

| READ COMMITTED

| REPEATABLE READ

| SNAPSHOT

| SERIALIZABLE}[;]

好，下面开始实例“快乐”之旅了。

五、隔离等级实例

测试数据准备：

CREATE DATABASE testcsdn;

CREATE TABLE TA(TCID INT PRIMARY KEY,TCNAME VARCHAR(20))

INSERT TA SELECT 1,'AA'

INSERT TA SELECT 2,'AA'

INSERT TA SELECT 3,'AA'

INSERT TA SELECT 4,'BB'

INSERT TA SELECT 5,'CC'

INSERT TA SELECT 6,'DD'

INSERT TA SELECT 7,'DD'

约定：以下提及的查询N，都是打开一个新连接执行查询

1、未提交读（uncommitted Read）

概念回顾：未提交读是最低等级的隔离，允许其它进程读取本进程未提交的数据行，也就是读取数据时不设置共享锁定直接读取，忽略已经存在的互斥锁定。很显然未提交读这种隔离级别不会造成丢失更新，但是其它意外行为还是可以发生的。它和select 加锁定提示NOLOCK效果相当。

测试实例：

查询一：

SELECT * FROM TA WHERE TCID = 1

BEGIN TRAN

UPDATE TA

SET TCNAME = 'TA'

WHERE TCID = 1

--COMMIT TRAN --Don't commit

SELECT * FROM TA WHERE TCID = 1

SELECT @@SPID

tcid Tcname

----------- --------------------

1 AA

(1 行受影响)

tcid Tcname

----------- --------------------

1 TA

(1 行受影响)

SPID

------

(1 行受影响)

查询二：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM TA WHERE TCID = 1

tcid Tcname

----------- --------------------

1 TA

(1 行受影响)

--显然未提交读模式我们读到SPID=54未提交的数据。

查询三：

SELECT * FROM TA WHERE TCID = 1

--查询一直进行中…… 无结果

--因为缺省下已提交读级别，所以修改数据设置了排它锁定必须等到SPID=54的事务结束

查询四：

--查看当前的锁定信息

exec sp_us_lockinfo

这个时候如果我们回头到查询一里执行commit tran ，你会发现查询三会得到结果，并且是查询一修改后的结果，如果你改用rollback ,那么结果就是原来的值不变，这个你们自己再测试。

1、已提交读(Read Committed)

概念回顾：已提交读是SQL SERVER的缺省隔离级别，悲观模型下是用锁定，乐观模型下使用行版本控制器。这个设置可以通过SET READ_CIMMITTED_SNAPSHOT来修改。在悲观模型下对于读取来说设置共享锁定仅阻止排它锁定，并在数据读取结束自动释放，其它进程方可进行修改操作。也就是说读不会阻止其它进程设置共享及更新锁定，仅阻止排它锁定。在悲观模型下对于修改数据来说设置排锁定阻止所有锁定请示，必须等到排它锁定释放。这个级别的隔离解决了脏读的意外行为。

A、 READ_COMMITTED_SNAPSHOT为OFF的情况(缺省)

I、读数据测试

查询一：

BEGIN TRAN

--用锁定提示模拟共享锁定，并强制共享锁定持续到事务结束

SELECT * FROM TA with(holdlock) WHERE TCID = 1

--COMMIT TRAN --Don't commit

SELECT @@SPID

tcid Tcname

----------- --------------------

1 CA

(1 行受影响)

------

(1 行受影响)

查询二：悲观模型下已提交读级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

UPDATE TA

SET TCNAME = 'TA'

WHERE TCID = 1

--查询一直没有结果，显然我们验证了共享锁定阻止了排它锁定。

查询三：

exec sp_us_lockinfo

--结果大家自己运行看结果。

II、修改数据测试

查询一：

SELECT * FROM TA WHERE TCID = 1

BEGIN TRAN

UPDATE TA

SET TCNAME = 'READ COMMITTED LOCK'

WHERE TCID = 1

--COMMIT TRAN --Don't commit

SELECT @@SPID

tcid Tcname

----------- --------------------

1 TA

(1 行受影响)

------

(1 行受影响)

查询二：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT * FROM TA WHERE TCID = 1

--查询一直进行中……被锁定无结果

--修改数据设置了排它锁定必须等到SPID=54的事务结束

查询三：

exec sp_us_lockinfo

A、 READ_COMMITTED_SNAPSHOT为ON的情况

先修改当前当前库的READ_COMMITTED_SNAPSHOT为ON

ALTER DATABASE TESTCSDN

SET READ_COMMITTED_SNAPSHOT ON

查询一：

SELECT * FROM TA WHERE TCID = 1

BEGIN TRAN

UPDATE TA

SET TCNAME = 'READ COMMITTED SNAP'

WHERE TCID = 1

--COMMIT TRAN --Don't commit

SELECT @@SPID

TCID TCNAME

----------- --------------------

1 AA

(1 行受影响)

------

(1 行受影响)

查询二：因为启用行版本控制器来锁定数据，保证其它进程读取到虽然被排它锁定但在事务开始前已经提交的保证一致性的数据。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT * FROM TA WHERE TCID = 1

TCID TCNAME

----------- --------------------

1 AA

(1 行受影响)

查询三：

exec sp_us_lockinfo

3、可重复读（Repeatable Read）

概念回顾：可重复读等级比已提交读多了一个约定：所有的共享锁定持续到事务结束，不是在读取完数据就释放。数据被设置了共享锁定后其它进程只能进行查询与增加不能更改，显然这个级别的隔离对程序有了更高的要求，因为可能因长时间的共享锁定影响系统的并发性能，增加死锁发生的机率。很显然是解决了不可重复读的意外行为。

数据测试：

查询一：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRAN

SELECT * FROM TA WHERE TCID = 1 --可重复查询，并且读不到未提交的数据

--COMMIT TRAN --Don't commit

SELECT @@SPID

tcid Tcname

----------- --------------------

1 READ COMMITTED LOCK

(1 行受影响)

------

(1 行受影响)

查询二：

INSERT TA SELECT 9,'FF'

(1 行受影响)

SELECT * FROM TA-- WITH(UPDLOCK)

WHERE TCID = 1

tcid Tcname

----------- --------------------

1 READ COMMITTED LOCK

(1 行受影响)

UPDATE TA

SET TCNAME = 'READ COMMITTED REP'

WHERE TCID = 1

--查询一直进行中……被锁定无结果

--修改数据设置了排它锁定必须等到SPID=52的事务结束

查询三：

很显然查询三中的S，Is(共享及意向共享)锁定一直没消失，因为查询一的事务没有结束，在查询二里可以发现插入与读取(包括在查询一里再次select)是不影响的，并且读取的是未修改前的数据。

4、快照(SnapShot)

概念回顾：这是SQL SERVER2005的新功能，启用快照后所有的读操作不再受其它锁定影响，读取的数据是通过行版本管制器读取事务开始前逻辑确定并符合一致性的数据行版本。这个级别隔离与已提交读的行版管理器的差别仅是行版本管理器里历史版本数据多久。

测试数据：

查询一：

ALTER DATABASE TESTCSDN

SET ALLOW_SNAPSHOT_ISOLATION ON

SELECT * FROM TA WHERE TCID = 1 --OLD数据

BEGIN TRAN

UPDATE TA

SET TCNAME = 'SNAPSHOT'

WHERE TCID = 1

--COMMIT TRAN --Don't commit

SELECT @@SPID

tcid Tcname

----------- --------------------

1 READ COMMITTED REP

(1 行受影响)

------

(1 行受影响)

查询二：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT * FROM TA WHERE TCID = 1

tcid Tcname

----------- --------------------

1 READ COMMITTED REP

(1 行受影响)

查询三：

exec sp_us_lockinfo

5、可串行化：

概念回顾：这是交易里最健壮最严谨最高级别的隔离。通过索引键范围完全隔离其它交易的干扰，此隔离和select与锁定提示HOLDLOCK效果一样。这个级别基本解决所有的意外行为，显而易见的是并发性能下降或系统资源的损耗上升。

测试数据：

查询一：

DROP TABLE TB

CREATE TABLE TB (ID INT Primary Key, COL VARCHAR(10))

INSERT INTO TB SELECT 1,'A'

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM TB WHERE ID BETWEEN 1 AND 5--OLD数据

--COMMIT TRAN --Don't commit

SELECT @@SPID

ID COL

----------- ----------

1 A

(1 行受影响)

------

(1 行受影响)

查询二：

SELECT * FROM TB WHERE ID = 1

ID COL

----------- ----------

1 A

(1 行受影响)

INSERT TB SELECT 2,'EE'

--查询一直进行中……被锁定无结果

--修改数据设置了排它锁定必须等到SPID=52的事务结束

UPDATE TB

SET COL = 'SERIALIZABLE'

WHERE ID = 1

--查询一直进行中……被锁定无结果

--修改数据设置了排它锁定必须等到SPID=52的事务结束

查询三：

exec sp_us_lockinfo

可以明显的发现出现大量的索引键范围(RangeS-S……)，确保在当前事务未结束之前另外的用户进程无法在索引键范围内插入数据，防此幼影意外行为的发生。可串行化后，除了数据能查询外，不可以修改、增加、删除索引键范围内的任意数据行，必须等到索引上的锁定释放。

结论：通过以的一些测试，我们知道通过隔离等级我们可以控制并发时意外行为，在实际操作的过程中我们可以用激活事务来控制锁的粒度、影响范围，以达到控制并发机制下数据的逻辑正确及数据一致性。最后我们发现通过锁定提示(LOCK HINTS)也可以改变表级锁定类型、锁定周期，达到和设置隔离等级类似的功能。

好，到目前为止我们把事务相关的东西介绍得差不多了，并且在提前介绍了部分的锁定，在下面的文章里我们重点对锁进行介绍。

在生产交易过程中多个用户同时访问数据是不可以避免的，通过不同的隔离等级对资源与数据进行各种类型的锁定保护并在适当时候释放保证交易的正确运行，使得交易完整并保证数据的一致性。不管是锁定还是行版本控制器都决定着商业逻辑的流畅、事务的完整、数据的一致。所以我们要根据实际情况进行部署，在并发性性能与资源管理成本之间找到平衡点，怎样才能找到这个平衡点呢，那我们就得对SQLSERVER如何管理资源与锁有一个了解，SQLSERVER不但管理锁定，还要管理锁定模式之间的兼容性或升级锁定及解决死锁问题。通过SQL SERVER强大的、细致的锁定机制，使得并发性能得到最大程度的发挥，但是使用尽可能少的系统资源也是我们最希望的。

SQLSERVER本身有两种锁定体系：一种是对共享数据的锁定，这种锁定就是我们大部时间讨论的锁定；一种是对内部数据结构及处理索引，这是一种称为闩锁的轻量级锁，比第一种锁定少耗资源，在sys.dm_tran_locks中是看不到这种锁的信息。我们在数据分页上放置物理记录或压缩、折分、转移分页数据时，这种锁就会发生了。我们在前面一直在说数据的逻辑一致性，那这种逻辑上的一致性就是通过锁定来控制的，而我们新提到的闩是保证物理的一致性(这种闩是系统内部使用所以我们不重点讨论了)。

并发访问数据时，SQL Server 2005使用下列机制确保事务完整并维护数据的一致性：

l 锁定
每个事务对所依赖的资源（如行、页或表）请求不同类型的锁。锁可以阻止其他事务以某种可能会导致事务请求锁出错的方式修改资源。当事务不再依赖锁定的资源时，它将释放锁。

l 行版本控制
当启用了基于行版本控制的隔离级别时，数据库引擎将维护修改的每一行的版本。应用程序可以指定事务使用行版本查看事务或查询开始时存在的数据，而不是使用锁保护所有读取。通过使用行版本控制，读取操作阻止其他事务的可能性将大大降低。

锁定和行版本控制可以防止用户读取未提交的数据，还可以防止多个用户尝试同时更改同一数据。如果不进行锁定或行版本控制，对数据执行的查询可能会返回数据库中尚未提交的数据，从而产生意外的结果。

最后说一下锁的粒度与并发性能是矛盾的，但是对管理锁定的成本却是有利的，粒度越大并发性能下降，粒度越小管理锁定成本越大。用图示例一下：

六、锁定

1、锁粒度和可锁定资源

SQL Server2005 具有多粒度锁定，允许一个事务锁定不同类型的资源。为了尽量减少锁定的开销，数据库引擎自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以提高并发度，但开销较高，因为如果锁定了许多行，则需要持有更多的锁。锁定在较大的粒度（例如表）会降低了并发度，因为锁定整个表限制了其他事务对表中任意部分的访问，但其开销较低，因为需要维护的锁较少。

SQL SERVER可以锁定表、分页、行级、索引键或范围。在这我提醒大家一下，对于聚集索引的表，因为数据行就是索引的叶级，所以锁定是键锁完成而不是行锁。

数据库引擎通常必须获取多粒度级别上的锁才能完整地保护资源。这组多粒度级别上的锁称为锁层次结构。例如，为了完整地保护对索引的读取，数据库引擎实例可能必须获取行上的共享锁以及页和表上的意向共享锁。

下表列出了数据库引擎可以锁定的资源：

查询一：

SELECT *

FROM MASTER..SPT_VALUES WHERE TYPE = 'LR'

name number type low high status

--------------- ----------- ---- ------- --------- -----------

LOCK RESOURCES 0 LR NULL NULL 0

NUL 1 LR NULL NULL 0

DB 2 LR NULL NULL 0

FIL 3 LR NULL NULL 0

TAB 5 LR NULL NULL 0

PAG 6 LR NULL NULL 0

KEY 7 LR NULL NULL 0

EXT 8 LR NULL NULL 0

RID 9 LR NULL NULL 0

APP 10 LR NULL NULL 0

MD 11 LR NULL NULL 0

HBT 12 LR NULL NULL 0

AU 13 LR NULL NULL 0

(13 行受影响)

备注：

RID RID 锁定堆中行的行标识符

KEY KEY 序列化事务中的键范围行锁

PAG PAGE 数据或索引页面，8K为单位

EXT EXTENT 数据或索引页面，连续的8*page

HBT HOBT 堆或B树，保护索引或堆表页堆的锁

TAB TABLE 整个表，包括数据及索引

FIL FILE 数据库文件

APP APPLICATION 应用程序资源

MD METADATA 元数据

AU ALLOCATION_UNIT 分配单元

DB DATABASE 数据库

注：SPT_VALUES这个大家不陌生吧，好多人用它生成一个连续的ID号的啦，当时也有人问这个表的用途，现在发现它的作用了吧。下面我们还会使用到。

2、锁定模式

我们在前提面前到的共享锁定、更新锁定、排它锁定，这是为了配合前面的事务而提及的，那么SQL SERVER2005一共有多少锁定模式呢？我们通过一个简单的查询来列表：

查询：

SELECT *

FROM MASTER..SPT_VALUES WHERE [TYPE] = 'L'

NAME NUMBER TYPE LOW HIGH STATUS

---------------- ----------- ---- ----------- ----------- -----------

LOCK TYPES 0 L NULL NULL 0

NULL 1 L NULL NULL 0

SCH-S 2 L NULL NULL 0

SCH-M 3 L NULL NULL 0

S 4 L NULL NULL 0

U 5 L NULL NULL 0

X 6 L NULL NULL 0

IS 7 L NULL NULL 0

IU 8 L NULL NULL 0

IX 9 L NULL NULL 0

SIU 10 L NULL NULL 0

SIX 11 L NULL NULL 0

UIX 12 L NULL NULL 0

BU 13 L NULL NULL 0

RANGES-S 14 L NULL NULL 0

RANGES-U 15 L NULL NULL 0

RANGEIN-NULL 16 L NULL NULL 0

RANGEIN-S 17 L NULL NULL 0

RANGEIN-U 18 L NULL NULL 0

RANGEIN-X 19 L NULL NULL 0

RANGEX-S 20 L NULL NULL 0

RANGEX-U 21 L NULL NULL 0

RANGEX-X 22 L NULL NULL 0

(23 行受影响)

我们可以看到一共有22种锁定模式，我简单的对上述[NAME]进行简单的枚举：

l S --- 共享锁定(Shared)

l U --- 更新锁定(Update)

l X --- 排它锁定(Exclusive)

l I --- 意向锁定(Intent)

l Sch --- 架构锁定(Schema)

l BU --- 大量更新(Bulk Update)

l RANGE --- 键范围(Key-Range)

l 其它是在上述锁定的变种组合，比如IS --- 意向共享锁定

其实对这些锁定模式没什么介绍，大家可以参考联机帮助：访问和更改数据库数据 -> 锁定和行版本控制 -> 数据库引擎中的锁定。其实这些锁定模式在前一篇基本都有出现，大家可以在看下面的定义再回头看看前一篇的相关内容。下面我就简单的说说：

共享锁（S 锁）

当我们查询(select)数据时SQL SERVER2005会尝试在数据上申请共享锁定，但是前提是在当前的数据上不存在与共享锁定互斥的锁定。资源上存在共享锁时，任何其他事务都不能修改数据但是可以读取数据。读取操作一完成，就立即释放资源上的共享锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示(HOLDLOCK)保留共享锁。

更新锁（U 锁）

更新新是一种介于共享锁与排它锁之间的锁定，是一种中继锁定，像一个中间闸门，把从共享锁定转为排它锁的请求进行排队，有效的防止常见的死锁。在可重复读或可序列化事务中，一个事务读取数据 [获取资源（页或行）的共享锁（S 锁）]，然后修改数据 [此操作要求锁转换为排他锁（X 锁）]。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁（X 锁）。共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排他锁（X 锁）以进行更新。由于两个事务都要转换为排他锁（X 锁），并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。而有了更新锁则可避免这种潜在的死锁问题，在查找到要更新的数据后SQL SERVER首先给数据设置更新锁定，因为共享锁定与更新锁定不互斥，在其它事务设置共享锁定时依然可以设置更新锁定，继而因更新锁定斥的，如果其它要修改数据的事务必须等待。如果事务修改资源，则更新锁转换为排他锁（X 锁）。

排他锁（X 锁）

排他锁可以防止并发事务对资源进行访问。使用排他锁（X 锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。

数据修改语句（如 INSERT、UPDATE 和 DELETE）合并了修改和读取操作。语句在执行所需的修改操作之前首先执行读取操作以获取数据。因此，数据修改语句通常请求共享锁和排他锁。例如，UPDATE 语句可能根据与一个表的联接修改另一个表中的行。在此情况下，除了请求更新行上的排他锁之外，UPDATE 语句还将请求在联接表中读取的行上的共享锁。

排他锁定随事务结束而释放。

意向锁（I锁）

数据库引擎使用意向锁来保护共享锁（S 锁）或排他锁（X 锁）放置在锁层次结构的底层资源上。意向锁之所以命名为意向锁，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。

意向锁有两种用途：

l 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。

l 提高数据库引擎在较高的粒度级别检测锁冲突的效率。

例如，在该表的页或行上请求共享锁（S 锁）之前，在表级请求共享意向锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排他锁（X 锁）。意向锁可以提高性能，因为数据库引擎仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁包括意向共享 (IS)、意向排他 (IX)、意向排他共享 (SIX)、意向更新 (IU)、共享意向更新 (SIU ，S和 IU 锁的组合)、更新意向排他 (UIX，U 锁和 IX 锁的组合)。

在这儿的SIX，SIU，UIX我们可以理解成一种转换锁定，并不是由SQLSERVER直接申请的，是由一种模式向另一种模式转换时中间状态。比如说SIX表示一种正持有共享锁定的进程正在企图申请意向排它锁定，或是这样理解一个持有共享锁定的资源中有部分分页或行被另一个进程的排它锁定锁定了。其它同理可以理解。

为了更好的说明一点，大家先看一个图：

这是我在TA表上加Where条件的一个更新动作，然后通过我以前写的一个工具：sp_us_lockinfo查看锁的信息，其实我的update只是影响一个行记录，但是我们发现有三个锁存在，只要当前事务不结束，其它事物对这个表申请不管是页面的锁定还是表级的锁定一定会与现在的表或页意向锁冲突，进而发生阻塞，而且我们在前面的隔离等级的实例中也有例子，你会发现它的请求状态是WAIT 而不是GRANT。

架构锁（架构修改锁 Sch-M 锁、架构稳定性锁Sch-S 锁）

执行表的数据定义语言 (DDL) 操作（例如添加列或删除表）时使用架构修改锁。在架构修改锁起作用的期间，会防止对表的并发访问。这意味着在释放架构修改锁（Sch-M 锁）之前，该锁之外的所有操作都将被阻止。

当编译查询时，使用架构稳定性锁。架构稳定性锁不阻塞任何事务锁，包括排他锁（X 锁）。因此在编译查询时，其他事务 [包括在表上有排他锁（X 锁）的事务] 都能继续运行。但不能在表上执行 DDL 操作。

大容量更新锁（BU 锁）

当将数据大容量复制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 设置了 table lock on bulk 表选项时，将使用大容量更新锁。大容量更新锁允许多个线程将数据并发地大容量加载到同一表，同时防止其他不进行大容量加载数据的进程访问该表。

键锁、键范围锁（Key-range锁）

在SQL SERVER2005有两种类型键锁：键锁及键范围锁。采用哪种类型的键锁取决于隔离级别。对于已提交读、可重复读、快照隔离时SQLSERVER锁定实际的索引键(如果是堆表除了实际非聚集索引上的键锁同时有实际行上的行锁)，如果是可串行化隔离时就可以看到键范围锁。在早期的版本中我们实验可以看到SQLSERVER是通过分页锁定或表锁来实现的，也许键范围锁不是最完美的，但是我们应该看到它比分页或表锁定所锁定的范围要小得多，在保证不出现幻影的前提下键范围锁比以前版本采用锁定提供了更高的并发性能。

键范围锁放置在索引上，指定开始键值和结束键值。此锁将阻止任何要插入、更新或删除任何带有该范围内的键值的行的尝试，因为这些操作会首先获取索引上的锁。键范围锁包括按范围-行格式指定的范围组件和行组件，是一种组合锁模式(Range范围-索引项的锁模式)。比如：RangeI-N ，RangeI 表示插入范围，N(NULL) 表示空资源，它表示在索引中插入新键之前测试范围。

在SELECT * FROM MASTER..SPT_VALUES WHERE [TYPE] = 'L'查询结果的最后9条个就是键范围锁。这种锁定因持续时间比较短一般在sys.dm_tran_locks中很难见到。比如RangeI_N这个锁定，是在键范围内插入记录时获得的，在键范围内找到位置立即升级为X锁定，这个过程很短，我们在sys.dm_tran_locks中很难找到它的踪影，不过我们是可以模拟出来的，下面我们来模拟一下：

查询一：

DROP TABLE TB

CREATE TABLE TB (ID INT primary key, COL VARCHAR(16))

INSERT INTO TB SELECT 1,'A'

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM TB WHERE id BETWEEN 1 AND 5 --OLD数据

--COMMIT TRAN --Don't commit

SELECT @@SPID

(1 行受影响)

ID COL

----------- ----------------

1 A

(1 行受影响)

------

(1 行受影响)

查询二：

INSERT TB SELECT 2,'E'

查询三：

exec sp_us_lockinfo

在使用可序列化事务隔离级别时，对于 Transact-SQL 语句读取的记录集，键范围锁在索引获上取锁定阻止一切尝试在包含索引键值落入范围内增删改的数据行，可以隐式保护该记录集中包含的行范围。键范围锁可防止幻读。通过保护行之间键的范围，它还防止对事务访问的记录集进行幻像插入或删除。

例如我们在上面有例子的可串行化隔离级别下，选择索引键值在’1-5’的数据时，SQL SERVER 对落在1-5之间键值设置键范围锁定，避免包含在这个范围内的键值的插入及这个范围内键值的删除及更新。

最后强调一下键范围键产生的条件：

1、务隔离级别必须设置为 SERIALIZABLE。

2、询处理器必须使用索引来实现范围筛选谓词。例如，SELEC中的 WHERE 子句。

3、锁兼容性矩阵

锁兼容性控制多个事务能否同时获取同一资源上的锁。如果资源已被另一事务锁定，则仅当请求锁的模式与现有锁的模式相兼容时，才会授予新的锁请求。如果请求锁的模式与现有锁的模式不兼容，则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。

4、深入可锁定资源及特殊锁定

可资源资源有哪些呢，我在前面已经用

SELECT * FROM MASTER..SPT_VALUES WHERE TYPE = 'LR'

进行了列表，一共有12种之多，其实我们从本篇的开始到现在一直在接触的行锁（RID），键锁（KEY），分页（PAG）、表（TAB）及对象(OBJECT)都是我们可锁定的资源，这几类我们已经接触到很多了，下面我就不常关注的几个进行一下说明。

EXT 这是数据或索引页面扩展。这一块如果以后有时间整理表的数据存储或索引分页的结构时可以细细说说扩展，现在我们可以简单的理解为：扩展是一个64K的分配单元，是由连续的8个8K分页组成。SQLSERVER在表或索引分配扩展时会分配8个连续的8K空间，每一个扩展的首页号是8的倍数，但是扩展间本身不一定连续哦，这个不连续就是碎片了。在扩展上也可以加锁定，其实这也好理解，在不同的表或索引需要新的扩展时，系统为了让同一扩展不被错误使用(比如两个表同时得到一个扩展，那比较恐怖哦)而进行共享或排它锁定。不过是系统自发进行的，我们一般看不到。这种物理上的一致性我们在前面提到过一种闩锁，嘿嘿有印象不？我们也可以把这个当作一种事实上的闩锁。

DB数据库(DATABAES)。其实只要我打开一个连接，如果你使用sp_us_lockinfo一定得到一条相应当前连接的DB类型的锁定。结果如图61

select @@spid

exec sp_us_lockinfo

那么在DATABASE这种类型下有几种锁呢，为别对应哪些操作呢？能不能模拟出来呢？好，下面我们来模拟一些吧，比如删除库操作：

1、先打开一个managerment studio,我们先创建一个数据库，库名为dblock，建好后运行一下我们前面那个工具sp_us_lockinfo

2、打开另一个managerment studio，右击dblock进行删除操作，在弹出的窗口点确认

3、在第二步操作后迅速切换到第一个managerment staido并运行如下代码，并得到图63

select @@spid

exec sp_us_lockinfo

由上图很明显我们看到我们第一个打开的连接对Dblock有一个DB资源类型的共享锁定排斥了第二个删除操作的排它锁。再比如我们来把Dblock设置成只读，这个更好玩，多了几个锁定哦!先打开两个查询(一定要同时打开两个哦!!)

查询一：

alter database dblock SET READ_ONLY

查询一直进行中……

查询二：(结果图63)

select @@spid

exec sp_us_lockinfo

其它动作大家可以自己模拟。

APP应用程序资源。有一个应用程序锁定模式与之对应。应用程序类型的资源锁定与我们前面讨论的所有锁定模式都不一样，前面所有的锁定全是SQLSERVER自己管理的，应用程序级锁定利用了SQLSERVER的检测阻塞及死锁的机制来锁定自己想要锁定的任何对象。比如我们现在想要达到这一种效果：一个表或过程同时只有一个进程能够运行。为了来模拟这种类型的锁定，先看如下语法：

sp_getapplock [ @Resource = ] 'resource_name', [ @LockMode = ] 'lock_mode' [ , [ @LockOwner = ] 'lock_owner' ] [ , [ @LockTimeout = ] 'value' ] [ , [ @DbPrincipal = ] 'database_principal' ][ ; ]

下面我们来模拟一下：

查询一：

exec sp_getapplock 'testapplock','exclusive','session';

select * from ta

--sp_releaseapplock 'testapplock','session';

查询二：

exec sp_getapplock 'testapplock','exclusive','session';

select * from ta

查询一直进行中。。。。。。

查询三：结果图64

--create database dblock

select @@spid

exec sp_us_lockinfo

METADATA 元数据。这种类型的可锁定资源我们其实前面已经有看过，我们在修改当前库为只读时，看如图65

好，到目前为止对可锁定的资源就说完了。嘿嘿，另外几个比如堆或B树类型锁定(HBT)不如以后整理索引时再旧事重提了。

5、锁的本质、生命周期及请求锁定的实体类型

首先我告诉大家SQLSERVER并不知道(或不关心)被它锁定的对象，有时即使我们能关联到对象却无法正确解析被锁定对象的结构，你相信吗？猛一听有点吓人吧，这么重要的任务交给它管理，它不知道被管理的东西是何方神圣？呵呵，下面稍稍说一点内部的结构。

锁是SQLSERVER的一种内存结构，并不是一种物理的数据结构，所以这种元数据追踪是无法被记录的。关闭或中止一个连接相应的部分锁定信息就会消失。这个结构书上都称锁块（Lock block），用这个锁块来跟踪数据、锁定及对锁相关的信息描述。显然这些锁块是要被不同进程所拥有的，SQL有另一个叫所有者块来管理这些，所有者块与锁块之间通过所有者的指针相连。

在锁块里有一个专门对锁定资源的描述结构叫资源块，资源块负责维护资源名称、锁块的指针、已经授权的所有者指针列表、转换中的所有者指针列表、等待的所有者列表。对资源块有一个类型描述，占用一个字节，这个类型描述我在前面已经通过SQL语句进行列表过：

查询一：

SELECT *

FROM MASTER..SPT_VALUES WHERE TYPE = 'LR'

对具体的资源描述有12个字节的内存结构来记录。我们在2000下使用过一个表syslockinfo，这些信息在rsc_bin中有体现，这个字段对资源进行描述，根据类型的不同，这12个字节有着不同的分工组合。在这里我还要提醒2000下的用户，rsc_bin在2005里意义不再等同于2000，但是仅是微小的差别。为了说明我上面的论点，所以对syslockinfo进行一下深入，证明SQLSERVER对被的对象是不关心的。我们先来实际看看这个表的数据，结果集如图66

通过这个图我们应该看到rsc_bin的最两个字节和2000下不一样了，这两个字节交换后就是资源块的类型，这一点大家要注意哦。

Query 1:

CREATE TABLE TB (ID INT primary key, COL VARCHAR(16))

INSERT INTO TB SELECT 1,'A'

SET TRANSACTION ISOLATION LEVEL repeatable read

BEGIN TRAN

SELECT * FROM TB WHERE id BETWEEN 1 AND 5

　　对上图我们很容易理解类型为objec及page的rsc_bin意义。重点说一下KEY类型的共享锁定。这一条记录在syslockinfo中rsc_bin列，前6个字节是分区ID，我们可以通过sys.partitions查找到相应的分区。紧接着的6个字节（上图用红粗线标识的部分）来头不小，是根据当前索引的所有索引列生成的中介现象哈希值，在这里对这个哈希会值是无法反推的。如果上面的表及页面我们可以查找到相应的结构的话，那个对这个键值锁定真的就是不是不想知道结构，而是真的不知道什么结构了哈。哈哈，这儿证明我上面的结论吧。其实我们不用担心，因为虽然不能反推，但是阻塞或死锁机制是还是可以查找匹配的呀，也就是如果相同时生成的哈希一样，那样SQLSERVER还知道这是相同听资源，阻塞或死锁已经发生。嘿嘿，其实只要关心被它锁定资源的标识串就足以管理阻塞或死锁，

　　请求锁定的实体类型，这个在前面的几乎每一个图表里都可以看到的一列。图68

在这个图里我们只看到两类，还有另外两类，一共四类：事务类型，游标类型、事务工作空间类型、会话类型。

　　事务类型：这个就不用多说了吧，上面我们显式的开始一个事务时，此连接都会有一个事务类型的实体。

　　事务工作空间类型：这是SQLSERVER2005引入的。SQLSERVER2000把所有除事务与游标类型的实体全归为会话型。通常我们看到的数据库级锁定就是空间类型的，也就是基本一个会话就对应一个工作空间，这样会话中所有的数据库级锁定会保留在相同的工作空间里，这对于绑定会话是有十分重要的意义。

就绑定来说MS明确会在以后的版本不支持了。因为我们在以前版本用过，所以这儿稍微说说。

先说说绑定出现的背景吧，首先明确一点锁定的阻塞是发生在不同的SQL进程间，也就是进程自己是不会把自己锁起来的。接着再明确一点何为相同进程，每一个独立的SQL连接，但是我们不要错误的认为同一个用户同一个程序就一定一个连接、一个进程。事实上同一个用户同一个应用程序拥有多个连接即进程的可能程是非常大的。那么我们显然就可以想像到同一个用户同一个程序自己可能锁定自己，因为它拥有多个连接是吧，而SQLSERVER是无法知道同一应用程序拥有哪几个连接，也不知道这些连接间怎么关联的。那么这个问题怎么解决呢。在SQL6.5以后SQL2005以前就是用绑字，当然2005也支持，但是MS明确在以后的版本可能不支持了。

绑定分为本地绑字及分布式绑定，它完成不同连接在同一事务空间内的交互或不同服务器上工作单元的交互。一般绑它以第一个连接进行连接并申请“绑定令牌”，这个令牌就是一个事务空间的变量字符串，这个变量字符串可供后续的连接进行检索，并且这个变量串对应的事务空间就会被共享。

下面简单的做一个测试吧！

查询一：

begin tran

declare @token varchar(100)

exec sp_getbindtoken @token output

select @token

select * from tb

update tb

set col = 'b'

where id = 1

-------------------------

.Y4bBahH9aCGCj[a[0b-:-5---03E=--

(1 行受影响)

ID COL

----------- ----------------

1 A

(1 行受影响)

commit tran　–-这一步留到查询三执行完后执行，一定要注意

此会话中的活动事务已由另外一个会话提交或终止。

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

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

查询二：

select * from tb where id = 1

查询一直进行中……

查询三：

exec sp_bindsession '.Y4bBahH9aCGCj[a[0b-:-5---03E=--'

update tb

set col = 'c'

where id = 1

commit tran

select * from tb

(1 行受影响)

ID COL

----------- ----------------

1 c

(1 行受影响)

在查询一里我们先是申请一个“令牌”，然后开始修改ID＝1的记录，注意　我们是更新为B，然后我们打另一连接并执行对ID＝1的查询，发现查询一直进行中，如果你打开另一连接执行sp_us_lockinfo你会发现在共离锁定。在查询三里我们先共享刚才的连接，然后进行数据的修改，注意我们修改ID＝1的COL值为c并提交事务，这时我们查询数据里的数据发现记录是第三个查询里执行修改的结果，显然这两个进程间没阻塞。如果此时我们现回到第一个查询里执行commit tran你发现这是提交操作失败，因为当前事务已经在第三个查询里提交了，所以出相应的错误。

　　最后说明一下绑定理论没有个数的限制，事实上如果程序不严谨也麻烦，在2005有一个多个活动结果集配置选项，可以实现与绑定相似基本功能，默认下是关闭，大家可以自行测试。

　　游标型：这个我们经常会用到，只是没有注意。这种实体必须在使用游标时显式申请，每提取一行时得到一个游标锁，直到提取下一条或关闭游标，否则即使你提交或rollback事务这个游标锁定也不会释放，这怎么来理解呢，下面我来实例：

BEGIN TRAN

DECLARE @f1 varchar(10)

DECLARE cur CURSOR SCROLL_LOCKS

FOR SELECT f1 FROM ta FOR UPDATE

OPEN cur

FETCH NEXT FROM cur INTO @f1

EXEC sp_us_lockinfo –　第一次图69

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE TA SET s1 = 0 WHERE CURRENT OF cur

EXEC sp_us_lockinfo –第二次图6a

BREAK;--只取回一条了，其它记录类同，所以不再模拟

FETCH NEXT FROM cur INTO @f1

END

EXEC sp_us_lockinfo—第三次图6b

CLOSE cur

DEALLOCATE cur

EXEC sp_us_lockinfo—第四次图6c

COMMIT

EXEC sp_us_lockinfo—第五次图6d

（图69）

（图6a）大家注意这一张图，因为这是一个可更新游标，我在取出第一行记录时企图去更新当行的信息，因为此时事务型和游标型实体同时出现了。

（图6b）

图6b和图6相同，我们只是模拟一下取完所有行后数据库上的锁定行为。

（图6c）这是关闭并删除游标引用后在数据库里留下的锁定，也就是在关闭游标后游标型实体锁定不存了，只有了事务型的实体，因为我们这是可更新游标，并且我在实例中有更新动作，所以最后还有事务型锁定存在。

（图6d）只有当前会话的事务工作空间型实体了

è给大家留一个课后作业：

1、对表结构作由A修改成B，会得到什么样的结果，注意哦，这时会出现一个NULL的锁定哦，这个锁的作用是为插入准备的（以上实例测试在A结构下测试的）：

A：

create table ta(f1 int,s1 char(10))

insert into ta select 1,'a'

＝＝＝＞增加一个主键，这意味着什么？

B：

create table ta(f1 int primary key ,s1 char(10))

insert into ta select 1,'a'

2、在上一问的B结构下，执行如下SQL看看又是什么样的结果，仔细比较

BEGIN TRAN

DECLARE @f1 varchar(10)

DECLARE cur CURSOR SCROLL_LOCKS

FOR SELECT f1 FROM ta --FOR UPDATE

OPEN cur

FETCH NEXT FROM cur INTO @f1

EXEC sp_us_lockinfo

WHILE @@FETCH_STATUS = 0

BEGIN

UPDATE TA SET s1 = 0 WHERE f1 = 0

EXEC sp_us_lockinfo

BREAK;--

FETCH NEXT FROM cur INTO @f1

END

EXEC sp_us_lockinfo

CLOSE cur

DEALLOCATE cur

EXEC sp_us_lockinfo

COMMIT

EXEC sp_us_lockinfo

会话型：会话型锁定必须显式的申请，在上面我介绍APP类型资源时就有明确的会话型锁定实体类型，大家可以看看篇首的APP类型资源。下面给一个图看看：

　　锁的生命周期：最后说说锁的持续时间，就像我们程序变量的生命周期一样，锁定也有一个持续时间。持续时间事实上取决于锁的模式及隔离等级，另外我们还可以使用锁定提示来改变锁的持续时间。因为这个持续时间和我们程序变量的生命周期一样，所以习惯称为生命周期。

关于锁定提示改变锁的生命周期这在以后再说，我把几个等级下常规模式的锁定做一个列表：

(对快照模式下锁定模式有点错误，特此更新，深表歉意)

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))

查询一：

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的行版本控制。

10、行版本控制综述

行版本控制是SQLSERVER2005保证数据完整及一致的新机制。我们前面提到并发模型有两种：悲观与乐观并发，而行版本控制是乐观并发下的一种保障数据完整及一致的新技术。行版本控制和前面提及的锁定机制不大一样，它保障了写的进程与读的进程间不会阻塞，并且在保证不读到未提交的数据下又提升了数据库的并发能力，然而我们要注意的是乐观并发下写的进程还是会获取排它锁定，上面提及的一些锁定模式、锁定时间及管理死锁的方式都适用于它。

SQLSERVER2005使用行版本控制的隔离有两个：RCSI　和　SI。RCSI是相对无阻塞的已提交读模式，所谓相对无阻塞是相对于传统的已提交读模式，在这种模式下写进程不会阻塞读进程，读进程不会设置共享锁定，而是使用行版本控制读取语句级的一致性的数据，简单的说就是任何读都可以得到语句开始那一时刻最近的已经提交数据。快照隔离(SI)可以使任何读进程读取到交易级的一致性数据，简单的说就是任何读进程都可以读取到交易开始时已提交数据。

SQLSERVER2005如何做到写不阻塞读的呢？一旦启用RCSI或SI后数据库开始在tempdb中存储所有已经修改过的记录副本(记录版本，以后我们直接称行版本)，同时保证在只要有进程需要这些数据就会一直维持这些行版本，所以tempdb又被我们称为版本存储区。很显然的是启用行版本控制后tempdb得需要更多的空间来管理行版本，所以如果你的数据库使用了行版本管理，一定要管理好tempdb。行版本如何存储在版本存储区我下面再说。

好，整体上来说我们应该有这样一个概念：已经提交的数据存储在当前数据库，而当数据修改前的数据被复制到tempdb中，那么它们之间如何联系呢？这儿我们引入另一个术语：XSN，注意哦，区别于LSN哦。XSN称为事务序列号，新行通过这个XSN和tempdb里的行版本之间保持联系，是不是有点指针味道呀，哈哈，同时我们要注意了，新行XSN指向行版本区的某一旧行，同时这一旧行可能包含指向列旧的数据行XSN，SQLSERVE通过这个链表可以访问到正确的版本。

说了这么多，感觉这个行版本管理后好处多多呀，至少增强了并发能力吧，可是在更改当前数据库使用行版本管理前还是要三思而行：首先，增加了tempdb的负担，这种负担不仅仅是空间上的。接着，维护旧版的数据行必然会降低更新操作的能力，不管有没有读进程存在，只要有更新存在数据库就得为此付出代价。第三，增加的并发能力使得每一个读进程都得付出额外的开销来访问刚才我们提到的XSN链表找到合适行版本。最后我们说阻塞是不能完全避免的，就是在这种乐观模式下写写还是阻塞的。在后续我们模拟在SI下的更新冲突。

有人要问了RCSI和SI之间有什么差别呢？其实RCSI和SI行为上基本类似的，都是可以在当前数据锁定的前提下读取到当前数据已经提交的早期版本，它们的主要差别有二：一，行版本记录在行版本区保存的时效。此话怎么理解呢，我在前面说到RCSI是语句级的而SI是事务级的，这就是直接导致数据行版有多久的关键。二，RCSI是已经提交读的无阻塞的变种，而SI是存在阻塞的。下面我们会说道说道这两种行为。

11、行版本区

SQLSERVER2005只要开启快照，所有更新和删除就会生成已经提交的行版本，而这些行版本是保存在行版本区，即tempdb数据库的数据分页上，随时保障快照的查询需要，换句话说只要有查询需要，行版本区数据就存在。SQLSERVER2005有一个清理线程，常规好像是一分钟就进行一次回收，对于SI隔离下的查询行版本保存事务结束，对于RCSI隔离下的查询行版本一直保存到当前查询语句结束。

这儿提到tempdb，得稍微提提这个tempdb，tempdb也是记录日志的，并不是好多人认为的不记录，它的日志是为了临时对象上的事务回滚，记住只能回滚，不能恢复或重做，当然是是题外话，一带而过。

Tempdb中有三种类型的对象：用户对象、内部对象、版本库。这个版本库的数据来源有三：一，重建索引或有快照级别的数据库上执行了DML(我们一会说这两个快照级别)；二，触发器，这有别于2000哦，2005的伪表(deleted 、inserted)是由行版本产生的；三，活动结果集。

12、已经提交读快照隔离下读写行为

RCSI我们一定要记住它是一个语句级的快照隔离级别，任何查询都可以查询到语句开始时最近的已经提交的数据。

如何开启这个级别隔离我们前面已经写过了，对，用alter database dbname set read_comm.itted_snapshot on 就行，在运行这一命令要注意时不能用户在连接数据库，如果有人在使用数据库这个命令就会阻塞。这个命令有两个开关项：with nowait 和rollback来避免阻塞和终止任何数据库连接，大家可以查查联机从书。

我在前面写隔离级别的事例时提到这个隔离级别和用锁定的已提交读具有一样的行为，下面我们用一个实例来看看：

先修改当前当前库的READ_COMMITTED_SNAPSHOT为ON

ALTER DATABASE TESTCSDN

SET READ_COMMITTED_SNAPSHOT ON

Exec sp_us_lockinfo

--test data and table

create table ta(id int,col varchar(10))

insert ta select 1 ,'a'

union all

select 2,'b'

union all

select 3,'c'

查询一：

begin tran

update ta

set col = 'd'

where id = 1

waitfor delay '00:00:05' –故意加延时看看这个锁定是否影响查询二

exec sP_us_lockinfo –查看当前锁定情况，由图我们知道在表上有排它锁定

commit

查询二：

begin tran

waitfor delay '00:00:01'—确保表上已经有排它锁定

select *

from ta

where id = 1 -–行版本读到最近提交的数据

id col

----------- ----------

1 a

(1 行受影响)

waitfor delay '00:00:05'—-保证查询一已经提交数据

select *

from ta

where id = 1 – 查询到最新行版本数据

id col

----------- ----------

1 d

(1 行受影响)

commit

回顾一下以上过程，我们发现这个已提交快照和已提交锁定方式一样的行为，但是它比锁定模式有更强的并发能力，因为读写进程间不再阻塞。另外我们注意到没有，不需要在每一个会话中使用SET来设置选项就可以使用RCSI，也就是我们无需对应用程序作任何修改就可以从缺省的锁定方式的已提交读切换到快照方式的已提交读，从而降低阻塞带来的并发冲突。

12、快照隔离下的读写行为

SI是SQLSERVER2005引入的一个新的隔离，要启用必须在两个地方同时启用：1、启用Allow_SNAPSHOW_ISOLATION；2、在会话中使用SET TRANSACTION ISOLATION LEVEL命令为每一个会话设置隔离。我们在前面说过它是一个乐观模式的隔离，类似于已提交读快照隔离，但是又有些差别。

启用命令：

ALTER DATABASE DB_NAME SET ALLOW_SNAPSHOT_ISOLATION ON；

当我们使用这个命令时，如果有活动连接时它不会像RCSI阻塞，但是如果有活动事务时还是会被阻塞。这个命令运行后数据的状态不会立即成ON状态，而是经历一个IN_TRANSITION_TO_ON的状态，这时数据库处于等待数据库中所有事务结束并开始为更新和删除产生版本数据，一旦在alter命令开始时已经进行的事务一结束，数据库就会进入ON状态。同理我修改为OFF时数据的库状态也会经历一个中间状态IN_TRANSITION_TO_OFF,等待活动的事务结束。一旦所有的活动事务结束数据库就会变为OFF状态。好下面我们来模拟启动过程，关闭的过程大家自己模拟吧。

我们模拟打开的过程：

查询一：开始一个事务，记住不要提交可回滚

BEGIN TRAN

UPDATE TA

SET COL = 'B' WHERE ID = 1

查询二：开启快照

ALTER DATABASE DBlock SET ALLOW_SNAPSHOT_ISOLATION on;

查询一直进行中……

查询三：

exec sp_us_lockinfo

--大家可以看到当前数据处于中间态：IN_TRANSITION_TO_ON

查询四：

--我们模拟这时开启SI进行数据访问，看看会是什么结果

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT * FROM TA WHERE ID = 1

id col

----------- ----------

消息3956，级别16，状态1，第4 行

快照隔离事务未能在数据库'dblock' 中启动，因为用于启用此数据库的快照隔离的

ALTER DATABASE 命令尚未完成。数据库正在转换到挂起ON 状态。您必须等待，

直到ALTER DATABASE 命令成功完成。

接着，我们在查询一里增加一句：COMMIT;然后我们再运行查询三：

那好，我们通过上面的命令已经学会使用这个隔离级别。SI保证事务级的数据一致性，任何读操作都可以得到事务开始时最近已经提交的数据版本。下面我们再模拟一下查询快照数据：

查询一：

SELECT * FROM TA

id col

----------- ----------

1 B

(1 行受影响)

BEGIN TRAN

UPDATE TA

SET COL = 'C' WHERE ID = 1

WAITFOR DELAY '00:00:05'

exec sp_us_lockinfo

COMMIT

查询二：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT * FROM TA WHERE ID = 1

id col

----------- ----------

1 B

(1 行受影响)

waitfor delay '00:00:05'

SELECT * FROM TA WHERE ID = 1

id col

----------- ----------

1 B

(1 行受影响)

commit tran

SELECT * FROM TA WHERE ID = 1

id col

----------- ----------

1 C

(1 行受影响)

还记得我在前面说过SI是有冲突阻塞(错误：3960)的哦，下面我们模拟一下，这也是在提醒大家使用SI模式时一定要潜在的阻塞，好看下面的实例：

查询一：

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT * FROM TA where id = 1

WAITFOR DELAY '00:00:05'

UPDATE TA

SET COL = 'c' WHERE ID = 1

消息3960，级别16，状态2，第9 行

快照隔离事务由于更新冲突而中止。您无法在数据库'dblock'中使用快照隔离来直接

或间接访问表'dbo.TA'，以便更新、删除或插入已由其他事务修改或删除的行。请重

试该事务或更改update/delete 语句的隔离级别。

查询二：

WAITFOR DELAY '00:00:02'

BEGIN TRAN

UPDATE TA

SET COL = 'd' WHERE ID = 1

commit tran

SELECT * FROM TA WHERE ID = 1

id col

----------- ----------

1 d

(1 行受影响)

13、锁定提示(LOCK HINTS)

隔离级别是会话级别的，在会话内内对持有锁、阻塞、锁的生命周期产生影响。然而，必要时我们使用表级锁定提示来改变这种默认锁定行为，但是我们一定要注意这种操作不当会影响并发性能。

我们一定要记住使用锁定提示是表级提示，因为我们一般是在From子句中使用wth指定。另外SQLSERVER2005推荐使用With(Locktype),不带with语法尽量不要使用。

下面枚举一下锁定提示的关键字(申明太晚了，我直接复制了以前收藏的)：

SELECT au_lname FROM authors WITH (NOLOCK)

锁定提示描述

HOLDLOCK 将共享锁保留到事务完成，而不是在相应的表、行或数据页不再需要

时就立即释放锁。它等同于SERIALIZABLE，只不过仅作用于表级。

NOLOCK 不发出共享锁，并且不要提供排它锁。当此选项生效时，可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读。仅应用于 SELECT语句，显然相当于未提交读级别。

PAGLOCK 在通常使用单个表锁的地方采用页锁。

READCOMMITTED 用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况

下，SQL Server 2000 在此隔离级别上操作。

READPAST 跳过锁定行。此选项导致事务跳过由其它事务锁定的行（这些行平常

会显示在结果集内），而不是阻塞该事务，使其等待其它事务释放在这

些行上的锁。READPAST锁提示仅适用于运行在提交读隔离级别的事

务，并且只在行级锁之后读取。仅适用于SELECT语句。

READUNCOMMITTED 等同于NOLOCK。

REPEATABLEREAD 用与运行在可重复读隔离级别的事务相同的锁语义执行扫描。

ROWLOCK 使用行级锁，而不使用粒度更粗的页级锁和表级锁。

SERIALIZABLE 用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。

等同于HOLDLOCK。

TABLOCK 使用表锁代替粒度更细的行级锁或页级锁。在语句结束前，SQL Server 一直持有该锁。但是，如果同时指定 HOLDLOCK，那么在事务结束之前，锁将被一直持有。

TABLOCKX 使用表的排它锁。该锁可以防止其它事务读取或更新表，并在语句或事务结束前一直持有。它和TABLOCKG与XLOCK连用的效果一样。

UPDLOCK 读取表时使用更新锁，而不使用共享锁，并将锁一直保留到语句或事务的结束。UPDLOCK的优点是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。消除转换类型的死锁重要技术。

XLOCK 提示SQLSERVER在语句使用的全部数据上使用排它锁，并一直保持到

事务结束时。可以使用 PAGLOCK或TABLOCK指定该锁，这种情况下

排它锁适用于适当级别的粒度

14、锁定超时

SET LOCK_TIMEOUT n；

晕，这个看联机从书就行了，不能再写了。

15、详述行版本存储区

前面说了好多的行版知识，那在这两种隔离等级下，数据有什么表现呢，这一节我们来详细的说说。

为了更好的分析行版的记录行，先说点相关的知识点。有如下相关的知识我们来学习行版会轻松点。

A、如何查看数据页面（DBCC PAGE、DBCC TRACEON ）

我们可以通过DBCC来查看数据页面内容，这个命令可以看到数据库中的页面报头、数据行及行偏移表。虽然这个命令只是系统管理员才可以执行，但是一般操作人员也不会去看页面内容。

DBCC PAGE的命令格式如下：

DBCC PAGE({dbid|dbname},filenum,pagenum[,printopt])

Dbid|dbname 数据库ID或库名

Filenum 页面的文件号

Pagenum 指定文件内的页面号

Printopt 输出选项：0－默认值，缓冲报头及页面报头

　　　　　　　　　　　　　1－对每记录行分别输出缓冲及页面报头，行偏离表

　　　　　　　　　　　　　2－整体的缓冲、页面报头及行偏离表

　　　　　　　　　　　　　3－完整的报头、行偏离表并可看到行中的各列值

DBCC TRACEON格式：

DBCC TRACEON(3604)

必须先打开跟踪3604来让DBCC PAGE的结果输出给客户端。

我们来看看一个DBCC PAGE的结果(表TA只二条记录)：

PAGE: (1:89)

BUFFER:

BUF @0x02BFFDF0

bpage = 0x04938000 bhash = 0x00000000 bpageno = (1:89)

bdbid = 8 breferences = 0 bUse1 = 23490

bstat = 0xc00009 blog = 0x21432159 bnext = 0x00000000

PAGE HEADER:

Page @0x04938000

m_pageId = (1:89) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0xa200

m_objId (AllocUnitId.idObj) = 86 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594043564032

Metadata: PartitionId = 72057594038583296 Metadata: IndexId = 0

Metadata: ObjectId = 21575115 m_prevPage = (0:0) m_nextPage = (0:0)

pminlen = 18 m_slotCnt = 2 m_freeCnt = 8022

m_freeData = 166 m_reservedCnt = 0 m_lsn = (45:288:2)

m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0

m_tornBits = -1441945315

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED

PFS (1:1) = 0x61 MIXED_EXT ALLOCATED 50_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

DATA:

Slot 0, Offset 0x60, Length 35, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFO

Memory Dump @0x3432C060

00000000: 50001200 01000000 61616161 61616161 †P.......aaaaaaaa

00000010: 61610200 fc000000 00000000 000c0000 †aa..............

00000020: 000000†††††††††††††††††††††††††††††††...

--删除了一条记录信息

OFFSET TABLE:

Row - Offset

1 (0x1) - 131 (0x83)

0 (0x0) - 96 (0x60)

Buffer:当前页面调入内存时，要为了便于管理内存中这个页面生成的一种结构。

Page Head:报头。(部分解释如下)

m_pageId当前页面的文件号及页面号。

m_level当前页面在索引中的级数。

AllocUnitId 分配单元ID，

PartitionId 分区ID，

ObjectId所属对象的ID。

IndexId页面的索引ID。

m_prevPage前一页面指针，

m_nextPage下一页面指针。

Pminlen 行定长部分字节数。

m_freeData页面第一个空闲字节偏移量。

m_slotCnt总记录数。

m_freeCnt 页面空闲字节数。

DATA：记录每一个行的信息。

Slot 0 行号

Offset 0x60 行在页面的偏移量

Length 35 记录长度,

Record Type 记录类型

Record Attributes 属性描述 VERSIONING_INFO行版

OFFSET TABLE：行偏移矩阵内容

1 (0x1) – 131 (0x83)

0 (0x0) – 96 (0x60)

B、数据页面

数据页面显然也是一种结构，它其中包含了表中用户数据，它固定为8K大小(8192byte)。一共有三种类型的页面(行内数据IN_ROW_DATA，行溢出数据ROW_OVERFLOW_DATA，大数据LOB页面LOB_DATA)，而每一个页面是有三部分级成(报头，数据行，行偏移矩阵)，这三部分我们在刚才上面的报告输出有所体现。页面报头总是占用页面的前96个字节，所以我们页面只有8096个字节用于数据行及行偏移存储，这个以后估算表大小时一定要注意。

96个字节后的区域是真正的行数据部分，对于行内数据来说一个行的最大为8060个字节。对于行固定的表来说，每一个页面上存储的记录数总是一定的，如果变长时要根据输入的数据而定。从减少IO及缓存命中率来说，我们要尽量使得页面容纳尽量多的记录。

行偏移矩阵是2个字节的块，以两个字节为单位，每一个行就有两个字节的块，矩阵体现了记录在页面上的逻辑顺序，在这儿得注意一下，有聚集索引的表，SQLSERVER按索引键值来存储，不是意思着页面上的物理存储也一定是键值的顺序哦，实际上这个顺序是由矩阵的逻辑顺序来保证的，下面我做一个测试给大家看看：

create table ta(

id int primary key ,

col3 char(1))

insert into ta select 1,'a'

insert into ta select 2,'a'

insert into ta select 3,'a'

insert into ta select 4,'a'

insert into ta select 5,'a'

insert into ta select 6,'a'

insert into ta select 11,'a'

insert into ta select 21,'a'

insert into ta select 31,'a'

insert into ta select 41,'a'

insert into ta select 51,'a'

insert into ta select 61,'a'

insert into ta select 10 ,'b'

dbcc ind(testcsdn,'ta',-1) –227

dbcc traceon(3604)

dbcc page('testcsdn',1,227,1)

DATA:

Slot 0, Offset 0x60, Length 12, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Memory Dump @0x443CC060

00000000: 10000900 01000000 610200fc ††††††††††........a...

。。。。。。--省略了中间的行记录

00000000: 10000900 06000000 610200fc ††††††††††........a...

Slot 6, Offset 0xf0, Length 12, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP

Memory Dump @0x443CC0F0

00000000: 10000900 0a000000 620200fc ††††††††††........b...

Slot 7, Offset 0xa8, Length 12, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP

Memory Dump @0x443CC0A8

00000000: 10000900 0b000000 610200fc ††††††††††........a...

。。。。。。--省略

OFFSET TABLE:

Row - Offset

12 (0xc) - 228 (0xe4)

11 (0xb) - 216 (0xd8)

10 (0xa) - 204 (0xcc)

9 (0x9) - 192 (0xc0)

8 (0x8) - 180 (0xb4)

7 (0x7) - 168 (0xa8)

6 (0x6) - 240 (0xf0)

5 (0x5) - 156 (0x9c)

4 (0x4) - 144 (0x90)

3 (0x3) - 132 (0x84)

2 (0x2) - 120 (0x78)

1 (0x1) - 108 (0x6c)

0 (0x0) - 96 (0x60)

注意报表的结果的红色部分。

C、数据行结构

上面的页面结构里我们已经看到数据行的结构，只是我们没有对行的数据做介绍，下面就是一个行的数据，我们来详细说说行的结构。

insert into ta select 10 ,'b'

Slot 6, Offset 0xf0, Length 12, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP

Memory Dump @0x443CC0F0

00000000: 10000900 0a000000 620200fc ††††††††††........b...

在这儿我们只讨论固定长度列的行结构，变长列的行结构不讨论。

我们先说行的长度，我们假设固定列类型基本长度和为N，固定列的列数为M，那么长度的基本公式为：N+ceiling(M/8)+6。上面的报告报头我可以看得出长度是12，那用这个公式来试试：5+ceiling( 2/8) + 6 = 12 .

好，行的结构究竟是什么样呢？（仅考虑固定列长）

B状态1 + B状态2 + W列偏移 +　L数据 + W列数 + NULL状态位

B　　　　　　　－　1　个字节

W　　　　　　　－　2　个字节

L　　　　　　　－　变长

NULL状态位　　－　Ceiling(W列数/8)

那好，我们根据上面的行结构来解释一下slot 6记录，第一个字节0x10 ,第四个bit位是1　表示存在NULL状态位，第5个Bit位是表示有无变长列，0表示行无变长列（其它bit位不介绍了）。第二个字节是状态2,暂未使用。第三、四字节对调为0x0009表示列位移量，那对于这个行记录列偏移量怎么是9的呢？　好，我们看列数据真正是从第5个字节开始的，一个整形（4个字节）＋一个长度为1的字符（1个字节）共5个字节，5＋4　＝　9　，　所以得到列当前的偏移为9。第10、11个字节对调后是0x0002表示有两个列，第12个字节表示的NULL状态位，这个正好和第一个字节的第四个bit位对应上，一个Bit表示一个列的NULL特性，Ceiling(2数/8) = 1，当前值是0xfc,即11111100（B），BIT位表示是否为NULL从高到低的顺序显示，本例中只有两列最后全是0　表示都不为空（1代表当前对应的列为NULL）。

根据上面的说明大家可以自己试试。　

D、如何得到页面号(pagenum)

上面说了这么多我们有一个关键的东东没说，什么呢，那就是如何得到当前表的页面号呢？好，那我们在这儿可以介绍两种办法：

1、 DBCC IND()

DBCC IND
(　

['dbname'|dbid], -- 数据库名或ID
　　tbname, -- 表名

Printopt｜noclustered index_id [, --输出选项

Partition_num] –-指定分区号，主要兼容2000

)

Printopt : --输出选项（常用的）

-- noclustered index_id 　所有IAM、数据及指定索引的分页信息

-- -2 所有IAM页面

-- -1 所有的数据、索引、IAM、行溢出及LOB页面

-- 0 行内数据、行内数据的IAM页面

-- 1 聚集索引及所有数据、IAM、LOB页面

Partition_num：

--主要是兼容2000，缺省0为所有分区，在2005里我们可以指定特定的分区号

2、从system_internals_allocation_units取到first_page

---------------------------------------------------------------------

-- 查询表的文件号及页面号

-- Author : HappyFlyStone

-- Date : 2009-11-19 13:23:02

-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)

-- Apr 14 2006 01:12:25

-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

-- blog : http://blog.csdn.net/happyflystone

--　　　　　转载注明出处及相关信息

---------------------------------------------------------------------

;WITH T1

(

SELECT OBJECT_NAME(OBJECT_ID) AS NAME,TYPE_DESC,FIRST_PAGE

FROM SYS.SYSTEM_INTERNALS_ALLOCATION_UNITS U

JOIN SYS.PARTITIONS P

ON U.CONTAINER_ID = P.PARTITION_ID

WHERE [OBJECT_ID] = OBJECT_ID('TA')

)

SELECT *,CAST(

CONVERT(INT,SUBSTRING(FIRST_PAGE,6,1)) * POWER(2,8)

+ CONVERT(INT,SUBSTRING(FIRST_PAGE,5,1))

AS VARCHAR)+':'+CAST(

(CONVERT(INT,SUBSTRING(FIRST_PAGE,4,1)) * POWER(2,24))

+ (CONVERT(INT,SUBSTRING(FIRST_PAGE,3,1)) * POWER(2,16))

+ (CONVERT(INT,SUBSTRING(FIRST_PAGE,2,1)) * POWER(2,8 ))

+ (CONVERT(INT,SUBSTRING(FIRST_PAGE,1,1))) AS VARCHAR) AS 'FILE:PAGE_NUM'

FROM T1

name type_desc first_page File:page_num

----- --------------- -------------- ----------------

ta IN_ROW_DATA 0x590000000100 1:89

(1 行受影响)

对于上述CTE大家可以封装成函数，在需要时调用。

3、 DBCC　IND输出的相关列名介绍

PageFID －页面的文件号
PagePID －页面号
IAMFID － IAM所在文件号，如果是IAM页面此项为空
IAMPID －对应的IAM页面号，对于IAM为NULL
ObjectID －　页面所属对象ID
IndexID – 索引的ID号
PartitionNumber – 分区号
PartitionID – 分区ID
iam_chain_type – 2005有如下几种类型：
1. IN_ROW_DATA 　

2. LOB_DATA　

3. ROW_OVERFLOW_DATA 　

PageType – 页面类型
1 - data page

2 - index page

3 and 4 - text pages

8 - GAM page

9 - SGAM page

10 - IAM page

11 - PFS page

IndexLevel – 这个对应页面报头部分的m_level，l当前页面在索引中的级数

16、详述行版本存储区二

好，下面具体来看行版数据的结构。建议大家先关注这几个动态管理视图(DWV)。

select * from sys.dm_tran_version_store

select * from sys.dm_tran_current_transaction

select * from sys.dm_tran_transactions_snapshot

sys.dm_tran_version_store 返回一个可显示版本存储区中所有版本记录的虚拟表。其中有几个字段要了解一下：

列名
说明

transaction_sequence_num
生成该记录版本的事务的序列号

version_sequence_num
版本记录序列号。此值在生成事务的版本中是唯一的。

status
指示有版本控制的记录是否已拆分为两个记录。如果此值为 0，则记录存储在一页中。如果此值为 1，则记录拆分为两个记录，且存储在两个不同页上。

min_length_in_bytes
记录的最小长度（字节）。

record_image_first_part
版本记录的第一部分的二进制图像。

record_image_second_part
版本记录的第二部分的二进制图像。

dm_tran_current_transaction显示当前会话中的事务状态信息。

首先我们来看一下当前库快照状态：

select name,snapshot_isolation_state_desc,is_read_committed_snapshot_on

from sys.databases

where name = 'dblock'

name snapshot_isolation_state_desc is_read_committed_snapshot_on

------------- ------------------------------- -----------------------------

dblock OFF 1

(1 行受影响)

好吧，我下面准备部分数据并用前面但要查看页面信息的命令先看看页面内容：

drop table ta

create table ta(id int,col char(10))

insert into ta select 1,'aaaaaaaaaa'

insert into ta select 1,'bbbbbbbbbb'

dbcc ind(dblock,'ta',-1) --73

dbcc traceon(3604)

dbcc page('dblock',1,89,1)

00000000: 50001200 01000000 63636363 63636363 †P.......cccccccc

00000010: 63200200 fcc80000 00010000 00120000 †c ..............

00000020: 000000†††††††††††††††††††††††††††††††...

Slot 1, Offset 0x83, Length 35, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFO

Memory Dump @0x433BC083

00000000: 50001200 01000000 63636363 63636363 †P.......cccccccc

00000010: 63200200 fcc80000 00010001 00120000 †c ..............

00000020: 000000†††††††††††††††††††††††††††††††...

---------------------------------------------------------------

Slot 0, Offset 0x60, Length 35, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFO

Memory Dump @0x444CC060

00000000: 50001200 01000000 64646464 64646464 †P.......dddddddd

00000010: 64640200 fcd00000 00010000 00130000 †dd..............

00000020: 000000†††††††††††††††††††††††††††††††...

Slot 1, Offset 0x83, Length 35, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFO

Memory Dump @0x444CC083

00000000: 50001200 01000000 64646464 64646464 †P.......dddddddd

00000010: 64640200 fcd00000 00010001 00130000 †dd..............

00000020: 000000†††††††††††††††††††††††††††††††...

至少上面显示的行信息不用我介绍了吧，直接用我上面介绍的，因为当前没有产生版本信息，我们用两个select来验证：

select * from sys.dm_tran_version_store

select * from sys.dm_tran_current_transaction

两个SQL没有返回任何信息，下面更新记录生成版本信息。

update ta

set col = 'ddddddddd' where id = 1

select * from sys.dm_tran_version_store

select * from sys.dm_tran_current_transaction

transaction_sequence_num version_sequence_num database_id rowset_id status min_length_in_bytes record_length_first_part_in_bytes record_image_first_part record_length_second_part_in_bytes record_image_second_part

---------------------------------------------------------------------

29 1 8 72057594038714368 0 18 35 0x5000120001000000616161616161616161610200FC0000000000000000100000000000 0 NULL

(1 行受影响)

transaction_id transaction_sequence_num transaction_is_snapshot first_snapshot_sequence_num last_transaction_sequence_num first_useful_sequence_num

-------------------- --------- -------- ----- -------------------

4872 18 0 NULL 29 21

(1 行受影响)

如果这时我们再查看原来的TA里的slot0信息：

Slot 0, Offset 0x60, Length 35, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFO

Memory Dump @0x445EC060

00000000: 50001200 01000000 64646464 64646464 †P.......dddddddd

00000010: 64200200 fcc00000 00010001 001d0000 †d ..............

00000020: 000000†††††††††††††††††††††††††††††††...

前面26个字节不用解释了吧，那这(c00000 00010001 001d0000 000000)14个字节是什么意思呢？大家是记得前面介绍过XSN啦，这就是14个字节指针信息，用于跟踪行版本信息的。sys.dm_tran_current_transaction 表返回的first_useful_sequence_num列就是是大XSN行版本。XSN的如下：

第一部分 8bytes
第二部分 6bytes

c00000 00010001 00
1d0000 000000

在tempdb中的文件号、页面号、slot号
记录版本的事务的序列号

exec sp_us_FPSinfo 0xc000000001000100

FILE_NUM:PAGE_ID:SLOT_ID

----------------------------

1 : 192 : 1
1d è 29

每一个事务都会分配一个独立并不断增加的XSN值

验证一下在tempdb中的内容 (注意红色部分)：

dbcc page('tempdb',1,192,1)

Slot 1, Offset 0xc8, Length 104, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = VARIABLE_COLUMNS

Memory Dump @0x441FC0C8

00000000: 26010068 0000851f 44f77f1b 01140000 †&..h....D.......

00000010: 00000000 00010000 00000000 0008001f †................