《《第8章游标、事务和锁》-精选课件（公开PPT）》由会员分享，可在线阅读，更多相关《《第8章游标、事务和锁》-精选课件（公开PPT）（51页珍藏版）》请在金锄头文库上搜索。
1、2020/6/23,1,第8章 游标、事务和锁,2,2020/6/23,第8章 游标、事务和锁,游标,1,事务,2,锁,3,本章小结,4,3,2020/6/23,8.1 游标,8.1.1 游标的定义及优点 1游标的定义 游标能够部分读取返回结果集合中的数据行，并允许应用程序通过游标来定位修改表中数据。 2游标的优点 允许定位在结果集的特定行。 从结果集的当前位置检索一行或一部分行。 支持对结果集中当前位置的行进行数据修改。 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。 提供脚本、存储过程和触发器中用于访问结果集中的数据的 Transact-SQL 语句。,4,2
2、020/6/23,8.1.2 游标的用法 （1）声明游标（DECLARE CURSOR） （2）打开游标（OPEN CURSOR） （3）提取游标（FETCH CURSOR） （4）根据需要，对游标中当前位置的行执行修改操作（更新或删除） （5）关闭游标（CLOSE CURSOR） （6）释放游标（DEALLOCATE CURSOR）,5,2020/6/23,1声明游标（DECLARE CURSOR） 可以使用DECLARE 语句声明或创建一个游标。语法格式如下： DECLARE cursor_name CURSOR LOCAL | GLOBAL FORWARD_ONLY | SCROLL S
3、TATIC | KEYSET | DYNAMIC | FAST_FORWARD READ_ONLY | SCROLL_LOCKS | OPTIMISTIC FOR select_statement FOR UPDATE OF column_name ,.n ;,6,2020/6/23,2打开游标（OPEN CURSOR） 可以使用OPEN语句打开声明过的游标。语法格式如下： OPEN cursor_name 其中，cursor_name 是已声明过的并且没有打开的游标名称。,7,2020/6/23,3从打开的游标中提取数据（FETCH CURSOR） 可以使用FETCH语句来提取数据。语法格式
4、如下： FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n FROM cursor_name INTO variable_name ,.n ,8,2020/6/23,4关闭游标（CLOSE CURSOR） 当不再使用游标时，应及时调用CLOSE语句关闭游标，以便释放游标所占用的系统资源。在关闭游标时，SQL Server删除游标中的所有数据，并释放游标对数据库的所有锁定。所以，在游标关闭后，禁止提取游标数据，或通过游标进行定位修改或删除操作。但是，关闭游标并不改变游标的定义，应用程序可以再次执行OPEN语句打开游标。 可以使用
5、CLOSE关闭游标。语法格式如下： CLOSE cursor_name 其中，cursor_name 是要被关闭的游标名。,9,2020/6/23,5释放（删除）游标（DEALLOCATE CURSOR） 由于关闭游标时并没有删除游标，因此，游标仍然占用着一定的系统资源。如果一个游标确定不再使用，将其关闭后，还需要使用DEALLOCATE语句来删除游标。语法格式如下： DEALLOCATE cursor_name 其中，cursor_name 是已声明的游标名称。,10,2020/6/23,8.1.3 使用游标修改数据 在SQL Server 中，UPDATE语句和DELETE语句也支持游标操
6、作，它们可以通过游标修改或删除游标基表中的当前数据行。这样，就可以通过游标更新和删除数据表中的数据。用于游标操作时，UPDATE语句的语法格式如下： UPDATE table_name SET column_name=expression WHERE CURRENT OF cursor_name,11,2020/6/23,8.2 事务,8.2.1 什么是事务 事务（transaction）是SQL Server 中的单个逻辑工作单元，也是一个操作序列，它包含了一组数据库操作命令。一个事务内的所有语句被作为一个整体执行。在事务执行过程中，如果遇到错误，则可以回滚事务，取消该事务所做的全部改变，从
7、而保证数据库的一致性和完整性。因此，事务是一个不可分割的工作逻辑单元，一个事务中的语句要么全部正确执行，要么全部不起作用。 事务作为一个逻辑工作单元必须具有四个属性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这四个属性简称ACID属性。,12,2020/6/23,1显示事务 显示事务就是可以显式地定义事务的开始和结束的事务，这类事务又称为用户定义事务。 （1）BEGIN TRAN SACTION transaction_name | tran_name_variable 标记一个显式本地事务的起始点。 （2）C
8、OMMIT TRANSACTION transaction_name | tran_name_variable 或 COMMIT WORK 标志一个成功的显示事务或隐性事务的结束。如果没有遇到错误，可使用该语句成功地结束事务。该事务中的所有数据修改在数据库中都将永久有效。事务占用的资源将被释放。,13,2020/6/23,（3）ROLLBACK TRAN SACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable 或 ROLLBACK WORK 将显式事务或隐式事务回滚到事务的起点或事务
9、内的某个保存点。用来清除遇到错误的事务。该事务修改的所有数据都返回到事务开始时的状态。事务占用的资源将被释放。 （4）SAVE TRANSACTION 在事务内设置保存点或标记。保存点可以定义在按条件取消某个事务的一部分后，该事务可以返回的一个位置。如果将事务回滚到保存点，则根据需要必须完成其他剩余的 Transact-SQL 语句 和 COMMIT TRANSACTION 语句，或者必须通过将事务回滚到起始点完全取消事务。若要取消整个事务，请使用 ROLLBACK TRANSACTION transaction_name 语句。这将撤消事务的所有语句和过程。 在事务中允许有重复的保存点名称，
10、但指定保存点名称的 ROLLBACK TRANSACTION 语句只将事务回滚到使用该名称的最近的 SAVE TRANSACTION。,7.1.2 事务类型与事务的状态,2事务的状态 图7.1说明了一个事务对数据库进行操作时，其生存周期内可能进入的状态。,图7.1 事务的状态描述,7.1.2 事务类型与事务的状态,活动状态：表示事务正在执行中。 提交未完成状态：表示事务虽然已完成，但事务对数据的更新可能还在缓冲区，未写到数据库中。 失败状态：在两种状态下，事务可能进入失败状态。 一个处于活动状态的事务在执行过程中发生故障，将进入失败状态。 一个处于提交未完成状态的事务执行时发生故障，将进入失败
11、状态。 对于处于失败状态的事务必须进行回滚，才能使数据库处于一致状态。 提交已完成状态：处于提交已完成状态的事务表示事务已执行完毕，数据已写入数据库，并处于一致状态。 终止状态：表示事务执行回滚操作，数据库恢复到事务执行前的一致状态。,事务内部的故障,例如，银行转账事务，这个事务把一笔金额从一个账户甲转给另一个账户乙。 BEGIN TRANSACTION 读账户甲的余额BALANCE； BALANCE=BALANCE-AMOUNT；(AMOUNT 为转账金额) 写回BALANCE； IF(BALANCE 0 ) THEN 打印金额不足，不能转账； ROLLBACK；(撤销刚才的修改，恢复事务)
12、 ELSE 读账户乙的余额BALANCE1； BALANCE1=BALANCE1+AMOUNT； 写回BALANCE1； COMMIT； ,17,2020/6/23,2隐式事务 隐式事务是指在当前事务提交或回滚后，SQL Server 自动开始的事务。所以，隐式事务不需要使用BEGIN TRANSACTION语句标识事务的开始，而只需要用户使用ROLLBACK TRANSACTION、COMMTT TRANSACTION 等语句回滚事务或结束事务。在回滚时候，SQL Server又自动开始一个新的事务。,18,2020/6/23,3自动事务 自动事务是一种能够自动执行并能自动回滚的事务。在自动
13、事务模式下，当一个语句成功执行后，它被自动提交，而当它执行过程中产生错误时则自动回滚。自动事务模式是SQL Server的默认事务管理模式，当与SQL Server建立连接后，直接进入自动事务模式，直到使用BEGIN TRANSCTION语句开始一个显示事务，或者执行SET IMPLICIT_TRANSACTIONS ON语句进入隐式事务模式为止。但当显示事务被提交或回滚，或者执行SET IMPLICIT_TRANSACTIONS OFF语句后，SQL Server 又进入自动事务管理模式。,19,2020/6/23,4批处理级事务 只能应用于多个活动结果集 (MARS)，在 MARS 会话中
14、启动的 Transact-SQL 显式或隐式事务变为批处理级事务。当批处理完成时没有提交或回滚的批处理级事务自动由 SQL Server 进行回滚。,20,2020/6/23,8.3 锁,当多个用户同时访问数据时，SQL Server 2005数据库引擎通过使用锁来保证事务完整性。在多用户环境中，锁可以防止多用户同时修改同一数据。在SQL Server中，锁是被自动实现的，但也可以显示使用。每个事务对所依赖的资源（如行、页或表）请求不同类型的锁，当事务不再依赖锁定的资源时，它将释放锁。应用程序可以通过选择事务隔离级别，为事务定义保护级别，以防被其他事务所修改。,21,2020/6/23,8.3
15、.1 并发问题 当多个用户同时访问一个数据库而没有进行锁定时，修改数据的用户会影响同时读取或修改相同数据的其他用户。即这些用户可以并发访问数据。如果数据存储系统没有并发控制，则用户可能会看到以下负面影响： 丢失更新 未提交的依赖（脏读） 不一致的分析（不可重复读） 幻读,7.2 并发控制,对数据库进行操作的事务可以以串行方式执行，即一个事务执行结束后，另一事务才开始执行，这种调度方式称为串行调度，存在的缺点是系统资源利用率低，对用户响应慢。 因此通常采用的方案是多个事务并发执行，这分为两种情况： 单处理机情况下，多个事务轮流交叉运行，称为交叉并发方式； 多处理机的情况下，多个事务在多个处理机上
16、同时运行，称为同时并发执行，在并发执行方式下，当多个事务同时对数据库中的同一数据进行操作时，如果DBMS 不进行有效的管理和控制，就会破坏数据的一致性。,7.2.1 并发控制需解决的问题,多个事务并发执行时，数据的不一致主要表现为：数据丢失更新、读“脏”数据、不可重复读。 1数据丢失更新 所谓丢失更新（Lost Update），是指两个或多个事务在并发执行的情况下，都对同一数据项更新（即先读后改，再写入），从而导致后面的更新覆盖前面的更新。,例如，对于联网售票系统，设有两个事务T1，T2都要求访问数据项A，设事务T1，T2执行前A的值为20，T1，T2的执行顺序如图7.2所示，当事务T1读得的值为20，T2读得的值也是20；T1写入A的值为19，T2写入A的值也是19
