第3章
事务处理，并发控制及数据库优化
|
|
|
|
l SQL Server 2005的事务处理
l SQL Server 2005的并发控制
l SQL Server 2005数据库优化
3-1 SQL Server 2005的事务处理
u 事务概述及基本特性
u 事务的类型及事务处理的基本语句
u 事务处理的基本实验
所谓事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。SQL Server 2005 提供了几种自动的可以通过编程来完成的机制，包括事务日志、SQL事务控制语句，以及事务处理运行过程中通过锁定保证数据完整性的机制。当用户对数据库并发访问时，为了确保事务完整性和数据库一致性，需要使用锁定。事务和锁是两个紧密联系的概念。通过事务的批处理和锁的使用，还可以监测系统，以及优化物理数据库。
本章主要介绍 SQL Server 2005数据库系统的事务和锁的基本概念，事务、批处理、锁的创建和使用，通过事务、批处理、锁监测系统和优化物理数据库的操作。
3-1-1 事务概述
关系型数据库有4个显著的特征：安全性、完整性、检测性和并发性。数据库的安全性就是要保证数据库数据的安全，防止未授权用户随意修改数据库中的数据，确保数据的安全。完整性是数据库的一个重要特征，也是保证数据库中的数据切实有效、防止错误、实现商业规则的一种重要机制。在数据库中，区别所保存的数据是无用的垃圾还是有价值的信息，主要是依据数据库的完整性是否健全，即实体完整性、域完整性和参考完整性。对任何发现影响系统性能的因素和瓶颈，采取切合实际的策略，解决问题，提高系统的性能。并发性是用来解决多个用户对同一数据进行操作时的问题。特别是对于网络数据库来说，这个特点更加突出。提高数据库的处理速度，单单依靠提高计算机的物理速度是不够的，还必须充分考虑数据库的并发性问题，提高数据库并发性的效率。
那么如何保证并发性呢？在 SQL Server 2005中，通过使用事务和锁机制，可以解决数据库的并发性问题。在 SQL Server 2005中，事务要求处理时必须满足ACID原则，即原子性（Ａ）、一致性（Ｃ）、隔离性（Ｉ）和持久性（Ｄ）。
1． 原子性：原子性也称为自动性，是指事务必须执行一个完整的工作，要么执行全部数据的操作，要么全部不执行。
2． 一致性：一致性是指当事务完成时，必须所有的数据具有一致的状态。
3． 隔离性：也称为独立性，是指并行事务的修改必须与其他并行事务的修改相互独立。一个事务处理数据，要么是其他事务执行之前的状态，要么是其他事务执行之后的状态，但不能处理其他正在处理的数据。
4． 持久性：是指当一个事务完成之后，将影响永久性地存于系统中，即事务的操作将写入数据库中。
事务的这种机制保证了一个事务或者提交后成功执行，或者提交后失败回滚，二者必居其一。因此，事务对数据的修改具有可恢复性，即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。而使用一般的批处理，则有可能出现有的语句被执行，而另一些语句没有被执行的情况，从而有可能造成数据不一致。
事务开始之后，事务所有的操作都陆续写到事务日志中。这些任务操作在事务日志中记录一个标志，用于表示执行了这种操作。当取消这种事务时，系统自动执行这种操作的反操作，保证系统的一致性。系统自动生成一个检查点机制，这个检查点周期地发生。检查点的周期是系统根据用户定义的时间间隔和系统活动的频度由系统自动计算出来的时间间隔。检查点周期地检查事物日志，如果在事务日志中，事务全部完成，那么检查点将事务提交到数据库中，并且在事务日志中做一个检查点提交标记。如果在事务日志中，事务没有完成，那么检查点将事务日志中的事务不提交到数据库中，并且在事务日志中做一个检查点未提交标记。
3-1-2 事务的类型
根据事务的设置、用途的不同，SQL Server 2005将事务分为多种类型。
1．根据系统的设置分类
根据系统的设置，SQL Server 2005将事务分为两种类型：系统提供的事务和用户定义的事务，分别简称为系统事务和用户定义事务。
(1) 系统事务
系统提供的事务是指在执行某些语句时，一条语句就是一个事务。但是要明确，一条语句的对象既可能是表中的一行数据，也可能是表中的多行数据，甚至是表中的全部数据。因此，只有一条语句构成的事务也可能包含了多行数据的处理。
系统提供的事务语句如下：ALTER TABLE 、CREATE、DELETE、DROP、FETCH、GRANT、INSERT、OPEN、REBOKE、SELECT、UPDATE、TRUNCATE TABLE。当这些SQL命令运行后，这些语句本身就构成了一个事务。
比如，当我们创建一张有三个属性列的物理表的时候，创建表的SQL语句本身就构成了一个事务。这条语句由于没有使用条件限制，那么在创建新表的过程中，要么创建全部成功，要么全部失败。
(2) 用户定义事务
在实际应用中，大多数的事务处理采用了用户定义的事务来处理。在开发应用程序时，可以使用BEGIN TRANSACTION语句来定义明确的用户定义的事务。在使用用户定义的事务时，一定要注意事务必须有明确的结束语句来结束。如果不使用明确的结束语句来结束，那么系统可能把从事务开始到用户关闭连接之间的全部操作都作为一个事务来对待。事务的明确结束可以使用两个语句中的一个：COMMIT语句和ROLLBACK语句。COMMIT语句是正常提交语句，将全部完成的语句明确地提交到数据库中。ROLLBACK语句是意外回滚语句，该语句将事务的操作全部取消，即表示事务操作失败。
2．根据运行模式分类
根据运行模式，SQL Server 2005将事务分为４种类型：自动提交事务、显示事务、隐式事务和批处理级事务。
(1) 自动提交事务
自动提交事务是指每条单独的语句都是一个事务。
(2) 显式事务
显式事务指每个事务均以BEGIN TRANSACTION语句显式开始，以COMMIT或ROLLBACK语句显示结束。
(3) 隐式事务
隐式事务指在前一个事务完成时新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显式完成。
(4) 批处理级事务
该事务只能应用于多个活动结果集（MARS），在MARS会话中启动的T-SQL显式或隐式事务变为批处理级事务。当批处理完成时，没有提交或回滚的批处理级事务自动由SQL Server语句集合分组后形成单个的逻辑工作单元。
3．事务处理语句
事务处理语句共包括四种：
(1) BEGIN TRANSAVTION语句：正常的开始一个事务；
(2) COMMIT TRANSACTION语句：正常的结束一个事务；
(3) ROLLBACK TRANSACTION语句：非正常回滚事务，撤销全部的操作；
(4) SAVE TRANSACTION语句：保存事务
实验：建立事务实验
--例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 tran
Delete from student where sno=544
If @@error>0 --如果系统出现意外
rollback tran --则进行回滚操作
Else
Commit tran
--注意：rollback tran 将完全取消事务，或者将事务恢复到初始状态
--例3：先删除张飞同学信息，而后回滚撤销删除操作
begin transaction --开始一个正常的事务
delete from student where sname='张飞' --删除张飞同学
select * from student where sname='张飞' --查询张飞同学是否还在
rollback transaction --回滚撤销删除操作
select * from student where sname='张飞' --再次查询张飞同学是否还在
--小问题：请分析为什么被删除了的数据又回来了呢
--例4：为教师表插入一名教师的信息，如果正常运行则插入数据表中，反之则回滚。此题注意学习SAVE TRANSACTION语句
Begin tran
update teacher set salary=salary+50 --给每名教师的薪水加50元
Save tran savepoint1
Insert into teacher values (‘840’,’李强’,1975-03-02,’计算机系’)
If @@error>0
rollback tran savepoint1
If @@error>0
rollback tran
Else
commit tran
--注意：save tran 命令后面有一个名字，这就是存储点的名字，这样在第一次恢复时，就可以恢复到这个存储点，就是savepoint1，而不是恢复整个的事务。Insert into teacher会被取消，但是事务本身仍然将继续。也就是插入的教师信息将从事务中除去，数据表撤销该教师信息的插入，但是给每名教师的薪水加50元的操作正常的被保存到数据库之中；到了后一个复原，由于没有给出恢复到的名字，rollback tran 将恢复到begin tran 前的状态，即修改和插入操作都被撤销，就像没有发生任何事情一样。
--例5：定义一个事务，向学生表中添加记录。如果添加成功，则给每个分数加10分。否则不操作。
BEGIN TRAN
Insert into student(sno,sname,sex,class) values(545,’刘备’,’男’,’95033’)
IF @@ error=0
BEGIN
PRINT ‘添加成功！’
UPDATE score SET degree=degree+10 WHERE sno=’101’
COMMIT TRAN --正常结束事务
END
ELSE
BEGIN
PRINT‘添加失败！’
ROLLBACK TRAN
END
4．如何编写有效的事务
事务的编写是T-SQL编程过程中非常重要的操作，因此数据库专家根据事务编程的特点，总结并归纳出以下几个要点，以期达到编写有效事务的目的：
（1） 不要在事务处理期间要求用户输入数据。
（2） 在事务启动之前，你必须要获得所有需要的用户输入。
（3） 在浏览数据的时候，尽量不要打开事务。
（4） 在所有的数据检索分析完毕之前，不应该启动事务。
（5） 事务的代码编写尽可能简短。
（6） 在知道了必须要进行的修改之后，启动事务，执行修改语句，然后立即提交或者回滚。
（7） 在事务中尽量使访问的数据量最小化。
（8） 尽量减少锁定数据表的行数，从而减少事务之间的竞争。
