第11章_游标与事务

1、第第1010章章 游标与事务游标与事务主要内容主要内容 游标的概念游标的概念游标的基本操作游标的基本操作事务和锁事务和锁2022-4-2729.1游标的管理 9.1.1 9.1.1 游标的简介及用途游标的简介及用途1.1.游标的概念游标的概念 游标（游标（CursorCursor）是一种处理数据的方法，为了查看或者）是一种处理数据的方法，为了查看或者处理结果集中的数据，游标提供了在结果集中向前或者向后处理结果集中的数据，游标提供了在结果集中向前或者向后浏览数据的能力。我们可以把游标看成一个在记录集中的指浏览数据的能力。我们可以把游标看成一个在记录集中的指针，通过这个指针既可以指向当前位置，又可

2、以指向结果集针，通过这个指针既可以指向当前位置，又可以指向结果集中的任意位置，并且允许用户对指定位置的数据进行处理，中的任意位置，并且允许用户对指定位置的数据进行处理，可以把结果集中的数据放在数组、应用程序或其他位置。可以把结果集中的数据放在数组、应用程序或其他位置。 2.2.使用游标的优点使用游标的优点 允许程序对由查询语句允许程序对由查询语句SELECTSELECT返回的记录集中的每一行数据返回的记录集中的每一行数据执行相同或不同的操作，而不是对整个行集合执行同一个操执行相同或不同的操作，而不是对整个行集合执行同一个操作；作； 提供对基于游标位置的行进行删除和更新的能力；提供对基于游标位置

3、的行进行删除和更新的能力； 游标实际上作为面向集合的数据库管理系统和面向行的游标实际上作为面向集合的数据库管理系统和面向行的程序设计之间的桥梁，使这两种处理方式通过游标联系起来。程序设计之间的桥梁，使这两种处理方式通过游标联系起来。提供脚本、存储过程和触发器中使用的用于访问结果集中数提供脚本、存储过程和触发器中使用的用于访问结果集中数据的据的T-SQLT-SQL语句。语句。 1 1游标的声明：游标的声明：格式：DECLARE cursor_nameinsensitiveSCROLLCURSOR local global static|keyset|dynamic FOR select_stat

4、ement forREAD ONLY|UPDATEOF column_name_list 9.1.2游标的基本操作游标的基本操作 2 2游标的创建过程游标的创建过程1)打开Microsoft SQL Server Manager 管理器。单击常用工具栏中的按钮，新建一个数据库查询文档，如图9.1所示。图 9. 1 新建查询2)2)在数据库引擎查询文档中输入如下代码：在数据库引擎查询文档中输入如下代码：Use xsUse xsDeclare hy1 cursor for Declare hy1 cursor for Select Select * * from from 学生学生 where w

5、here 性别性别= =男男只读游标的创建：只读游标的创建：Use xsUse xsDeclare hy2 cursor forDeclare hy2 cursor forSelect Select * * from from 学生学生where where 入学成绩入学成绩500500For read onlyFor read only更新游标的创建：更新游标的创建：Use xsUse xsDeclare hy3 cursor for Declare hy3 cursor for Select Select * * from from 学生学生 where where 班级班级= =计算机计

6、算机For updateFor update正确输入后，按键盘上的正确输入后，按键盘上的“F5F5”键执行键执行SQLSQL语句，显示如下提语句，显示如下提示信息：示信息：Command(s) completed successfullyCommand(s) completed successfully。创建游标完毕。创建游标完毕。3游标的打开游标的打开格式：OPEN GLOBAL cursor_name|cursor_variable_name说明：Cursor_name:要打开的游标名。cursor_variable_name:游标变量名，该变量名是引用了的一个游标。GLOBAL: 用来说

7、明打开的是全局游标，省略时打开局部游标。4游标数据的读取游标数据的读取 创建游标并打开游标以后，利用T-SQL可以读取游标中的数据，语法格式如下： Fetch next | prior | first | last absolute n | relative n From 游标名 into 变量名5关闭游标：关闭游标：利用游标处理完数据之后，必须关闭游标，格式如下：Close 游标名称6释放游标释放游标游标关闭以后并没有释放游标所占用的系统资源，因此关闭游标以后，必须释放游标才能释放游标所占的系统资源，格式如下： Deallocate 游标名称9.2 9.2 事务事务9.2.1 9.2.1 事

8、务及工作原理事务及工作原理1 1事务事务 事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。2 2事务特性事务特性 原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持续性(Durability)。这四个特性简称为ACID特性。3 3SQL ServerSQL Server通过记录事务日志保持事务的持久性。通过记录事务日志保持事务的持久性。数据库系统一般采用记录日志和恢复机制来保证事务的一致性；采用并发控制机制来保证多个事务的交叉运行不影响这些事务的原子性。4 4事务的工作原理（事务的工作原理（COMMIT,R

9、OLLBACK,UNDO,REDOCOMMIT,ROLLBACK,UNDO,REDO）102022-4-279.2.29.2.2执行事务及执行模式执行事务及执行模式1 1事务的执行事务的执行 应用程序主要通过指定事务启动和结束的时间来控制事务。2 2事务的类型事务的类型 根据运行模式，SQL Server 2005将事务分为4种类型：自动提交事务、显式事务、隐式事务和批处理事务。1） 显式事务 显式事务指每个事务通过API函数或通过发出Transact-SQL BEGIN TRANSACTION 语句来显式启动事务，以COMMIT或ROLLBACK语句显式结束。2） 自动提交事务 自动提交事务

10、是指每条语句都是一个事务。3） 隐式事务 隐式事务是指在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。4） 批范围的事务 只适用于多个活动的结果集 (MARS)，在 MARS 会话中启动的 Transact-SQL 显式或隐式事务将变成批范围的事务。112022-4-279.2.3 9.2.3 编写事务编写事务控制事务用到的T-SQL语句:1 1BEGIN TRANSACTION BEGIN TRANSACTION 语句语句2 2COMMI T TRANSACTIONCOMMI T TRANSACTION语句语句3 3ROLLBACK TRANSAC

11、TION ROLLBACK TRANSACTION 或或 ROLLBACK WORK ROLLBACK WORK 语句语句例8-12 定义一个事务，将所有外聘教师的考评成绩定为良好，并提交该事务。BEGIN TRANSACTION EX_teacher_gradeUPDATE 考评SET 得分情况=良好WHERE 档案类型=外聘GO COMMIT TRANSACTION GO122022-4-279.2.4 9.2.4 事务保存点事务保存点保存点提供了一种机制，用于回滚部分事务。在创建保存点后，可以执行 ROLLBACK TRANSACTION savepoint_name 语句以回滚到保存点

12、，而不是回滚到事务的起点。 语法格式为：SAVE TRAN | TRANSACTION savepoint_name | savepoint_variable ; 132022-4-279.2.59.2.5事务隔离级别事务隔离级别 隔离级别定义一个事务必须与其他事务所进行的资源或数据更改相隔离的程度。 Microsoft SQL Server 2005的隔离级别由低到高包括： 未提交读（隔离事务的最低级别，只能保证不读取物理上损坏的数据） 已提交读（数据库引擎的默认级别） 可重复读 可序列化（隔离事务的最高级别，事务之间完全隔离） Transact-SQL使用 SET TRANSACTION

13、ISOLATION LEVEL 语句设置隔离级别142022-4-279.3 9.3 锁锁9.3.1 9.3.1 锁及其作用锁及其作用当多个用户并发地存取数据库时会产生多个事务同时存取同一数据的情况，如果对并发操作不加控制就可能会存取和存储不正确的数据，破坏事务的一致性和数据库的致性。由多用户并发事务执行所导致的数据不一致可能会导致下面几种情况：1丢失修改2不可重复读3脏读 (Dirty Read)锁是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段。封锁是指一个事务在对某个数据对象操作之前，先向系统提出请求，对其加锁，在事务结束之后释放锁。在事务释放它的锁之前，其他事务不能更新此数

14、据对象。152022-4-279.3.2 9.3.2 锁的对象与模式锁的对象与模式1 1锁的对象锁的对象 SQL Server具有多粒度锁定，允许一个事务锁定不同类型的资源。锁的对象包括下表所列的资源：162022-4-27资源资源 说明说明 RID用于锁定堆中的单个行的行标识符。KEY索引中用于保护可序列化事务中的键范围的行锁。PAGE数据库中的 8 KB 页，例如数据页或索引页。EXTENT一组连续的八页，例如数据页或索引页。HOBT堆或B树。保护索引或没有聚集索引的表中数据页堆的锁。TABLE包括所有数据和索引的整个表。FILE数据库文件。APPLICATION应用程序专用的资源。MET

15、ADATA元数据锁。ALLOCATION_UNIT分配单元。DATABASE整个数据库。9.3.2 9.3.2 锁的对象与模式锁的对象与模式2 2锁模式锁模式1） 共享锁共享锁（S 锁）允许并行事务读取同一种资源。2）排他锁排他锁（X 锁）可以防止并发事务对资源进行访问。3） 更新锁更新锁（U 锁）可以防止常见的死锁。一次只有一个事务可以获得资源的更新锁（U 锁）。如果事务修改资源，则更新锁（U 锁）转换为排他锁（X 锁）。4） 意向锁数据库引擎使用意向锁来保护共享锁（S 锁）或排他锁（X 锁）放置在锁层次结构的底层资源上。意向锁有两种用途： 防止其他事务以会使较低级别的锁无效的方式修改较高级

16、别资源。提高数据库引擎在较高的粒度级别检测锁冲突的效率。172022-4-279.3.3 9.3.3 死锁问题死锁问题死锁是有两个或以上的事务处于等待状态，每个事务都在等待另一个事务解除封锁，它才能继续执行下去，结果任何一个事务都无法执行，这种现象就是死锁。下面两种情况下可能会出现死锁：1两个事务同时锁定了两个单独的对象，并且第一个事务要求在另外一个事务锁定的对象上获得一锁；2在一个数据库中有若干个长时间运行的事务执行并操作。降低死锁的原则：1按同一顺序访问对象2避免事务中的用户交互3保持事务简短并在一个批处理中4使用低隔离级别182022-4-279.3.4 9.3.4 查看锁信息查看锁信息Microsoft SQL Server 2005 提供的获取有关数据库引擎实例中的当前锁活动的信息的方法： 使用 SQL Server Profiler，可以