第8章-游标事务锁课件.ppt

1、学习要点：学习要点：游标的定义和使用方法 事务的定义、操作以及具体应用 锁的定义和使用方法8.1 游标游标8.2 事务事务8.3 锁锁 游标是一种数据结构。通过这种结构，程序可以将查询结果保存在其中，并可对其中某行（或某些行）的数据进行操作。游标中的数据保存在内存中，从其中提取数据的速度要比从数据表中直接提取数据的速度要快得多。8.1.1 游标的定义及优点游标的定义及优点 1游标的定义游标的定义 SELECT 语句返回的所有行的集合均作为一个整体处理，而无法对其中的一行或部分行做单独处理。而在实际开发中，尤其是在交互应用程序设计中，人们常常需要对SELECT语句所返回结果集合中的不同行做不同处

2、理。游标正是这样一种方法，它能够部分读取返回结果集合中的数据行，并允许应用程序通过游标定位修改表中数据。2游标的优点游标的优点 游标的主要好处是可以逐行的处理数据。通过游标允许程序对由select产生的结果集的每一行执行相同或不同的操作；允许从结果集中检索指定的行；允许结果集中的当前行被修改；允许被其它用户修改的数据在结果集中是可见的。8.1.2 游标的用法游标的用法 1声明游标声明游标 语法形式如下：DECLARE cursor_name CURSOR FOR select_statement FOR READ ONLY|UPDATE OF column_name ,.n v cursor_

3、name：是所定义的游标名称。cursor_name 必须遵从标识符规则。v select_statement：是定义游标结果集的标准 SELECT 语句。在游标声明的 select_statement内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和INTO。v FOR READ ONLY：指出游标的结果集是只读的，不能修改。v FOR UPDATEOF column_name,.n：指出游标的结果集是可以被修改的。如果指定 OF column_name,.n 参数，则只允许修改所列出的列。如果在 UPDATE 中未指定列的列表，则可以更新所有列。例8-1 在

4、cjgl数据库中定义一个游标，其数据为表cj中的全部数据。Use cjglGODECLARE myCursor CURSORFor select*from cj2 2打开游标打开游标 OPEN cursor_name OPEN cursor_name v cursor_name 是已声明过的并且没有打开的游标名称。v 打开游标后，可以从全局变量CURSOR_ROWS中读取游标结果集合中的行数。例8-2 打开myCursor游标，替换上次提取行之后的行记录中的成绩=3，并显示结果。Use cjglGOOPEN myCursorPrint CURSOR_ROWSPrint CURSOR_ROWSF

5、ETCH NEXT FROM myCursorUpdate cj Set 成绩成绩=3Where CURRENT OF myCursorPrint CURSOR_ROWSPrint CURSOR_ROWSGO3从打开的游标中提取数据从打开的游标中提取数据 FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n FROM cursor_name INTO variable_name ,.n v NEXT：提取上次提取行之后的行，即向下移动。如果 FETCH NEXT 为对游标的第一次提取操作，则返回结果集中的第一行。NEXT 为默认的游标提取选项。v

6、PRIOR：提取上次提取行之前的行。如果 FETCH PRIOR 为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。v LAST：提取游标中的最后一行并将其作为当前行。ABSOLUTE n：如果 n 为正数，则提取游标中从第 1 行开始的第 n 行。如果 n 为负整数，则提取游标中的倒数第 n 行。v RELATIVE n：如果 n 为正数，则提取所上次提取行之后的第 n 行。如果 n 为负数，则提取上次所提取行之前的第 n 行。如果 n 为 0，则同一行被再次提取。v cursor_name：要从中进行提取的游标的名称。v INTO variable_name,.n：允许将提取操

7、作的列数据放到局部变量中。注意：FETCH语句每次只能提取一行数据。因为Transact-SQL游标不支持块（多行）提取操作。F E T C H 语 句 的 执 行 状 态 保 存 在 全 局 变 量FETCH_STATUS中，该变量有三种取值：当取值为0时，说明FETCH语句执行成功。当取值为-1时，说明FETCH 语句失败或此行不在结果集中。当取值为-2时，说明被提取的行不存在。4 4关闭游标关闭游标 当提取完游标结果集中的数据时，应及时调用CLOSE语句关闭游标，以便释放游标所占用的系统资源。CLOSE cursor_name v cursor_name 是要被关闭的游标名。例8-3 关

8、闭myCursor游标。CLOSE myCursor5 5释放（删除）游标释放（删除）游标由于关闭游标时并没有删除游标，因此，游标仍然占用着系统资源。则将其关闭后，还需要使用DEALLOCATE语句来释放游标所占用的系统资源。DEALLOCATE cursor_nameDEALLOCATE cursor_name例8-4 释放释放myCursor游标。DEALLOCATEDEALLOCATE myCursor8.1.3 使用游标修改数据使用游标修改数据 UPDATE语句和DELETE语句也支持游标操作，它们可以通过游标修改或删除游标基表中的当前数据行。这样，就可以通过游标更新和删除数据表中的数

9、据。UPDATE table_nameUPDATE table_nameSET SET 子句子句WHERE CURRENT OF cursor_nameWHERE CURRENT OF cursor_namev table_name：为需要修改的数据表名。v cursor_name：为游标名。例8-5 打开myCursor游标，替换所有行记录中的成绩=3。Use cjglGOOPEN myCursorFETCH NEXT FROM myCursorUpdate cj Set 成绩成绩=3DELETE FROM table_nameWHERE CURRENT OF cursor_namev ta

10、ble_name：为需要修改的数据表名。v cursor_name：为游标名。例8-6 打开myCursor游标，将游标“myCursor”中当前行中内容删除。Use cjglGOOPEN myCursorDELETE FROM cjWhere CURRENT OF myCursor8.2.1 8.2.1 什么是事务什么是事务 事务（Transaction）是SQL Server 中的单个逻辑工作单元，一个事务内的所有语句被作为一个整体执行。在事务执行过程中，如果遇到错误，则可以回滚事务，取消该事务所做的全部改变，从而保证数据库的一致性和可恢复性。也就是说，一个事务要么其中的语句全部正确执行，

11、要么全部语句不起作用。事务作为一个逻辑工作单元必须有四个属性，称为 ACID（原子性、一致性、隔离性和持久性）属性。v 原子性原子性：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。v 一致性一致性：事务在完成时，必须使所有的数据都保持一致状态。v 隔离性隔离性：由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。v 持久性持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。在SQL Server 中，系统将事务模式分为显式事务、隐式显式事务、隐式事务和自动事务事务和自动事务三种。1 1显示事务显示事务 显示事务是指由用户通过Tra

12、nsace-SQL 事务语句而定义的事务。这类事务又称为用户定义事务。（1）BEGIN TRANSACTION 标记一个显式本地事务的起始点。（2）COMMIT TRANSACTION 标志一个成功的显示事务或隐性事务的结束。（3）ROLLBACK TRANSACTION 将显式事务或隐式事务回滚到事务的起点或事务内 的某个保存点。（4）SAVE TRANSACTION 将显式事务回滚到事务的起点。2 2隐式事务隐式事务隐式事务是指在当前事务提交或回滚后，SQL Server 自动开始的事务。隐式事务不需要使用BEGIN TRANSACTION语句标识事务的开始，而只需要用户使用ROLLBAC

13、K TRANSACTION、COMMTT TRANSACTION 等语句回滚事务或结束事务。在回滚时候，SQL Server又自动开始一个新的事务。3 3自动事务自动事务自动事务是一种能够自动执行并能自动回滚的事务。在自动事务模式下，当一个语句成功执行后，它被自动提交，而当它执行过程中产生错误则自动回滚。自动事务模式是SQL Server的默认事务管理模式，当与SQL Server建立连接后，直接进入自动事务模式，直到使用BEGIN TRANSCTION语句开始一个显示事务，或者执行SET IMPLICT_TRANSACTIONS ON语句进入隐式事务模式为止。但当显示事务被提交或回滚，或者执

14、行SET IMPLICIT_TRANSACTIONS OFF语句后，SQL Server 又进入自动事务管理模式。锁是为了确保事务完整性和数据库一致性。锁可以防止用户读取正在由其他用户更改的数据，并可以防止多个用户同时更改相同数据。如果不使用锁，则数据库中的数据可能在逻辑上不正确，并且对数据的查询可能会产生意想不到的结果。8.3.1 8.3.1 并发问题并发问题1.什么是并发问题 如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生数据不一致问题，这就是并发问题。2.数据库的并发操作导致的数据不一致性的问题包括：(1)丢失或覆盖更新(Lost Update)。

15、(2)未确认的相关性(脏读，Dirty Read)。(3)不一致的分析(不可重复读，Non-Repeatable Read)。(4)幻想读。(1)丢失更新 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其他事务的存在。最后的更新将重写由其他事务所做的更新，这将导致数据丢失。例如，两个用户A和B，分别在T1、T2、T3和T4时间对表中的数据进行操作，如表1所示。表1 丢失更新用户用户 T1 T2 T3 T4 A 读取x=50 x=x-20,并写回数据库B 读取x=50 x=x-40,并写回数据库 用户A和B都读取x(x=50)，然后分别把x减去2

16、0和40，用户A在T3时刻将修改后的x(x=30)写回数据库。随后用户B在T4时刻将修改后的x(x=10)写回数据库。于是，对于用户A而言，其修改在T4时刻丢失了(应为30，确成为10)。因为用户A和B用的都是同一个数据库。(2)脏读(未确认的相关性)当第二个事务选择其他事务正在更新的行时，会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。例如，用户A在T1时刻读取x(x=50)，然后将x加上40，用户B在T3时刻由数据缓存读取x(x=90)，但是用户A在T4时刻回滚事务，撤销了对x的修改，数据库中仍然维持x=50，但是用户B已经把改变的数据(x=90

17、)读走。如表2所示。表2 脏读用户用户 T1 T2 T3 T4 A 读取x=50 x=x+40 回滚事务 B 读取x,x此时是90(3)不一致的分析(不可重复读)当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析与未确认的相关性类似，因为其他事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次(两次或更多)读取同一行，而且每次信息都由其他事务更改，因而该行被非重复读取。如表3所示。表3 不可重复读用户用户 T1 T2 T3 T4 T5 T6 A 读取x=50 读取y=

18、30,x+y=80 读取z=16,x+y+z=96B 读取x=50 x=x+30 将x=80写入数据库 例如，用户A和B分别读取x(x=50)，在T3时刻用户A读取 y(y=30)并计算x+y=80，在T4时刻用户B把x加上30，并在T5时刻将其写入数据库。在T6时刻用户A读取z，并仍然使用前面的x和y，计算x+y+z=95。如表3所示。如果用户A为进行校核，而把x、y和z重新读取了一次，并进行计算，却出现x+y+z=126。(4)幻想读 当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为

19、该行已被其他事务删除。同样，由于其他事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。8.3.2 8.3.2 事务隔离级别事务隔离级别 为了保证数据在使用中的正确性，需要对事务进行隔离。隔离级别是一个事务必须与其他事务进行隔离的程度，共分为四个事务隔离级别。(1)未提交读(Read Uncommitted)(2)提交读(Read committed)(3)可重复读(Repeatable Read)(4)可串行读(Serializable Read)隔离级别脏读不可重复读取幻像未提交读是是是提交读否是是可重复读否否是可串行读否否否8.3.3 8.3.3 锁的分类锁的分类 为了使锁

20、定的成本减至最少，SQL Server自动将资源锁定在适合任务的级别。通常将锁分为：共享锁(S)、更新锁(U)、排他锁(X)、意向锁、架构锁和大容量更新锁。8.3.4 8.3.4 锁定时间的设置锁定时间的设置 LOCK_TIMEOUT语句设置允许应用程序设置语句等待阻塞资源的最长时间。当语句等待的时间大于LOCK_TIMEOUT语句设置时，系统将自动取消阻塞的语句，并给应用程序返回“已超过了锁请求超时时段”的1222号错误信息。例如：若要查看当前LOCK_TIMEOUT的值，可以使用 LOCK_TIMEOUT全局变量。下面的SQL语句设置LOCK_TIMEOUT的值为1800毫秒，并使用 LO

21、CK_TIMEOUT来显示该值。SET LOCK_TIMEOUT 1800GODECLARE Timeout intSELECT Timeout=lock_timeoutPRINT TimeoutGO执行结果如下：1800 8.3.5 8.3.5 锁的自定义锁的自定义 1.死锁 例如，运行事务1的线程T1具有表A上的排他锁；运行事务2的线程T2具有表B上的排他锁，并且之后需要表A上的锁。事务2无法获得这一锁，因为事务1已拥有他。事务2被阻塞，等待事务1。然后，事务1需要表B的锁，但也无法获得，因为事务2将他锁定了。这就是死锁。2.2.自定义事务隔离级别自定义事务隔离级别 默认情况下，SQL S

22、erver在提交读(READ COMMITTED)的一个隔离级别上操作。但是，应用程序可能必须运行于不同的隔离级别。若要在应用程序中使用更加严格或较为宽松的隔离级别，则可以通过使用SET TRANSACTION ISOLATION LEVEL语句设置会话的隔离级别，来自定义整个会话的锁定。SET TRANSACTION ISOLATION LEVEL语句的语法格式如下：SET TRANSACTION ISOLATION LEVELREAD COMMITTED -提交读|READ UNCOMMITTED -未提交读|REPEATABLE READ 可重复读|SERIALIZABLE-可串行读 注

23、明：一次只能设置这些选项中的一个，而且设置的选项将一直对那个连接保持有效，直到显示更改该选项为止。这是默认行为，除非在语句的FROM子句中在表级上指定优化选项。例如：若要设置事务隔离级别为可串行读，以确保并发事务不能在cj表中插入幻像行，则可以执行下面SQL语句：USE cjglGOSET TRANSACTION ISOLATION LEVEL SERIALIZABLEGOBEGIN TRANSACTIONSELECT*FROM cjGO执行结果：例如：若要确定当前设置的事务隔离级别，可以使用DBCC USEROPTIONS语句。USE cjglGOSET TRANSACTION ISOLAT

24、ION LEVEL SERIALIZABLEGODBCC USEROPTIONSGO执行结果：3.3.锁定提示锁定提示 可以使用SELECT、INSERT、UPDATE和DELETE语句指定表级锁定提示的范围，以引导SQL Server使用所需的锁类型。当需要对对象所获得锁类型进行更精细控制时，可以使用表级锁定提示。这些锁定提示取代了会话的当前事务隔离级别。例如，如果将事务隔离级别设置为SERIALIZABLE，并且在SELECT语句中使用表级锁定提示NOLOCK(不要发出共享锁，并且不要提供排他锁),则可以执行下面SQL语句：USE cjglGOSET TRANSACTION ISOLATION LEVEL SERIALIZABLEGOBEGIN TRANSACTIONSELECT 课程号 FROM cj WITH(NOLOCK)GO执行结果：对上例可以使用sp_lock存储过程来查看锁定：EXEC sp_lockGO对上例可以使用object_name()函数来返回此锁定的数据库对象(表)：select object_name(581577110)go小结：小结：本章介绍了游标定义、填充游标、关闭游标、删除游标、应用游标、数据更新和删除操作等内容，还有事务的定义、操作以及具体应用。谢谢！谢谢！再见再见