《数据库应用-数据库原理与SQL Server》课件第9章

第9章数据完整性管理

9.1批处理9.2事务9.3锁9.4@@ERROR小结习题

9.1批处理

批处理是一组SQL语句的集合，一个批处理以批处理结束符GO而终结。批处理中的所有语句被一次提交给SQLServer2005， SQLServer2005将这些语句编译为一个执行单元，称SQLServer2005执行计划。

实例9-1

在数据库student中建立一个名为s_view的视图。

在查询编辑器中输入SQL语句并执行，如图9-1所示。图9-1批处理

9.2事务

9.2.1事务的属性

事务必须满足四个要求，才能算是一项有效的事务。这些要求被称为ACID属性，即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。SQLServer2005提供了一整套机制，有助于确保事务满足这四个属性。

1．原子性

如果事务成功，SQLServer2005确保在事务中，所有的数据修改作为一个整体。如果事务没有成功，就不会有任何修改发生。也就是说，SQLServer2005能确保事务的原子性。事务要想取得成功，事务中的每一个操作(语句)都必须成功。如果其中的任何一个操作失败，则整个事务就会失败，而且自事务开始所做的任何修改都会被撤销。

2．一致性

SQLServer2005还能保证事务的一致性。一致性意味着全部数据都保持在一致的状态。在一个事务开始之前，数据库处于一致的状态，当事务结束后，不管它是成功还是失败的，数据库还应该处于一致的状态，这意味着数据的完整性得以维持。

3．隔离性

如果有两个或者多个事务并发执行时，系统应保证执行的结果与这些事务先后单独执行的结果一样。也就是说，多事务并发执行时，应保证执行的结果是正确的，如同单用户环境一样。隔离性可以通过锁来实现。

4．永久性

事务一旦完成，它对数据库所进行的修改，将被永久保存，即使以后系统发生故障，也应该保留这个事务执行的痕迹。

另外，SQLServer2005每次启动时，都会根据事务日志，修复可能存在的错误，回滚所有未完成的事务。

SQLServer2005的事务分为显式事务、隐式事务、自动事务和分布式事务四种。9.2.2显式事务

显式事务是用户使用“事务处理语句”定义的事务。事务处理语句主要包括：

(1) BEGINTRANSACTION。该语句的作用是启动一个事务，它标志着一个事务的开始。

(2) COMMITTRANSACTION和COMMITWORK。该语句的作用是提交事务。在事务中对数据库所做的修改，将在此时进行提交，它标志着事务的结束。

(3) ROLLBACKTRANSACTION和ROLLBACKWORK。该语句的作用是回滚事务。通常如果在事务的执行过程中发生了错误，需要执行这个语句，放弃事务中对数据库所做的修改，使数据库恢复到事务开始之前的状态。

实例9-2

提交事务。

在查询编辑器中输入SQL语句并执行，如图9-2所示。图9-2提交事务实例分析：在事务中执行了两个插入操作，然后提交事务。执行后可以看到，两行被成功插入。

实例9-3

回滚事务。

在查询编辑器中输入SQL语句并执行，如图9-3所示。图9-3回滚事务实例9.2.3隐式事务

用户可以执行SETIMPLICIT_TRANSACTIONSON语句，使SQLServer2005进入隐式事务处理模式。这时用户不必使用BEGINTRANSACTION来启动事务处理，只需要使用COMMITTRANSACTION或COMMITWORK语句提交事务，或者使用ROLLBACKTRANSACTION或ROLLBACKWORK回滚事务。在一个事务结束后，不管用户是提交了该事务还是回滚了该事务，SQLServer2005自动启动下一个事务。如果要退出隐式事务处理模式，可以使用SETIMPLICIT_TRANSACTIONSOFF语句。

实例9-4

隐式事务实例。

在查询编辑器中输入SQL语句并执行，如图9-4所示。图9-4隐式事务实例9.2.4自动事务

在自动事务模式下，任何一个语句执行如果成功，则它对数据库所做的修改马上被自动提交，反之如果失败，则自动回滚。自动事务是SQLServer2005默认的事务处理模式，当与SQLSever建立连接后，直接进入自动事务模式，直到使用BEGINTRANSACTION语句开始一个显式事务，或者打开了IMPLICIT_TRANSACTIONS选项进入隐式事务模式为止。可以看出，如果需要将多个数据库操作语句组合成一个事务，就不能使用自动事务处理模式，而必须使用显式事务或者隐式事务。9.2.5分布式事务

SQLServer2005可以通过网络，实现跨服务器的数据操作，这种事务称为“分布式事务”。分布式事务具有非常强大的功能，但必须通过网络来传送数据，因此出错的几率也就大大增加了。为了解决这个问题，分布式事务的处理被分成两个阶段：准备阶段和提交阶段，也就是所谓的两阶段提交。

(1)准备阶段：分布式事务管理器接收提交请求，并向所有参加该事务的SQLServer2005服务器发出准备命令。每个服务器接到命令后，立即开始做接收该事务的准备工作。准备工作完成后，通知事务管理器。

(2)提交阶段：事务管理器接收到所有服务器准备就绪的信号后，向所有的服务器发出提交命令，然后各服务器进行事务提交。如果在这个过程中，有任何一个服务器的提交出错，则事务管理器将命令所有的服务器进行事务回滚操作。

用SQL语句处理分布式事务非常简单，和显式事务处理语句基本相似，这就是SQLServer2005功能强大之处，大量繁琐的工作都交给系统去完成，用户只需关心要实现的目标就可以了。对用户来说，只需要告诉SQLServer2005“做什么”，而不需要详细地告诉SQLServer2005“怎么做”。通常分布式事务处理过程可以分为以下几步：

(1)使用BEGINDISTRIBUTEDTRANSACTION语句启动一个分布式事务。此时该服务器成为本事务的事务管理器。

(2)应用程序执行分布式查询或执行远程服务器上的存储过程。

(3)事务管理器调用MSDTC，通知远程服务器开始参与该分布式事务。

(4)应用程序执行提交事务或回滚事务的语句来结束事务。此时事务管理器将调用MSDTC来管理两阶段提交过程，本服务器和远程服务器提交或回滚事务。9.2.6使用事务时的注意事项

(1)事务应尽可能短。因为SQLServer2005为了确保事务遵守ACID规则，要占用大量的资源，因此长时间运行事务的代价是昂贵的。尽管这在用户数较少的系统中不会成为大问题，但是在支持很多用户的系统中，如果事务太长，系统性能将会严重下降。

(2)定义有效的锁策略。锁可以防止用户读取已经被修改但还没有提交的数据，详见9.3节。

(3)避免用户在事务中输入数据。人的响应时间当然比计算机慢，由于事务的资源开销非常昂贵，因此应在事务开始以前读取所有的用户输入数据。

(4)在浏览数据时避免打开事务。这有助于减少锁定问题。实际上，事务的最大作用在于修改数据，而不是检索数据。

(5)减少事务中访问的数据量。事务处理中往往会锁定数据，因此减少事务中访问的数据量，能够减少其他用户可能遇到的锁问题，提高数据库的并发性。

9.3锁

9.3.1锁的类型

SQLServer2005中的锁包括以下几种类型。

(1)排它锁。

(2)共享锁。

(3)更新锁。

(4)结构锁。

(5)意向锁。9.3.2锁的粒度

根据不同的情况，SQLServer2005中的锁可以灵活地运用在不同的资源层次(也就是粒度)上。锁的粒度越大，被锁定的数据越多，数据的并发性就越低。锁的粒度可以分为以下几种。

(1) RID行标识符：锁定表中的单行数据。

(2)键值：锁定索引中的单行数据。

(3)页面：锁定一个数据页面或者索引页面，页面的大小为8 KB。

(4)区域：锁定一组连续的数据页面或者索引页面。

(5)表：锁定整个表。

(6)数据库：锁定整个数据库。9.3.3死锁

一般而言，读操作获得共享锁，写操作获得排它锁。在更新操作的开始阶段读取数据时，获得的是更新锁，更新锁和共享锁兼容。之后如果数据要被修改，更新锁自动升级为排它锁。用户可以锁定不同层次的数据，如整个表、表中的一行、页和多个页等。当使用锁来锁定资源时，应当考虑锁的兼容性以及可能出现的死锁问题。设置事务优先级语句的基本语法格式为：

SETDEADLOCK_PRIORITY{low|normal}

另外，用户还可以设置事务请求锁定的最长等待时间。如果一个事务的锁请求超过了最长等待时间，则该事务被自动回滚而结束。

设置事务请求锁定的最长等待时间语句的基本语法格式为：

SETLOCK_timeout<时间长度>

其中，“时间长度”以毫秒为单位。例如，将当前事务的锁请求的最大等待时间设置为5秒，则使用下面的语句：

SETLOCK_timeout50009.3.4检索锁信息

检索系统中锁的有关信息可以通过执行系统存储过程sp_lock实现。其基本语法格式为：

sp_lock[[@spid1=]'<进程ID1>'][,[@spid2=]'<进程ID2>']

其中，“进程ID1”和“进程ID2”是来自master.dbo.sysprocesses的SQLServer2005进程ID号，数据类型为int，默认值为NULL。如果没有指定进程ID号，则显示所有锁的信息。

实例9-5

显示当前SQLServer2005中所有的锁的信息。

在查询编辑器中输入SQL语句并执行，如图9-5所示。图9-5显示系统中的锁信息9.3.5使用锁时的注意事项

与事务类似，设计锁策略时也需要小心。对于少数几个用户来说，所有事务只使用一种类型的锁(细粒度)可能还行，但是当应用程序规模扩大时，就会发现服务器的性能下降。因此，当设计锁时应注意以下几点：

(1)遵守事务指导原则。

(2)对应用程序进行强度测试。强度测试是指大量用户执行相同操作，执行操作的用户数量应为应用程序可能有的最多用户数。

(3)允许用户中止长时间运行的查询。

(4)在查询期间禁止用户输入以减少查询的运行时间。

(5)当一个查询在运行时，它将在资源上保持一个某种类型的锁。

(6)虽然必要时能够改变查询对象的锁，但实际应用中应该尽可能让SQLServer2005来管理锁。

9.4@@ERROR

9.4.1错误的产生

以数据库student为例说明在对数据进行操作的过程中有可能会出现的错误。例如，表sc中有学号(sno)、课程号(cno)和成绩(score)三列，其中学号列的数据必须是在表s中存在的学号，同样课程号列的数据也必须是表c中存在的课程号。假如在选课表中插入一行数据，它的学号是不存在的，看一下会出现什么情况。

实例9-6

插入一行非法的选课数据。

在查询编辑器中输入SQL语句并执行。如果表s中不存在学号为“1010”的学生，则插入操作将失败。如图9-6所示。图9-6插入错误实例9.4.2错误的捕获

在SQLServer2005中，用户通过检查@@ERROR的值来确定是否已经发生错误。@@ERROR的值是一个整数，如果为0则一切正常。如果这个值不为0，则表示已经发生了一个错误。这时，通常需要对出现的错误进行处理。

使用@@ERROR检查并处理错误可以使用如下形式：

IF@@ERROR<>0

BEGIN

--错误处理部分

END9.4.3错误的处理

前面说明了错误是怎么发生的，以及怎样发现错误。当然一旦发生错误，应该采取有力的措施来防止错误造成严重后果。一般情况下，在错误发生后应该采取如下一些处理

方法：

(1)放弃任务。当任务产生一个能够捕获到的错误时，首先应该撤销到目前为止所做的修改，这能够确保数据一致性。这是最常用的处理方法。

(2)立即退出或尝试继续执行。当捕获一个错误后，可以立即退出命令的执行或者尝试继续执行。如果错误只是一个低级警告或者不影响性能，则可以继续执行。否则，就应该在发生错误时退出。

(3)向用户发送消息解释错误原因。尽管数据库管理人员和开发人员能够看懂SQLServer2005内部返回的错误消息，但是对一般用户来说这一消息通常没有太大用处。因此，应该向用户返回解释后的错误信息。9.4.4错误处理实例