预备篇

Size: px

Start display at page:

Download "预备篇"

悍祁
4 years ago
Views:

1 第 10 章游标事务与锁游标用来获得一个结果集, 逐行逐行地读取事务的作用是保证一系列的数据操作可以全部正确完成, 不会造成数据操作到一半未完成, 而导致数据的完整性出错锁的作用是保证数据在操作的过程中不会受到任何的其他影响游标事务和锁都是数据为中十分重要的功能, 合理使用事务和锁, 可以保证数据库中的数据有正确而完整本章涉及的数据库的内容比较多, 要给读者讲解游标事务和锁的定义和各种操作方法, 希望读者通过本章的学习, 理解游标事务和锁的定义, 掌握游标事务和锁的基本操作方法 10.1 游标在 SQL Server 中, 使用 SELECT 语句可以得到一个记录集, 然后将这个记录集返回给查询编辑器或应用程序当中去然而, 对于一些应用程序而言, 特别是那种交互式联机的应用程序, 并不是总能将整个结果集再进行有效地处理, 或者应用程序只要求返回处理后的结果, 而并非处理前的记录这就要求在 SQL Serve 当中进行处理后再返回结果给应用程序使用游标就可以对 SELECT 的结果集进行处理游标概述游标主要用在 T-SQL 批处理存储过程以及触发器当中, 其作用是存储一个结果集, 并能通过循环将这个结果集里的数据一条条取出来进行处理游标处理结果集的方法有以下几种 : 定位到结果集的某一行从当前结果集的位置搜索一行或一部分行允许对结果集中的当前行进行数据修改在存储过程或触发器中使用 Transact-SQL 游标的典型过程为 : (1) 声明 Transact-SQL 变量包含游标返回的数据为每个结果集列声明一个变量声明足够大的变量来保存列返回的值, 并声明变量的类型为可从列数据类型隐式转换得到的数据类型 (2) 使用 DECLARE CURSOR 语句将 Transact-SQL 游标与 SELECT 语句相关联另外,DECLARE CURSOR 语句还定义游标的特性, 例如游标名称以及游标是只读还是只进 (3) 使用 OPEN 语句执行 SELECT 语句并填充游标 (4) 使用 FETCH INTO 语句提取单个行, 并将每列中的数据移至指定的变量中然后, 其他 Transact-SQL 语句可以引用那些变量来访问提取的数据值 Transact-SQL 游标不支持提取行块

2 2 (5) 使用 CLOSE 语句结束游标的使用关闭游标可以释放某些资源, 例如游标结果集及其对当前行的锁定, 但如果重新发出一个 OPEN 语句, 则该游标结构仍可用于处理由于游标仍然存在, 此时还不能重新使用该游标的名称 DEALLOCATE 语句则完全释放分配给游标的资源, 包括游标名称释放游标后, 必须使用 DECLARE 语句来重新生成游标游标定义游标是一种数据结构, 在使用之前需要预先定义 ( 也就是声明游标 ) 游标定义通过 DECLARE CURSOR 语句来实现在 SQL Server 2000 中游标定义有两种方法 : 一种是 SQL-92 语法 ; 另一种是 Transact-SQL 语法下面分别介绍这两种语法 1. SQL-92 游标定义 SQL-92 游标定义的语法格式为 : DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY UPDATE [ OF column_name [,...n ] ] } ] SQL-92 游标定义的语法中各参数的含义如下 : cursor_name 表示游标的名字 INSENSITIVE 表明定义游标时将在 tempdb 数据库下创建一个临时数据表, 用于存储欲该游标使用的数据, 对该游标的读取操作皆由临时表来应答因此, 对基本表的修改并不影响游标提取的数据, 即游标不会随着基本表内容的改变而改变, 同时也无法通过游标来更新基本表如果不使用 INSENSITIVE 关键字, 那么对基本表所做的更新删除都会反映到后面的游标提取操作中 SCROLL 表明所有的提取操作 ( 如 FIRST LAST PRIOR NEXT RELATIVE ABSOLUTE) 都可用如果不使用该关键字, 那么只能进行 NEXT 提取操作因此,SCROLL 极大地增加了提取数据的灵活性, 可以随意读取结果集中的任一行数据记录, 而不必重新打开游标 select_statement 为 SELECT 语句, 它定义游标的返回结果集但不允许在 SELECT 语句中使用关键字 COMPUTE COMPUTE BY FOR BROWSE 和 INTO READ ONLY 指定游标为只读游标它禁止 UPDATE 或 DELETE 语句通过游标修改基本表中的数据 UPDATE [OF column_name [,...n]] 用来定义游标内可更新的列如果指定 OF 参数, 则只允许修改所列出的列如果在 UPDATE 中未指定列的列表, 则可以更新所有列 2. Transact-SQL 游标定义 Transact-SQL 语法是 SQL Server 中扩展了的游标定义语法, 该语法较复杂 Transact-SQL 游标定义的语法如下 : DECLARE cursor_name CURSOR [ LOCAL GLOBAL ] [ FORWARD_ONLY SCROLL ]

3 3 [ STATIC KEYSET DYNAMIC FAST_FORWARD ] [ READ_ONLY SCROLL_LOCKS OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [,...n ] ] ] Transact-SQL 游标定义的语法中各参数的含义如下 ( 在 Transact-SQL 语法中, 有一部分参数与 SQL-92 中的参数相同, 这里仅介绍其中不同的参数 ): LOCAL 表明所定义的是局部游标局部游标的作用域在其所在的存储过程触发器或批处理中当建立游标的存储过程执行结束后, 游标会被自动释放因此, 常在存储过程中使用 OUTPUT 保留字, 将游标传递给该存储过程的调用者, 这样在存储过程执行结束后, 可以引用该游标变量, 直到引用该游标的最后一个就是被释放时, 游标才会自动释放 GLOBAL 指定该游标的作用域是全局的选择 GLOBAL 表明在整个会话层的任何存储过程触发器或批处理中都可以使用该游标, 只有当用户脱离数据库时该游标才会被自动释放 FORWARD_ONLY 选项指明在从游标中提取记录时, 只能按照从第一行到最后一行的顺序, 此时只能选用 FETCH NEXT 操作如果未指明是使用 FORWARD_ONLY 还是使用 SCROLL, 那么 FORWARD_ONLY 将成为默认选项, 因为若使用 STATIC KEYSEÔ 和 DYNAMIC 关键字, 则变成了 SCROLL 游标另外,FORWARD_ONLY 与 FAST_FORWARD 不能同时被使用 STATIC 与 SQL-92 游标定义中 INSENSITIVE 参数的意义和作用相同 KEYSET 指出所定义游标是一个键集驱动游标当游标被打开时, 游标中列的顺序是固定的, 并且 SQL SERVER 会在 tempdb 内建立一个表, 该表即为 KEYSET KEYSET 的键值可惟一识别游标中的某行数据 DYNAMIC 指出将游标定义为动态游标使用这个选项会最大程度上保证数据的一致性提取动态游标数据时, 不支持 ABSOLUTE 提取选项 FAST_FORWARD 使得 FORWARD_ONLY READ_ONLY 游标执行最优化如果 SCROLL 或 FOR_UPDATE 选项被定义, 则 FAST_FORWARD 选项不能被定义, 反之, 也如此 SCROLL_LOCKS 指明在数据被读入游标中时, 就锁定基本表中的数据行, 以确保以后能通过游标成功的对基本表进行更新和删除操作如果 FAST_FORWARD 被定义, 则不能选择该选项 OPTIMISTIC 指明在数据被读入游标后, 如果游标中某行数据已发生变化, 那么对游标数据进行更新或删除可能会导致失败如果使用了 FAST_FORWARD, 则不能使用该选项 TYPE_WARNING 指明若游标类型被修改成与用户定义的类型不同时, 将发送一个警告信息给客户端在游标定义中, 如果有下列条件之一, 无论是否指定 STATIC 选项, 系统将自动把所建立的游标定义为静态游标 : SELECT 语句中使用了 DISTINCT UNION GROUP BY 和 HYVING 等关键字

4 4 SELECT 语句的选择列表中包含有集合表达式所有游标基表均没有建立唯一索引, 而又要求建立键集游标时例 : 创建一个游标 cur_student, 取得学员的学号姓名性别出生年月日其代码如下 : DECLARE cur_student SCROLL CURSOR for select s_no,s_name,s_sex,s_age from t_student 游标操作节讲述了游标的几个步骤, 即声明游标, 打开游标, 获取数据, 关闭游标, 释放游标, 节讲述了声明游标, 下面详细介绍其他操作 1. 打开游标定义了游标之后, 在使用之前必须先将其打开才能使用, 打开游标的语法如下 : OPEN { { [ GLOBAL ] cursor_name } cursor_variable_name } 参数说明 : GLOBAL 指定 cursor_name 指的是全局游标 cursor_name 是已声明但没有打开的游标如果全局游标和局部游标都使用 cursor_name 作为其名称, 那么如果指定了 GLOBAL,cursor_name 指的是全局游标, 否则 cursor_name 指的是局部游标 cursor_variable_name 是游标变量的名称, 该名称引用一个游标在打开游标之后, 来显示游标内的记录条数来返回上一条游标 fetch 语句的状态有以下几种取值 : n 表示该游标所定义的数据已完全从表中读入,n 为全部的数据行 -m 表示该游标所定义的数据未完全从表中读入,m 为目前游标数据子集内的数据行 0 表示无符合条件的数据或该游标已被关闭或释放 -1 表示该游标为动态的, 数据行经常变动, 无法确定 2. 获取数据打开游标之后, 可以使用 fetch 语句来读取游标中的记录 Fetch 语句用于检索游标中的特定行, 如上一条记录下一条记录首条记录尾条记录等, 其语法如下 : FETCH [[ NEXT PRIOR FIRST LAST ABSOLUTE { RELATIVE { ] FROM ] { { [ GLOBAL ] cursor_name } [ [,...n ] ]

5 5 参数说明 : NEXT: 紧跟当前行返回结果行, 并且当前行递增为返回行如果 FETCH NEXT 为对游标的第一次提取操作, 则返回结果集中的第一行 NEXT 为默认的游标提取选项 PRIOR: 返回紧邻当前行前面的结果行, 并且当前行递减为返回行如果 FETCH PRIOR 为对游标的第一次提取操作, 则没有行返回并且游标置于第一行之前 FIRST: 返回游标中的第一行并将其作为当前行 LAST: 返回游标中的最后一行并将其作为当前行 ABSOLUTE { 如果 n 为正数, 则返回从游标头开始的第 n 行, 并将返回行变成新的当前行 RELATIVE { 如果 n 为正数, 则返回从当前行开始的第 n 行, 并将返回行变成新的当前行 GLOBAL: 指定 cursor_name 是指全局游标 cursor_name: 要从中进行提取的打开的游标的名称 cursor_variable_name: 游标变量名, 引用要从中进行提取操作的打开的游标允许将提取操作的列数据放到局部变量中 3. 关闭游标在处理完游标中的数据之后, 必须关闭游标来释放数据结果集和定位于数据上的锁所以, 在关闭游标后, 禁止提取游标数据或通过游标进行定位修改和删除操作但关闭游标后并不释放游标占用的数据结构可以使用 CLOSE 语句关闭游标,CLOSE 语句的语法格式为 : CLOSE cursor_name CLOSE 语句的语法中参数的含义如下 : cursor_name 是需要关闭的游标名称 4. 释放游标由于关闭游标时并没有删除游标, 因此, 它仍然占用着系统资源要再一次使用它时, 可以使用 OPEN 语句重新打开关闭一个游标从本质上来说是关闭了它的结果集, 而并不是它的全部内容如果一个游标确定不再使用, 可以使用 DEALLOCATE 命令释放游标所占用的资源, 此时, 游标的名字可以被再次使用 DEALLOCATE 语句的语法格式为 : DEALLOCATE { { [ GLOBAL ] cursor_name } DEALLOCATE 语句的中参数的含义如下 : cursor_name 是已声明游标的名称当全局和局部游标都以 cursor_name 作为它们的名称存在时, 如果指定 GLOBAL, 则 cursor_name 引用全局游标, 如果未指定 GLOBAL, 则 cursor_name 是 cursor 变量的名称, 且其必须为 cursor 类型

6 游标示例教务管理系统, 逐条读取学员的学号姓名性别出生年月日, 并打印, 其代码如下 : varchar(20) DECLARE cur_student SCROLL CURSOR for select s_no,s_name,s_sex,s_age from t_student open cur_student fetch next from cur_student =0 begin print ' 学号 :'+@s_no + ', 姓名 :'+@s_name +', 性别 :'+ when 1 then ' 男 ' when 0 then ' 女 ' else ' 未知性别 ' end +', 出生年月日 :'+@s_age fetch next from cur_student end close cur_student deallocate cur_student 运行结果如图 10-1 所示

7 7 图 10-1 游标示例运行结果 10.2 事务概述事务是 SQL Server 中的单个逻辑工作单元, 一个事务内的所有语句被作为一个整体执行在事务执行过程中, 如果遇到错误, 则可以回滚事务, 取消该事务所做的全部改变或部分改变, 从而保证数据库的一致性和可恢复性也就是说, 一个事务要么其中的语句全部正确执行, 要么全部语句不起作用事务概述事务是作为单个逻辑工作单元执行的一系列操作一个逻辑工作单元必须有四个属性, 称为原子性一致性隔离性和持久性 (ACID) 属性, 只有这样才能成为一个事务原子性 : 事务必须是原子工作单元 ; 对于其数据修改, 要么全都执行, 要么全都不执行一致性 : 事务在完成时, 必须使所有的数据都保持一致状态在相关数据库中, 所有规则都必须应用于事务的修改, 以保持所有数据的完整性事务结束时, 所有的内部数据结构 ( 如 B 树索引或双向链表 ) 都必须是正确的隔离 : 由并发事务所作的修改必须与任何其他并发事务所作的修改隔离事务识别数据时数据所处的状态, 要么是另一并发事务修改它之前的状态, 要么是第二个事务修改它之后的状态, 事务不会识别中间状态的数据这称为可串行性, 因为它能够重新装载起始数据, 并且重播一系列事务, 以使数据结束时的状态与原始事务执行的状态相同

8 8 持久性 : 事务完成之后, 它对于系统的影响是永久性的该修改即使出现系统故障也将一直保持事务分类事务是单个的工作单元如果某一事务成功, 则在该事务中进行的所有数据修改均会提交, 成为数据库中的永久组成部分如果事务遇到错误且必须取消或回滚, 则所有数据修改均被清除 SQL Server 以下列事务模式运行自动提交事务 : 每条单独的语句都是一个事务显式事务 : 每个事务均以 BEGIN TRANSACTION 语句显式开始, 以 COMMIT 或 ROLLBACK 语句显式结束隐式事务 : 在前一个事务完成时新事务隐式启动, 但每个事务仍以 COMMIT 或 ROLLBACK 语句显式完成批处理级事务 : 只能应用于多个活动结果集 (MARS), 在 MARS 会话中启动的 Transact-SQL 显式或隐式事务变为批处理级事务当批处理完成时没有提交或回滚的批处理级事务自动由 SQL Server 进行回滚事务日志每个 SQL Server 2005 数据库都具有事务日志, 用于记录所有事务以及每个事务对数据库所做的修改事务日志是数据库的一个重要组件, 如果系统出现故障, 它将成为最新数据的唯一源删除或移动事务日志以前, 必须完全了解此操作带来的后果事务日志支持以下操作 : 恢复个别的事务 : 如果应用程序发出 ROLLBACK 语句, 或者数据库引擎检测到错误 ( 例如失去与客户端的通信 ), 就使用日志记录回滚未完成的事务所做的修改 SQL Server 启动时恢复所有未完成的事务 : 当运行 SQL Server 的服务器发生故障时, 数据库可能处于这样的状态 : 还没有将某些修改从缓存写入数据文件, 在数据文件内有未完成的事务所做的修改当启动 SQL Server 实例时, 它对每个数据库执行恢复操作前滚日志中记录的可能尚未写入数据文件的每个修改在事务日志中找到的每个未完成的事务都将回滚, 以确保数据库的完整性将还原的数据库文件文件组或页前滚到故障点 : 在硬件丢失或磁盘故障影响到数据库文件后, 可以将数据库还原到故障点首先还原上一次的完整备份和差异备份, 然后将事务日志备份后续序列还原到故障点当还原每个日志备份时, 数据库引擎重新应用日志中记录的所有修改, 以前滚所有事务当最后的日志备份还原后, 数据库引擎将使用日志信息回滚到该点未完成的所有事务支持事务复制 : 日志读取器代理程序监视已为事务复制配置的每个数据库的事务日志, 并将已设复制标记的事务从事务日志复制到分发数据库中有关详细信息, 请参阅事务复制的工作机制

9 9 支持备用服务器解决方案 : 备用服务器解决方案数据库镜像和日志传送高度依赖于事务日志在日志传送方案中, 主服务器将主数据库的活动事务日志发送到一个或多个目标服务器每个辅助服务器将该日志还原为其本地的辅助数据库 10.3 事务操作前面讲述了事务的基础知识, 读者肯定想自己创建一个事务, 并对事务进行操作这里就讲述如何编写事务, 编写事务要注遵守以下原则 : 事务要尽可能简短 : 事务在启动之后, 数据库管理系统会在事务结束之前保留很多资源, 以保证事务的原子性一致性隔离性和持久性, 如果在事务里还要修改数据, 数据管理系统还会使用锁来保护修改过的行, 此时其他事务不能对这些进行控制, 直到事务结束在多用户的系统里, 这种过大资源的占有和过长时间的锁定, 将是不能忍受的在事务中访问的数据量要尽量最少 : 由于事务会锁定记录, 访问的数据量越少, 锁定的行数就越少, 这样事务之间的争夺就越少浏览数据时尽量不要打开事务 : 如果只是浏览数据, 尽可能不要打开事务, 避免占用过量资源在事务处理期间尽量不要请求用户输入 : 在事务处理过程, 如果还要等待用记入, 事务占用的所有资源都会保留相当长的时间, 有可能会造成阻塞问题开始事务标记一个显式本地事务的起始点, 在 T-SQL 语言里是使用 begin transaction 语句, 其语法如下 : BEGIN { TRAN TRANSACTION } [ { } [ WITH MARK ['description']] ] [ ; ] 参数说明 : transaction_name: 分配给事务的名称 tran_name_variable: 用户定义的含有有效事务名称的变量的名称必须用 char varchar nchar 或 nvarchar 数据类型声明变量如果传递给该变量的字符多于 32 个, 则仅使用前面的 32 个字符 ; 其余的字符将被截断 WITH MARK [ 'description' ]: 指定在日志中标记事务 description 是描述该标记的字符串提交事务自动提交模式是 SQL Server Database Engine 的默认事务管理模式每个 Transact-SQL 语句在完成时, 都被提交或回滚如果一个语句成功地完成, 则提交该

10 10 语句 ; 如果遇到错误, 则回滚该语句只要没有显式事务或隐性事务覆盖自动提交模式, 与数据库引擎实例的连接就以此默认模式操作自动提交模式也是 ADO OLE DB ODBC 和 DB 库的默认模式标志一个成功的隐性事务或显式事务的结束为 1, COMMIT TRANSACTION 使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分, 释放事务所占用的资源, 减少到 0 大于 1, 则 COMMIT TRANSACTION 按 1 递减并且事务将保持活动状态提交事务的语法为 : COMMIT { TRAN TRANSACTION } [ ] ] [ ; ] 参数说明 : transaction_name : 指定由前面的 BEGIN TRANSACTION 用户定义的含有有效事务名称的变量的名称必须用 char varchar nchar 或 nvarchar 数据类型声明变量如果传递给该变量的字符数超过 32, 则只使用 32 个字符, 其余的字符将被截断回滚事务清除自事务的起点或到某个保存点所做的所有数据修改叫做回滚事务, 还释放由事务控制的资源, 在 T-SQL 语言里使用 rollback 回滚事务, 其语法如下 : ROLLBACK { TRAN TRANSACTION } [ ] [ ; ] 参数说明 : transaction_name: 是为 BEGIN TRANSACTION 是用户定义的包含有效事务名称的变量的名称必须 char varchar nchar 或 nvarchar 数据类型声明变量 savepoint_name: 是 SAVE TRANSACTION 语句中的是用户定义的包含有效保存点名称的变量的名称必须用 char varchar nchar 或 nvarchar 数据类型声明变量锁定数据当多个用户同时访问数据时,Microsoft SQL Server 2005 Database Engine 使用下列机制确保事务的完整性并维护数据库的一致性 : 锁定 : 每个事务对所依赖的资源 ( 如行页或表 ) 请求不同类型的锁锁可以阻止其他事务以某种可能会导致事务请求锁出错的方式修改资源当事务不再依赖锁定的资源时, 它将释放锁行版本控制 : 当启用了基于行版本控制的隔离级别时, 数据库引擎将维护修改的每一行的版本应用程序可以指定事务使用行版本查看事务或查询开始时存在的数据, 而不是使用锁保护所有读取通过使用行版本控制, 读取操作阻

11 11 止其他事务的可能性将大大降低锁定和行版本控制可以防止用户读取未提交的数据, 还可以防止多个用户尝试同时更改同一数据如果不进行锁定或行版本控制, 对数据执行的查询可能会返回数据库中尚未提交的数据, 从而产生意外的结果显式事务显式事务是指显式定义了其启动和结束的事务可以通过使用 SQL 语句或数据库 API 函数来指定显式事务 SQL Server 提供下列三种来定义显式事务 : BEGIN TRANSACTION: 为一个连接标记出显式事务的起始点 COMMIT TRANSACTION : 在没有出现错误时成功结束事务由事务修改的所有数据都会永久成为数据库的一部分事务所持有的资源将被释放 ROLLBACK TRANSACTION : 清除出现错误的事务由事务修改的所有数据都将返回到事务启动时的状态事务所持有的资源将被释放隐式事务当连接以隐性事务模式进行操作时,SQL Server 实例将在提交或回滚当前事务后自动启动新事务无须描述事务的开始, 只需提交或回滚每个事务隐性事务模式生成连续的事务链为连接将隐性事务模式设置为打开之后, 当数据库引擎实例首次执行下列任何语句时, 都会自动启动一个事务 : ALTER TABLE INSERT CREATE OPEN DELETE REVOKE DROP SELECT FETCH TRUNCATE TABLE GRANT UPDATE 在发出 COMMIT 或 ROLLBACK 语句之前, 该事务将一直保持有效在第一个事务被提交或回滚之后, 下次当连接执行以上任何语句时, 数据库引擎实例都将自动启动一个新事务该实例将不断地生成隐性事务链, 直到隐性事务模式关闭为止例 : 查询二个语句来模拟隐式事务, 其代码为 : SET IMPLICIT_TRANSACTIONS ON; -- 开始第一个新的事务 select * from t_class -- 提交第一个新的事务 commit transaction

12 12 -- 开始第二个新的事务 select * from t_student -- 提交第二个新的事务 commit transaction SET IMPLICIT_TRANSACTIONS off; 嵌套事务显式事务可以嵌套这主要是为了支持存储过程中的一些事务, 这些事务可以从已在事务中的进程调用, 也可以从没有活动事务的进程中调用虽然事务也支持嵌套, 但是事务的嵌套与其他的嵌套方式有些不同通常来说, 一个对象里嵌套另一个对象, 都会先执行外层嵌套里的 T-SQL 语句, 当执行到嵌套语句时, 将转向去内层嵌套的 T-SQL 语句, 当内层嵌套的 T-SQL 语句执行完毕, 然后再执行外层嵌套 T-SQL 语句, 直到所有 T-SQL 语句执行完毕然后嵌套事务里并不是将嵌套的语句完全执行完毕, 嵌套事务会将内层事务的 commit 语句忽略, 只提交最外层事务的 commit 语句由于事务具有原子性, 事务内的所有语句操作都是一个单元, 要么全部执行, 要么全部不执行, 所以在内层事务里的 commit 都是没有意义的虽然在内层事务里的 commit 是没有意义的, 但是也不能将其省略, 因为事务是以 commit 或 rollback 语句作为事务结束的标志如果省略, 系统无法知道内层事务语句是那哪里结束的在内层事务里,rollback 语句仍然有效, 但并不可以将事务回滚到内层事务开始之前的状态, 而是回滚到外层事务开始之前的状态例 : 创建一个嵌套事务, 在外层事务里为复学表增加二记录, 在内层事务里添加一条记录, 其代码如下 : -- 查看执行事务之前的记录 select * from t_stu_rest -- 开始事务 begin tran -- 插入二条记录 insert t_stu_rest(s_id,old_class_id,rest_reason,apply_date) values(45,10,' 家中有事情 ',' ') goto label insert t_stu_rest(s_id,old_class_id,rest_reason,apply_date) values(46,10,' 家中有事情 ',' ') goto label -- 查看嵌套事务之前的记录 select * from t_stu_rest

13 -- 嵌套开始 begin tran insert t_stu_rest(s_id,old_class_id,rest_reason,apply_date) values(54,10,' 家中有事情 ','2007-6-5') if @@error>0 or @@rowcount<>1 rollback tran else commit tran -- 嵌套结束 --

13 13 -- 嵌套开始 begin tran insert t_stu_rest(s_id,old_class_id,rest_reason,apply_date) values(54,10,' 家中有事情 ',' ') if or rollback tran else commit tran -- 嵌套结束 -- 查看嵌套事务之后的记录 select * from t_stu_rest begin label: rollback tran end else commit tran -- 查看所有事务完成之后的记录 select * from t_stu_rest 该代码运行结果如图 10-2 所示图 10-2 嵌套事务运行结果管理事务隔离事务指定一个隔离级别, 该隔离级别定义一个事务必须与其他事务所进行的资源

14 14 或数据更改相隔离的程度隔离级别从允许的并发副作用 ( 例如, 脏读或幻读 ) 的角度进行描述选择事务隔离级别不影响为保护数据修改而获取的锁事务总是在其修改的任何数据上获取排他锁并在事务完成之前持有该锁, 不管为该事务设置了什么样的隔离级别对于读取操作, 事务隔离级别主要定义保护级别, 以防受到其他事务所做更改的影响较低的隔离级别可以增强许多用户同时访问数据的能力, 但也增加了用户可能遇到的并发副作用 ( 例如脏读或丢失更新 ) 的数量相反, 较高的隔离级别减少了用户可能遇到的并发副作用的类型, 但需要更多的系统资源, 并增加了一个事务阻塞其他事务的可能性应平衡应用程序的数据完整性要求与每个隔离级别的开销, 在此基础上选择相应的隔离级别最高隔离级别 ( 可序列化 ) 保证事务在每次重复读取操作时都能准确检索到相同的数据, 但需要通过执行某种级别的锁定来完成此操作, 而锁定可能会影响多用户系统中的其他用户最低隔离级别 ( 未提交读 ) 可以检索其他事务已经修改但未提交的数据在未提交读中, 所有并发副作用都可能发生, 但因为没有读取锁定或版本控制, 所以开销最少 10.4 锁锁是防止其他事务访问指定的资源控制实现并发控制的一种主要手段并发控制的主要方法是封锁, 锁就是在一段时间内禁止用户做某些操作以避免产生数据不一致当 INSERT UPDATE DELETE 语句更改某个行中的数据时, 就在行上加了一个锁这些锁被放在表中没有提交的行上, 以防止在最后完成更改之前修改这些行每个行的锁都独立于其他行的锁完成提交或者回滚操作后, 锁被释放锁定模式确定并发事务可以访问数据的方式 SQL Server Mobile 根据必须锁定的资源和必须执行的操作来确定使用哪种锁定模式共享 (S): 保护资源, 以便只能对其进行读取访问当资源上存在共享 (S) 锁时, 其他事务均不能修改数据排他 (X): 指示数据修改, 例如插入更新或删除确保不能同时对同一资源进行多个更新更新 (U): 防止常见形式的死锁每次只有一个事务可以获得资源上的 U 锁如果事务修改资源, 则 U 锁将转换为 X 锁架构 : 在执行依赖于表架构的操作时使用架构锁的类型是架构修改 (Sch-M) 和架构稳定性 (Sch-S) 意向 : 建立锁层次结构最常见的意向锁类型是 IS IU 和 IX 这些锁指示事务正在处理层次结构中较低级别的某些资源, 而不是所有资源较低级别的资源将具有 S U 或 X 锁当两个连接相互阻止对方时, 就会出现死锁如果一个死锁涉及到两个事务, 则其中一个事务会等待资源超时, 同时会出现错误超时的事务仍然处于活动状态 ; 它不会被提交或回滚另一个事务于是获得了所需的锁, 然后继续执行

15 示例图书管理系统, 书籍类型插入二条记录, 然后删除刚才插入的最后一条记录, 看看事务的结果, 代码如下 : -- 查看事务开始之前的原始数据 select * from booktype -- 事务开始 begin tran -- 插入第一条记录 insert booktype values('0004',' 物理 ',' 物理 ') goto label -- 插入第二条记录 insert booktype values('0005',' 英语 ',' 英语 ') goto label -- 查看插入记录后的数据集 select * from booktype -- 删除最后插入的记录 delete from booktype where booktypeno in(select max(booktypeno) from booktype) begin label: rollback tran end else commit tran -- 事务结束 -- 事务结束后的数据 select * from booktype 运行结果如图 10-3 所示

16 16 图 10-3 示例运行结果

untitled

untitled OO 1 SQL Server 2000 2 SQL Server 2000 3 SQL Server 2000 DDL 1 2 3 DML 1 INSERT 2 DELETE 3 UPDATE SELECT DCL 1 SQL Server 2 3 GRANT REVOKE 1 2 1 2 3 4 5 6 1 SQL Server 2000 SQL Server SQL / Microsoft SQL