数据库原理与应用：第11章 事务和游标的创建和使用

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

2、cCurrentPosition = 0001WHERE cEmployeeCode= 000002UPDATE PositionSET iCurrentStrength=iCurrentStrength + 1WHERE cPositionCode=0001 系统崩溃是由于两个更新之间导致数据不一系统崩溃是由于两个更新之间导致数据不一致性而引起的。需要防止这种情况，要确保两个致性而引起的。需要防止这种情况，要确保两个更新或者都发生或者都不发生。更新或者都发生或者都不发生。问题：问题： 怎样防止数据的不一致性？ 执行事务 验证数据在两个表中都已更新怎样防止数据的不一致性？事务事务一个事务可以被

3、定义为作为工作的单个的逻辑单元被一起执行的一串的操作。单个的工作单元必须具有称为ACID（原子性，一致性，独立性，和持久性）的四个性质原子性原子性一致性一致性独立性独立性持久性持久性怎样防止数据的不一致性怎样防止数据的不一致性(续续) 为实现ACID性质的需求，SQL Server提供了下面的特性:3事务管理3上锁3日志 事务日志-是SQL Server管理所有它的的事务而维护的日志 显式事务-是事务的开始和结束都被显式地定义的事务。怎样防止数据的不一致性(续)BEGIN TRANSACTION:该该语句标志显示事务的开始语句标志显示事务的开始语法BEGIN TRANSACTION trans

4、action_name | tran_name_variableCOMMIT TRANSACTION或或 COMMIT WORK:语句标志显语句标志显式事务的结束点式事务的结束点语法 COMMIT TRANSACTIONtransaction_name |tran_name_variable怎样防止数据的不一致性(续)Autocommit事务事务autocommit模式是SQL Server的缺省事务管理模式，当使用SET AUTOCOMMIT OFF开启事务支持时，所有insert，delete或update操作列表存储在内存中，因此，当进行ROLLBACK事务时，这些操作能够被撤销。跨越几

5、百个数据改变的事务将会花费很多的内存知道下一个COMMIT或ROLLBACK清除了操作列表。结果：结果：事务的使用可以避免数据的不一致性UPDATE语句可通过使用BEGIN TRANSACTION和COMMIT TRANSACTION语句来维持其原子性执行事务动作：动作：在 Query Analyzer窗口中，键入： BEGIN TRANSACTION trnUpdatePositionUPDATE Employee SET cCurrentPosition = 0001 WHERE cEmployeeCode= 000002 UPDATE Position SET iCurrentStren

6、gth = iCurrentStrength + 1 WHERE cPositionCode = 0001 COMMIT TRANSACTION trnUpdatePosition验证两个表中的数据都已被更新动作：动作：下面的SELECT语句来验证那些行已被更新：SELECT * FROM PositionWHERE cPositionCode = 0001 SELECT * FROM Employee WHERE cEmployeeCode = 000002 识别下面单个工作单元的性质：识别下面单个工作单元的性质：由并发事务引起的任何数据修改必须与其他并发事务所作的修改隔离开来所有的数据修改

7、都被执行或者都没有被执行已完成事务的任何数据改变在系统中永久起作用事务成功地完成后所有的数据必须处于一致的状态2.回复改变职位职位0015已经招募了已经招募了10个候选人。为了反映这一变个候选人。为了反映这一变化，对于化，对于RequisitionCode 0015 ，Requisition表的表的NuOfRequir属性将会减少属性将会减少10。还有，对于。还有，对于cPositionCode 0015，Position表的表的iCurrentStrength属属性要加性要加10，使用下面命令：，使用下面命令：UPDATE Requisitionset NuOfRequir = NuOfRe

8、quir - 10WHERE RequisitionCode=0015 2.回复改变(续)UPDATE Positionset iCurrentStrength=iCurrentStrength + 10WHERE cPositionCode=0015这两个语句都应该是原子的，如果这两个语句都应该是原子的，如果iCurrentStrength属属性变得不只是性变得不只是iCurrentStrength属性，那么，由属性，那么，由UPDATE语句所作的改变必须回复。语句所作的改变必须回复。 识别怎样恢复所作的改变 执行事务 验证事务是否被执行如何回复所作的改变?ROLLBACK TRANSACT

9、ION或或 ROLLBACK WORK:这些这些语句把显式的或隐式的事务回滚到语句把显式的或隐式的事务回滚到事务的开始，或者回滚到事务内的事务的开始，或者回滚到事务内的保存点保存点语法ROLLBACK TRANSACTION transaction_name |tran_name_variable |savepoint_name |savepoint_variable结果结果事务可用ROLLBACK TRANSACTION语句回复BEGIN TRANSACTIONUPDATE RequisitionSET NuOfRequir = NuOfRequir - 10WHERE cRequisiti

10、onCode=0015UPDATE PositionSET iCurrentStrength=iCurrentStrength + 10WHERE cPositionCode=0015IF (SELECT iMaxStrength-iCurrentStrength FROM Position WHERE cPositionCode = 0015) 0 BEGINPRINT Current strength cannot be more than Max strength. Transaction has not been committed. ROLLBACK TRANSACTION ENDE

11、LSE BEGINPRINT The transaction has been committed.COMMIT TRANSACTION END验证事务是否被执行动作：动作：查看结果窗口中所显示的输出。3.回复事务的一部分Employee和和Position表需要用下面的事务来更新：表需要用下面的事务来更新：事务事务1:UPDATE EmployeeSET cCurrentPosition = 0015WHERE cEmployeeCode = 000002UPDATE PositionSET iCurrentStrength = iCurrentStrength + 1WHERE cPosi

12、tionCode = 0015Requisition 和和Position表需要使用下面的事务来更新：表需要使用下面的事务来更新： 事务事务 2: UPDATE Requisition SET NuOfRequir = NuOfRequir - 10 WHERE cRequisitionCode=0015UPDATE Position SET iCurrentStrength=iCurrentStrength + 10 WHERE cPositionCode=00153回复事务的一部分(续)所有更新应一起来做。对于所有更新应一起来做。对于cPositionCode 0015，如果，如果iCur

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

14、供了一种机制，用于回滚部分事务。可以使用保存点提供了一种机制，用于回滚部分事务。可以使用 SAVE TRANSACTION savepoint_name 语句创建一个保存点，然后再执语句创建一个保存点，然后再执行行 ROLLBACK TRANSACTION savepoint_name 语句回滚到该语句回滚到该保存点，从而无须回滚到事务的开始。保存点，从而无须回滚到事务的开始。在不可能发生错误的情况下，保存点很有用。在很少出现错误在不可能发生错误的情况下，保存点很有用。在很少出现错误的情况下使用保存点回滚部分事务，比让每个事务在更新之前测试的情况下使用保存点回滚部分事务，比让每个事务在更新之前

15、测试更新的有效性更为有效。更新和回滚操作代价很大，因此只有在遇更新的有效性更为有效。更新和回滚操作代价很大，因此只有在遇到错误的可能性很小，而且预先检查更新的有效性的代价相对很高到错误的可能性很小，而且预先检查更新的有效性的代价相对很高的情况下，使用保存点才会非常有效。的情况下，使用保存点才会非常有效。BEGIN TRANSACTION UPDATE Employee SET cCurrentPosition = 0015 WHERE cEmployeeCode = 000002UPDATE Position SET iCurrentStrength = iCurrentStrength +

16、1 WHERE cPositionCode = 0015 SAVE TRANSACTION trnTransaction1 UPDATE Requisition SET NuOfRequir=NuOfRequir - 10 WHERE cRequisitionCode=0015 UPDATE Position SET iCurrentStrength=iCurrentStrength+10 WHERE cPositionCode=0015IF (SELECT iMaxStrength-iCurrentStrength FROM Position WHERE cPositionCode = 00

17、15) 80 UPDATE ExternalCandidate SET dInterviewDate = getdate()+ 2 WHERE siTestScore 80 COMMIT TRANSACTIONUser2为什么不能执行此事务?为什么User2不能执行此事务?上锁上锁确保事务的完整性和数据库的一致性是自动实施的不上锁，查看事务处理是不可能的。为什么为什么User2不能执行此事务不能执行此事务(续续)*事务的并发性3 SQL Server提供了乐观的和悲观的并发性控件3 乐观并发性控件建立在多用户间资源冲突大概是不可能的假设的基础上允许事务执行不用锁定任何资源只有在提交事务时才进行

18、资源检查为什么User2不能执行此事务(续)3 悲观并发性悲观并发性控件控件 在事务处理期间的锁定资源并发性问题并发性问题丢失更新 丢失更新问题发生在当两个或多个事务基于原先所选值试图修改同一丢失更新问题发生在当两个或多个事务基于原先所选值试图修改同一行的时候行的时候自由依赖性自由依赖性自由依赖性问题问题（uncommitted dependency ）也称为）也称为无效读入（无效读入（dirty read）问题问题为什么User2不能执行此事务(续)不一致性分析不一致性分析不一致性分析问题又称为问题又称为 不可重复不可重复问题问题幻象读取幻象读取幻象读取又称为作幻象问题又称为作幻象问题为什么

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

20、ser2不能执行他的事务，因为当它正在被User1使用时SQL Server已经锁定了ExternalCandidate表死锁死锁是这样一种情形：两个用户（或事务）在个别的对象的上死锁是这样一种情形：两个用户（或事务）在个别的对象的上锁，并且每个用户正在等待另一个对象的锁锁，并且每个用户正在等待另一个对象的锁DISTRIBUTORPRODUCTSTRANSACTION ATRANSACTION B死锁(续)设置死锁优选级设置死锁优选级为探测死锁的情况，SQL Server扫描在等待锁请求的会话SQL Server提供SET DEADLOCK_PRIORITY命令来定制死锁语法SET DEADL

21、OCK_PRIORITY LOW|NORMAL|deadlock_var控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据，并且直到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。LOW指定当前会话为首选死锁牺牲品。Microsoft SQL Server 自动回滚死锁牺牲品的事务，并给客户端应用程序返回 1205 号死锁错误信息。NORMAL指定会话返回到默认的死锁处理方法。定制定制LOCK_TIMEOUT SET LOCK_TIMEOUT命令可被用来设置等待被阻塞资源语句的最长时间游标的定义及其优点 前面介绍的数据检索方法可以得到数据库中有关表的数据，但这些数据是

22、作为一个结果集得到的，用户可以把这个结果集保存到一个文件里，或生成一个新表以便于以后使用。这种查询是非常重要的。但这种查询形式有一个很大的缺点，它不能对结果集中每一行的数据进行处理。使用游标可以实现对查询结果集中的数据逐行处理。游标的概念 游标(Cursor)是一种处理数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力。可以把游标看成一种指针，它既可以指向当前位置，也可以指向结果集中的任意位置，它允许用户对指定位置的数据进行处理，可以把结果集中的数据放在数组、应用程序中或其它地方。游标游标游标是一个在给定是一个在给定结果集结果集中帮助访问和操纵数据中帮助

23、访问和操纵数据的数据库对象的数据库对象游标能以下列方式处理结果集中的行：游标能以下列方式处理结果集中的行：允许从结果集中检索指定的行允许结果集中当前行被修改帮助从结果集中当前行导航到不同的行允许被其它用户修改的数据在结果集中是可见的使用游标的步骤使用游标的步骤有如下几个步骤：有如下几个步骤： 创建游标。使用创建游标。使用T-SQL语句生成一个结果集，并且定义游标的特征，如游标语句生成一个结果集，并且定义游标的特征，如游标中的记录是否可以修改。中的记录是否可以修改。 打开游标打开游标 从游标的结果集中读取数据。从游标中检索一行或多行数据称为取数据。从游标的结果集中读取数据。从游标中检索一行或多行

24、数据称为取数据。 对游标中的数据逐行操作。对游标中的数据逐行操作。 关闭和释放游标。关闭和释放游标。5.把指定属性作为变量显示你需要召集所有部门头目开会。为此你需要部门你需要召集所有部门头目开会。为此你需要部门和相应部门头目的列表如下，其格式如下：和相应部门头目的列表如下，其格式如下： Department Name = Production Department Head = Samuel Moore Department Name = Sales Department Head = Donald Fleming . . 创建报告所需的步骤创建报告所需的步骤执行创建报告所需的语句执行创建报告

25、所需的语句按所需的结果验证其输出按所需的结果验证其输出游标的定义及使用过程游标的定义及使用过程1.声明游标声明游标声明游标是指用声明游标是指用DECLARE语句声明或创建一个游标语句声明或创建一个游标。声明游标的语法如下：声明游标的语法如下：DECLARE cursor_name SCROLL CURSORFOR select_statementFOR READ ONLY|UPDATEOF column_name_list其中：其中： cursor_name:cursor_name:是游标的名字，为一个合法的是游标的名字，为一个合法的SQL ServerSQL Server标识符，游标的名字必

26、须遵循标识符，游标的名字必须遵循SQL SQL ServerServer命名规范。命名规范。SCROLLSCROLL：表示取游标时可以使用关键字表示取游标时可以使用关键字NEXTNEXT、PRIORPRIOR、FIRSTFIRST、LASTLAST、ABSOLUTEABSOLUTE、RELATIVERELATIVE。每个关键字的含义将在介绍每个关键字的含义将在介绍FETCHFETCH子句时讲解。子句时讲解。select_statementselect_statement：是定义游标结果集的标准是定义游标结果集的标准 SELECTSELECT语句，它可以是一个完整语法和语义的语句，它可以是一个完

27、整语法和语义的Transact-SQLTransact-SQL的的SELECTSELECT语句。语句。 但是这个但是这个SELECT语句必须有语句必须有FROM子句，不允许使用关键字子句，不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和和 INTO。FOR READ ONLY：指出该游标结果集只能读，不能修改。：指出该游标结果集只能读，不能修改。FOR UPDATE：指出该游标结果集可以被修改。：指出该游标结果集可以被修改。OF column_name_list：列出可以被修改的列的名单。：列出可以被修改的列的名单。应该注意：应该注意： 游标有且只有两种方式：游

28、标有且只有两种方式：FOR READ ONLY或或FOR UPDATE。 当游标方式指定为当游标方式指定为FOR READ ONLY时，游标涉及的表不能被修改。时，游标涉及的表不能被修改。 当游标方式指定为当游标方式指定为FOR UPDATE时，可以删除或更新游标涉及的表中的行。时，可以删除或更新游标涉及的表中的行。通常，通常，这也是缺省方式这也是缺省方式，即不指定游标方式时为，即不指定游标方式时为FOR UPDATE方式。方式。 声明游标的声明游标的DECLARE CURSOR 语句必须是在该游标的任何语句必须是在该游标的任何OPEN语句之前。语句之前。2. 打开游标打开游标打开游标是指打开

29、已被声明但尚未被打开的游标，打开游标使用打开游标是指打开已被声明但尚未被打开的游标，打开游标使用OPEN语句。语句。打开游标的语法如下：打开游标的语法如下： OPEN cursor_name其中：其中：cursor_name是一个已声明的尚未打开的游标名。是一个已声明的尚未打开的游标名。注意：注意： 当游标打开成功时，游标位置指向结果集的第一行之前。当游标打开成功时，游标位置指向结果集的第一行之前。 只能打开已经声明但尚未打开的游标。只能打开已经声明但尚未打开的游标。3. 从打开的游标中提取行从打开的游标中提取行游标被打开后，游标位置位于结果集的第一行前，此时可以从结果集中提取（游标被打开后，

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

31、or_name：为一已声明并已打开的游标名字。：为一已声明并已打开的游标名字。NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE：游标移动方向，缺省情况下是：游标移动方向，缺省情况下是NEXT，即向，即向下移动。下移动。NEXT：取下一行数据。：取下一行数据。PRIOR：取前一行数据。：取前一行数据。FIRST：取第一行数据。：取第一行数据。 LAST：取最后一行数据。ABSOLUTE：按绝对位置取数据。RELATIVE：按相对位置取数据。游标位置确定了结果集中哪一行可以被提取，如果游标方式为FOR UPDATE的话，也就确定该位置一行数据可以被更新或删除。 INT

32、O fetch_target_list：指定存放被提取的列数据的目的变量清单。这个清单中变量的个数、数据类型、顺序必须与定义该游标的select_statement的SELECT_list中列出的列清单相匹配。为了更灵活地操纵数据，可以把从已声明并已打开的游标结果集中提取的列数据，分别存放在目的变量中。INTO fetch_target_list是T-SQL对ANSI-92 SQL标准的扩充。 有两个全局变量提供关于游标活动的信息： FETCH_STATUS 保存着最后FETCH语句执行后的状态信息，其值和含义如下：0 ：表示成功完成FETCH 语句。-1：表示FETCH语句执行有错误，或者当

33、前游标位置已在结果集中的最后一行，结果集中不再有数据。-2：提取的行不存在。 rowcount保存着自游标打开后的第一个保存着自游标打开后的第一个FETCH语句，直到最近一次的语句，直到最近一次的FETCH语句为止，已从游标结果集中提取的行数。语句为止，已从游标结果集中提取的行数。也就是说它保存着任何时间点客户机程序看到的已提取的总行数。一旦结果集中所有行都被提取，那么rowcount的值就是该结果集的总行数。每个打开的游标都与一特定的rowcount有关，关闭游标时，该rowcount变量也被删除。在FETCH语句执行后查看这个变量，可得知从游标结果集中已提取的行数。 4. 关闭游标关闭游标

34、关闭(Close)游标是停止处理定义游标的那个查询。关闭游标并不改变它的定义，可以再次用open语句打开它，SQL Server会用该游标的定义重新创建这个游标的一个结果集。关闭游标的语法如下：CLOSE cursor_name其中：cursor_name：是已被打开并将要被关闭的游标名字。在如下情况下，SQL Server会自动地关闭已打开的游标：当你退出这个SQL Server会话时从声明游标的存储过程中返回时创建报告所需的步骤(续)5.解除分配游标解除分配游标你可以抹去由DECLARE游标语句定义的游标的定义语法 DEALLOCATE cursor_name报告所需的步骤(续)-7号你需

35、要用下面的语句来显示报告。.-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

36、,vDepartmentHead FROM Department- Open the cursorOPEN curDepartment- Fetch the rows into variablesFETCH curDepartment into DepartmentName, DepartmentHead- Start a loop to display all the rows of - the cursor.While (fetch_status = 0)BEGINPrint Department Name = + DepartmentNamePrint Department Head =

37、 + DepartmentHead- Fetch the next row from the cursor. FETCH curDepartment into DepartmentName, DepartmentHeadEND- Close the cursorCLOSE curDepartment- Deallocate the cursor.DEALLOCATE curDepartment定义一个游标，将学生表student中所有学生的姓名、性别显示出来。DECLARE student_name VARCHAR(8),student_sex VARCHAR(16)DECLARE stude

38、nt_coursor SCROLL CURSOR FOR SELECT name，sex FROM student FOR READ ONLYOPEN student_coursorFETCH student_coursor INTO student_name,student_sexWHILE FETCH_STATUS=0 BEGIN PRINT 学生姓名:+student_name+ +性别: + student_sex FETCH FROM student_coursor INTO student_name , student_sex ENDCLOSE student_coursorDEALLOCATE 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_nam