事务、锁和作业.ppt
《事务、锁和作业.ppt》由会员分享,可在线阅读,更多相关《事务、锁和作业.ppt(58页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、数据库原理及应用案例教程 北京大学出版社2023年7月1日 第1页第10 章事务、锁和作业SQL Server2005 数据库应用与开发教程 北京大学出版社教学目标教学重点教学过程数据库原理及应用案例教程 北京大学出版社2023年7月1日 第2页教学目标l 了解事务的基本概念、特征和类型及其工作原理;l 掌握事务的处理语句及其定义l 了 解 锁 的 概 念、锁 的 粒 度、分 类、隔 离 级 别、死 锁 及其处理;l 掌握如何在SQL Server 中查看数据库中的锁。l 掌握SQL Server2005 中创建作业的方法数据库原理及应用案例教程 北京大学出版社2023年7月1日 第3页教学重
2、点及难点重点:l 掌握事务的处理语句及其定义l 掌握如何在SQL Server 中查看数据库中的锁。l 掌握SQL Server2005 中创建作业的方法难点:l 了解事务的基本概念、特征和类型及其工作原理;l 了解锁的概念、锁的粒度、分类、隔离级别、死锁及其处理;数据库原理及应用案例教程 北京大学出版社2023年7月1日 第4页教学过程l 引例l 10.1 事务(Transaction)l 10.2 锁l 10.3 作业l 10.5 本章小结数据库原理及应用案例教程 北京大学出版社2023年7月1日 第5页引例科龙软件有限公司最近新洽谈了一个项目,为某银行开发一个“银行业务管理”系统。在该系
3、统的设计方案中,特别强调了该系统两个业务的注意事项:一是转帐业务的注意事项,二是个人存款统计业务的注意事项。保证数据的安全性,数据库因引入了事务和锁的机制数据库原理及应用案例教程 北京大学出版社2023年7月1日 第6页10.1 事务(Transaction)事务和存储过程类似,由一系列T-SQL 语句组成,是SQL Server 2005 系统的执行单元。10.1.1 事务的由来10.1.2 事务的概念及其工作原理10.1.3 事务的类型10.1.4 事务处理语句10.1.5 事务和批数据库原理及应用案例教程 北京大学出版社2023年7月1日 第7页10.1.1 事务的由来第一条DELETE
4、语句删除department表数据:DELETEFROMdepartmentWHEREDept_ID=1012第二条DELETE语句删除employee表数据:DELETEFROMemployeeWHEREDept_ID=1012在执行第一条DELETE语句后,数据库中的数据已处于不一致状态,因为此时已经没有“后勤部”了,但employee表中仍然保存着属于“后勤部”的员工记录。只有执行了第二条DELETE语句后,数据才重新处于一致状态。但是,如果在执行完第一条语句后,计算机突然出现故障,无法再继续执行第二条DELETE语句,则数据库中的数据将永远处于不一致状态。因此,必须保证这两条DELET
5、E语句要么同时都执行,要么都不执行。为解决此问题,数据库系统通常都引入了事务(Transaction)的概念。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第8页10.1.2 事务的概念及其工作原理事务(Transaction)是一种机制,是由T-SQL语句组成的能够完成一系列操作的逻辑单元。整个逻辑单元作为一个整体出现,要么逻辑单元中的语句全部成功执行。在数据库系统上执行并发操作时,事务是作为最小的控制单元来使用的。在SQLServer2005中,事务要求处理时必须满足ACID原则,即原子性(A)、一致性(C)、隔离性(I)和持久性(D)。(1)原子性:也称为自动性,是指事务必
6、须执行一个完整的工作,要么执行全部数据的操作。要么全部不执行。(2)一致性:是指当事务完成时,必须使所有的数据具有一致的状态。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第9页10.1.2 事务的概念及其工作原理(3)隔离性隔离性也称为独立性,是指并行事务的修改必须与其他并行事务的修改相互独立。一个事务处理的数据,要么是其他事务执行之前的状态,要么是其他事务执行之后的状态。但不能处理其他事务正在处理的数据。(4)持久性持久性是指当一个事务完成之后,将影响永久性地存于系统中,即事务的操作将写入数据库中。事务的这种机制保证了一个事务或者提交后成功执行,或者提交后失败回滚,二者必居其
7、一,因此,事务对数据的修改具有可恢复性,即当事务失败时,它对数据的修改都会恢复到该事务执行前的状态。而使用一般的批处理,则有可能出现有的语句被执行,而另外一些语句没有被执行的情况,从而有可能造成数据不一致。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第10页10.1.2 事务的概念及其工作原理事务的工作原理如图10-1所示。事务开始之后,事务所有的操作都陆续写到事务日志中。这些任务操作在事务日志中记录一个标志,用于表示执行了这种操作。当取消这种事务时,系统自动执行这种操作的反操作,保证系统的一致性。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第11页10.1.2
8、 事务的概念及其工作原理(3)选择备份设备的类型,以确定备份设备的物理名称。然后使用下列方法之一来确定备份设备的物理名称:一是输入备份设备所使用的文件名。它应该是一个完整的路径和文件名;二是单击【】按钮,显示“定位数据库文件”对话框,再选择备份设备所使用的本地计算机上的物理文件。默认情况下,SQLServer根据输入的设备逻辑名自动在系统目录BACKUP下生成同名的物理名称。(4)单击【确定】按钮,完成创建备份设备的操作。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第12页10.1.2 事务的概念及其工作原理事务执行时,系统自动生成一个检查点机制,这个检查点周期地发生。检查点机
9、制可以使用如图10-2所示的示例说明。事务1的完成发生在系统失败前的检查点A之前,所以事务1的操作被提交到数据库中。事务2和事务4虽然没有在系统失败前的检查点A之前完成,却在发生在系统失败之前完成,所以这两个事务可以被系统向前回滚提交到数据库中。而事务3和事务5由于系统失败时没有完成,所以这两个事务的所有操作均被取消。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第13页10.1.3 事务的类型1 根据系统的设置分类(1)系统事务系统提供的事务是指在执行某些语句时,一条语句就是一个事务。系统提供的事务语句如下:ALTERTABLE、CREATE、DELETE、DROP、FETCH
10、、GRANTINSERT、OPEN、REVOKE、SELECT、UPDATE、TRUNCATETABLE【例10.1】使用CREATETABLE创建一个表。CREATETABLEtestTable(IDint,DnameVARCHAR(20)数据库原理及应用案例教程 北京大学出版社2023年7月1日 第14页10.1.3 事务的类型(2)用户定义事务在实际应用中,大量使用的是用户定义的事务。事务的定义方法是:用BEGINTRANSACTION语句指定一个事务的开始,用COMMIT或ROLLBACK语句表明一个事务的结束。还有一种特殊的用户定义的事务,这就是分布式事务。在分布式事务中,所有的操作
11、都可以涉及对多个服务器的操作,当这些操作都成功时,那么所有这些操作都提交到相应服务器的数据库中,如果这些操作中有一条操作失败,那么这个分布式事务中的全部操作都将被取消。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第15页10.1.3 事务的类型2 根据运行模式分类(1)自动提交事务:是指每条单独的语句都是一个事务。(2)显式事务:指每个事务均以BEGINTRANSACTION语句显式开始,以COMMIT或ROLLBACK语句显式结束。(3)隐式事务:指在前一个事务完成时新事务隐式启动,但每个事务仍以COMMIT或ROLLBACK语句显式完成。(4)批处理级事务:该事务只能应用于
12、多个活动结果集(MARS),在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。当批处理完成时,没有提交或回滚的批处理级事务自动由SQLServer进行回滚。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第16页10.1.4 事务处理语句1 BEGIN TRANSACTION BEGINTRANSACTION声明一个事务的起始点,代表着一组逻辑单元的开始。如果事务中的语句发生任何错误,整个事务将回滚至BEGINTRANSACTION语句声明的起始点。其简单语法格式如下:BEGINTRANSACTION事务名|事务变量名WITHMARK描述字符串SQLServer服务器
13、中有一个全局变量TRANCOUNT对事务进行跟踪,BEGINTRANSACTION语句将该变量值增1数据库原理及应用案例教程 北京大学出版社2023年7月1日 第17页10.1.4 事务处理语句2 COMMIT TRANSACTIONCOMMITTRANSACTION结束一个隐性事务或用户定义的事务,并将TRANCOUNT减1。COMMITTRANSACTION将从事务起始点所发生的对数据库数据修改操作成为永远操作,然后释放事务连接所占用的资源。其简单语法格式如下:COMMITTRANSACTION事务名事务变量名与BEGINTRANSACTION语句相反,COMMITTRANSACTION语
14、句的执行使全局变量TRANCOUNT的值减1。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第18页10.1.4 事务处理语句3 ROLLBACK TRANSACTION回滚到显式事务或隐性事务到起点或者事务内的某个保存点。其简单语法格式如下:ROLLBACKTRANSACTION事务名|事务变量名|保存点名称|保存点变量如果事务回滚到开始点,则全局变量TRANCOUNT的值减1,而如果只回滚到指定存储点,则TRANCOUNT的值不变。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第19页10.1.4 事务处理语句4 SAVE TRANSACTION事务回滚时对系统
15、资源开销较大,如果能够预见事务执行时极少有错误发生,可以使用保存点机制。它提供了回滚部分事务的能力,从而在执行回滚语句时可以不必回滚到事务的开始点。其简单语法规则如下:SAVETRANSACTION保存点名称|保存点变量5 BEGIN DISTRIBUTED TRANSACTION指定一个由Microsoft分布式事务处理协调器(MSDTC)管理的T-SQL分布式事务的起始。其简单语法规则如下:BEGINDISTRIBUTEDTRANSACTION事务名|事务变量名数据库原理及应用案例教程 北京大学出版社2023年7月1日 第20页10.1.4 事务处理语句【例10.2】定义一个事务,将所有选
16、修了C3号课程的学生的分数加10分,并提交该事务。DECLARECnameVARCHAR(20)SETCname=Add_ScoreBEGINTRANSACTIONCnameUSEStudentCourseUPDATESCSETGrade=Grade+10WHERECno=C3COMMITTRANSACTIONCnameGO数据库原理及应用案例教程 北京大学出版社2023年7月1日 第21页10.1.4 事务处理语句【例10.3】定义一个事务,向学生表中添加一条记录,并设置保存点。然后删除该记录,并回滚到事务的保存点,提交该事务。USEStudentCourseBEGINTRANSACTION
17、INSERTINTOStudentsVALUES(0703002,成小伊,女,1984-3-21,计算机,null)SAVETRANMy_saveDELETEFROMStudentsWHERESno=0703002ROLLBACKTRANMy_saveCOMMITTRAN数据库原理及应用案例教程 北京大学出版社2023年7月1日 第22页10.1.5 事务和批将整个操作组织成一个简单的事务处理,称为批处理或批。【例10.4】将多个操作定义为一个事务。BEGINTRANSACTIONUSEStudentCourseUPDATESCSETGrade=Grade-10WHERECno=C3INSER
18、TINTOStudents(Sno,Sname)VALUES(0703003,成小路)DELETEFROMCoursesWHERECnameLIKE数%COMMITTRANSACTION数据库原理及应用案例教程 北京大学出版社2023年7月1日 第23页10.2 锁锁是实现数据库并发控制的主要手段。锁可以防止用户读取正在由其他用户更改的数据,并可以防止多个用户同时更改相同数据。10.2.1 锁概述10.2.2 锁的空间管理及粒度10.2.3 锁的模式10.2.4 在SQL Server 2005 中查看数据库中的锁10.2.5 死锁及其防止10.2.6 使用SQL Server Profile
19、r 确定死锁的原因数据库原理及应用案例教程 北京大学出版社2023年7月1日 第24页10.2.1 锁概述锁可以防止丢失更新、脏读、不可重复读和幻像读。(1)丢失更新(Lostupdate),指当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,因此最后的更新将重写由其他事务所做的更新,这将导致数据丢失。(2)脏读(dirtyread):指一个事务正在访问数据,而其他事务正在更新该数据,但尚未提交,此时就会发生脏读问题,即第一个事务所读取的数据是“脏”(不正确)数据,它可能会引起错误。(3)当一个事务访问数据时,另外的事务也访问该数据并对其进行修改,
20、因此就发生了由于第二个事务对数据的修改而导致第一个事务两次读到的数据不一样的情况,这就是不可重复读。(4)事务第一次读的某一行己不存在于第二次读或后续读中,因为该行已被其他事务删除。同样,由于其他事务的插入操作,事务的第二次或后续读的某一行不存在于原始读中,这就是幻像读。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第25页10.2.2 锁的空间管理及粒度锁是防止其他事务访问指定的资源控制、实现并发控制的一种主要手段.Server2005系统的空间管理结构示意图如图10-3所示。锁的粒度(Granularity)行级锁、页级锁、簇级锁、表级锁和数据库级锁,如图10-4所示。图10
21、-4 表、簇、页、行的结构图数据库原理及应用案例教程 北京大学出版社2023年7月1日 第26页10.2.3 锁的模式根据锁定资源的方式的不同,SQLServer2005提供了4种锁模式:共享锁、排他锁、更新锁、意向锁。(1)共享锁:共享锁也称为S锁,允许并行事务读取同一种资源,这时的事务不能修改访问的数据。(2)排他锁:排他锁也称为X锁,就是在同一时间内只允许一个事务访问一种资源,其他事务都不能在有排他锁的资源上访问。(3)更新锁:更新锁也称为U锁,可以防止常见的死锁。一次只有一个事务可以获得资源的更新锁。如果事务修改资源,则更新锁转换为排他锁。(4)意向锁:数据库引擎使用意向锁来保护共享锁
22、或排他锁放置在锁层次结构的底层资源上。之所以命名为意向锁,是因为在较低级别锁前可获取它们,因此,会通知意向将锁放置在较低级别上。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第27页10.2.3 锁的模式意向锁有两种用途:防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。提高数据库引擎在较高的粒度级别检测锁冲突的效率。意向锁又分为意向共享锁(IS)、意向排他锁()以及意向排他共享锁(SIX).从程序员的角度来区分,锁通常分为乐观锁(OptimisticLock)和悲观锁(PessimisticLock)两种模式。数据库原理及应用案例教程 北京大学出版社2023年7月1日
23、第28页10.2.4 在SQL Server 2005 中查看数据库中的锁1 用SQL Server Management Studio 查看锁(1)右击“对象资源管理器”视图中的【管理】|【活动监视器】,在弹出的快捷菜单中可以选择【按进程查看锁(L)】命令,也可以选择【按对象查看锁(O)】命令来查看锁的信息,如图10-5所示。(2)若选择了“按进程查看锁”命令,即可查看当前锁定的对象,如图10-6所示该子节点列出了各个进程的锁信息,包括锁住的对象、锁类型、锁模式、锁状态、所有者等信息。数据库原理及应用案例教程 北京大学出版社2023年7月1日 第29页10.2.4 在SQL Server 2
24、005 中查看数据库中的锁图10-5 在“对象资源管理器”中查看锁图10-6 按对象查看锁数据库原理及应用案例教程 北京大学出版社2023年7月1日 第30页10.2.5 死锁及其防止2 用系统存储过程sp_lock 查看锁用系统存储过程sp_lock也可以列出当前的锁,其语法格式如下:sp_lockspidspid是INT类型的数据,如果不指定spid,则显示所有的锁。【例10.5】显示当前系统中所有的锁。USEStudentCourseEXECsp_lock【例10.6】显示编号为53的锁的信息。USEStudentCourseEXECsp_lock53数据库原理及应用案例教程 北京大学出
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 事务 作业
限制150内