第11章 游标事务和锁.docx
- 文档编号:28205532
- 上传时间:2023-07-09
- 格式:DOCX
- 页数:26
- 大小:163.40KB
第11章 游标事务和锁.docx
《第11章 游标事务和锁.docx》由会员分享,可在线阅读,更多相关《第11章 游标事务和锁.docx(26页珍藏版)》请在冰豆网上搜索。
第11章游标事务和锁
第11章游标、事务和锁
【学习目标】
本章将要学习游标的作用、基本操作步骤、应用,事务的基本概念、作用、属性、类型和编写方法,并发问题、锁的类型、封锁协议、死锁和活锁、封锁粒度。
本章学习要点:
◆游标的作用及使用;
◆事务的作用及各种事务的编写方法;
◆并发问题及封锁方法。
【学习导航】
在数据库应用程序中,常常需要游标来逐行处理数据;在一些业务中可以通过事务来帮助实现数据的一致性和完整性,通过封锁或解锁来实现数据库应用系统的并发操作问题。
游标、事务和锁在实际应用中具有很重要的实用价值。
本章主要内容见图11-1所示的学习导航。
图11-1本章内容学习导航
11.1游标
关系数据库中的操作是集合操作,由SELECT语句返回的结果是纪录的集合,被作为一个整体单元处理,无法对其中的一条或一部分纪录单独处理。
但实际应用中,尤其是交互式联机应用程序,需要对这些结果集进行逐行操作。
游标就提供了这样的一种处理机制。
使用游标可以在纪录集中逐行向前或向后访问数据,可以在任意位置显示、修改和删除当前纪录的数据。
11.1.1游标的基本操作
使用游标的一般步骤:
⑴声明游标、⑵打开游标、⑶提取游标、⑷对游标中当前位置的纪录执行操作、⑸关闭游标、⑹释放游标。
使用游标时的基本语句:
⑴声明游标
游标在使用之前需要声明,定义游标的属性,声明游标的基本语法格式为:
DECLARE游标名CURSOR
[LOCAL|GLOBAL]/*游标作用域*/
[FORWARD_ONLY|SCROLL]/*游标移动方向*/
[READ_ONLY]/*访问属性*/
FORSELECT语句/*SELECT语句*/
[FORUPDATE[OF列名称[,...n]]]/*修改的范围*/
参数含义:
●LOCAL与GLOBAL:
说明游标的作用域。
LOCAL说明所声明的游标是局部游标;GLOBAL说明所声明的游标是全局游标;
●FORWARD_ONLY|SCROLL:
FORWARD_ONLY说明该游标只能从第一行滚动到最后一行。
SCROLL说明该游标可能后滚动;
●SELECT语句:
由该查询产生与所声明的游标相关联的结果集;
●FORUPDATE:
指出游标中可以更新的列。
若有参数“OF列名称”,则只能修改给出的这些列,若在UPDATE中未指出列,则可以修改所有。
⑵打开游标
游标使用之前需要先打开,才能从游标中提取数据。
打开游标的语法格式为:
OPENcursor_name
【提示】
·OPEN命令只能打开已声明但尚未打开的游标。
·游标在打开状态下,不能再被打开。
·打开一个游标以后,可以使用全局变量@@ERROR判断打开操作是否成功,如果返回值为0,表示游标打开成功,否则表示打开失败。
·当游标被成功打开时,游标位置指向记录集的第一行之前。
·游标成功打开后,可以使用全局变量@@CURSOR_ROWS返回游标中的记录数。
⑶提取游标
游标被成功打开后,就可以使用FETCH命令从游标中检索特定的一行,提取游标中的数据的语法格式为:
FETCH
[[NEXT|PRIOR|FIRST|LAST|ABSOLUTEn|RELATIVEn|]FROM]
cursor_name
[INTO@variable_name[,…n]]
参数含义:
●NEXT:
返回紧跟当前行之后的结果行。
如果FETCHNEXT为对游标的第一次提取操作,则运行结果集中的第一行。
●PRIOR:
返回紧临当前行前面的结果行。
如果FETCHPRIOR为对游标的第一次提取操作,则没有行返回并且游标置于第一行之前。
●FIRST:
返回游标总的第一行并将其作为当前行。
●LAST:
返回游标中的最后一行并将其作为当前行。
●ABSOLUTEn:
如果n为正数,返回从游标头开始的第n行并将返回的行变成新的当前行。
如果n为负数,返回游标尾之前的第n行并将返回的行变成新的当前行。
如果n为0,则没有行返回。
●RELATIVEn:
返回当前行之前或之后的第n行并将返回的行变成新的当前行。
●Cursor_name:
要从中进行提取的游标的名称。
●INTO@variable_name[,…n]:
允许将提取操作的列数据放到局部变量中。
列表中的各个变量从左到右与游标结果集中的相应列相关联。
各变量的数据类型必须与相应的结果列的数据类型匹配。
变量的数目必须与游标选择列表中的列的数目一致。
⑷关闭游标
游标使用完毕后,应该关闭游标,释放当前结果集,以便释放游标所占用的系统资源。
关闭游标语法格式为:
CLOSEcursor_name
【提示】
·关闭游标后,不能在从游标中提取数据。
·可以在使用OPEN命令重新打开游标。
⑸删除游标
如果一个游标确定不再被使用,可以将其删除。
释放游标的语法格式为:
DEALLOCATEcursor_name
游标删除后,如果想重新使用游标就必须重新声明一个新的游标。
11.1.2游标实例
【案例11-1】在stu数据库中,定义游标cur_student,然后打开游标,提取游标中不同位置的学生信息。
代码如下:
USEstu
go
DECLAREcur_studentCURSOR
SCROLL/*游标可前后滚动*/
FORSELECT*FROMstudent
GO
OPENcur_student/*打开游标*/
FETCHFIRSTFROMcur_student/*指向第一条*/
FETCHNEXTFROMcur_student/*指向第二条*/
FETCHPRIORFROMcur_student/*指向第一条*/
FETCHABSOLUTE5FROMcur_student/*指向第五条*/
FETCHRELATIVE5FROMcur_student/*指向第十条*/
FETCHRELATIVE-5FROMcur_student/*指向第五条*/
FETCHLASTFROMcur_student/*指向最后一条*/
【提示】
如果定义游标时未使用参数SCROLL,则游标只能向前进,不能前后滚动,也就是FETCH语句中只能使用NEXT参数,FETCH中的其他所有参数(FIRST、PRIOR、ABSOLUTE等)都不能执行。
【案例11-2】在stu数据库中,使用游标逐行显示student表中的学生学号、姓名、性别。
代码如下:
USEstu
GO
/*开始定义游标*/
DECLAREstu_xsxxCURSORKEYSETFORSELECTs_num,s_name,s_sexFROMstudent
OPENstu_xsxx/*使用游标前,先把游标打开*/
DECLARE@s_xhchar(12),@s_xmvarchar(8),@s_xbchar
(2)
/*定义变量,用于存放从游标提取的数据*/
IF@@ERROR=0/*判断游标打开是否成功*/
BEGIN
IF@@CURSOR_ROWS>0/*由游标记录数判断*/
BEGIN
PRINT'共有学生'+RTRIM(CAST(@@CURSOR_ROWSASCHAR(3)))+'名,分别是:
'
PRINT''
/*把游标中的第一条记录提取出来并分别存入变量中*/
FETCHNEXTFROMstu_xsxxINTO@s_xh,@s_xm,@s_xb
/*判断是否有记录,如果有则继续循环*/
WHILE(@@FETCH_STATUS=0)/*值为0,表示上一条FETCH语句执行成功*/
BEGIN
PRINT@s_xh+','+@s_xm+','+@s_xb
/*如果游标中还有记录则继续提取出来并分别放入变量中*/
FETCHNEXTFROMstu_xsxxINTO@s_xh,@s_xm,@s_xb
END
END
END
ELSE
PRINT'游标有问题!
'/*如果游标不能正常打开,则显示该字样*/
CLOSEstu_xsxx/*使用完后关闭游标*/
DEALLOCATEstu_xsxx/*最后删除游标,释放空间*/
GO
运行代码,结果如图11-2所示。
图11-2使用游标逐行显示学生的信息
11.2事务
事务(transaction)是构成单一逻辑工作单元的操作集合,也是用户定义的一个数据库操作序列。
这组操作集合作为一个整体单元来执行,要么所有的操作都成功完成,要么就是失败。
如果执行过程中某一操作失败,则将在该事务中所做的一切复原。
例如,在银行转账,将A账户的3000元转入B账户中,银行方面将从A账户中取出3000元钱,然后将这3000元再存入B账户中,取和存的这两个操作应该作为一个事务(单元)来处理,要么都做,要么都不做,否则,就会出现客户不愿意接受的钱已取但未存的结果或银行不愿意接受的钱未取但已存的结果,导致产生银行数据库中数据的不一致性。
因此,事务是一个不可侵害的逻辑单元,在SQLServer中应用事务来保证数据库的一致性和恢复性。
11.2.1事务属性
事务通常有四个属性:
(1)原子性:
一个事务必须作为原子单元。
要么成功完成所有的操作,要么就是失败。
如果执行过程有某一操作失败,则将所做的一切复原;
(2)一致性:
当事务完成或失败后,所有数据必须处于一致性状态。
由事务引发的从一种状态到另一种状态的变化是一致的;
(3)隔离性:
一个事务不与数据库的其他事务交互或冲突。
即在并发处理过程中,一个事务所看到的数据状态必须为另一个事务处理前或后的数据,而不能为正被其他事务所修改的数据。
事务的隔离性通过锁来实现;
(4)持久性:
事务完成后,它对数据库所做的修改被永久保持.如果系统掉电或数据库服务崩溃,事务保证在服务器重启后数据库中的数据仍是完整的。
【提示】
·事务的概念是相对的,是把相关的几件事情入在一起做为一个整体处理。
·事务的大小取决于用户和业务逻辑的需要。
11.2.2事务类型
事务有以下几种类型:
(1)显示事务:
每个事务均以BEGINTRANSACTION语句显式开始,以COMMIT或ROLLBACK语句显式结束,显式定义了它的启动和结束;
(2)隐式事务:
没有使用事务的明显标志,但它们却是可能隐性地存在,这种事务叫做隐式事务。
隐式模式启动后,当前一个事务完成时新事务就隐式启动,形成连续的事务链,只需使用COMMITTRAN或ROLLBACKTRAN语句提交或回滚每个事务。
启动隐式事务模式SETIMPLICIT-TRANSACTIONSON语句,结束隐式事务模式执行SETIMPLICIT-TRANSACTIONSOFF语句;
(3)自动处理事务:
系统默认每条T-SQL命令都是一个事务。
如果一条语句成功完成,则提交该语句;如果遇到错误或失败,则自动回滚该语句之前的整个批处理语句。
11.2.3编写事务
显示事务可以使用BEGINTRANSACTION、COMMITTRANSACTION、SAVETRANSACTION、ROLLBACKTRANSACTION等语句进行控制处理。
其中:
1、BEGINTRANSACTION命令标识一个事务的开始;
2、COMMITTRANSACTION命令标识事务结束。
BEGINTRANSACTION和COMMITTRANSACTION命令之间的所有语句被视为一体,即事务;
3、SAVETRANSACTION命令生成存储点。
当遇到错误的事务,如果要让事务回滚到指定位置,则需要在事务中设定事务存储点;
4、ROLLBACKTRANSACTION命令使事务回滚。
即当事务中的某一语句执行失败时,将对数据库的操作恢复到事务执行前或某个指定位置,同时释放事务占用的资源。
编写事务命令的基本语句如下。
BEGINTRAN[SACTION][事务名|@事务变量名]
…
SAVETRAN[SACTION][保存点名|@保存点变量名]
…
ROLLBACKTRAN[SACTION][保存点名|@保存点变量名]
…
COMMIT[TRAN[SACTION][事务名|@事务变量名]]
【案例11-3】删除班级表class中班级代码为“05010101”的班级信息,但是因为学生表student中可能有该班级的学生记录,为了保证数据库中数据的一致性,要求在表class和student中的班级代码为“05010101”的信息要么都删除,要么都保留。
(1)在事务执行前,先分别查看CLASS表和STUDENT表中的信息。
SELECT*
FROMCLASS
GO
SELECT*
FROMSTUDENT
GO
得出的结果发现:
两个表中都有C_id为“05010101”的记录信息。
(2)执行以下事务
BEGINTRAN
DELETECLASS
WHEREC_ID=’05010101’
DELETESTUDENT
WHERES_CLASSID=’05010101’
COMMITTRAN
得出的结果发现:
两个表中的相关记录信息要么都执行,要么都不执行;但是如果设置了主键和外键约束,则不能成功的执行。
【案例11-4】在教务管理数据库stu中,要求将选修课程成绩表stucourse里学号为“0501010102”的学生的0001号课程成绩(88分)减去3分,加到该生选修的0002号课程成绩(72分)上(该生两门课程的总成绩不变)。
程序代码如下:
USEstu
GO
DECLARE@numint
BEGINTRANSACTION
UPDATEstucourseSETsc_grade=sc_grade-3WHEREs_num='0501010102'andcs_id='0001'
SET@num=@@ROWCOUNT/*受影响的记录数存放在变量中*/
IF@num=0/*如果受影响的记录数为0,即减分操作不成功,则回滚取消所有操作。
*/
BEGIN
ROLLBACKTRANSACTION
RETURN
END
UPDATEstucourseSETsc_grade=sc_grade+3WHEREs_num='0501010102'andcs_id='0002'
SET@num=@@ROWCOUNT
IF@num=0/*如果受影响的记录数为0,即加分操作不成功,则回滚取消所有操作。
*/
BEGIN
ROLLBACKTRANSACTION
RETURN
END
COMMiTTRANSACTION
运行以上代码后,再查询学生成绩信息,发现2条记录成绩有变化,结果如图11-3所示。
图11-3程序运行结果
从结果中我们发现0001号课程少了3分,0002号课程多了3分,总成绩160分不变,说明整个事务被提交完成。
如果我们把两次更新操作的条件中的任何一个故意写错,比如把加分的cs_id号由“0002”改为“0005”,再重新执行该事务,就会发现操作不成功,两门课程成绩都没有变化。
原因是事务中的任何一组语句发生错误,事务都将被回滚取消。
11.3并发控制与锁
11.3.1并发问题
数据库是面向多用户的多任务系统,为了能有效地利用计算机硬件资源和数据资源,DBMS允许多用户同时访问数据库系统。
当多个用户并发地访问一个数据库时就会产生多个事务同时存取同一数据的情况。
若对并发操作不加控制就可能会破坏事务的隔离性,发生存取和存储不正确的数据,引发数据库中数据的不一致性。
下面看一个例子,说明并发操作带来的数据的不一致性问题。
飞机订票系统中的一个活动序列:
①甲售票点(甲事务)读出某航班的机票余额A,设A=16;
②乙售票点(乙事务)读出同一航班的机票余额A,也为16;
③甲售票点卖出一张机票,修改余额AA-1,所以A为15,把A写回数据库;
④乙售票点也卖出两张机票,修改余额AA-2,所以A为14,也把A写回数据库;
按理来说,甲乙共卖出了三张机票,该航班机票余额应为13,但数据库中机票数只减少2余额为14。
这时数据库中数据与实际不符,这种情况称为数据库数据的不一致性。
这种不一致性是由并发操作引起的。
原因是由于第④步中乙事务修改A并写回后覆盖了甲事务的修改,甲事务的修改被丢失。
并发操作带来的数据不一致性主要包括:
丢失修改、不可重复读、读“脏”数据。
1.丢失修改
丢失修改是指事务1与事务2从数据库中读入同一数据并修改,事务2的提交结果破坏了事务1提交的结果,导致事务1的修改被丢失。
如图11-4,飞机订票系统,T2的修改覆盖了T1的修改。
图11-4丢失修改图11-5不可重复读
2.不可重复读
不可重复读是指事务1读取数据后,事务2执行更新操作,使事务1无法再现前一次读取结果。
如图11-5所示,由于事务T2对B做了修改并写回B,使得事务T1对A和B两次求和的结果不一样。
三类不可重复读:
事务1读取某一数据后:
(1)事务2对其做了修改,当事务1再次读该数据时,得到与前一次不同的值。
(2)事务2删除了其中部分记录,当事务1再次读取数据时,发现某些记录神密地消失了。
(3)事务2插入了一些记录,当事务1再次按相同条件读取数据时,发现多了一些记录。
后两种不可重复读有时也称为幻影现象。
3.读“脏”数据
事务1修改某一数据,并将其写回磁盘,事务2读取同一数据后,事务1由于某种原因被撤消。
这时事务1已修改过的数据恢复原值,事务2读到的数据就与数据库中的数据不一致,是不正确的数据,称为“脏”数据。
如图11-6所示。
图11-6读“脏”数据
图11-6中,C的原值为100,事务T1修改C的值为200,T2读到C值为200,但后来T1由于各种原因撤销对C的修改,,C恢复回原值100,这时T2读到的C值仍为200,与数据库中C的值不一致,T2读到的C值就是“脏”数据。
产生上述三类数据不一致性的主要原因是并发操作破坏了事务的隔离性。
并发控制就是要用正确的方式调度并发操作,使每一个用户的事务执行不受其他事务的干扰,从而避免造成数据的不一致性。
SQLServer并发控制的主要技术是封锁,通过锁来防止数据库并发操作过程中出现的问题。
例如,在飞机订票例子中,甲事务要修改A,若在读出A前先锁住A,其他事务就不能再读取和修改A了,直到甲修改并写回A后解除了对A的封锁为止。
这样,就不会丢失甲的修改。
11.3.2锁
锁是实现数据库并发控制的一个非常重要的技术。
如果没有锁,SQLServer就没有防止多个用户同时更新同一数据的机制。
封锁就是事务T在对某个数据对象(例如表、记录等)操作之前,先向系统发出请求,对其加锁。
加锁后事务T就对该数据对象有了一定的控制,在事务T释放它的锁之前,其它的事务不能访问或更新此数据对象。
DBMS通常提供了多种类型的封锁。
一个事务对某个数据对象加锁后究竟拥有什么样的控制是由封锁的类型决定的。
基本的封锁类型有两种:
排它锁(eXclusivelock,简记为X锁)和共享锁(Sharelock,简记为S锁)。
排它锁又称为写锁。
若事务T对数据对象A加上X锁,则只允许T读取和修改A,其它任何事务都不能再对A加任何类型的锁,直到T释放A上的锁。
共享锁又称为读锁。
若事务T对数据对象A加上S锁,则其它事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。
排它锁和共享锁的控制方式可以用相容矩阵来表示,如图11-7所示。
图11-7相容矩阵
1.封锁协议
在运用X锁和S锁对数据对象加锁时,需要约定一些规则,何时申请X锁或S锁,持锁时间、何时释放等,这些规则称为封锁协议(LockingProtocol)。
SQLServer主要采用了三级封锁协议。
并发操作的不正确调度可能会带来数据不一致性问题,而三级封锁协议则为并发操作的正确调度提供了一定的保证。
不同的封锁协议,在不同的程度上为并发操作的正确调度提供一定的保证。
(1)1级封锁协议。
事务T在修改数据R之前必须先对其加X锁,直到事务结束才释放。
1级封锁协议可防止丢失修改。
如图11-8,采用了1级封锁协议的封锁机制,事务TI没有丢失修改。
图11-81级封锁协议图11-92级封锁协议图11-103级封锁协议
(2)2级封锁协议。
1级封锁协议+事务T在读取数据R前必须先加S锁,读完后即可释放S锁。
2级封锁协议可以防止丢失修改和读“脏”数据。
如图11-9,采用了2级封锁协议的封锁机制,避免了事务T2读“脏”数据。
(3)3级封锁协议。
1级封锁协议+事务T在读取数据R之前必须先对其加S锁,直到事务结束才释放。
3级封锁协议可防止丢失修改、读脏数据和不可重复读。
如图11-10,采用了3级封锁协议的封锁机制,使得事务T1可重复读。
2.活锁和死锁
封锁技术可以有效地解决并行操作的一致性问题,但也带来一些新的问题:
活锁和死锁。
(1)活锁
由于系统调度的原因,某些事务的加锁请求得不到响应而永远等待下去,称为活锁。
图11-11活锁图11-12死锁
如图11-11,如果事务T1封锁了数据R,事务T2又请求封锁R,于是T2等待。
T3也请求封锁R,当T1释放了R上的封锁之后系统首先批准了T3的请求,T2仍然等待。
然后T4又请求封锁R,当T3释放了R上的封锁之后系统又批准了T4的请求……T2有可能永远等待,出现活锁。
避免活锁的简单方法是采用先来先服务的策略。
当多个事务请求封锁同一数据对象时,封锁子系统按请求封锁的先后次序对事务排队,数据对象上的锁一旦释放就批准申请队列中第一个事务获得锁。
(2)死锁
两个或多个事务都已封锁了一些数据对象,然后又都请求对已被其他事务封锁的数据对象加锁,从而出现死等待,这种现象称为死锁。
如图11-12所示,事务T1锁住了R1一直没有释放,又请求获得R2的锁;而事务T2锁住了R2也一直没有释放,又请求获得R1的锁,事务T1和T2都在等待获得被对方已经封锁了的数据对象的锁,一直等待着。
而解决的唯一方法是取消其中一个事务。
解决死锁的方法:
预防死锁和诊断解除死锁。
(1)预防死锁
预防死锁的发生就是要破坏产生死锁的条件,通常有两种方法:
①一次封锁法:
要求每个事务必须一次将所有要使用的数据全部加锁,否则就不能继续执行。
一次封锁法虽然可以有效地防止死锁的发生,但也存在问题。
第一,难于事先精确确定封锁对象。
数据库中数据是不断变化的,原来不要求封锁的数据,在执行过程中可能会变成封锁对象,所以很难事先精确地确定每个事务所要封锁的数据对象。
第二,降低并发度。
将以后要用到的全部数据加锁,势必扩大了封锁的范围,从而降低了系统的并发度。
②顺序封锁法:
顺序封锁法是预先对数据对象规定一个封锁顺序,所有事务都按这个顺序实行封锁。
顺序封锁法虽然可以有效地防止死锁的发生,但也同样存在问题。
第一,维护成本高。
数据库系统中可封锁的数据对象极其众多,并且随数据的插入、删除等操作而不断地变化,要维护这样极多而且变化的资源
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第11章 游标事务和锁 11 游标 事务
![提示](https://static.bdocx.com/images/bang_tan.gif)