实现处理事务和游标

1、NIITSQL/Lesson 11/Slide 1 of 72实现处理事务和游标目标目标*在这课里，你将学习：3创建事务3提交事务3回滚事务3回滚事务的一部分3用锁3理解死锁3声明游标NIITSQL/Lesson 11/Slide 2 of 72实现处理事务和游标目标目标(续续)3打开游标3从游标取数据3关闭游标NIITSQL/Lesson 11/Slide 3 of 72实现处理事务和游标11.D.1确保数据的一致性确保数据的一致性*一个员工代码为000002的内部候选人已经被选定为Sales Manager（ 岗位代码为0001）岗位。这必须在Employee表中被更新，当前已在的岗位的人

2、数也需要在Position表中更新。*上面要做的UPDATE语句如下：UPDATE EmployeeSET cCurrentPosition = 0001WHERE cEmployeeCode= 000002UPDATE PositionSET iCurrentStrength=iCurrentStrength + 1WHERE cPositionCode=0001NIITSQL/Lesson 11/Slide 4 of 72实现处理事务和游标11.D.1确保数据的一致性确保数据的一致性(续续) *系统崩溃是由于两个更新之间导致数据不一致性而引起的。需要防止这种情况，要确保两个更新或者都发生或

3、者都不发生。NIITSQL/Lesson 11/Slide 5 of 72实现处理事务和游标任务单任务单*识别怎样防止数据的不一致性*执行事务*验证数据在两个表中都已更新NIITSQL/Lesson 11/Slide 6 of 72实现处理事务和游标识别怎样防止数据的不一致性识别怎样防止数据的不一致性*事务3 一个事务可以被定义为作为工作的单个的逻辑单元被一起执行的一串的操作。3 单个的工作单元必须具有称为ACID（原子性，一致性，独立性，和持久性）的四个性质原子性一致性独立性持久性NIITSQL/Lesson 11/Slide 7 of 72实现处理事务和游标识别怎样防止数据的不一致性识别怎

4、样防止数据的不一致性(续续)*为实现ACID性质的需求，SQL Servr提供了下面的特性:3事务管理3上锁3日志*事务日志-是SQL Server管理所有它的的事务而维护的日志*显式事务-是事务的开始和结束都被显式地定义的事务。NIITSQL/Lesson 11/Slide 8 of 72实现处理事务和游标识别怎样防止数据的不一致性识别怎样防止数据的不一致性(续续)*BEGIN TRANSACTION:该语句标志显示事务的开始3 语法 BEGIN TRANSACTION transaction_name | tran_name_variable*COMMIT TRANSACTION或 COM

5、MIT WORK:语句标志显式事务的结束点3 语法 COMMIT TRANSACTIONtransaction_name |tran_name_variableNIITSQL/Lesson 11/Slide 9 of 72实现处理事务和游标识别怎样防止数据的不一致性识别怎样防止数据的不一致性(续续)*Autocommit事务3 autocommit模式是SQL Server的缺省事务管理模式*结果：3 事务的使用可以避免数据的不一致性3 UPDATE语句可通过使用BEGIN TRANSACTION和COMMIT TRANSACTION语句来维持其原子性NIITSQL/Lesson 11/Sli

6、de 10 of 72实现处理事务和游标执行事务执行事务*动作：3 在 Query Analyzer窗口中，键入： BEGIN TRANSACTION trnUpdatePositionUPDATE Employee SET cCurrentPosition = 0001 WHERE cEmployeeCode= 000002 UPDATE Position SET iCurrentStrength = iCurrentStrength + 1 WHERE cPositionCode = 0001 COMMIT TRANSACTION trnUpdatePosition3 按F5来执行此语句N

7、IITSQL/Lesson 11/Slide 11 of 72实现处理事务和游标验证两个表中的数据都已被更新验证两个表中的数据都已被更新*动作：3 执行下面的SELECT语句来验证那些行已被更新：SELECT * FROM PositionWHERE cPositionCode = 0001 SELECT * FROM Employee WHERE cEmployeeCode = 000002 NIITSQL/Lesson 11/Slide 12 of 72实现处理事务和游标稍等一下稍等一下*识别下面单个工作单元的性质：3 由并发事务引起的任何数据修改必须与其他并发事务所作的修改隔离开来3 所

8、有的数据修改都被执行或者都没有被执行3 已完成事务的任何数据改变在系统中永久起作用3 事务成功地完成后所有的数据必须处于一致的状态NIITSQL/Lesson 11/Slide 13 of 72实现处理事务和游标11.D.2回复改变回复改变*职位0015已经招募了10个候选人。为了反映这一变化，对于cRequisitionCode 000004，Requisition表的siNoOfVacancy属性将会减少10。还有，对于cPositionCode 0015，Position表的iCurrentStrength属性也要减少10，使用下面命令：UPDATE Requisitionset siN

9、oOfVacancy=siNoOfVacancy - 10WHERE cRequisitionCode=000004 NIITSQL/Lesson 11/Slide 14 of 72实现处理事务和游标11.D.2回复改变回复改变(续续)UPDATE Positionset iCurrentStrength=iCurrentStrength + 10WHERE cPositionCode=0015*这两个语句都应该是原子的，如果iCurrentStrength属性变得不只是iCurrentStrength属性，那么，由UPDATE语句所作的改变必须回复。NIITSQL/Lesson 11/Sli

10、de 15 of 72实现处理事务和游标任务单任务单*识别怎样恢复所作的改变*执行事务*验证事务是否被执行NIITSQL/Lesson 11/Slide 16 of 72实现处理事务和游标识别如何回复所作的改变识别如何回复所作的改变*ROLLBACK TRANSACTION或 ROLLBACK WORK:这些语句把显式的或隐式的事务回滚到事务的开始，或者回滚到事务内的保存点3 语法ROLLBACK TRANSACTION transaction_name |tran_name_variable |savepoint_name |savepoint_variable*结果3 事务可用ROLLBA

11、CK TRANSACTION语句回复NIITSQL/Lesson 11/Slide 17 of 72实现处理事务和游标执行事务执行事务*动作：3 在Query Analyzer 窗口中，键入：BEGIN TRANSACTIONUPDATE RequisitionSET siNoOfVacancy=siNoOfVacancy - 10WHERE cRequisitionCode=000004UPDATE PositionSET iCurrentStrength=iCurrentStrength + 10WHERE cPositionCode=0015NIITSQL/Lesson 11/Slide

12、 18 of 72实现处理事务和游标执行事务执行事务(续续)IF (SELECT iBudgetedStrength-iCurrentStrength FROM Position WHERE cPositionCode = 0015) 0BEGINPRINT Current strength cannot be more than budgeted strength. Transaction has not been committed.ROLLBACK TRANSACTIONENDELSENIITSQL/Lesson 11/Slide 19 of 72实现处理事务和游标执行事务执行事务(续续

13、)BEGINPRINT The transaction has been committed.COMMIT TRANSACTIONEND3按下F5执行事务NIITSQL/Lesson 11/Slide 20 of 72实现处理事务和游标验证事务是否被执行验证事务是否被执行*动作：3 查看结果窗口中所显示的输出。对于PositionCode0015，如果iBudgetedStrength和iCurrentStrength的差小于零，事务就被回滚且显示Current strength cannot be more than budgeted strength. Transaction has no

14、t been committed.。否则，事务被提交且显示相应的消息。NIITSQL/Lesson 11/Slide 21 of 72实现处理事务和游标11.D.3回复事务的一部分回复事务的一部分*Employee和Position表需要用下面的事务来更新：事务1:UPDATE EmployeeSET cCurrentPosition = 0015WHERE cEmployeeCode = 000002UPDATE PositionSET iCurrentStrength = iCurrentStrength + 1WHERE cPositionCode = 0015NIITSQL/Lesso

15、n 11/Slide 22 of 72实现处理事务和游标11.D.3回复事务的一部分回复事务的一部分(续续)*Requisition 和Position表需要使用下面的事务来更新：事务 2: UPDATE RequisitionSET siNoOfVacancy=siNoOfVacancy - 10WHERE cRequisitionCode=000004UPDATE PositionSET iCurrentStrength=iCurrentStrength + 10WHERE cPositionCode=0015NIITSQL/Lesson 11/Slide 23 of 72实现处理事务和游

16、标11.D.3回复事务的一部分回复事务的一部分(续续)*所有更新应一起来做。对于cPositionCode 0015，如果iCurrentStrength值大于iBudgetedStrength值，由第二个事务所产生的改变必须被回复，而由第一个事务产生的改变是允许的。NIITSQL/Lesson 11/Slide 24 of 72实现处理事务和游标任务单任务单*识别怎样把事务分解成部分*执行事务*验证事务的执行NIITSQL/Lesson 11/Slide 25 of 72实现处理事务和游标识别怎样把事务分解成部分识别怎样把事务分解成部分*保存事务3 它在事务内设置保存点 。保存点把事务分成几

17、个逻辑单元，这样事务可以返回到保存点，如果事务的一部分是有条件地被取消。3 语法SAVE TRANSACTION savepoint_name | savepoint_variable*结果3 事务可以用SAVE TRANSACTION语句分解成几个逻辑单元NIITSQL/Lesson 11/Slide 26 of 72实现处理事务和游标执行事务执行事务*动作：3 在Query Analyzer 窗口中，键入：BEGIN TRANSACTION UPDATE EmployeeSET cCurrentPosition = 0015WHERE cEmployeeCode = 000002UPDAT

18、E PositionSET iCurrentStrength = iCurrentStrength + 1WHERE cPositionCode = 0015NIITSQL/Lesson 11/Slide 27 of 72实现处理事务和游标执行事务执行事务(续续)SAVE TRANSACTION trnTransaction1UPDATE RequisitionSET siNoOfVacancy=siNoOfVacancy - 10WHERE cRequisitionCode=000004UPDATE PositionSET iCurrentStrength=iCurrentStrength+

19、10WHERE cPositionCode=0015NIITSQL/Lesson 11/Slide 28 of 72实现处理事务和游标执行事务执行事务(续续)IF (SELECT iBudgetedStrength-iCurrentStrength FROM Position WHERE cPositionCode = 0015) 80UPDATE ExternalCandidateSET dInterviewDate = getdate()+ 2WHERE siTestScore 80*识别为什么User2不能执行此事务。NIITSQL/Lesson 11/Slide 33 of 72实现处

20、理事务和游标任务单任务单*识别User2为什么不能执行此事务*模拟两个事务来理解上锁*在终端1上执行事务*在终端2上执行事务*在终端1上执行命令*验证事务进行中表被上锁*在终端2上执行COMMIT TRANSACTION语句NIITSQL/Lesson 11/Slide 34 of 72实现处理事务和游标识别为什么识别为什么User2不能执行此事务不能执行此事务*上锁3 确保事务的完整性和数据库的一致性3 是自动实施的3 不上锁，查看事务处理是不可能的。NIITSQL/Lesson 11/Slide 35 of 72实现处理事务和游标识别为什么识别为什么User2不能执行此事务不能执行此事务(

21、续续)*事务的并发性3 SQL Server提供了乐观的和悲观的并发性控件3 乐观并发性控件建立在多用户间资源冲突大概是不可能的假设的基础上允许事务执行不用锁定任何资源只有在提交事务时才进行资源检查NIITSQL/Lesson 11/Slide 36 of 72实现处理事务和游标识别为什么识别为什么User2不能执行此事务不能执行此事务(续续)3悲观并发性控件在事务处理期间的锁定资源*并发性问题3 丢失更新丢失更新问题发生在当两个或多个事务基于原先所选值试图修改同一行的时候3 自由依赖性自由依赖性问题（uncommitted dependency ）也称为无效读入（dirty read）问题N

22、IITSQL/Lesson 11/Slide 37 of 72实现处理事务和游标*识别为什么识别为什么User2不能执行此事务不能执行此事务(续续)3 不一致性分析不一致性分析问题又称为 不可重复问题3 幻象读取幻象读取又称为作幻象问题NIITSQL/Lesson 11/Slide 38 of 72实现处理事务和游标*识别为什么识别为什么User2不能执行此事务不能执行此事务(续续)*锁项- SQL Server 可对以下资源上锁：3 RID3 Key3 Page3 Extent3 Table3 DatabaseNIITSQL/Lesson 11/Slide 39 of 72实现处理事务和游标

23、*识别为什么识别为什么User2不能执行此事务不能执行此事务(续续)*SQL Server锁模型3 共享锁允许并发事务来读取资源3 更新锁避免了常见形式的死琐发生3 互斥型锁唯一地限制并发事务访问一个资源NIITSQL/Lesson 11/Slide 40 of 72实现处理事务和游标*识别为什么识别为什么User2不能执行此事务不能执行此事务(续续)3 意向锁指示SQL Server要在层次结构较低的某个资源上获得一个共享或排它锁3 模式锁当任何数据定义（DDL）操作在表上执行时，SQL Server考察模式修改 (Sch-M) 锁NIITSQL/Lesson 11/Slide 41 of

24、72实现处理事务和游标*识别为什么识别为什么User2不能执行此事务不能执行此事务(续续)*结果：3 User2不能执行他的事务，因为当它正在被User1使用时SQL Server已经锁定了ExternalCandidate表NIITSQL/Lesson 11/Slide 42 of 72实现处理事务和游标模拟两个事务来理解上锁模拟两个事务来理解上锁*动作：3 为模拟两个事务，从名为Terminal1 和 Terminal2的二个终端连接到SQL Server。NIITSQL/Lesson 11/Slide 43 of 72实现处理事务和游标在在Terminal1上执行事务上执行事务*动作：3

25、 在Query Analyzer 窗口中，键入：BEGIN TRANSACTIONUPDATE ExternalCandidateSET siTestScore = 90WHERE cCandidateCode = 000002UPDATE ExternalCandidateSET dTestDate = getdate()WHERE cCandidateCode = 000002 3 按F5执行事务NIITSQL/Lesson 11/Slide 44 of 72实现处理事务和游标在在Terminal2上执行事务上执行事务*动作：3 在Query Analyzer 窗口中，键入：BEGIN T

26、RANSACTION SELECT * FROM ExternalCandidateWHERE siTestScore 80 UPDATE ExternalCandidateSET dInterviewDate = getdate()+ 2WHERE siTestScore 803 按下F5执行事务NIITSQL/Lesson 11/Slide 45 of 72实现处理事务和游标在在Terminal1上执行命令上执行命令*动作：3 在Query Analyzer 窗口中，键入：COMMIT TRANSACTION3 按下F5来执行语句NIITSQL/Lesson 11/Slide 46 of

27、72实现处理事务和游标*验证事务进行时表的锁定验证事务进行时表的锁定*动作：3 在Terminal2上事务将要等待由Terminal1解锁的ExternalCandiate表3 一旦COMMIT TRANSACTION命令在Terminal1上给出，在Terminal2上的事务就进行且User1的最近更新将是可见的 NIITSQL/Lesson 11/Slide 47 of 72实现处理事务和游标在在Terminal2上执行上执行COMMIT TRANSACTION语句语句*动作：3 在Query Analyzer 窗口中，键入：COMMIT TRANSACTION3 按下F5来执行此语句NI

28、ITSQL/Lesson 11/Slide 48 of 72实现处理事务和游标稍等一下稍等一下*下面哪些并发问题关系到：3 何时两个或多个事务试图基于原先选择的值来修改同一行3 何时把文档分发给人们，这些文档包含了原先文档中不再存在的信息NIITSQL/Lesson 11/Slide 49 of 72实现处理事务和游标死锁死锁*死锁是这样一种情形：两个用户（或事务）在个别的对象的上锁，并且每个用户正在等待另一个对象的锁DISTRIBUTORPRODUCTSTRANSACTION ATRANSACTION BNIITSQL/Lesson 11/Slide 50 of 72实现处理事务和游标死锁死

29、锁(续续)*设置死锁优选级3 为探测死锁的情况，SQL Server扫描在等待锁请求的会话3 SQL Server提供SET DEADLOCK_PRIORITY命令来定制死锁3 语法SET DEADLOCK_PRIORITY LOW|NORMAL|deadlock_var*定制LOCK_TIMEOUT3 SET LOCK_TIMEOUT命令可被用来设置等待被阻塞资源语句的最长时间NIITSQL/Lesson 11/Slide 51 of 72实现处理事务和游标死锁死锁(续续)3 按缺省方式， SQL Server不实施超时周期3 语法SET LOCK_TIMEOUT timeout_perio

30、dNIITSQL/Lesson 11/Slide 52 of 72实现处理事务和游标稍等一下稍等一下.*什么是死锁？你可怎样定制死锁?NIITSQL/Lesson 11/Slide 53 of 72实现处理事务和游标游标游标*游标是一个在给定结果集中帮助访问和操纵数据的数据库对象*游标能以下列方式处理结果集中的行：3 允许从结果集中检索指定的行3 允许结果集中当前行被修改3 帮助从结果集中当前行导航到不同的行3 允许被其它用户修改的数据在结果集中是可见的NIITSQL/Lesson 11/Slide 54 of 72实现处理事务和游标游标的结构游标的结构*当在SQL Server中使用游标时，

31、需要执行下面任务：3 需要定义游标和设置的它的属性。3 需要打开游标。3 所需的行要从游标取得。3 如果需要的话，游标的当前行中的数据可被修改的。3 需要关闭游标。3 游标应解除分配。这是一个很好的习惯由于游标所用的资源被释放。NIITSQL/Lesson 11/Slide 55 of 72实现处理事务和游标11.D.5把指定属性作为变量显示把指定属性作为变量显示*你需要召集所有部门头目开会。为此你需要部门和相应部门头目的列表如下，其格式如下： Department Name = Production Department Head = Samuel Moore Department Name

32、 = Sales Department Head = Donald Fleming . . NIITSQL/Lesson 11/Slide 56 of 72实现处理事务和游标任务单任务单*识别创建报告所需的步骤*执行创建报告所需的语句*按所需的结果验证其输出NIITSQL/Lesson 11/Slide 57 of 72实现处理事务和游标识别创建报告所需的步骤识别创建报告所需的步骤*声明游标3 你可以用DECLARE CURSOR语句来定义游标和它的特性集3 语法DECLARE cursor_name INSENSITIVE SCROLLCURSOR FOR select_statementF

33、OR READ ONLY | UPDATE OF column_listNIITSQL/Lesson 11/Slide 58 of 72实现处理事务和游标识别创建报告所需的步骤识别创建报告所需的步骤(续续)*打开游标3 你可以用OPEN语句打开前面声明过的游标3 语法OPEN cursor_nameNIITSQL/Lesson 11/Slide 59 of 72实现处理事务和游标识别创建报告所需的步骤识别创建报告所需的步骤(续续)*取数据3 在打开一个游标后，你可以从游标的结果集中检索指定行。SQL Server 2000提供FETCH语句来完成这项任务。3 语法FETCH NEXT | PR

34、IOR | FIRST | LAST | ABSOLUTE n | RELATIVE n FROM cursor_name INTO variable_name ,.n NIITSQL/Lesson 11/Slide 60 of 72实现处理事务和游标识别创建报告所需的步骤识别创建报告所需的步骤(续续)*关闭游标3 为了释放被游标持有的资源你必须关闭游标。游标可以用CLOSE语句关闭。 3 SyntaxCLOSE cursor_nameNIITSQL/Lesson 11/Slide 61 of 72实现处理事务和游标识别创建报告所需的步骤识别创建报告所需的步骤(续续)*解除分配游标3 你可以抹

35、去由DECLARE游标语句定义的游标的定义3 语法 DEALLOCATE cursor_nameNIITSQL/Lesson 11/Slide 62 of 72实现处理事务和游标识别创建报告所需的步骤识别创建报告所需的步骤(续续)*结果：3 你需要用下面的语句来显示报告。.-Create two variables that would store the -values returned by the fetch statement.DECLARE DepartmentName char(25)DECLARE DepartmentHead char(25)- Defines the curs

36、or that can be used to - access the records of the table,row by -row.NIITSQL/Lesson 11/Slide 63 of 72实现处理事务和游标识别创建报告所需的步骤识别创建报告所需的步骤(续续)DECLARE curDepartment cursor forSELECT vDepartmentName,vDepartmentHead FROM Department- Open the cursorOPEN curDepartment- Fetch the rows into variablesFETCH curDep

37、artment into DepartmentName, DepartmentHeadNIITSQL/Lesson 11/Slide 64 of 72实现处理事务和游标识别创建报告所需的步骤识别创建报告所需的步骤(续续)- Start a loop to display all the rows of - the cursor.While (fetch_status = 0)BEGINPrint Department Name = + DepartmentNamePrint Department Head = + DepartmentHead- Fetch the next row from the cursor. FETCH curDepartment into DepartmentName, DepartmentHeadENDNIITSQL/Lesson 11/Slide 65 of 72实现处理事务和游标识别创建报告所需的步骤识别创建报告所需的步骤(续续)- Close the cursorCLOSE curDepartment- De