游标事务和锁.ppt
《游标事务和锁.ppt》由会员分享,可在线阅读,更多相关《游标事务和锁.ppt(51页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、第第8章章游标、事务和锁游标、事务和锁第第8 8章章 游标、事务和锁游标、事务和锁游标游标1事务事务2锁锁3本章小结本章小结 48.1 游标 8.1.1游标的定义及优点游标的定义及优点1游标的定义游标能够部分读取返回结果集合中的数据行,并允许应用程序通过游标来定位修改表中数据。2游标的优点 允许定位在结果集的特定行。从结果集的当前位置检索一行或一部分行。支持对结果集中当前位置的行进行数据修改。为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持。提供脚本、存储过程和触发器中用于访问结果集中的数据的 Transact-SQL 语句。8.1.2游标的用法游标的用法(1)声明游
2、标(DECLARE CURSOR)(2)打开游标(OPEN CURSOR)(3)提取游标(FETCH CURSOR)(4)根据需要,对游标中当前位置的行执行修改操作(更新或删除)(5)关闭游标(CLOSE CURSOR)(6)释放游标(DEALLOCATE CURSOR)1声明游标(声明游标(DECLARECURSOR)可以使用可以使用DECLARE语句声明或创建一个游标。语法格式如语句声明或创建一个游标。语法格式如下:下:DECLAREcursor_nameCURSORLOCAL|GLOBALFORWARD_ONLY|SCROLLSTATIC|KEYSET|DYNAMIC|FAST_FORW
3、ARDREAD_ONLY|SCROLL_LOCKS|OPTIMISTICFORselect_statementFORUPDATEOFcolumn_name,.n;2打开游标(打开游标(OPENCURSOR)可以使用可以使用OPEN语句打开声明过的游标。语法格式语句打开声明过的游标。语法格式如下:如下:OPENcursor_name其中,其中,cursor_name是已声明过的并且没有打开的是已声明过的并且没有打开的游标名称。游标名称。3从打开的游标中提取数据(从打开的游标中提取数据(FETCHCURSOR)可以使用可以使用FETCH语句来提取数据。语法格式如下:语句来提取数据。语法格式如下:F
4、ETCHNEXT|PRIOR|FIRST|LAST|ABSOLUTEn|RELATIVEnFROMcursor_nameINTOvariable_name,.n4关闭游标(关闭游标(CLOSECURSOR)当不再使用游标时,应及时调用当不再使用游标时,应及时调用CLOSE语句关闭游标,以便语句关闭游标,以便释放游标所占用的系统资源。在关闭游标时,释放游标所占用的系统资源。在关闭游标时,SQLServer删除游标中的所有数据,并释放游标对数据库的所删除游标中的所有数据,并释放游标对数据库的所有锁定。所以,在游标关闭后,禁止提取游标数据,或有锁定。所以,在游标关闭后,禁止提取游标数据,或通过游标进
5、行定位修改或删除操作。但是,关闭游标并通过游标进行定位修改或删除操作。但是,关闭游标并不改变游标的定义,应用程序可以再次执行不改变游标的定义,应用程序可以再次执行OPEN语句打语句打开游标。开游标。可以使用可以使用CLOSE关闭游标。语法格式如下:关闭游标。语法格式如下:CLOSEcursor_name其中,其中,cursor_name是要被关闭的游标名。是要被关闭的游标名。5释放(删除)游标(释放(删除)游标(DEALLOCATECURSOR)由于关闭游标时并没有删除游标,因此,游标仍然由于关闭游标时并没有删除游标,因此,游标仍然占用着一定的系统资源。如果一个游标确定不再占用着一定的系统资源
6、。如果一个游标确定不再使用,将其关闭后,还需要使用使用,将其关闭后,还需要使用DEALLOCATE语句来删除游标。语法格式如下:语句来删除游标。语法格式如下:DEALLOCATEcursor_name其中,其中,cursor_name是已声明的游标名称。是已声明的游标名称。8.1.3使用游标修改数据使用游标修改数据在SQL Server 中,UPDATE语句和DELETE语句也支持游标操作,它们可以通过游标修改或删除游标基表中的当前数据行。这样,就可以通过游标更新和删除数据表中的数据。用于游标操作时,UPDATE语句的语法格式如下:UPDATE table_nameSET column_nam
7、e=expressionWHERE CURRENT OF cursor_name8.2 事务8.2.1什么是事务什么是事务事务(transaction)是SQL Server 中的单个逻辑工作单元,也是一个操作序列,它包含了一组数据库操作命令。一个事务内的所有语句被作为一个整体执行。在事务执行过程中,如果遇到错误,则可以回滚事务,取消该事务所做的全部改变,从而保证数据库的一致性和完整性。因此,事务是一个不可分割的工作逻辑单元,一个事务中的语句要么全部正确执行,要么全部不起作用。事务作为一个逻辑工作单元必须具有四个属性:原子性(Atomicity)、一致性(Consistency)、隔离性(Is
8、olation)和持久性(Durability)。这四个属性简称ACID属性。1显示事务显示事务显示事务就是可以显式地定义事务的开始和结束的事务,这类事务又称显示事务就是可以显式地定义事务的开始和结束的事务,这类事务又称为用户定义事务。为用户定义事务。(1)BEGINTRANSACTIONtransaction_name|tran_name_variable标记一个显式本地事务的起始点。标记一个显式本地事务的起始点。(2)COMMITTRANSACTIONtransaction_name|tran_name_variable或或COMMITWORK标志一个成功的显示事务或隐性事务的结束。如果没
9、有遇到错误,可使标志一个成功的显示事务或隐性事务的结束。如果没有遇到错误,可使用该语句成功地结束事务。该事务中的所有数据修改在数据库中都用该语句成功地结束事务。该事务中的所有数据修改在数据库中都将永久有效。事务占用的资源将被释放。将永久有效。事务占用的资源将被释放。(3)ROLLBACKTRANSACTIONtransaction_name|tran_name_variable|savepoint_name|savepoint_variable或或ROLLBACKWORK将显式事务或隐式事务回滚到事务的起点或事务内的某个保存点。用来清除遇到将显式事务或隐式事务回滚到事务的起点或事务内的某个保存
10、点。用来清除遇到错误的事务。该事务修改的所有数据都返回到事务开始时的状态。事务占用错误的事务。该事务修改的所有数据都返回到事务开始时的状态。事务占用的资源将被释放。的资源将被释放。(4)SAVETRANSACTION在事务内设置保存点或标记。保存点可以定义在按条件取消某个事务的一部分后,在事务内设置保存点或标记。保存点可以定义在按条件取消某个事务的一部分后,该事务可以返回的一个位置。如果将事务回滚到保存点,则根据需要必须完该事务可以返回的一个位置。如果将事务回滚到保存点,则根据需要必须完成其他剩余的成其他剩余的Transact-SQL语句语句和和COMMITTRANSACTION语句,或语句,
11、或者必须通过将事务回滚到起始点完全取消事务。若要取消整个事务,请使用者必须通过将事务回滚到起始点完全取消事务。若要取消整个事务,请使用ROLLBACKTRANSACTIONtransaction_name语句。这将撤消事务的所语句。这将撤消事务的所有语句和过程。有语句和过程。在事务中允许有重复的保存点名称,但指定保存点名称的在事务中允许有重复的保存点名称,但指定保存点名称的ROLLBACKTRANSACTION语句只将事务回滚到使用该名称的最近的语句只将事务回滚到使用该名称的最近的SAVETRANSACTION。7.1.2 事务类型与事务的状态2事务的状态图7.1说明了一个事务对数据库进行操作
12、时,其生存周期内可能进入的状态。图7.1 事务的状态描述7.1.2 事务类型与事务的状态活动状态:表示事务正在执行中。活动状态:表示事务正在执行中。提交未完成状态:表示事务虽然已完成,但事务对数据的更新可能还在缓冲提交未完成状态:表示事务虽然已完成,但事务对数据的更新可能还在缓冲区,未写到数据库中。区,未写到数据库中。失败状态:在两种状态下,事务可能进入失败状态。失败状态:在两种状态下,事务可能进入失败状态。一个处于活动状态的事务在执行过程中发生故障,将进入失败状态。一个处于活动状态的事务在执行过程中发生故障,将进入失败状态。一个处于提交未完成状态的事务执行时发生故障,将进入失败状态。一个处于
13、提交未完成状态的事务执行时发生故障,将进入失败状态。对于处于失败状态的事务必须进行回滚,才能使数据库处于一致状态。对于处于失败状态的事务必须进行回滚,才能使数据库处于一致状态。提交已完成状态:处于提交已完成状态的事务表示事务已执行完毕,数据已提交已完成状态:处于提交已完成状态的事务表示事务已执行完毕,数据已写入数据库,并处于一致状态。写入数据库,并处于一致状态。终止状态:表示事务执行回滚操作,数据库恢复到事务执行前的一致状态。终止状态:表示事务执行回滚操作,数据库恢复到事务执行前的一致状态。事务内部的故障例如,银行转账事务,这个事务把一笔金额从一个账户甲转给另一个账户乙。例如,银行转账事务,这
14、个事务把一笔金额从一个账户甲转给另一个账户乙。BEGINTRANSACTION读账户甲的余额读账户甲的余额BALANCE;BALANCE=BALANCE-AMOUNT;(AMOUNT为转账金额为转账金额)写回写回BALANCE;IF(BALANCE0)THEN打印打印金额不足,不能转账金额不足,不能转账;ROLLBACK;(撤销刚才的修改,恢复事务撤销刚才的修改,恢复事务)ELSE读账户乙的余额读账户乙的余额BALANCE1;BALANCE1=BALANCE1+AMOUNT;写回写回BALANCE1;COMMIT;2隐式事务隐式事务隐式事务是指在当前事务提交或回滚后,隐式事务是指在当前事务提交
15、或回滚后,SQLServer自动开始的事务。所以,隐式事务不需要自动开始的事务。所以,隐式事务不需要使用使用BEGINTRANSACTION语句标识事务的开语句标识事务的开始,而只需要用户使用始,而只需要用户使用ROLLBACKTRANSACTION、COMMTTTRANSACTION等语句回滚事务或结束事务。在回滚时候,等语句回滚事务或结束事务。在回滚时候,SQLServer又自动开始一个新的事务。又自动开始一个新的事务。3自动事务自动事务自动事务是一种能够自动执行并能自动回滚的事务。在自动事务是一种能够自动执行并能自动回滚的事务。在自动事务模式下,当一个语句成功执行后,它被自动提自动事务模
16、式下,当一个语句成功执行后,它被自动提交,而当它执行过程中产生错误时则自动回滚。自动事交,而当它执行过程中产生错误时则自动回滚。自动事务模式是务模式是SQLServer的默认事务管理模式,当与的默认事务管理模式,当与SQLServer建立连接后,直接进入自动事务模式,直到使用建立连接后,直接进入自动事务模式,直到使用BEGINTRANSCTION语句开始一个显示事务,或者执语句开始一个显示事务,或者执行行SETIMPLICIT_TRANSACTIONSON语句进入隐式语句进入隐式事务模式为止。但当显示事务被提交或回滚,或者执行事务模式为止。但当显示事务被提交或回滚,或者执行SETIMPLICI
17、T_TRANSACTIONSOFF语句后,语句后,SQLServer又进入自动事务管理模式。又进入自动事务管理模式。4批处理级事务批处理级事务只能应用于多个活动结果集只能应用于多个活动结果集(MARS),在,在MARS会话中启动的会话中启动的Transact-SQL显式或隐式事务变显式或隐式事务变为批处理级事务。当批处理完成时没有提交或回为批处理级事务。当批处理完成时没有提交或回滚的批处理级事务自动由滚的批处理级事务自动由SQLServer进行回滚。进行回滚。8.3 锁当多个用户同时访问数据时,当多个用户同时访问数据时,SQLServer2005数数据库引擎通过使用锁来保证事务完整性。在多用据
18、库引擎通过使用锁来保证事务完整性。在多用户环境中,锁可以防止多用户同时修改同一数据。户环境中,锁可以防止多用户同时修改同一数据。在在SQLServer中,锁是被自动实现的,但也可以中,锁是被自动实现的,但也可以显示使用。每个事务对所依赖的资源(如行、页显示使用。每个事务对所依赖的资源(如行、页或表)请求不同类型的锁,当事务不再依赖锁定或表)请求不同类型的锁,当事务不再依赖锁定的资源时,它将释放锁。应用程序可以通过选择的资源时,它将释放锁。应用程序可以通过选择事务隔离级别,为事务定义保护级别,以防被其事务隔离级别,为事务定义保护级别,以防被其他事务所修改。他事务所修改。8.3.1并发问题并发问题
19、当多个用户同时访问一个数据库而没有进行锁定时,修改数当多个用户同时访问一个数据库而没有进行锁定时,修改数据的用户会影响同时读取或修改相同数据的其他用户。据的用户会影响同时读取或修改相同数据的其他用户。即这些用户可以并发访问数据。如果数据存储系统没有即这些用户可以并发访问数据。如果数据存储系统没有并发控制,则用户可能会看到以下负面影响:并发控制,则用户可能会看到以下负面影响:丢失更新丢失更新 未提交的依赖(脏读)未提交的依赖(脏读)不一致的分析(不可重复读)不一致的分析(不可重复读)幻读幻读7.2 并发控制对数据库进行操作的事务可以以串行方式执行,即一个对数据库进行操作的事务可以以串行方式执行,
20、即一个事务执行结束后,另一事务才开始执行,这种调度方式称为事务执行结束后,另一事务才开始执行,这种调度方式称为串行调度,存在的缺点是系统资源利用率低,对用户响应慢。串行调度,存在的缺点是系统资源利用率低,对用户响应慢。因此通常采用的方案是多个事务并发执行,这分为两种因此通常采用的方案是多个事务并发执行,这分为两种情况:情况:单处理机情况下,多个事务轮流交叉运行,称为交叉单处理机情况下,多个事务轮流交叉运行,称为交叉并发方式;并发方式;多处理机的情况下,多个事务在多个处理机上同时运多处理机的情况下,多个事务在多个处理机上同时运行,称为同时并发执行,在并发执行方式下,当多个事务同行,称为同时并发执
21、行,在并发执行方式下,当多个事务同时对数据库中的同一数据进行操作时,如果时对数据库中的同一数据进行操作时,如果DBMS不进行不进行有效的管理和控制,就会破坏数据的一致性。有效的管理和控制,就会破坏数据的一致性。7.2.1 并发控制需解决的问题多个事务并发执行时,数据的不一致主要表现为:数据丢失更新、多个事务并发执行时,数据的不一致主要表现为:数据丢失更新、读读“脏脏”数据、不可重复读。数据、不可重复读。1 1数据丢失更新数据丢失更新所谓丢失更新(所谓丢失更新(Lost UpdateLost Update),是指两个或多个事务在并发执行的情况下,),是指两个或多个事务在并发执行的情况下,都对同一
22、数据项更新(即先读后改,再写入),从而导致后面的更新覆盖前面都对同一数据项更新(即先读后改,再写入),从而导致后面的更新覆盖前面的更新。的更新。例如,对于联网售票系统,设有两个例如,对于联网售票系统,设有两个事务事务T1,T2都要求访问数据项都要求访问数据项A,设,设事务事务T1,T2执行前执行前A的值为的值为20,T1,T2的执行顺序如图的执行顺序如图7.2所示,当事务所示,当事务T1读得的值为读得的值为20,T2读得的值也是读得的值也是20;T1写入写入A的值为的值为19,T2写入写入A的值也的值也是是19,显然这与事实不符,这是由于,显然这与事实不符,这是由于两个事务并发地对同一数据写入
23、而引两个事务并发地对同一数据写入而引起的,因此这种情况又称为写起的,因此这种情况又称为写-写冲突。写冲突。图7.2 数据丢失更新7.2.1 并发控制需解决的问题2读读“脏脏”数据数据读读“脏脏”数据是由于一个事务正在读另一个更新事务尚未提交的数数据是由于一个事务正在读另一个更新事务尚未提交的数据引起的,这种数据不一致的情况又称为读据引起的,这种数据不一致的情况又称为读-写冲突。写冲突。图7.3 读“脏”数据例如,对于如图例如,对于如图7.3所示的两个并所示的两个并发执行的事务发执行的事务T1,T2,T2先读得先读得A的值,的值,T1读得读得A的值,修改并写的值,修改并写入,然后入,然后T2读得
24、读得T1修改后的修改后的A的值,的值,T1执行回滚操作,显然执行回滚操作,显然T2第二次第二次读到的读到的A的值是一个不存在的值,的值是一个不存在的值,这是一个这是一个“脏脏”数据。读数据。读“脏脏”数数据是由读据是由读-写冲突引起的。写冲突引起的。7.2.1 并发控制需解决的问题3不可重复读不可重复读不可重复读分不可重复读分3种情况:种情况:对于并发执行的两个事务对于并发执行的两个事务T1,T2,当事务,当事务T1读取数据某一数据后,事务读取数据某一数据后,事务T2对该数据执行了更新操作,使得对该数据执行了更新操作,使得T1无无法再次读取与前一次相同的结果,如图法再次读取与前一次相同的结果,
25、如图7.4所示,所示,T1读数据读数据A后,后,T2修改了数修改了数据据A,T1再次读数据再次读数据A,却得到不同的,却得到不同的结果。结果。图7.4 不可重复读事务事务T1按一定条件读取某些数据记录后,事务按一定条件读取某些数据记录后,事务T2插入了一些记录,插入了一些记录,T1再次以相同条件读取记录时得到不同的结果集。再次以相同条件读取记录时得到不同的结果集。事务事务T1按一定条件读取某些数据记录后,事务按一定条件读取某些数据记录后,事务T2删除了其中的一些删除了其中的一些记录,记录,T1再次以相同条件读取记录时得到不同的结果集。再次以相同条件读取记录时得到不同的结果集。后面两种情况又称为
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 游标 事务
限制150内