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

1、孙 发 勤 扬州大学新闻与传媒学院 YZU.SUN163.COM,数据库原理与应用第十一讲 事务和游标的创建和使用,创建事务 提交事务 回滚事务 回滚事务的一部分 用锁 理解死锁 声明游标 打开游标 从游标取数据 关闭游标,本讲主要内容,例： 一个员工代码为000002的内部候选人已经被选定为Sales Manager（ 岗位代码为0001）岗位。这必须在Employee表中被更新，当前已在该岗位的人数也需要在Position表中更新。 上面要做的UPDATE语句如下： UPDATE Employee SET cCurrentPosition = 0001 WHERE cEmployeeCod

2、e= 000002 UPDATE Position SET iCurrentStrength=iCurrentStrength + 1 WHERE cPositionCode=0001,系统崩溃是由于两个更新之间导致数据不一致性而引起的。需要防止这种情况，要确保两个更新或者都发生或者都不发生。,问题： 怎样防止数据的不一致性？ 执行事务 验证数据在两个表中都已更新,怎样防止数据的不一致性？ 事务 一个事务可以被定义为作为工作的单个的逻辑单元被一起执行的一串的操作。 单个的工作单元必须具有称为ACID（原子性，一致性，独立性，和持久性）的四个性质 原子性 一致性 独立性 持久性,怎样防止数据的不

3、一致性(续) 为实现ACID性质的需求，SQL Server提供了下面的特性: 事务管理 上锁 日志 事务日志-是SQL Server管理所有它的的事务而维护的日志 显式事务-是事务的开始和结束都被显式地定义的事务。,怎样防止数据的不一致性(续) BEGIN TRANSACTION:该语句标志显示事务的开始 语法 BEGIN TRANSACTION transaction_name | tran_name_variable COMMIT TRANSACTION或 COMMIT WORK:语句标志显式事务的结束点 语法 COMMIT TRANSACTIONtransaction_name |tr

4、an_name_variable,怎样防止数据的不一致性(续) Autocommit事务 autocommit模式是SQL Server的缺省事务管理模式，当使用SET AUTOCOMMIT OFF开启事务支持时，所有insert，delete或update操作列表存储在内存中，因此，当进行ROLLBACK事务时，这些操作能够被撤销。跨越几百个数据改变的事务将会花费很多的内存知道下一个COMMIT或ROLLBACK清除了操作列表。 结果： 事务的使用可以避免数据的不一致性 UPDATE语句可通过使用BEGIN TRANSACTION和COMMIT TRANSACTION语句来维持其原子性,执行

5、事务 动作： 在 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语句来验证那些行已

6、被更新： SELECT * FROM Position WHERE cPositionCode = 0001 SELECT * FROM Employee WHERE cEmployeeCode = 000002,识别下面单个工作单元的性质： 由并发事务引起的任何数据修改必须与其他并发事务所作的修改隔离开来 所有的数据修改都被执行或者都没有被执行 已完成事务的任何数据改变在系统中永久起作用 事务成功地完成后所有的数据必须处于一致的状态,2.回复改变 职位0015已经招募了10个候选人。为了反映这一变化，对于RequisitionCode 0015 ，Requisition表的NuOfRequi

7、r属性将会减少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属性变得不只是iCurrentStren

8、gth属性，那么，由UPDATE语句所作的改变必须回复。,识别怎样恢复所作的改变 执行事务 验证事务是否被执行,如何回复所作的改变? ROLLBACK TRANSACTION或 ROLLBACK WORK:这些语句把显式的或隐式的事务回滚到事务的开始，或者回滚到事务内的保存点 语法 ROLLBACK TRANSACTION transaction_name |tran_name_variable |savepoint_name |savepoint_variable 结果 事务可用ROLLBACK TRANSACTION语句回复,BEGIN TRANSACTION UPDATE Requisi

9、tion 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. Tr

10、ansaction 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

11、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回复事务的一部分(续) 所有更新应一起来做。

12、对于cPositionCode 0015，如果iCurrentStrength值大于iMaxStrength值，由第二个事务所产生的改变必须被回复，而由第一个事务产生的改变是允许的。,识别怎样把事务分解成部分 执行事务 验证事务的执行,怎样把事务分解成部分? 保存事务 它在事务内设置保存点 。保存点把事务分成几个逻辑单元，这样事务可以返回到保存点，如果事务的一部分是有条件地被取消。 语法 SAVE TRANSACTION savepoint_name | savepoint_variable 结果 事务可以用SAVE TRANSACTION语句分解成几个逻辑单元,保存点提供了一种机制，用于回滚

13、部分事务。可以使用 SAVE TRANSACTION savepoint_name 语句创建一个保存点，然后再执行 ROLLBACK TRANSACTION savepoint_name 语句回滚到该保存点，从而无须回滚到事务的开始。 在不可能发生错误的情况下，保存点很有用。在很少出现错误的情况下使用保存点回滚部分事务，比让每个事务在更新之前测试更新的有效性更为有效。更新和回滚操作代价很大，因此只有在遇到错误的可能性很小，而且预先检查更新的有效性的代价相对很高的情况下，使用保存点才会非常有效。,BEGIN TRANSACTION UPDATE Employee SET cCurrentPosi

14、tion = 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 WHER

15、E cPositionCode=0015,IF (SELECT iMaxStrength-iCurrentStrength FROM Position WHERE cPositionCode = 0015) 0 BEGIN PRINT Transaction 1 has been committed but transaction 2 has not been ommitted. ROLLBACK TRANSACTION trnTransaction1 END ELSE BEGIN RINT Both the transactions have been committed. COMMIT T

16、RANSACTION END,验证事务的执行 动作: 查看在结果窗口中显示的输出。,4.体验上琐的用法 User1给出下面的语句来更新ExternalCandidate表中的材料，表中有cCandidateCode000002的候选人参加测试后的测试分数和测试日期。 BEGIN TRANSACTION UPDATE ExternalCandidate SET siTestScore = 90 WHERE cCandidateCode=000002 UPDATE ExternalCandidate SET dTestDate = getdate() WHERE cCandidateCode =

17、000002 COMMIT TRANSACTION,4体验上琐的用法(续) 当上面的事务被执行时，User2要安排一个候选人来面试，但是不能查看高于80分候选人的材料。他/她用下面的语句来查看材料和安排面试： BEGIN TRANSACTION SELECT * from ExternalCandidate WHERE siTestScore 80 UPDATE ExternalCandidate SET dInterviewDate = getdate()+ 2 WHERE siTestScore 80 COMMIT TRANSACTION,User2为什么不能执行此事务?,为什么User2

18、不能执行此事务? 上锁 确保事务的完整性和数据库的一致性 是自动实施的 不上锁，查看事务处理是不可能的。,为什么User2不能执行此事务(续) 事务的并发性 SQL Server提供了乐观的和悲观的并发性控件 乐观并发性控件 建立在多用户间资源冲突大概是不可能的假设的基础上 允许事务执行不用锁定任何资源 只有在提交事务时才进行资源检查,为什么User2不能执行此事务(续) 悲观并发性控件 在事务处理期间的锁定资源 并发性问题 丢失更新 丢失更新问题发生在当两个或多个事务基于原先所选值试图修改同一行的时候 自由依赖性 自由依赖性问题（uncommitted dependency ）也称为无效读入

19、（dirty read）问题,为什么User2不能执行此事务(续) 不一致性分析 不一致性分析问题又称为 不可重复问题 幻象读取 幻象读取又称为作幻象问题,为什么User2不能执行此事务(续) SQL Server锁模型 共享锁 允许并发事务来读取资源 更新锁 避免了常见形式的死琐发生 互斥型锁 唯一地限制并发事务访问一个资源,为什么User2不能执行此事务(续) 意向锁 指示SQL Server要在层次结构较低的某个资源上获得一个共享或排它锁 模式锁 当任何数据定义（DDL）操作在表上执行时，SQL Server考察模式修改 (Sch-M) 锁,为什么User2不能执行此事务(续) 结果：

20、User2不能执行他的事务，因为当它正在被User1使用时SQL Server已经锁定了ExternalCandidate表,死锁 死锁是这样一种情形：两个用户（或事务）在个别的对象的上锁，并且每个用户正在等待另一个对象的锁,死锁(续) 设置死锁优选级 为探测死锁的情况，SQL Server扫描在等待锁请求的会话 SQL Server提供SET DEADLOCK_PRIORITY命令来定制死锁 语法 SET DEADLOCK_PRIORITY LOW|NORMAL|deadlock_var 控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据，并且直到其它进程释放自己的锁时，每个进程才能

21、释放自己的锁，即发生死锁情况。 LOW 指定当前会话为首选死锁牺牲品。Microsoft SQL Server 自动回滚死锁牺牲品的事务，并给客户端应用程序返回 1205 号死锁错误信息。 NORMAL 指定会话返回到默认的死锁处理方法。 定制LOCK_TIMEOUT SET LOCK_TIMEOUT命令可被用来设置等待被阻塞资源语句的最长时间,游标的定义及其优点,前面介绍的数据检索方法可以得到数据库中有关表的数据，但这些数据是作为一个结果集得到的，用户可以把这个结果集保存到一个文件里，或生成一个新表以便于以后使用。这种查询是非常重要的。但这种查询形式有一个很大的缺点，它不能对结果集中每一行的

22、数据进行处理。使用游标可以实现对查询结果集中的数据逐行处理。 游标的概念 游标(Cursor)是一种处理数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力。可以把游标看成一种指针，它既可以指向当前位置，也可以指向结果集中的任意位置，它允许用户对指定位置的数据进行处理，可以把结果集中的数据放在数组、应用程序中或其它地方。,游标 游标是一个在给定结果集中帮助访问和操纵数据的数据库对象 游标能以下列方式处理结果集中的行： 允许从结果集中检索指定的行 允许结果集中当前行被修改 帮助从结果集中当前行导航到不同的行 允许被其它用户修改的数据在结果集中是可见的,使用游

23、标的步骤,有如下几个步骤： 创建游标。使用T-SQL语句生成一个结果集，并且定义游标的特征，如游标中的记录是否可以修改。 打开游标 从游标的结果集中读取数据。从游标中检索一行或多行数据称为取数据。 对游标中的数据逐行操作。 关闭和释放游标。,5.把指定属性作为变量显示 你需要召集所有部门头目开会。为此你需要部门和相应部门头目的列表如下，其格式如下： Department Name = Production Department Head = Samuel Moore Department Name = Sales Department Head = Donald Fleming . .,创建报

24、告所需的步骤 执行创建报告所需的语句 按所需的结果验证其输出,游标的定义及使用过程,声明游标 声明游标是指用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、R

25、ELATIVE。每个关键字的含义将在介绍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或F

26、OR UPDATE。 当游标方式指定为FOR READ ONLY时，游标涉及的表不能被修改。 当游标方式指定为FOR UPDATE时，可以删除或更新游标涉及的表中的行。通常，这也是缺省方式，即不指定游标方式时为FOR UPDATE方式。 声明游标的DECLARE CURSOR 语句必须是在该游标的任何OPEN语句之前。,2. 打开游标 打开游标是指打开已被声明但尚未被打开的游标，打开游标使用OPEN语句。 打开游标的语法如下： OPEN cursor_name 其中： cursor_name是一个已声明的尚未打开的游标名。 注意： 当游标打开成功时，游标位置指向结果集的第一行之前。 只能打开已

27、经声明但尚未打开的游标。,3. 从打开的游标中提取行 游标被打开后，游标位置位于结果集的第一行前，此时可以从结果集中提取（FETCH）行。SQL Server将沿着游标结果集一行或多行向下移动游标位置，不断提取结果集中的数据，并修改和保存游标当前的位置，直到结果集中的行全部被提取。 从打开的游标中提取行的语法如下： FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE FROM cursor_name INTO fetch_target_list 其中： cursor_name：为一已声明并已打开的游标名字。 NEXT|PRIOR|FIRST|LAST|A

28、BSOLUTE|RELATIVE：游标移动方向，缺省情况下是NEXT，即向下移动。 NEXT：取下一行数据。 PRIOR：取前一行数据。 FIRST：取第一行数据。,LAST：取最后一行数据。 ABSOLUTE：按绝对位置取数据。 RELATIVE：按相对位置取数据。 游标位置确定了结果集中哪一行可以被提取，如果游标方式为FOR UPDATE的话，也就确定该位置一行数据可以被更新或删除。 INTO fetch_target_list：指定存放被提取的列数据的目的变量清单。这个清单中变量的个数、数据类型、顺序必须与定义该游标的select_statement的SELECT_list中列出的列清单

29、相匹配。为了更灵活地操纵数据，可以把从已声明并已打开的游标结果集中提取的列数据，分别存放在目的变量中。INTO fetch_target_list是T-SQL对ANSI-92 SQL标准的扩充。,有两个全局变量提供关于游标活动的信息： FETCH_STATUS 保存着最后FETCH语句执行后的状态信息，其值和含义如下： 0 ：表示成功完成FETCH 语句。 -1：表示FETCH语句执行有错误，或者当前游标位置已在结果集中的最后一行，结果集中不再有数据。 -2：提取的行不存在。 rowcount保存着自游标打开后的第一个FETCH语句，直到最近一次的FETCH语句为止，已从游标结果集中提取的行数

30、。也就是说它保存着任何时间点客户机程序看到的已提取的总行数。一旦结果集中所有行都被提取，那么rowcount的值就是该结果集的总行数。每个打开的游标都与一特定的rowcount有关，关闭游标时，该rowcount变量也被删除。在FETCH语句执行后查看这个变量，可得知从游标结果集中已提取的行数。,4. 关闭游标 关闭(Close)游标是停止处理定义游标的那个查询。关闭游标并不改变它的定义，可以再次用open语句打开它，SQL Server会用该游标的定义重新创建这个游标的一个结果集。 关闭游标的语法如下： CLOSE cursor_name 其中： cursor_name：是已被打开并将要被关

31、闭的游标名字。 在如下情况下，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) DECLA

32、RE 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 Depa

33、rtmentName, 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 - Clos

34、e 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_courso

35、r 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：是要更新的列的列名，可以加或不加限定。但它们必须是声明游标的SELEC