3.《第三章.事务处理,并发控制及数据库优化》

1、SQL Server 2005安装配置管理与开发设计主讲教师：钱 哨交通部管理干部学院计算机系 钱哨教案 2009第二章 . T-SQL 程序设计基础1本章概述3.1 SQL Server 2005的事务处理3.2 SQL Server 2005的并发控制3.3 SQL Server 2005数据库优化23-1 SQL Server 2005的事务处理 事务概述及基本特性 事务的类型及事务处理的基本语句 事务处理的基本实验3一、事务的基本概念事务的概念：所谓事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。例如，在关系数据库中，一个事务可以是一条SQL语

2、句、一组SQL语句或整个程序。定义事务的三条语句： BEGIN TRANSACTIONCOMMITROLLBACK4一、事务的基本概念例如:银行转帐SQL中事务的定义事务以Begin transaction开始，以Commit work或 Rollback work结束Commit work表示提交，事务正常结束Rollback work表示事务非正常结束，撤消事务已做的操作，回滚到事务开始时状态5一、事务的基本概念示例银行转帐：事务T从A帐户过户50￥到B帐户T：read(A);A := A 50;write(A);read(B);B := B + 50;write(B);read(X)：从

3、数据库传送数据项X到事务的工作区中write(X)：从事务的工作区中将数据项X写回数据库6二、事务的ACID特性事务的ACID特性：原子性(Atomicity)一致性(Consistency) 隔离性(Isolation)持续性(Durability)事务是并发控制和恢复的基本单位。保证事务ACID特性是事务处理的重要任务。7二、事务的ACID特性事务特性(ACID)原子性(Atomicity)事务中包含的所有操作要么全做，要么全不做原子性由恢复机制实现一致性(Consistency)事务的隔离执行必须保证数据库的一致性事务开始前，数据库处于一致性的状态；事务结束后，数据库必须仍处于一致性状态

4、数据库的一致性状态，由并发控制机制实现如银行转帐，转帐前后两个帐户金额之和应保持不变8二、事务的ACID特性隔离性(Isolation)系统必须保证事务不受其它并发执行事务的影响对任何一对事务T1，T2，在T1看来，T2要么在T1开始之前已经结束，要么在T1完成之后再开始执行隔离性通过并发控制机制实现持久性(Durability)一个事务一旦提交之后，它对数据库的影响必须是永久的系统发生故障不能改变事务的持久性持久性通过恢复机制实现9三、显式事务显式事务就是手工配置的事务。用保留字显示事务的开始和结束： BEGIN TRANSACTION COMMIT TRANSACTION ROLLBACK

5、 TRANSACTION SAVE TRANSACTION10显式事务示例-例1：修改陆君同学所在班级为95033班，然后再插入一名叫张飞的95031班男学生Beging tran -此处开始一个事务Update student Set class=95033 where name=陆君Insert into student(sno,sname,sex,class) values(544,张飞,男,95033)Commit tran -此处正常结束一个事务-例2：或许你需要处理意外的事务，为此你必须使用rollback tran命令，即回滚事务Begin tranDelete from stu

6、dent where sno=544If error0 -如果系统出现意外 rollback tran -则进行回滚操作ElseCommit tran-注意：rollback tran 将完全取消事务，或者将事务恢复到初始状态11-例3：先删除张飞同学信息，而后回滚撤销删除操作begin transaction -开始一个正常的事务delete from student where sname=张飞 -删除张飞同学select * from student where sname=张飞 -查询张飞同学是否还在rollback transaction -回滚撤销删除操作select * from

7、 student where sname=张飞 -再次查询张飞同学是否还在-小问题：请分析为什么被删除了的数据又回来了呢显式事务示例12显式事务示例 (注意：save tran 命令后面有一个名字，这就是存储点的名字，这样在第一次恢复时，就可以恢复到这个存储点，就是savepoint1，而不是恢复整个的事务。Insert into table2会被取消，但是事务本身仍然将继续。也就是insert从事务中除去；到了后一个复原，由于没有给出恢复到的名字，rollback tran 将恢复到begin tran 前的状态)-例4：为教师表插入一名教师的信息，如果正常运行则插入数据表中，反之则回滚。此

8、题注意学习SAVE TRANSACTION语句Begin tranupdate teacher set salary=salary+50 -给每名教师的薪水加50元Save tran savepoint1Insert into teacher values (840,李强,1975-03-02,计算机系)If error0 rollback tran savepoint1If error0 rollback tranElse commit tran13显式事务示例-例5：定义一个事务，向学生表中添加记录。如果添加成功，则给每个分数加10分。否则不操作。BEGIN TRAN Insert int

9、o student(sno,sname,sex,class) values(545,刘备,男,95033)IF error=0 BEGINPRINT 添加成功！UPDATE score SET degree=degree+10 WHERE sno=101COMMIT TRAN -正常结束事务END ELSEBEGIN PRINT添加失败！ ROLLBACK TRANEND14四、编写有效的事务你在编写事务的时候，请你记住以下的经验。1、不要在事务处理期间要求用户输入数据。2、在事务启动之前，你必须要获得所有需要的用户输入。3、在浏览数据的时候，尽量不要打开事务。4、在所有的数据检索分析完毕之前

10、，不应该启动事务。5、事务的代码编写尽可能简短。6、在知道了必须要进行的修改之后，启动事务，执行修改语句，然后立即提交或者回滚。7、在事务中尽量使访问的数据量最小化。8、尽量减少锁定数据表的行数，从而减少事务之间的竞争。151、脏读 是指在事务提交之前，它对数据进行的修改就已经被其他事务看见了。如果一个事务读取另一个事务尚未提交的更新，就叫做脏读。五、事务的缺陷T1Update.Commit隔离T2:Select16五、事务的缺陷T1T2 读取中央数据库服务器中的数据读 A=16 读取中央数据库服务器中的数据读 A=16 AA-1写回中央数据库服务器A=15 AA-1写回中央数据库服务器A=1

11、5错误(一)丢失修改17五、事务的缺陷T1T2读A=50 读B=100 SUM(A,B) 结 果200 读B=100 B B*2回写数据库服务器 B=200 读A=50 读B=200 SUM(A,B) 结 果250错误(二)不可重复读不可重复读类似于脏读，只不过它发生在事务能够看见其他事务已经提交更新的情况下18五、事务的缺陷T1T2 读C=100 CC*2 写回中央数据库服务器C C=200 读取C=200ROLLBACK TRAN 则 此时C=100现在读取的数据C是脏数据错误(三)读取脏数据193-2 SQL Server 2005的并发控制 了解锁技术概述 掌握锁的模式，掌握：共享锁，

12、排他锁，更新锁和意向锁的概念和区别。 学习如何查看锁的信息。20一、封锁定义 就是事务T在对某个数据对象操作之前就向系统发出请求，对其进行加锁。二、封锁的基本类型1、排它锁又被称为是写锁。如果事务T对数据对象A加上X锁，则其他任何事务都不可以对A增加任何类型的锁，直到事务T释放A上面的锁为止。增加对于数据的X封锁数据库A申请被拒绝申请增加X锁或者S锁我是一个事务3-2 SQL Server 2005的并发控制3-2-1锁概述212、共享锁又被称做是“读锁”。如果事务T对数据对象A加上S锁，则其他任何事务都不可以对A增加X锁，但是仍然可以增加S锁，直到事务T释放A上面的S锁为止。增加对于数据的X

13、封锁数据库A申请被拒绝申请增加X锁我是一个事务但是，如果申请增加S封锁没有问题！223、封锁关于S锁和X锁的相容矩阵 T2 T1XS无XNNYSNYY无YYY234、封锁协议一、一级封锁协议事务T在修改数据之前必须增加X封锁，直到事务结束才释放。（事务正常结束的标志是COMMIT 或者是ROLLBACK）二、二级封锁协议一级封锁协议的基础上加上事务T在读取数据R之前必须先对其增加S封锁，读完既释放。三、三级封锁协议一级封锁协议的基础上加上事务T在读取数据R之前必须先对其增加S封锁，直到事务结束才释放。245、 死锁与活锁封锁技术可以有效地解决并行操作的一致性问题，但也带来一些新的问题死锁活锁T

14、1 T2 Xlock R1.Xlock R2等待等待等待.Xlock R2.Xlock R1等待等待.255、 死锁与活锁创建死锁的实验：1、在查询分析器中创建2个窗口，分别键入下面的代码。2、分析原因。263-2 SQL Server 2005的并发控制实验：查看锁信息实验第一步：启动SQL Server Profiler，在对象资源管理器中选择菜单“工具”项下的SQL Server Profiler，启动后的窗口如图所示 273-2 SQL Server 2005的并发控制实验：查看锁信息实验第二步：选择系统菜单“文件”中的“新建跟踪”选项，新建一个跟踪事件，连接到服务器 283-2 SQ

15、L Server 2005的并发控制实验：查看锁信息实验第三步：连接成功，设置根据事件属性。在“跟踪属性”对话框的“常规”选项卡中，用户可以设置跟踪名称、使用模板，以及启用跟踪停止时间和将跟踪存储到指定文件 293-2 SQL Server 2005的并发控制实验：查看锁信息实验第四步：还可以将跟踪保存到指定表。在“跟踪属性”对话框的“事件选择”选项卡中，用户可以设置跟踪的事件以及事件的列 303-2 SQL Server 2005的并发控制实验：查看锁信息实验第五步：设置完毕，在SQL Server Profiler中显示跟踪事件，如图所示。同时，在SSMS的对象资源管理器中，系统将新建一个

16、新表，即设置的保存到表。打开该表，显示用户设置跟踪的事件以及事件的列。为了和SQL Server兼容，还可以使用sys.dm_tran_locks动态管理视图来替代sp_lock系统存储过程 313-3 SQL Server 2005数据库优化 了解数据库引擎优化顾问基本内容 掌握数据库引擎优化顾问的使用 掌握通过命令行的方式进行索引的优化DTA323-3 SQL Server 2005数据库优化 实验1：数据库索引优化的基本步骤第一步：启动SQL Server Profiler，准备生成负载测试文件 333-3 SQL Server 2005数据库优化 实验1：数据库索引优化的基本步骤第二步

17、：启动“新建跟踪”项，准备配置跟踪文件内容 343-3 SQL Server 2005数据库优化 实验1：数据库索引优化的基本步骤第三步：登录服务器后，配置跟踪属性，点击保存到文件，将跟踪的TSQL脚本结果保存到用户选择的trc文件中，同时启动文件滚动更新，从而滚动记录数据库工作过程中的负荷反馈信息。点击运行，启动负荷跟踪 353-3 SQL Server 2005数据库优化 实验1：数据库索引优化的基本步骤第四步：启动后将执行相关的TSQL脚本，并将执行的结果记录到用户指定的trc文件中。由于是滚动执行的，因此该trc文件随着时间的推移将逐渐变大。因此，这种跟踪一般是由DBA根据一天平均时候

18、的用户访问量时段进行记录比较容易反映出数据库服务器在建构过程中的索引问题 363-3 SQL Server 2005数据库优化 实验1：数据库索引优化的基本步骤第五步：工作负荷工作执行完毕后，将形成一个TRC文件，至此负载跟踪工作任务完成。随后启动数据库引擎优化顾问，如图所示。数据库引擎优化顾问主要用于优化数据库以及查看优化的建议和报告的单独图形化用户界面 373-3 SQL Server 2005数据库优化 实验1：数据库索引优化的基本步骤第六步：在弹出的引擎优化顾问界面中，选择工作负荷为文件，在弹出的选择“工作负荷文件”的对话框中，选择刚才生成的工作负荷文件。鼠标左键单击工具栏中的绿色三角标按钮开始执行优化操作。 383-3 SQL Server 2005数据库优化 实验1：数据库索引优化的基本步骤注意：此时在优化过程中，经常会出现“正在占用工作负荷”的错误。主要原因是默认的存储空间必须大于2MB才可以，故我们选择优化进度中的高级选项，将建议最大空间改称为100MB，如图所示 393-3 SQL Serve