《课件第9章事务、锁和游标.ppt》由会员分享，可在线阅读，更多相关《课件第9章事务、锁和游标.ppt（44页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、LOGO问题：当系统发生软、硬件故障对数据造成破坏时，该如何处理问题：当系统发生软、硬件故障对数据造成破坏时，该如何处理？例：银行转帐例：银行转帐设从帐号设从帐号A拨一笔款拨一笔款X到帐号到帐号B，执行过程如下：，执行过程如下： 查看帐号查看帐号A上是否有足够的款数，上是否有足够的款数， 即余额即余额 X？ 若余额若余额 0 -如果系统出现意外 ROLLBACK TRAN TransactionName -则进行回滚操作Else COMMIT TRAN TransactionName/*显式提交事务*/ PRINT TRANCOUNT SELECT * FROM TestTable/*查询表的
2、所有记录*/ -结果 -ID name - -3 王力 -4 王为 DROP TABLE TestTable/*删除表*/【例9-2】向教师表中插入一名教师的信息，如果正常运行则插入数据表中，反之则回滚。此例主要介绍SAVE TRANSACTION语句。【例9-3】删除“工业工程”系，将“工业工程”系的学生划归到“企业管理”系。【例9-4】使用SQL Server 2014的存储过程实现银行转账业务的事务处理。【例9-5】某学籍管理系统中需要将某学生的学号由2010066103改为2010066200，这一修改涉及“选课”表和“学生”表两个表。本例中的事务就是为了保证这两个表的数据一致性。【例
3、9-3】删除“工业工程”系，将“工业工程”系的学生划归到“企业管理”系。USE 教学管理GObegin transaction my_transaction_deleteuse 教学管理 /*使用数据库“教学管理”*/GOdelete from 系部 where 系别 = 工业工程save transaction after_delete /*设置事务恢复断点*/vupdate 学生学生vset系别系别 = 企业管理企业管理 where 系别系别 = 工业工程工业工程v/*“工业工程工业工程”系学生的系别编号改为系学生的系别编号改为“企企业管理业管理”系的系别编号系的系别编号*/vif err
4、or0 or rowcount=0 thenv/*检测是否成功更新，检测是否成功更新，ERROR返返回上一个回上一个SQL语句状态，非零即说明出语句状态，非零即说明出错，错则回滚之错，错则回滚之*/vbeginvrollback tran after_deletev/*回滚到保存点回滚到保存点 after_delete，如果，如果使用使用 rollback my_transaction_delete，则会回滚，则会回滚到事务开始前到事务开始前*/vcommit tranvprint 更新学生表时产生错误更新学生表时产生错误vreturnvendvcommit transaction my_tr
5、ansaction_deletevGO9.2.7 使用事务时的考虑使用事务时的考虑v 在使用事务时，用户不可以随意定义事务，在使用事务时，用户不可以随意定义事务，它有一些考虑和限制。它有一些考虑和限制。 1. 事务应该尽可能短事务应该尽可能短 2. 避免事务嵌套避免事务嵌套9.3 锁锁 在多用户数据库系统中，当多个用户并发存取数据库时就会产生多在多用户数据库系统中，当多个用户并发存取数据库时就会产生多个事务同时存取同一数据的情形。若不加控制，可能会存取和存储不正个事务同时存取同一数据的情形。若不加控制，可能会存取和存储不正确的数据，造成数据库的不一致性。确的数据，造成数据库的不一致性。 在并发
6、操作情况下，对事务的操作序列的调度是随机的，考虑飞机在并发操作情况下，对事务的操作序列的调度是随机的，考虑飞机订票系统，若按下面的序列调度：订票系统，若按下面的序列调度： 考虑飞机订票系统中的一个活动序列： 甲售票点读出某航班的机票余额A，设 A=16， 乙售票点读出同一航班的机票余额A，也为16， 甲售票点卖出一张机票，修改余额 AA1， A变为15，把A 写回数据库 乙售票点也卖出一张机票，修改余额 AA1， A也为15，把A写回数据库。卖出两张机票，而余额只减少卖出两张机票，而余额只减少1。错误！。错误！ 这种情况就造成数据库的不一致性，这种不一致性是由并发操作引起的。这种情况就造成数据
7、库的不一致性，这种不一致性是由并发操作引起的。v为了提高系统效率、满足实际应用的要求，为了提高系统效率、满足实际应用的要求，系统允许多个事务并发执行，即允许多个系统允许多个事务并发执行，即允许多个用户同时对数据库的操作。但由于并发事用户同时对数据库的操作。但由于并发事务对数据的操作不同，可能会带来以下四务对数据的操作不同，可能会带来以下四大问题：大问题：1. 丢失更新（丢失更新（lose update）2. 脏读（脏读（dirty read）3. 不可重复读（不可重复读（unrepeateable read）4. 幻读幻读 （Phantom Read）9.3.1 事务的缺陷9.3.2 锁的概念
8、锁的概念v在单用户数据库中，由于只有一个用户在单用户数据库中，由于只有一个用户修改信息，不会产生数据不一致的情况，修改信息，不会产生数据不一致的情况，因此并不需要锁。当允许多个用户同时因此并不需要锁。当允许多个用户同时访问和修改数据时，就需要使用锁来防访问和修改数据时，就需要使用锁来防止对同一个数据的并发修改，避免产生止对同一个数据的并发修改，避免产生丢失更新、脏读、不可重复读和幻像读丢失更新、脏读、不可重复读和幻像读等问题。等问题。v锁（锁（lock）的基本原则是允许一个事务）的基本原则是允许一个事务更新数据，当必须回滚所有修改时，能更新数据，当必须回滚所有修改时，能够确信在第一个事务修改完
9、数据之后，够确信在第一个事务修改完数据之后，没有其他事务在数据上进行过修改。即没有其他事务在数据上进行过修改。即锁提供了事务的隔离性。锁提供了事务的隔离性。9.3.3 隔离性的级别隔离性的级别1. 隔离的级别隔离的级别 （1）未提交读（read uncommitted） （2）已提交读（read committed） （3）可重复读（repeateable read） （4）可串行读（serializable） （5）快照（snapshot）和已提交读快照（read committed snapshot）2. 隔离级别的选择隔离级别的选择合理选择用于事务的隔离级别是非常重要的。由于获取和释放锁
10、所需的资源因隔离级别不同而不同，因此，隔离级别不仅影响数据库的并发性实现，而且还影响包含该事务的应用程序的整体性能。通常，使用的隔离级别越严格，要获取并占有的资源就越多，因而对并发性提供的支持就越少，而整体性能也会越低。3. 隔离级别的设定隔离级别的设定尽管隔离级别是为事务锁定资源服务的，但隔离级别是在应用程序级别指定的。当没有指定隔离级别时，系统默认使用“游标稳定性”隔离级别。对于嵌入式SQL应用程序，隔离级别在预编译或将应用程序绑定到数据库时指定。大多数情况下，隔离级别是用受支持的编译语言(如C或C+)编写，通过 PRECOMPILE PROGRAM、BIND命令或API的ISOLATIO
11、N选项来设置。9.3.4 锁的空间管理及粒度锁的空间管理及粒度 当一个事务锁定特定资源时，在事务终止之前，当一个事务锁定特定资源时，在事务终止之前，其他事务对该资源的访问都可能被拒绝。锁保证了事其他事务对该资源的访问都可能被拒绝。锁保证了事务运行的正确性，但也牺牲了系统的一部分并发性。务运行的正确性，但也牺牲了系统的一部分并发性。为了获取最大并发性，我们引入了锁的管理空间及粒为了获取最大并发性，我们引入了锁的管理空间及粒度的概念。度的概念。锁的粒度是指被锁定目标的大小。锁定粒度和数据锁的粒度是指被锁定目标的大小。锁定粒度和数据库并发访问度是一对矛盾，锁定粒度大，系统的开销库并发访问度是一对矛盾
12、，锁定粒度大，系统的开销小但并发度会降低；锁定粒度小，系统开销大但并发小但并发度会降低；锁定粒度小，系统开销大但并发度会提高。在度会提高。在SQL Server中，可被锁定的资源从小中，可被锁定的资源从小到大分别是行、页、扩展盘区、表和数据库。到大分别是行、页、扩展盘区、表和数据库。 9.3.5 锁的类别锁的类别9.3.6 如何在如何在SQL Server中查看数据中查看数据库中的锁库中的锁1. 使用SSMS查看锁信息打开SQL Server 2016的SSMS，在查询分析器中使用快捷键“Ctrl+2”，即可查看到进程、锁以及对象等信息。如图9-1所示。图9-1 查看锁的信息2. 使用系统存储
13、过程使用系统存储过程sp_lock查看锁的信息查看锁的信息SQL Server 2016提供系统存储过程帮助我们查看锁的信息。使用格式为：EXECUTE sp_lock执行结果如图9-2所示。图9-2 使用系统存储过程查看锁的信息9.3.7 死锁及其防止死锁问题： 死锁是有两个或以上的事务处于等待状态，每个事务都在等待另一个事务解除封锁，它才能继续执行下去，结果任何一个事务都无法执行，这种现象就是死锁。 下面两种情况下可能会出现死锁： （1）两个事务同时锁定了两个单独的对象，并且第一个事务要求在另外一个事务锁定的对象上获得一锁； （2）在一个数据库中有若干个长时间运行的事务执行并操作。处理死锁
14、最好的方法就是防止死锁的发生，即不让满足死锁条件的情况发生。为此，用户需要遵循以下原则： (1) 尽量避免并发地执行涉及修改数据的语句。(2) 要求每个事务一次就将所有要使用的数据全部加锁，否则就不予执行。(3) 预先规定一个加锁顺序。(4) 每个事务的执行时间不可太长，尽量缩短事务的逻辑处理过程，及早提交或回滚事务。(5) 一般不要修改SQL Server事务的默认级别，不推荐强行加锁。对于死锁，SQL Server 2016自动进行定期搜索，并根据各会话的死锁优先级结束一个代价最低的事务；然后将被中断的事务回滚，同时向应用程序返回1025号错误信息。 9.4 游标游标v 关系数据库中的操作
15、会对整个行集起作用。关系数据库中的操作会对整个行集起作用。v由由SELECT语句返回的行集包括满足该语句返回的行集包括满足该语句的语句的WHERE子句的所有行，这种由子句的所有行，这种由SELECT语句返回的完整行集称为结果语句返回的完整行集称为结果集。集。v应用程序，特别是交互式联机应用程序，应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来并不总能将整个结果集作为一个单元来有效地处理。这些应用程序往往采用非有效地处理。这些应用程序往往采用非数据库语言数据库语言(如如C、VB、ASP或其他开发或其他开发工具工具)内嵌内嵌Transact-SQL的形式来开发，的形式来开发，
16、而这些非数据库语言无法将表作为一个而这些非数据库语言无法将表作为一个单元来处理，因此，这些应用程序需要单元来处理，因此，这些应用程序需要一种机制以便每次处理一行或一部分行。一种机制以便每次处理一行或一部分行。v 游标游标(Cursor)就是提供这种机制的对结果集的一种扩就是提供这种机制的对结果集的一种扩展。展。9.4.1 游标（游标（Cursor）概述）概述1. 游标的概念游标的概念v游标是一种处理数据的方法，具有对结果游标是一种处理数据的方法，具有对结果集进行逐行处理的能力。可以把游标看为集进行逐行处理的能力。可以把游标看为一种一种特殊的指针特殊的指针，它与某个查询结果相联，它与某个查询结果
17、相联系，可以指向结果集的任意位置，可以将系，可以指向结果集的任意位置，可以将数据放在数组、应用程序中或其他地方，数据放在数组、应用程序中或其他地方，允许用户对指定位置的数据进行处理。允许用户对指定位置的数据进行处理。v使用游标，可以实现以下功能：使用游标，可以实现以下功能： 允许对SELECT返回的表中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作； 从表中的当前位置检索一行或多行数据； 游标允许应用程序还提供对当前位置的数据进行修改、删除的能力； 对与其他用户对结果集包含的数据所做的修改，支持不同的可见性级别。v在实现上，游标总是与一条在实现上，游标总是与一条SQL 选择语
18、句选择语句相关联。因为游标由结果集和结果集中指相关联。因为游标由结果集和结果集中指向特定记录的游标位置组成。当决定对结向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集进行处理时，必须声明一个指向该结果集的游标。果集的游标。2. 游标使用遵循以下游标使用遵循以下5个步骤个步骤: 声明游标声明游标 打开游标打开游标 读取数据并处理数据读取数据并处理数据 关闭游标关闭游标 释放游标释放游标9.4.2 声明游标声明游标是指用DECLARE语句声明或创建一个游标。声明游标主要包括以下内容：游标名称、数据来源、选取条件和属性。声明游标的DECLARE语法格式如下： DECLAR
19、E 游标名称CURSORLOCAL|GLOBAL-游标的作用域FORWORD_ONLY|SCROLL-游标的移动方向STATIC|KEYSET|DYNAMIC|FAST_FORWARD-游标的类型READ_ONLY|SCROLL_LOCKS|OPTIMISTIC -游标的访问类型TYPE_WARNING-类型转换警告信息FOR SELECT查询语句-SELECT查询语句FOR READ ONLY|UPDATEOF 列名称,.n-可修改的列【例9-6】创建游标cur1，使cur1可以对student表所有的数据行进行操作，并将游标变量var_cur1与cur1相关联。对应的Transact-SQ
20、L语句如下：DECLARE cur1 CURSORFOR SELECT * FROM studentDECLARE var_cur1 CURSORSET var_cur1=cur19.4.3 打开游标游标声明后，如果要从游标中读取数据必须要打开游标。打开游标是指打开已经声明但尚未打开的游标，并执行游标中定义的查询。语法格式如下：语法格式如下： OPEN OPEN 游标名称游标名称如果游标声明语句中使用了STATIC关键字，则打开游标时会产生一个临时表来存放结果集；如果声明游标时使用了KEYSET选项，则OPEN会产生一个临时表来存放键值。所有的临时表都存放在tempdb数据库中。【例9-7】创
21、建游标cur1，使cur1可以对student表中所有的数据行进行操作，然后打开该游标，输出游标中的行数。对应的Transact-SQL语句如下：USE stuInfogoDECLARE cur1 CURSORFOR SELECT * FROM studentgoOPEN cur1SELECT 游标cur1数据行数=CURSOR_ROWS执行结果如图9-3所示，结果为-1，说明该游标是一个动态游标，其值无法确定。图9-3 打开游标9.4.4 读取游标当游标被成功打开后，就可以使用FETCH命令从游标中逐行地读取数据，以进行相关处理。其语法规则如下：FETCHNEXT | PRIOR | FIR
22、ST | LAST | ABSOLUTEn|nvar| RELATIVE n|nvarFROM-读取数据的位置GLOBAL 游标名称 | 游标变量名称 INTO 游标变量名称 ,n-将读取的游标数据存放到指定变量中【例9-8】打开游标cur1，从游标中读取数据，并查看FETCH命令的执行状态。 执行结果如图9-4所示。可以看到，返回了student表第一条学生记录，FETCH_ STATUS的返回值为0，这说明执行成功。对应的Transact-SQL语句如下：OPEN cur1FETCH NEXT FROM cur1SELECT NEXT_FETCH执行情况=FETCH_STATUS图9-4
23、读取游标9.4.5 关闭游标游标使用完后要及时关闭。关闭游标使用CLOSE语句，但不释放游标占用的数据结构。其语法规则如下：CLOSEGLOBAL游标名称|游标变量名称【例9-9】关闭游标cur1。对应的Transact-SQL语句如下：CLOSE cur1 9.4.6 删除游标游标关闭后，其定义仍在，需要时可以再用OPEN语句打开继续使用。若确认游标不再使用，可以删除游标，释放其所占用的系统空间。删除游标用DEALLOCATE语句，其语法格式如下：DEALLOCATE GLOBAL 游标名称 | 游标变量名称【例9-10】删除游标cur1。对应的Transact-SQL语句如下：DEALLOCATE cur1 9.5 经典习题1. 什么是事务？简述事务ACID原则的含义。2. 为什么要使用锁？SQL Server 2016提供了哪几种锁的模式。3. 什么是死锁？如何预防死锁？如何解决死锁？4. 试说明使用游标的步骤和方法。LOGO