《数据库原理与应用》11.事务和游标的创建和使用

孙 发 勤 扬州大学新闻与传媒学院 YZU.SUN163.COM 数据库原理与应用 第十一讲 事务和游标的创建和使 用 创建事务 提交事务 回滚事务 回滚事务的一部分 用锁 理解死锁 声明游标 打开游标 从游标取数据 关闭游标 本讲主要内容 例： 一个员工代码为 000002的内部候选人已经被选定 为 Sales Manager（ 岗位代码为 0001）岗位。这 必须在 Employee表中被更新，当前已在该岗位的 人数也需要在 Position表中更新。 上面要做的 UPDATE语句如下： UPDATE Employee SET cCurrentPosition = 0001 WHERE cEmployeeCode= 000002 UPDATE Position SET iCurrentStrength=iCurrentStrength + 1 WHERE cPositionCode=0001 系统崩溃是由于两个更新之间导致数据不一 致性而引起的。需要防止这种情况，要确保两个 更新或者都发生或者都不发生。 问题： 怎样防止数据的不一致性？ 执行事务 验证数据在两个表中都已更新 怎样防止数据的不一致性？ 事务 一个事务可以被定义为作为工作的单个的逻辑单元被一起执行的 一串的操作。 单个的工作单元必须具有称为 ACID（原子性，一致性，独立性， 和持久性）的 四个性质 原子性 一致性 独立性 持久性 怎样防止数据的不一致性 (续 ) 为实现 ACID性质的需求， SQL Server提供了下面的特性 : 3事务管理 3上锁 3日志 事务日志 -是 SQL Server管理所有它的的事务而维护的日志 显式事务 -是事务的开始和结束都被显式地定义的事务。 怎样防止数据的不一致性 (续 ) BEGIN TRANSACTION:该 语句标志显示事务的开始 语法 BEGIN TRANSACTION transaction_name | tran_name_variable COMMIT TRANSACTION或 COMMIT WORK:语句标志显 式事务的结束点 语法 COMMIT TRANSACTIONtransaction_name |tran_name_variable 怎样防止数据的不一致性 (续 ) Autocommit事务 autocommit模式是 SQL Server的缺省事务管理模式， 当使用 SET AUTOCOMMIT OFF开启事务支持时，所有 insert， delete或 update 操作列表存储在内存中，因此，当进行 ROLLBACK事务时，这些 操作能够被撤销。跨越几百个数据改变的事务将会花费很多的内 存知道下一个 COMMIT或 ROLLBACK清除了操作列表。 结果： 事务的使用可以避免数据的不一致性 UPDATE语句可通过使用 BEGIN TRANSACTION和 COMMIT TRANSACTION语句来维持其原子性 执行事务 动作： 在 Query Analyzer窗口中，键入： BEGIN TRANSACTION trnUpdatePosition UPDATE Employee SET cCurrentPosition = 0001 WHERE cEmployeeCode= 000002 UPDATE Position SET iCurrentStrength = iCurrentStrength + 1 WHERE cPositionCode = 0001 COMMIT TRANSACTION trnUpdatePosition 验证两个表中的数据都已被更新 动作： 下面的 SELECT语句来验证那些行已被更新： SELECT * FROM Position WHERE cPositionCode = 0001 SELECT * FROM Employee WHERE cEmployeeCode = 000002 识别下面单个工作单元的性质： 由并发事务引起的任何数据修改必须与其他并发事务所作的 修改隔离开来 所有的数据修改都被执行或者都没有被执行 已完成事务的任何数据改变在系统中永久起作用 事务成功地完成后所有的数据必须处于一致的状态 2.回复改变 职位 0015已经招募了 10个候选人。为了反映这一变 化，对于 RequisitionCode 0015 ， Requisition表的 NuOfRequir属性将会减少 10。还有，对于 cPositionCode 0015， Position表的 iCurrentStrength属 性要加 10，使用下面命令： UPDATE Requisition set NuOfRequir = NuOfRequir - 10 WHERE RequisitionCode=0015 2.回复改变 (续 ) UPDATE Position set iCurrentStrength=iCurrentStrength + 10 WHERE cPositionCode=0015 这两个语句都应该是原子的，如果 iCurrentStrength属 性变得不只是 iCurrentStrength属性，那么，由 UPDATE语句所作的改变必须回复。 识别怎样恢复所作的改变 执行事务 验证事务是否被执行 如何回复所作的改变 ? ROLLBACK TRANSACTION或 ROLLBACK WORK:这些 语句把显式的或隐式的事务回滚到 事务的开始，或者回滚到事务内的 保存点 语法 ROLLBACK TRANSACTION transaction_name |tran_name_variable |savepoint_name | savepoint_variable 结果 事务可用 ROLLBACK TRANSACTION语句回复 BEGIN TRANSACTION UPDATE Requisition SET NuOfRequir = NuOfRequir - 10 WHERE cRequisitionCode=0015 UPDATE Position SET iCurrentStrength=iCurrentStrength + 10 WHERE cPositionCode=0015 IF (SELECT iMaxStrength-iCurrentStrength FROM Position WHERE cPositionCode = 0015) 0 BEGIN PRINT Current strength cannot be more than Max strength. Transaction has not been committed. ROLLBACK TRANSACTION END ELSE BEGIN PRINT The transaction has been committed. COMMIT TRANSACTION END 验证事务是否被执行 动作： 查看结果窗口中所显示的输出。 3.回复事务的一部分 Employee和 Position表需要用下面的事务来更新： 事务 1: UPDATE Employee SET cCurrentPosition = 0015 WHERE cEmployeeCode = 000002 UPDATE Position SET iCurrentStrength = iCurrentStrength + 1 WHERE cPositionCode = 0015 Requisition 和 Position表需要使用下面的事务来更新： 事务 2: UPDATE Requisition SET NuOfRequir = NuOfRequir - 10 WHERE cRequisitionCode=0015 UPDATE Position SET iCurrentStrength=iCurrentStrength + 10 WHERE cPositionCode=0015 3回复事务的一部分 (续 ) 所有更新应一起来做。对于 cPositionCode 0015，如果 iCurrentStrength值大于 iMaxStrength值，由第二个事务所产 生的改变必须被回复，而由第一个事务产生的改变是允许 的。 识别怎样把事务分解成部分 执行事务 验证事务的执行 怎样把事务分解成部分 ? 保存事务 它在事务内设置保存点 。保存点把事务分成几个逻辑单元 ， 这样事务可以返回到保存点，如果事务的一部分是有条件地被 取消。 语法 SAVE TRANSACTION savepoint_name | savepoint_variable 结果 事务可以用 SAVE TRANSACTION语句 分解成几个逻辑单元 保存点提供了一种机制，用于回滚部分事务。可以使用 SAVE TRANSACTION savepoint_name 语句创建一个保存点，然后再执 行 ROLLBACK TRANSACTION savepoint_name 语句回滚到该 保存点，从而无须回滚到事务的开始。 在不可能发生错误的情况下，保存点很有用。在很少出现错误 的情况下使用保存点回滚部分事务，比让每个事务在更新之前测试 更新的有效性更为有效。更新和回滚操作代价很大，因此只有在遇 到错误的可能性很小，而且预先检查更新的有效性的代价相对很高 的情况下，使用保存点才会非常有效。 BEGIN TRANSACTION UPDATE Employee SET cCurrentPosition = 0015 WHERE cEmployeeCode = 000002 UPDATE Position SET iCurrentStrength = iCurrentStrength + 1 WHERE cPositionCode = 0015 SAVE TRANSACTION trnTransaction1 UPDATE Requisition SET NuOfRequir=NuOfRequir - 10 WHERE cRequisitionCode=0015 UPDATE Position SET iCurrentStrength=iCurrentStrength+10 WHERE cPositionCode=0015 IF (SELECT iMaxStrength-iCurrentStrength FROM Position WHERE cPositionCode = 0015) 80 UPDATE ExternalCandidate SET dInterviewDate = getdate()+ 2 WHERE siTestScore 80 COMMIT TRANSACTION User2为什么不能执行此事务 ? 为什么 User2不能执行此事务 ? 上锁 确保事务的完整性和数据库的一致性 是自动实施的 不上锁，查看事务处理是不可能的。 为什么 User2不能执行此事务 (续 ) * 事务的并发性 3 SQL Server提供了乐观的和悲观的并发性控件 3 乐观并发性控件 建立在多用户间资源冲突大概是不可能的假设的基础 上 允许事务执行不用锁定任何资源 只有在提交事务时才进行资源检查 为什么 User2不能执行此事务 (续 ) 3 悲观并发性 控件 在事务处理期间的锁定资源 并发性问题 丢失更新 丢失更新问题发生在当两个或多个事务基于原先所选值试图修改同一 行的时候 自由依赖性 自由依赖性 问题 （ uncommitted dependency ）也称为 无效读入（ dirty read） 问题 为什么 User2不能执行此事务 (续 ) 不一致性分析 不一致性分析 问题又称为 不可重复 问题 幻象读取 幻象读取 又称为作幻象问题 为什么 User2不能执行此事务 (续 ) SQL Server锁模型 共享锁 允许并发事务来读取资源 更新锁 避免了常见形式的死琐发生 互斥型锁 唯一地限制并发事务访问一个资源 为什么 User2不能执行此事务 (续 ) 意向锁 指示 SQL Server要在层次结构较低的某个资源上获得一个共享或排它锁 模式锁 当任何数据定义（ DDL）操作在表上执行时， SQL Server考察模式修改 (Sch-M) 锁 为什么 User2不能执行此事务 (续 ) 结果： User2不能执行他的事务，因为当它正在被 User1使用时 SQL Server已经锁定了 ExternalCandidate表 死锁 死锁是这样一种情形：两个用户（或事务）在个别的对象的上 锁，并且每个用户正在等待另一个对象的锁 D I S T R I B U T O R P R O D U C T S T R A N S A C T I O N A T R A N S A C T I O N B 死锁 (续 ) 设置死锁优选级 为探测死锁的情况， SQL Server扫描在等待锁请求的会话 SQL Server提供 SET DEADLOCK_PRIORITY命令来定制死锁 语法 SET DEADLOCK_PRIORITY LOW|NORMAL|deadlock_var 控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据，并且直 到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。 LOW 指定当前会话为首选死锁牺牲品。 Microsoft SQL Server 自动回滚死锁牺 牲品的事务，并给客户端应用程序返回 1205 号死锁错误信息。 NORMAL 指定会话返回到默认的死锁处理方法。 定制 LOCK_TIMEOUT SET LOCK_TIMEOUT命令可被用来设置等待被阻塞资源语句的最长时间 游标的定义及其优点 前面介绍的数据检索方法可以得到数据库中有关表的数据，但 这些数据是作为一个结果集得到的，用户可以把这个结果集保存到 一个文件里，或生成一个新表以便于以后使用。这种查询是非常重 要的。但这种查询形式有一个很大的缺点，它不能对结果集中每一 行的数据进行处理。使用游标可以实现对查询结果集中的数据逐行 处理。 游标的概念 游标 (Cursor)是一种处理数据的方法，为了查看或者处理结果集中的数 据，游标提供了在结果集中向前或者向后浏览数据的能力。可以把游标看 成一种指针，它既可以指向当前位置，也可以指向结果集中的任意位置， 它允许用户对指定位置的数据进行处理，可以把结果集中的数据放在数组、 应用程序中或其它地方。 游标 游标 是一个在给定 结果集 中帮助访问和操纵数据 的数据库对象 游标能以下列方式处理结果集中的行： 允许从结果集中检索指定的行 允许结果集中当前行被修改 帮助从结果集中当前行导航到不同的行 允许被其它用户修改的数据在结果集中是可见的 使用游标的步骤 有如下几个步骤： 创建游标。使用 T-SQL语句生成一个结果集，并且定义游标的特征，如游标 中的记录是否可以修改。 打开游标 从游标的结果集中读取数据。从游标中检索一行或多行数据称为取数据。 对游标中的数据逐行操作。 关闭和释放游标。 5.把指定属性作为变量显示 你需要召集所有部门头目开会。为此你需要部门 和相应部门头目的列表如下，其格式如下： Department Name = Production Department Head = Samuel Moore Department Name = Sales Department Head = Donald Fleming . . 创建报告所需的步骤 执行创建报告所需的语句 按所需的结果验证其输出 游标的定义及使用过程 1. 声明游标 声明游标是指用 DECLARE语句声明或创建一个游标 。 声明游标的语法如下： DECLARE cursor_name SCROLL CURSOR FOR select_statement FOR READ ONLY|UPDATEOF column_name_list 其中： cursor_name:是游标的名字，为一个合法的 SQL Server标识符，游标的名字必须遵循 SQL Server命名规范。 SCROLL： 表示取游标时可以使用关键字 NEXT、 PRIOR、 FIRST、 LAST、 ABSOLUTE、 RELATIVE。每 个关键字的含义将在介绍 FETCH子句时讲解。 select_statement： 是定义游标结果集的标准 SELECT语句，它可以是一个完整语法和语义的 Transact-SQL的 SELECT语句。 但是这个 SELECT语句必须有 FROM子句，不允许使用关键字 COMPUTE、 COMPUTE BY、 FOR BROWSE 和 INTO。 FOR READ ONLY：指出该游标结果集只能读，不能修改。 FOR UPDATE：指出该游标结果集可以被修改。 OF column_name_list：列出可以被修改的列的名单。 应该注意： 游标有且只有两种方式： FOR READ ONLY或 FOR UPDATE。 当游标方式指定为 FOR READ ONLY时，游标涉及的表不能被修改。 当游标方式指定为 FOR UPDATE时，可以删除或更新游标涉及的表中的行。 通常， 这也是缺省方式 ，即不指定游标方式时为 FOR UPDATE方式。 声明游标的 DECLARE CURSOR 语句必须是在该游标的任何 OPEN语句之前。 2. 打开游标 打开游标是指打开已被声明但尚未被打开的游标，打开游标使用 OPEN语句。 打开游标的语法如下： OPEN cursor_name 其中： cursor_name是一个已声明的尚未打开的游标名。 注意： 当游标打开成功时，游标位置指向结果集的第一行之前。 只能打开已经声明但尚未打开的游标。 3. 从打开的游标中提取行 游标被打开后，游标位置位于结果集的第一行前，此时可以从结果集中提取（ FETCH）行。 SQL Server将沿着游标结果集一行或多行向下移动游标位置，不断提取结果集中的数据，并修改和保存游 标当前的位置，直到结果集中的行全部被提取。 从打开的游标中提取行的语法如下： FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE FROM cursor_name INTO fetch_target_list 其中： cursor_name：为一已声明并已打开的游标名字。 NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游标移动方向，缺省情况下是 NEXT，即向 下移动。 NEXT：取下一行数据。 PRIOR：取前一行数据。 FIRST：取第一行数据。 LAST：取最后一行数据。 ABSOLUTE：按绝对位置取数据。 RELATIVE：按相对位置取数据。 游标位置确定了结果集中哪一行可以被提取，如果游标方式为 FOR UPDATE的 话，也就确定该位置一行数据可以被更新或删除。 INTO fetch_target_list：指定存放被提取的列数据的目的变量清单。这个清单 中变量的个数、数据类型、顺序必须与定义该游标的 select_statement的 SELECT_list中列出的列清单相匹配。为了更灵活地操纵数据，可以把从已声明并 已打开的游标结果集中提取的列数据，分别存放在目的变量中。 INTO fetch_target_list是 T-SQL对 ANSI-92 SQL标准的扩充。 有两个全局变量提供关于游标活动的信息： FETCH_STATUS 保存着最后 FETCH语句执行后的状态信息，其值和含义如 下： 0 ：表示成功完成 FETCH 语句。 -1：表示 FETCH语句执行有错误，或者当前游标位置已在结果集中的最后一行，结果集中 不再有数据。 -2：提取的行不存在。 rowcount保存着自游标打开后的第一个 FETCH语句，直到最近一次的 FETCH语句为止，已从游标结果集中提取的行数。 也就是说它保存着任何时间点客户 机程序看到的已提取的总行数。一旦结果集中所有行都被提取，那么 rowcount的 值就是该结果集的总行数。每个打开的游标都与一特定的 rowcount有关，关闭游 标时，该 rowcount变量也被删除。在 FETCH语句执行后查看这个变量，可得知从 游标结果集中已提取的行数。 4. 关闭游标 关闭 (Close)游标是停止处理定义游标的那个查询。关闭游标并不改变它的定义，可以 再次用 open语句打开它， SQL Server会用该游标的定义重新创建这个游标的一个结果 集。 关闭游标的语法如下： CLOSE cursor_name 其中： cursor_name： 是已被打开并将要被关闭的游标名字。 在如下情况下， SQL Server会自动地关闭已打开的游标： 当你退出这个 SQL Server会话时 从声明游标的存储过程中返回时 创建报告所需的步骤 (续 ) 5.解除分配游标 你可以抹去由 DECLARE游标语句定义的游标的定义 语法 DEALLOCATE cursor_name 报告所需的步骤 (续 )-7号 你需要用下面的语句来显示报告。 . -Create two variables that would store the -values returned by the fetch statement. DECLARE DepartmentName char(25) DECLARE DepartmentHead char(25) - Defines the cursor that can be used to - access the records of the table,row by row. DECLARE curDepartment cursor for SELECT vDepartmentName,vDepartmentHead FROM Department - Open the cursor OPEN curDepartment - Fetch the rows into variables FETCH curDepartment into DepartmentName, DepartmentHead - Start a loop to display all the rows of - the cursor. While (fetch_status = 0) BEGIN Print Department Name = + DepartmentName Print Department Head = + DepartmentHead - Fetch the next row from the cursor. FETCH curDepartment into DepartmentName, DepartmentHead END - Close the cursor CLOSE curDepartment - Deallocate the cursor. DEALLOCATE curDepartment 定义一个游标，将学生表 student中所有学生的姓名、性别显示出来。 DECLARE student_name VARCHAR(8),student_sex VARCHAR(16) DECLARE student_coursor SCROLL CURSOR FOR SELECT name， sex FROM student FOR READ ONLY OPEN student_coursor FETCH student_coursor INTO student_name,student_sex WHILE FETCH_STATUS=0 BEGIN PRINT 学生姓名 :+student_name+ +性别 : + student_sex FETCH FROM student_coursor INTO student_name , student_sex END CLOSE student_coursor DEALLOCATE student_coursor 用户可以在 UPDATE或 DELETE语句中使用游标来更新、删除表或视图中的行，但不 能用来插入新行。 更新数据 通过在 UPDATE语句中使用游标可以更新表或视图中的行。被更新的行依赖于游标位 置的当前值。 更新数据语法形式如下： UPDATE table_name|view_name SET table_name.|view_name. column_name = new_value .n WHERE CURRENT OF cursor_name 使用游标修改数据 其中： 紧跟 UPDATE之后的 table_name| view_name：要更新的表名或视图名，可 以加或不加限定。但它必须是声明该游标的 SELECT语句中的表名或视图名。 column_name： 是要更新的列的列名，可以加或不加限定。但它们必须是声 明游标的 SELECT语句中 UPDATE OF column_name_list的子集。 new_value：为被更新列的新值，它可以是一个表达式、空值或子查询。 WHERE CURRENT OF：使 SQL Server只更新由指定游标的游标位置当前值 确定的行。 cursor_name： 是已声明为 FOR UPDATE方式并已打开的游标名。 注意： 使用 UPDATE.CURRENT OF语句 一次只能更新当前游标位置确定的那一行 ， OPEN语句将游标位置定位在结果集第一行前，可以使用 FETCH语句把游标位置定位在 要被更新的数据行处。 用 UPDATE.WHERE CURRENT OF语句更新表中的行时， 不会移动游标位置， 被更新的行可以再次被修改 ，直到下一个 FETCH语句的执行。 UPDATE.WHERE CURRENT OF语句可以更新多表视图或被连接的多表，但只 能更新其中 一个表 的行，即所有被更新的列都来自同一个表。 DECLARE student_coursor SCROLL CURSOR FOR SELECT name,sex FROM student FOR UPDATE OF name OPEN student_coursor FETCH ABSOLUTE 3 FROM student_coursor UPDATE student SET name = ABC WHERE CURRENT OF student_coursor CLOSE student_coursor DEALLOCATE student_coursor 删除数据 通过在 DELETE语句中使用游标来删除表或视图中的行。被删除的行依赖于游标 位置的当前值。 删除数据语法形式如下： DELETE FROM database.owner.table_name|view_name WHERE CURRENT OF cursor_name 其中： table_name|view_name：为要从其中删除行的表名或视图名，可以加或不加限 定。但它必须是定义该游标的 SELECT语句中的表名或视图名。 cursor_name：为已声明并已打开的游标名。 WHERE CURRENT OF：它使 SQL Server只删除由指定游标的游标位置当前值 确定的行。 注意： 使用游标的 DELETE语句，一次只能删除当前游标位置确定的那一行。 OPEN语句 将游标位置定位在结果集第一行之前，可以用 FETCH语句把游标位置定位在要被删除 的行处。 在 DELETE语句中使用的游标必须声明为 FOR UPDATE方式 。而且声明游标的 SELECT语句中不能含有连接操作或涉及多表视图，否则即使声明中指明了 FOR UPDATE方式，也不能删除其中的行。 对使用游标删除行的表，要求有一个唯一索引。 使用游标的 DELETE语句，删除一行后将游标位置向前移动一行。