SQL Server 数据库技术∶第9章事务和锁的使用与管理.ppt

1、第9章 事务和锁的使用与管理,第10周 第一讲,本章学习目标,掌握事务具有的4个属性； 掌握事务模式的分类； 掌握Transact-SQL语言的常用事务语句； 掌握实现事务管理的方法； 掌握基本锁的分类及特点； 了解专用锁的分类。,9.1 事务和锁概述,事务与锁可以确保数据能够正确地被修改,而不会造成数据只修改到一部分而导致数据不完整,或是在修改途中受到其它用户的干扰。 这两项功能都非常重要，必须对它们有完整的了解并合理利用，以确保数据库能保存正确而完整的数据。,9.1.1 事务概述,在SQL Server 2000中的事务是一个单个的逻辑工作单元，在一个事务中可以包括一条或多条Transac

2、t-SQL语句； 事务的功能是可用来设置多个连续的数据操作必须全部执行成功，否则即回滚到未执行任何操作的状态. 如果一个事务执行成功，则表明该事务中的所有语句均执行成功，只要事务中的一条语句出现错误，则会取消整个事务的执行。,可保证让我们对数据库的多项修改只有2种结果,就是全部修改完成或是一项也不修改。,事务的4个属性：,（1）原子性Atomicity ：一个事务是SQL Server 2000工作的原子单位，事务中的所有语句要么全部执行，要么全部取消。 （2）一致性Consistency：事务如果能够执行成功，则事务所修改的数据必须遵循数据库中各种约束、规则的要求，所有数据应该处于一致性状态

3、。 （3）隔离性Isolation：一个事务所做的修改必须与其他事务所做的修改隔离开。 （4）永久性Durability：当事务执行成功后，事务对数据库所做的修改会永久保持。,不可分割的特性。,ACID特性,批：一次一起执行的一组语句，批处理是客户端作为一个单元发出的一个或多个 SQL 语句的集合。每个批处理编译为一个执行计划。 事务：用户定义的一组操作系列。 区别是： 当批中的语句无语法错误的时候，整个批执行，有语法错误的时候，批中没有错误的语句执行，,而事务则不同，事务要么都执行，要么都不执行。如果一个事务在执行的时候没有被全部执行，那么事务会产生回滚，即一条语句都不执行！,BATCH V

4、S TRANSACTION,批,事务,活动状态,部分 提交状态,失败状态,提交状态,中止状态,初始状态,事务无法继续正常执行,最后一条语句被执行后,事务回滚，数据库恢复到事务开始前状态,成功完成，永久写入数据库。,事务生命周期图,对于以上事务的4个属性，在SQL Server 2000中是通过以下处理方式来实现的： 事务的原子性和一致性通过SQL Server 2000内部的事务管理功能来实现； 事务的隔离性通过锁来实现； 事务的永久性通过事务日志来实现。?/,SQL Server 2000实现4属性的方法：,9.1.2 锁概述,为了解决事务的并发问题可以使用锁。 锁是由软件使用的对象，用它来

5、表明用户与资源之间的某种相关性。 在某个用户对数据进行修改时，对所修改的数据进行加锁，可以防止其他用户此时读取或修改这些数据，引起数据的更新冲突。,9.2 事务管理,9.2.1 事务模式 在SQL Server 2000中，事务模式分为三种： 显式事务 隐式事务 自动事务,（1）显式事务。又称为用户定义事务，是只由用户执行Transact-SQL事务语句定义的事务。（有明确的标识）,可用来激活或结束事务的语句,（2）隐式事务。隐式事务可由SQL Server 2000自动开始，不需要用户启动事务，用户只需要提交或回滚事务即可。,直到遇到COMMIT或ROLLBACK时结束。,（3）自动事务。自

6、动事务模式是SQL Server 2000默认的事务管理模式。在自动事务模式下，当一个语句成功执行后，它被自动提交；当它在执行过程中产生错误时，则被自动回滚。,例：UPDATE student set 入学成绩=入学成绩-10,此语句会更改到许多条记录,若其中有一条记录无法更改(例入学成绩减10后小于0，违反了自定义的字段限制），则会自动将已更改的记录全部ROLLBACK，而回滚到未执行UPDATE前的状态。,9.2.2 事务管理,Transact-SQL的常用事务语句包括以下几种。 （1）BEGIN TRANSACTION语句。启动一个显式事务，用此语句来标识一个事务的开始。 （2）COMM

7、IT TRANSACTION语句。标识一个显式事务或隐式事务的结束。用此语句说明事务被成功执行，事务所修改的数据被永久保存到数据库中。,（3）ROLLBACK TRANSACTION语句。标识一个显式事务或隐式事务的结束。用此语句说明事务没有执行成功，事务所修改的数据被回滚到事务执行前的状态。,事务不能包含以下语句：,CREATE DATABASE语句。 ALTER DATABASE语句。 DROP DATABASE语句。 LOAD DATABASE语句。 RESTORE DATABASE语句。,这些无法回滚的语句不允许使用在事务中。,LOAD TRANSACTION语句。 BACKUP LO

8、G(备份日志)语句。 DUMP TRANSACTION语句。 RESTORE LOG(恢复日志)语句。,UPDATE STATISTICS(将统计设为自动更新)语句。 SP_DBOPTION(显示或更改数据库选项)。语句。 使用系统存储过程修改master数据库的语句。,1显式事务管理,定义一个显式事务时： BEGIN TRANSACTION语句应与COMMIT TRANSACTION语句或ROLLBACK TRANSACTION语句成对出现。,例9-1 创建一个显式事务“事务_取消数据插入”，在此事务中首先在“读者基本信息表”中插入两条记录，查询此时的记录情况，然后取消此事务，再查询此时的记

9、录情况。,use books go -查询此时表中的记录 select * from 读者基本信息表 -进入显示事务模式 begin transaction 事务_取消数据插入 insert into 读者基本信息表(读者编号,姓名)values(jsj07,王一飞) insert into 读者基本信息表(读者编号,姓名)values(jsj09,李丽萍) -查询此时表中的记录 select * from 读者基本信息表 -回滚此事务 rollback transaction 事务_取消数据插入 -查询此时表中的记录 select * from 读者基本信息表,全执行完或一条也不执行,运行：

10、,为了分析程序的运行结果，在查询分析器中分3段执行以上程序： （1）运行程序中的第一部分： -打开数据库 USE BOOKS GO -查询此时表中的内容 SELECT * FROM 读者基本信息表,（2）运行程序的第二部分。,-进入显式事务模式 BEGIN TRANSACTION 事务_取消数据插入 INSERT INTO 读者基本信息表(读者编号,姓名) VALUES(JSJ07,王一飞) INSERT INTO 读者基本信息表(读者编号,姓名) VALUES(JSJ08,李丽萍) -查询此时表中的记录 SELECT * FROM 读者基本信息表,（3）运行程序的第三部分。 -回滚此事务 R

11、OLLBACK TRANSACTION 事务_取消数据插入 -查询此时表中的记录 SELECT * FROM 读者基本信息表,第一部分,第二部分,查询读者基本信息表，表中有6条记录；,回滚事务，再查询表时表中记录又回到事务执行前的数目，只有6条记录。,第三部分,成功插入两条记录后查询读者基本信息表，表中有8条记录；,2隐式事务管理,在SQL Server 2000中进入隐式事务模式的方法是执行以下语句： SET IMPLICIT_TRANSACTIONS ON,在隐式事务模式下，当执行以下任何一个语句时，SQL Server 2000都会自动重新启动一个新的事务： 所有CREATE语句。 AL

12、TER TABLE语句。 所有DROP语句。,TRUNCATE TABLE（删除所有行） 语句。 GRANT（ 赋予一个用户，一个组或所有用户访问权限）语句。 REVOKE（解除用户访问权限）语句。 INSERT语句。 UPDATE语句。 DELETE语句。 SELECT语句。,例9-2 隐式事务举例：创建一个新的数据表“EXAMPLE1”，查询此时的数据表情况，然后取消此事务，再查询此时的数据表情况。,use books go -进入隐式事务模式 set IMPLICIT_TRANSACTIONS on -创建一个新的数据库表example1 create table example1 (c

13、ol1 int,col2 char(8) -查询此时的数据表情况 select * from example1 -取消事务后再查询此时的数据表情况 rollback select * from example1,为了分析程序的运行结果，在查询分析器中分2步运行以上程序： （1）运行程序中的第一部分： use books go -进入隐式事务模式 set IMPLICIT_TRANSACTIONS on -创建一个新的数据库表example1 create table example1 (col1 int,col2 char(8) -查询此时的数据表情况 select * from exampl

14、e1,（2）运行程序的第二部分。 -取消事务后再查询此时的数据表情况 rollback select * from example1,第一部分,第二部分,CREATE TABLE创建一新数据表,会使系统自动开始一个新的事务。无数据所以查询为0行。,回滚事务，会取消对表的创建工作，再查询表时原表名无效。,退出隐式事务模式的方法,在SQL Server 2000中退出隐式事务模式的方法是执行以下语句： SET IMPLICIT_TRANSACTIONS OFF 退出有什么?,3自动事务管理,自动事务模式是SQL Server 2000的默认事务管理模式； 在与SQL Server 2000建立连接

15、后，如果用户没有开始一个显式事务或没有进入隐式事务模式，则系统会直接进入自动事务模式。,自动事务举例：,例9-3 创建一个新的数据表EXAMPLE2，并设置主键。在此数据表中插入一条新的记录，查询此时的数据表情况，再插入一条主键值完全相同的记录，然后再查询此时的数据表情况。,-取消隐式事务模式 set implicit_transactions off -此时进入自动事务模式 -打开books数据库 use books go -创建一个新的数据表example2 create table example2(col1 int primary key, col2 char(20) ) go -在数

16、据表中插入一条新的记录 insert into example2 values(1,第一条记录) go -查询此时的数据表情况 select * from example2 -在数据表中插入第二条记录，但主键值一样 insert into example2 values(1,第二条记录) go -查询此时的数据表情况 select * from example2,在自动事务模式下,系统行动判断事务的开始和结束，并能行动提交或回滚事务，不成功的事务会回滚并取消对数据的修改。,9.3 锁的管理,锁定是指将指定的数据临时锁起来供我们使用，以防止该数据被别人修改或读取。 SQL Server会自动且适

17、时地处理锁定数据的工作，例如：在进行事务的过程中，所有被修改的数据会自动锁定，以确保万一失败而必须回滚时，不会受到其它用户的干扰。,9.3.1 资源锁定模式,SQL Server 2000是一个大型的网络数据库管理系统，可以同时允许多个用户使用同一个数据库中的数据。 为了防止数据访问发生冲突，SQL Server 2000使用资源锁定的方法管理用户的并发访问操作。,SQL Server 2000有各种粒度的锁，它可以根据事务所执行的任务来灵活选择所锁定的资源粒度。SQL Server 2000能够锁定的资源粒度包括以下几种。 1）RID：行标识符，以记录（行）为单位作锁定。 2）键值（KEY）

18、：已设置为索引的字段。,资源粒度（锁的对象）,3）页面（PAGE）：一个数据页面或索引页面（8KB大小的页面）。 4）区域（EXTENT）：一组连续的8个数据页面或索引页面。 5）表（TABLE）：整个表，包括其所有的数据和索引。 6）数据库（DB）：一个数据库。,说明：,如果锁定较低层次的资源,那么可以提高资源的并发性,例如锁定某一条记录时,该数据表仍可供多人使用,只要他们不是同时要修改同一条记录; 但是当有很多的记录被锁定时,则会使系统花较多的时间来处理锁的检查及控制; 反之,锁定较高层次的资源,例如数据表,可以节省系统在处理锁时的负担,但并发性就降低了,因为整个表每次只能供一个事务使用。

19、,9.3.2 锁的分类,在SQL Server 2000中，有两种主要类型的锁，即基本锁和专用锁。,1基本锁,基本锁分为共享锁和排他锁两种。具体使用如下。 （1）共享锁： 一般情况下，读操作使用共享锁。使用共享锁时，允许多个并发事务读取所锁定的资源，但禁止其他事务对锁定的资源进行修改。 默认情况下，当数据被读取之后，SQL Server 2000立即释放共享锁。,（2）排他锁： 用于数据修改操作，如执行INSERT语句、UPDATE语句或者DELETE语句。 使用排他锁可以防止并发事务对资源的访问，包括读取数据或者修改数据。,2专用锁,根据具体情况，SQL Server 2000可以使用其他类

20、型的锁。主要包括以下几种。 （1）更新锁：在修改操作的初始化阶段用于锁定可能被修改的资源，以防止使用共享锁可能会产生的死锁现象。 （2）架构锁：当执行数据定义语言（DDL）操作或进行编译查询时，SQL Server 2000使用架构锁。,（3）大容量更新锁：在系统将大批数据并发地复制到同一个数据表中时，需要使用大容量更新锁。 （4）意向锁：如果SQL Server 2000需要在某些资源的低层资源上获得共享锁或排他锁的意向时，可以使用意向锁对此部分资源进行锁定。,9.3.3 锁的管理,在事务执行过程中，SQL Server 2000能够自动为事务选择合适的资源锁定模式和锁定资源的粒度，并能够动

21、态调整锁定资源的粒度。 可以使用系统存储过程sp_lock查看系统对资源的锁定情况。,spid：SQL Server 2000进程标识号。 dbid：锁定资源的数据库标识号。 objid：锁定资源的数据库对象标识号。,sp_lock返回的结果集中各列的含义如下：,indid：锁定资源的索引标识号。 type：锁定资源的类型代码。 resource：对锁定资源的描述信息。 mode：对资源的锁定模式。 status：锁状态。,例9-4 对“读者基本信息表”进行插入记录和查询记录的操作，查看在程序执行过程中系统对锁的使用情况。,-进入显示事务模式 -开始一个新的事务 begin transaction -在“读者基本信息表”中插入一条记录 insert into 读者基本信息表(读者编号,姓名)values(jsj07,王一飞) -查看此时系统对锁的使用情况 execute