《ch06-事务、锁、索引与数据完整性》由会员分享，可在线阅读，更多相关《ch06-事务、锁、索引与数据完整性（67页珍藏版）》请在金锄头文库上搜索。
1、网络数据库开发技术 事务、锁、索引与数据完整性,张蕾 ,事务,3,事务,事务的概念 事务是并发控制的基本单位。所谓事务，就是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。 如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分。如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除。,4,事务的特性,原子性：对数据的修改，要么都完成，要么都取消。 一致性：事务完成时，保持数据的一致性、完整性。 隔离性：并行事务之间相互隔离。 持久性：事务完成后，对数据所做的所有修改就保 存到数据库中。,5,SQL Server以下列事务模式运行。
2、（1）自动提交事务 每条单独的语句都是一个事务。 （2）显式事务 每个事务均以BEGIN TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显式结束。 （3）隐式事务 在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。 （4）批处理级事务 只能应用于多个活动结果集（MARS），在MARS会话中启动的Transact-SQL显式或隐式事务变为批处理级事务。,事务,6,事务的使用,事务控制语句 BEGIN TRAN | TRANSACTION tran_name：标识一个用户定义的事务的开始。tran_name为事务的名字 COMMI
3、T TRAN tran_name：表示提交事务中的一切操作，结束一个用户定义的事务。使得对数据库的改变生效。 ROLLBACK TRAN tran_name|save_name：回退一个事务到事务的开头或一个保存点。表示要撤消该事务已做的操作，回滚到事务开始前或保存点前的状态。 SAVE TRAN save_name：在事务中设置一个保存点。它可以使一个事务内的部分操作回退。,7,rowcount上一语句影响的行数； error检测或使用error时上一条语句执行时的错误代码。error=0表示执行成功； trancount当前连接的活动事务数,事务中使用的全局变量,8,事务控制语句的使用方法
4、： begin transaction / * A组语句序列* / save transaction 保存点1 / * B组语句序列* / if error 0 rollback transaction 保存点1 / *回滚到保存点1 * / else commit transaction / *提交A组语句，同时如果未回滚B组语句则提交B组语句* /,事务控制语句,9,例使用事务向表book中插入数据。 USE jwgl BEGIN TRAN tran_exam INSERT INTO book ( book_id , book_name , publish_company) VALUES
5、( dep04_s006_01 , VFP程序设计 , 南京大学出版社 ) SAVE TRAN int_point INSERT INTO book ( book_id , book_name , publish_company ) VALUES ( dep04_s006_02 , VFP实验指导书 , 东南大学出版社 ),事务使用示例,INSERT INTO book (book_id , book_name) VALUES (dep04_s006_03 , VFP课程设计指导书) IF error0 ROLLBACK TRAN int_point else COMMIT TRAN tran
6、_exam GO,10,事务中不可使用的语句（不能够撤消）,创建数据库：create database； 修改数据库：alter database； 删除数据库：drop database； 备份数据库：dump database、backup database； 还原数据库：load database、restore database； 日志备份：dump transaction、backup log； 日志还原：load transaction、restore log； 配置：reconfigure； 磁盘初始化：disk init； 统计：update statistics； 显示或设置
7、数据库选项：sp_dboption。,锁,12,锁,锁的概念 锁定是Microsoft SQL Server Database Engine用来同步多个用户同时对同一个数据块的访问的一种机制。 锁的类型 Microsoft SQL Server Database Engine使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。 （1）共享锁(S锁) 共享允许并发事务读取 (SELECT) 一个资源。资源上存在共享锁时，任何其它事务都不能修改数据。一旦已经读取数据便立即释放资源上的共享锁。 （2）排它锁(X锁) 排它锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X
8、) 锁锁定的数据。 （3）更新锁（U锁） 它可以防止常见的死锁。更新锁用来预定要对资源施加锁，它允许其他事务读，但不允许再施加锁或锁。,13,（4）意向锁 数据库引擎使用意向锁来保护共享锁（S锁）或排他锁（X锁），放置在锁层次结构的底层资源上。 （5）架构锁 执行表的数据定义语言（DDL）操作（例如添加列或删除列）时使用架构修改锁（Sch-M锁）。在架构修改锁（Sch-M锁）起作用的期间，会防止对表的并发访问。 （6）大容量更新锁 当将数据大容量复制到表，且指定了TABLOCK提示或者使用sp_tableoption设置了table lock on bulk表选项时，将使用大容量更新锁（BU锁
9、）。 （7）键范围锁 在使用可序列化事务隔离级别时，对于Transact-SQL语句读取的记录集，键范围锁可以隐式保护该记录集中包含的行范围。,锁,14,死锁,在两个或多个任务中，如果每个任务锁定了其他任务试图锁定的资源，此时会造成这些任务永久阻塞，从而出现死锁。 除非某个外部进程断开死锁，否则死锁中的两个事务都将无限期等待下去。 尽管死锁不能完全避免，但遵守以下特定的编码惯例可以将发生死锁的机会降至最低。 （1）按同一顺序访问对象 （2）避免事务中的用户交互 （3）保持事务简短并处于一个批处理中 （4）使用较低的隔离级别 （5）使用基于行版本控制的隔离级别 （6）使用绑定连接,15,锁的使用
10、,Microsoft SQL Server Database Engine具有多粒度锁定，允许一个事务锁定不同类型的资源。为了尽量减少锁定的开销，数据库引擎自动将资源锁定在适合任务的级别。锁定在较小的粒度（例如行）可以提高并发度，但开销较高，因为如果锁定了许多行，则需要持有更多的锁。 锁定在较大的粒度（例如表）可以降低并发度，因为锁定整个表限制了其他事务对表中任意部分的访问，但其开销较低，因为需要维护的锁较少。,16,示例：如何加锁,1 如何锁一个表的某一行 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM table
11、 ROWLOCK WHERE id = 1 2 锁定数据库的一个表 SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除 SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除,索 引,18,索引,SQL Server的索引是一种物理结构，根据表中一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表。 对表中的列是否创建索引，以及创建何种索引，对于查询的响应速度会有很大差别。创建了索引的列几乎是立即响应，而不创建索引的列则需要较长时间的等待。 在数据库系统中建立索引主要有以下作用： 快速
12、存取数据； 保证数据记录的唯一性； 实现表与表之间的参照完整性； 在使用ORDER BY、GROUP BY子句进行数据检索时，利用索引可以减少排序和分组的时间。,19,索引的类型,按照索引的组织方式的不同，可以将索引分为聚集索引和非聚集索引。 （1）聚集索引 在聚集索引中，行的物理存储顺序与索引顺序完全相同，即索引的顺序决定了表中行的存储顺序，因为行是经过排序的，所以每个表中只能有一个聚集索引。 （2）非聚集索引 非聚集索引并不在物理上排列数据，即索引中的逻辑顺序并不等同于表中行的物理顺序，索引仅仅记录指向表中行的位置的指针，这些指针本身是有序的，通过这些指针可以在表中快速地定位数据。,20,
13、创 建 索 引,在考虑是否在列上创建索引时，应考虑该列在查询中所起的作用。以下情况适合创建索引： 用作查询条件的列 频繁按范围搜索的列 连接中频繁使用的列,提示：在缺省情况下，所创建的索引是非聚集索引；在每一个表上面，可以创建不多于249个非聚集索引。 注意：由于非聚集索引使用索引页存储，因此它比聚集索引需要更多的存储空间，且检索效率较低。,21,设计索引,设计索引时，应考虑以下数据库准则: （1）一个表如果建有大量索引，会影响INSERT、UPDATE和DELETE语句的性能。 （2）避免对经常更新的表进行过多的索引，并且索引应保持较窄。 （3）使用多个索引可以提高更新少而数据量大的查询的性
14、能。 （4）对小表进行索引可能不会产生优化效果。 （5）视图包含聚集函数、连接或聚集函数和连接的组合时，视图的索引可以显著地提升性能。,提示：在SQL Server 2000数据库中，为表定义一个主键，将会自动在主键所在列上创建一个唯一索引，称之为主键索引。主键索引是唯一索引的特殊类型。,22,索引的创建,在XSCJ数据库中，经常要对XS、KC、XS_KC三个表查询和更新，为了提高查询和更新速度，可以考虑对三个表建立如下索引： 对于XS表，按学号建立主键索引，索引组织方式为聚集索引； 对于XS表，按姓名建立索引（UNIQUE约束），索引组织方式为非聚集索引； 对于KC表，按课程号建立主键索引或
15、者唯一索引，索引组织方式为聚集索引； 对于XS_KC表，按学号+课程号建立唯一索引，索引组织方式为聚集索引。 在SQL Server 2000中，可利用企业管理器创建上述索引，也可以利用SQL命令，通过查询分析器建立索引。,23,索引的创建,第2步 选择选择XS表的图标，右击，出现如图所示的快捷菜单，选择“设计表”菜单项，出现如图所示XS的表设计器界面；,下面以XS表中按学号建立索引为例，介绍索引的创建方法。 通过企业管理器建立索引 第1步 进入企业管理器，打开XSCJ数据库；,24,索引的创建,第3步 在XS表设计器界面上右击， 出现如图所示的快捷菜单，快捷菜单中选择“索引/键”菜单项，出现
16、如图所示XS的属性界面；,25,索引的创建,第4步 在“索引键”属性页选择“新建”命令，然后，确定新索引的名称（用系统缺省的名或重新取名）；在“列名”下选择要创建索引的列，在此选择“学号”这一列，最多可选择16 列。,26,索引的创建,2利用T-SQL命令建立索引 语法格式： CREATE UNIQUE /*是否为唯一索引*/ CLUSTERED | NONCLUSTERED /*索引的组织方式*/ INDEX index_name /*索引名称*/ ON table | view ( column ASC | DESC ,.n ) /*索引定义的依据*/ WITH ,.n /*索引选项*/ ON filegroup /*指定索引
