SQL2005中的事务与锁定.doc

SQL2005中的事务与锁定窗体顶端窗体底端SQL2005中的事务与锁定 一到九整合版 中的事务与锁定(一到九整合版 一到九整合版) 一、并发及并发控制模型 对于这个我在一文有所提及，你可以通过如 下链接进行访问：SQL2005 数据库引擎结构（三）并有一起的意思，显然就是 多个的意思啦，光书面来理解并发就是多个东西同时发生，在数据库并发就是多 个进程同时取、存数据库里数据的能力。着眼我们开发的系统，当然是激动态的 并互不打架的并发用户进程越多并发能力就越强大啦， 你想想看好多的网上购物 系统，如果没有并发处理的能力，那么在上面登记的用户信息、商品有库存信息 及用户帐户信息很难保证正确性和一致性，比如一个物品本身库存只有 100 个， 结果如果 100 人同时在线进行预定，库存就有可能搞一个 1001 的效果出来。 很显然对上述的例子我们希望一个进程在修改库存数据时必须阻止其它进程 读或修改数据， 或是正在读的用户进程限制其它活动的用户进程进行读或修改的 操作，这样一来势必造成系统的并发性能下降，但是如果不采用这种办法又无法 保证数据正确性和一致性。那怎么解决这个问题呢，办法只有通过不同的并发模 式来管理这些并发事件。我们下面来理解并发控制的模式、并发下可能发生的非 一致数据行为， 即并发副作用， 并由模式及数据行为引入事务及相关的 5 个隔离 等级等概念，进而来理解不同隔离等级下并发实现的机理，显然我们自己也就可 以回答上面这个问题了。 并发控制模式：一般并发控制模式有两种：积极并发（又称乐观并发）和消极并 发（又称悲观并发）。积极并发是 SQL2005 才引入的新模式，在 2005 以前的 版本其实只有唯一的并发模式即：消极并发。那什么是消极并发呢？消极并发就 是 SQLSERVER 默认行为是进程以获取锁的方式来阻止其它进程对正在使用的 数据进行修改的能力。对于数据库来说对数据修改的操作进程肯定很多，这些进 程肯定都会去影响其它进程读取数据的能力，反之，对数据进行读时加上锁也一 定会影响其它进程修改数据的能力，简而言之，就是读取与修改数据之间是冲突 的、互相阻塞的。乐观并发是 SQL2005 利用一个行版本控制器的新技术来处理 上述的冲突。行版本控制器在当前进程读取数据时生成旧版本的数据，使得其它 请求读的进程能看到当前进程一开始读取时的数据状态， 并且不受当前进程或其 它进程对数据进行修改的影响。简而言之读与修改之间是不冲突的，但是修改与 修改之间还是冲突的。 对于这两种并发模式两个进程同时请求数据修改必然会冲突的，除此以外的 差别在于一个是在冲突发生前进行控制，另一个在冲突发生了进行协调处理。这 好比生活一样，两种方式就是两种不同的人生，一种消极怠工一种积极向上。 2、 并发下可能发生的并发副作用：丢失更新、脏读、不可重复读、幻影。 为了把这些可能发生的并发副作用说清楚，我们先“布置”一个场景：这是一个 卖工艺石头的小商店， 平时在前场完成交易， 客户凭单据到后场领取石头， AMM 和 BMM 是营业员，她们平时掌握库存数是通过大厅里的一块 LED 显示牌得之， 并且在各自完成一笔交易后修改 LED 显示，以保证数据的实时性。在这个场景 下我们来观察可能发生的行为： 1、 丢失更新： 丢失更新估计是所有数据库用户都不想发生的情况， 什么是丢失更新呢？丢失更 新是当 2 个或两个以上的用户进程同时读取同样的数据后又企图修改原来的数 据时就会发生。好在上述场景下，大厅 LED 显示牌显示当前库存 1000，这时同 时有两个客户上门了， AMM 和 BMM 满面春风接待， 比如 AMM 卖出 1 个， BMM 呢卖出了 10 个， AMM 处理完业务后赶紧把 LED 显示数修改为 1000 为 1000 10 990 1 999 个，几乎同一时间 BMM 处理完自己的业务后习惯性的把 LED 显示数修改 个，这时老板从后场过来，看着 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、 3、 不可重复读 不可重复读又称不一致分析， 不过， 个人以为似乎不一致分析更让人好理解一点， 但是大部分地方称不可重复读。 不可重复读是指一个用户进程两次读取数据得到 不同样的数据。比如那个英明的老板吧，他知道要盘点，掌握库存的变化，忙得 満头大汗，终于计出库存数来，比如说 1000 吧，或是当他跑到大厅一看 LED 显示牌却只有了 900，显然这一次的检查库存的过程中两次得到库存数不一样， 原因就是 AMM 在老板从后场走到前场的过程中做了一担生意，卖出 100 块。嘿 嘿，老板气又不是不气又不是，这 AMM 真可爱，做生意挺两下呀！显然在一个 用户进程两次读取数据间隔内另一个用户进程修改了数据，这就是不可重复读。 4、 4、 幻影 幻影，嘿嘿，我们不是经常无视 BS 自己的人吗？你无视他并不代表他不 BS 你 吧，这个 BS 你的人就成了幻影，嘿嘿开个玩笑。这种情况多数在查询带谓词时 结果集内部分数据变化的时候发生， 如果谓词限定下在一个交易里两次同一查询 的结果集不同，那些不同的行或行集就是幻影。比方说英明的老板到大厅走走， 顺便请大家吃饭，数数人数，BMM，。一路数过去，发现有 10 人，呵呵， 正好一桌，通知好她们后老板回办会室拿人民币，回到前场看见 AMM，再一 数 11 人，晕，刚才怎么看到 AMM ？AMM 也知道了老板请客没数到他，很是 生气，这时 AMM 就成了幻影。 以上是四种并发副作用只是一个交易事务里或事务间可能发生的异常的非一致 的数据行为 （记好并发副作用和不一致的数据行为术语， 这在以后会经常提及） ， 其实还是有好多的行为是我们所期望的，那么我们期望的行为是什么呢，下面我 们在事务里来介绍。 我们可以通过隔离级别来设定一个合适级别以决定上述上种 数据行为哪些是允许的。那什么是交易事务，什么又是隔离等级呢？ 3、 事务 事务是数据库一笔交易的基本单元，存于两种并发模型中。又分为显式事务 和隐式事务。显式事务是显式的开始一个事务并显式的滚回或提交事务，除了显 式的事务还有隐式的了，隐式事务是数据库自己根据情况完成的事务处理，如单 独的 select、update、delete、select 语句。 作为一个事务，它能保证数据库的数据一致性及重做。提到事务不得不提及 事务的 ACID 属性：原子性、一致性、隔离性及持久性。不管是显式还是隐式的， 都必须维持这四个属性。 原子性：一个事务是一个整体，要不全部提交，要不全部中止。意思就是要 不全部成功提交到数据，要不全部回滚恢复事务开始前的状态。比方我们做一个 入库操作，在这个事务里，审核入库单和修改库存作为一个整体，要不单据变成 审核过同时库存增加相应的值，要不就是单据未审核同时库存不变。 一致性：一致性要求事务保证数据在逻辑上正确，处理的结果不是一个不确 定的状态，什么是不确定状态呢，比如说我们完成一个库存减少的操作，如果没 有一个出货单据那么这个库存的当前修改就是一个不确定状态， 因为你无法知道 减少的东东到哪儿去了。 隔离性：这个隔离和锁定有关，以后在说锁的过程中会提到这些，你先记住 这个就行。 持久性：持久很显然是要求正确提交的修改必须保证长久存在，不会因为关机或 掉电把这笔交易丢掉。进行中的事务发生故障那事务就完全撤销，像没有发生一 样，如果事务提交的确认已经反馈给应用程序发生故障，那么这些日志利用先写 技术，在启动恢复阶段自动完成相应的动作保证事务的持久性。 （这个在前面的 引擎组件有过介绍哦。）4、 隔离等级 首先来说说隔离，隔离是一个事务必须与其他事务所进行的资源或数据更改相隔开，显 然隔离等级就是相隔的程度了吧。 在说隔离级别不得不提及锁的概念， 但是在本单不提及锁， 在以后听章节里再作说明，大家只要有个印象就行。在这儿我们必须明白两件事： 1，隔离级别不会影响进程获得数据修改的排它锁，并且这个锁会保存到事务结束。相对 于读进程来说， 隔离级别就是对读操作的一个保护级别， 保护读操作受其它事务影响的程序。 2，较低的隔离级别可以增强许多用户同时访问数据的能力，但也增加了用户可能遇到的 并发副作用（例如脏读或丢失更新）的数量。相反，较高的隔离级别减少了用户可能遇到的 并发副作用，却需要太多的系统资源及一个事务阻塞其他事务的可能性。 应平衡应用程序的完整性要求与相应隔离级别的系统开销，在此基础上选择相应的隔离 级别。最高隔离级别（可序列化）保证事务在每次重复读取操作时都能准确检索到相同的数 据，但需要通过执行某种级别的锁定来完成此操作，而锁定可能会影响其他用户进程。最低 隔离级别（未提交读）可以检索其他事务已经修改但未提交的数据。在未提交读中，所有并 发副作用都可能发生，但因为没有读锁定或修改阻塞读取，所以开销最少。 不同的隔离级别决定我们有哪些数据副作用可以发生，而并发模型决定不同隔离等级下 如何来限制这些数据行为或如何协调这数据行为。 好， 那我们来关注一下不同隔离等级下如 何限制这些行为的发生。 未提交读（uncommitted Read）：字面理解一下，修改了的未提交数据可以读取。准确 点：一个用户进程可以读取另一个用户进程修改却未提交的数据。SQL SERVER 对这个等 级下的读操作不需要获得任何锁就可以读取数据， 因为不需要锁所以不会和其它任何进程互 相阻塞， 自然而然能读取其它进程修改了的却未提交数据。 显然这不是我们理想的一种模式， 但是它却有了高并发性， 因为读操作没有锁不会影响其它进程的读或写操作。 在这种级别下， 除了丢失更新（上一讲中的数据可能发生的行为）外，其它行为都有可能发生，冒着数据不 一致的风险来避免修改的进程阻塞读取的进程， 事务的一致性肯定是得不到保障， 显然这是 消极并发模式下的回避阻塞频繁的一种解决方案。 未提交读那肯定是不适合于股票、 金融系 统的，但是在一些趋势分析的系统里，要求的只是一种走向，准确性可以不是那么严格时， 这个级别因并发性能超强成为首选。 已提交读(Read Committed)：它和未提交读相反，已提交读级别保证一个进程不可能读 到另一个进程修改但未提交的数据。这个级别是引擎默认的级别，也是 2005 乐观并发模式 下支持的级别， 也就是说已提交读可是乐观的也可以是悲观的， 那究竟当前库是属于哪个并 发模型下的已提交读呢，这取决于一个 READ_COMMITED_SNAPSHOT 数据库配置项， 并且缺省是悲观并发控制的。这个配置项决定已提交读级别下事务使用锁定还是行版本控 制，并很显然行版本控制是乐观并发模式，锁定是悲观并发模式。我们来点角本看看： -设置已提交读隔离使用行版本控制 ALTER DATABASE testcsdn SET READ_COMMITTED_SNAPSHOT ON GO -查看当前已提交读隔离并发模型 select name,database_id,is_read_committed_snapshot_on from sys.databases /* name -master tempdb model msdb database_id is_read_committed_snapshot_on - -1 2 3 4 0 0 0 0 5 6 0 0 ReportServer$SQL2005 ReportServer$SQL2005TempDB TestCsdn 7 1 -current (7 行受影响) */ -设置已提交读隔离使用锁定 ALTER DATABASE testcsdn SET READ_COMMITTED_SNAPSHOT OFF GO -查看已提交读隔离并发模型 select name,database_id,is_read_committed_snapshot_on from sys.databases /* name -master tempdb model msdb database_id is_read_committed_snapshot_on - -1 2 3 4 0 0 0 0 5 6 0 0 ReportServer$SQL2005 ReportServer$SQL2005TempDB TestCsdn 7 0 -curret (7 行受影响) */ 已提交读在逻辑上保证了不会读到不实际存在的数据。悲观并发下的已提交读，当进程 要修改数据时会在数据行上申请排它锁，其它进程（无论是读还是写）必须等到排它锁释放 才可以使用这些数据。 如果进程仅是读取数据时会使用共享锁， 其它进程虽然可以读取数据 但是无法更新数据，必须等到共离锁释放（共享锁在数据处理完即释放，比如行共享锁在当 前数据行数据处理完就自动释放，不会在整个事务内保留发。）。乐观并发的已提交读，也 确保不会读到未提交的数据， 不是通过锁定的方式来实现， 而是通过行版本控制器生成行的 提前交的数据版本， 被修改的数据虽然仍然锁定， 但是其它进程可以可以读取更新前版本数 据。 可重复读（Repeatable Read）：这也是一个悲观并发的级别。可重复读比已提交读要 求更严格，在已提交读的基础上增加了一个限制：获取的共享锁保留到事务结束。在这个限 制下，进程在一个事务里两交次读取的数据一致，也就是不会读取到其它进程修改了数据。 在这儿我们提到共享锁会保留到事务结束， 那得申明一下无论哪种级别及并发模型， 排它锁 是一定要保留到事务结束的。 在可重复读级别共享锁同样也会保留到事务结束。 那么这种对 数据安全的保证是通过增加共享保留的开销为代价的， 也就是只要开始一个事务， 其它用户 进程是不可能修改数据的， 显而易见的系统的并发性和性能必然下降。 这似乎是我们想像中 的一种级别，虽然这个级别暂时无法回避幻影读，而且我们也默许并发及性能下降，那只有 对程序员对事务的控制有严格的要求： 事务要短并尽量不要人为因素的干扰， 减少潜在的锁 竞争。 快照(SnapShot)：乐观并发级别。这是 2005 新增加的一个隔离级别。快照级别与使用 乐观并发的已提交读差不多， 差别在于行版控制器里的数据版本有多早， 这个在以后讲锁时 再说。 这个级别保证了一个事务读取的数据是事务开始时就在数据库逻辑上确认并符合一致 性的数据。读操作不会要求共享锁定，如果要求的数据已经排它，就会通过行版本控制器读 取最近的符合一致性的数据。 可串行化：是目前最严谨、最健壮的一个级别，属于悲观并发。它防止幻影的发生，回 避了以前所有意外行为的发生。 可串行化意味着系统按进程进入队列的顺序依次、 序列化的 执行的结果与事务同时运行得到一致的结果。 这个最健壮的级别显然共享锁也是随事务开始 随事务结束，并通过锁定部分不存在的数据（即索引键范围锁定）来回避幻影的发生。 在前面的两篇里我从纯理论上把事务相关的知识作了一个梳理，有人看了一定觉得无 味了吧，好这一篇我们加入一点 T-SQL 语句把前面所说有东东关联起来，我们人为产生锁 定来理解不同的意外数据行为在不同隔离等级下的表现， 顺便再重温一下意外数据行及隔离 等级，让大家对交易事务有一个直观的认识。 在进行实例前不得不先介绍一点锁的知识， 注意这儿只是简单的说一下， 不作深入讨论。 我们根据用户访问资源的行为先归纳出几种锁，这几种锁在下面的实例里会出现，它们为： 共享锁定、排它锁定、更新锁定及用意向这个限定词限定的三种锁(意向共享、意向排它、 意向更新)，当当然还有其它的模式，我们在下一篇再说。意向锁的存在是解决死锁的发生， 保证进程在申请锁定前确定当前数据是否存在不兼容性的锁定。 先对上面提到的锁作一个简单的描述，更详细的下面再说。 共享锁定发生在查询记录时，直观就是我们 select 啦，但是并不是只有 select 才有共享 锁定。 一个查询记录的语句必须在没有与共享锁定互斥锁定存在或等待互斥锁定结束后， 才 能设置共享锁定并提取数据(互斥不互斥就是锁的兼容性，这在以后再说明)。 排它锁定发生在对数据增加、删除、修改时，事务开始以后语句申请并设置排它锁定(前 提是没有其它互斥锁定存在)，以明确告知其它进程对当前数据不可以查询或修改，等待事 务结束后其它进程才可以查询或修改。 更新锁定是一个介于共享与排它之间的中继锁定，比如我们带 where 条件的 update 语 句， 在查询要更新的记录时是设置共享锁定， 当要更新数据时这时锁定必须由共享锁定升级 成更新锁定继而升级为排它锁定， 当排它锁定设置成功才可以进行数据修改操作。 显然也是 要要求在锁升级的过程中没有互斥锁定的存在。 简单的理解更新锁定是一个中继闸一样， 把 升级成排它锁定进程“序列化”，以解决死锁。最后重点说明一下，数据更新阶段是要对数据 排它锁定不是更新锁定，不要被字面意思训导哦。 最后说一下在上述锁定模式下的互斥，共享锁定只与排它锁定互斥，更新锁定只与共享 锁定不互斥。 在进行具体实例前我们一定要有一个工具来对我们实例过程进行监控，好，下面我写了 一个过程，在需要时直接调用就行，过程如下： Create Proc sp_us_lockinfo - Author : HappyFlyStone - Date - BLOG - 申明 : 2009-10-03 15:30:00 : /happyflystone ：请保留作者信息，转载注明出处 -AS 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 锁定的对象, 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 ON 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, 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 GO /* 调用示例：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; GO 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 GO 约定：以下提及的查询 N，都是打开一个新连接执行查询 1、 未提交读（uncommitted Read） 概念回顾：未提交读是最低等级的隔离，允许其它进程读取本进程未提交的数据行，也就是 读取数据时不设置共享锁定直接读取， 忽略已经存在的互斥锁定。 很显然未提交读这种隔离 级别不会造成丢失更新，但是其它意外行为还是可以发生的。它和 select 加锁定提示 NOLOCK 效果相当。 测试实例： 查询一： SELECT * FROM TA WHERE TCID = 1 BEGIN TRAN UPDATE TA SET TCNAME = TA WHERE TCID = 1 -COMMIT TRAN -Dont commit SELECT * FROM TA WHERE TCID = 1 SELECT SPID /* tcid Tcname - -1 AA (1 行受影响) (1 行受影响) tcid Tcname - -1 TA (1 行受影响) SPID -54 (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 -Dont commit SELECT SPID /* tcid Tcname - -1 CA (1 行受影响) -54 (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 -Dont commit SELECT SPID /* tcid Tcname - -1 TA (1 行受影响) -54 (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 GO 查询一： SELECT * FROM TA WHERE TCID = 1 BEGIN TRAN UPDATE TA SET TCNAME = READ COMMITTED SNAP WHERE TCID = 1 -COMMIT TRAN -Dont commit SELECT SPID /* TCID TCNAME - -1 AA (1 行受影响) (1 行受影响) -56 (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 -Dont commit SELECT SPID /* tcid Tcname - -1 READ COMMITTED LOCK (1 行受影响) -52 (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 GO SELECT * FROM TA WHERE TCID = 1 -OLD 数据 BEGIN TRAN UPDATE TA SET TCNAME = SNAPSHOT WHERE TCID = 1 -COMMIT TRAN -Dont commit SELECT SPID /* tcid Tcname - -1 READ COMMITTED REP (1 行受影响) (1 行受影响) -52 (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 GO CREATE TABLE TB (ID INT Primary Key, COL VARCHAR(10) GO INSERT INTO TB SELECT 1,A GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM TB WHERE ID BETWEEN 1 AND 5-OLD 数据 -COMMIT TRAN -Dont commit SELECT SPID /* ID COL - -1 A (1 行受影响) -52 (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 NUL DB FIL TAB PAG KEY EXT RID APP MD HBT AU (13 行受影响) */ 备注： RID KEY PAG EXT HBT RID KEY PAGE EXTENT HOBT 1 2 3 5 6 7 8 9 10 11 12 13 0 LR LR LR LR LR LR LR LR LR LR LR LR LR NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 0 0 0 0 0 0 0 0 0 0 0 0 锁定堆中行的行标识符 序列化事务中的键范围行锁 数据或索引页面，8K 为单位 数据或索引页面，连续的 8*page 堆或 B 树，保护索引或堆表页堆的锁 TAB FIL APP MD AU DB TABLE FILE APPLICATION METADATA 整个表，包括数据及索引 数据库文件 应用程序资源 元数据 分配单元 数据库 ALLOCATION_UNIT 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 SCH-S SCH-M S U X IS IU IX SIU SIX UIX BU RANGES-S RANGES-U RANGEIN-NULL RANGEIN-S RANGEIN-U RANGEIN-X RANGEX-S RANGEX-U 4 5 6 7 8 9 1 2 3 L L L L L L L L L NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 0 0 0 0 0 0 0 0 NULL NULL NULL NULL NULL NULL L L L L NULL NULL NULL NULL L L L