数据库原理与SQL Server 第9章 保证数据完整性

第9章 保证数据完整性 9.1 构造Transact-SQL执行单元 —批处理 9.2 保证数据完整性—事务 9.3 维护数据一致性—锁 第9章 保证数据完整性 9.1 构造Transact-SQL执行单元 —批处理 9.2 保证数据完整性—事务 9.3 维护数据一致性—锁 9.4 处理错误— @@ERROR 实训 错误的捕获和处理

9.1 构造Transact-SQL执行单元 —批处理 批处理是一组SQL语句的集合，一个批处理以批结束符GO而终结。批处理中的所有语句被一次提交给SQL Server 2000，SQL Server 2000将这些语句编译为一个执行单元，如果出现编译错误，SQL Server 2000将取消整个批处理内所有语句的执行。 例9-1 在数据库student中建立一个名为s_view的视图。 脚本：

9.2 保证数据完整性—事务 问题：如果在修改了数据库中的数据之后又立即意识到不该做这些修改，应该怎样处理的呢? 9.2 保证数据完整性—事务 问题：如果在修改了数据库中的数据之后又立即意识到不该做这些修改，应该怎样处理的呢? 最简单的办法，就是重新输入这些数据。但是，如果已经修改了多个表中的一些数据时，想要重新输入，就比较困难了。 另一种方法是自动地将数据恢复到它们修改之前的原始状态。为了解决这样的问题，SQL Server 2000提供了事务和锁来保证数据的一致性和完整性。

9.2.1 事务的属性 事务是构成单一逻辑单元的操作的集合。 事务必须满足4个要求，称为ACID属性，即原子性、一致性、隔离性和持久性。

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

2. 一致性 SQL Server 2000能保证事务的一致性。一致性意味着全部数据都保持在一致的状态。在一个事务开始之前，数据库处于一致的状态，当事务结束后，不管它是成功还是失败，数据库还应该处于一致的状态。

3. 隔离性 如果有两个或者多个事务，这些事务必须按照一定的顺序先后执行，而不能在执行一个事务的同时，又穿插执行另外的一个事务，也就是说，多事务并发执行时，应保证执行的结果是正确的，如同单用户环境一样。这可以通过锁来实现。

4. 永久性 事务一旦完成，它对数据库所进行的修改将被永久保存，即使以后系统发生故障，也应该保留这个事务执行的痕迹。 SQL Server 2000的事务分为显式事务、隐式事务、自动事务和分布式事务。

9.2.2 显式事务 显式事务就是用户使用“事务处理语句”定义的事务。 事务处理语句主要包括： (1)BEGIN TRANSACTION 9.2.2 显式事务 显式事务就是用户使用“事务处理语句”定义的事务。 事务处理语句主要包括： (1)BEGIN TRANSACTION 作用是启动一个事务，它标志着一个事务的开始。 (2)COMMIT TRANSACTION和COMMIT WORK 作用是提交事务。在事务中对数据库所做的修改，将在此时进行提交，它标志着事务的结束。 (3)ROLLBACK TRANSACTION和ROLLBACK WORK 作用是回滚事务。通常如果在事务的执行过程中发生了错误，需要执行这个语句，放弃事务中对数据库所做的修改，使数据库恢复到事务开始之前的状态。

例9-2 提交事务。 脚本： 例9-3 回滚事务。

9.2.3 隐式事务 (1)SET IMPLICIT_TRANSACTION ON。 脚本： 9.2.3 隐式事务 (1)SET IMPLICIT_TRANSACTION ON。 使SQL Server 2000进入隐式事务处理模式，使用COMMIT TRANSACTION/WORK语句提交事务，或者使用ROLLBACK TRANSACTION/WORK回滚事务。 (2)SET IMPLICIT_TRANSACTION OFF。 退出隐式事务处理模式。 例9-4 隐式事务实例。 脚本：

9.2.4 自动事务 自动事务是SQL Server 2000默认的事务处理模式。 9.2.4 自动事务 自动事务是SQL Server 2000默认的事务处理模式。 在这种模式下，如果任何一个语句执行成功，则它对数据库所做的修改马上被自动提交，反之如果失败，则自动回滚。

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

分布式事务处理过程 (1)使用BEGIN DISTRIBUTED TRANSACTION语句启动一个分布式事务。此时该服务器成为本事务管理服务器。 (2)应用程序执行分布式查询或执行远程服务器上的存储过程。 (3)事务管理器调用MS DTC，通知远程服务器开始参与该分布式事务。 (4)应用程序执行提交事务或回滚事务的语句来结束事务。此时事务管理器将调用MS DTC来管理两阶段提交过程，本服务器和远程服务器提交或回滚事务。

9.2.6 使用事务时的注意事项 (1)事务应尽可能短。 (2)定义有效的锁策略。 锁可以防止用户读取已经被修改但还没有提交的数据。 (3)避免用户在事务中输入数据。 (4)在浏览数据时避免打开事务。 这有助于减少锁定问题。除此之外，事务的最大作用在于修改数据，而不是检索数据。 (5)减少事务中所访问的数据量。 事务处理中往往会锁定数据，因此能够减少其他用户可能遇到的锁问题，提高数据库的并发性。

9.3 维护数据一致性—锁 SQL Server 2000使用锁来防止多个用户在同一时间内对同一数据进行修改，并能防止一个用户查询正在被另一个用户修改的数据，防止可能发生的数据混乱。锁有助于保证数据库逻辑上的一致性。

9.3.1 锁的类型 (2)共享锁：允许读取锁定资源。 (3)更新锁：锁定资源。 (4)结构锁：结构修改锁；结构稳定锁。 9.3.1 锁的类型 (1)排它锁：不允许读取、修改锁定资源。 (2)共享锁：允许读取锁定资源。 (3)更新锁：锁定资源。 (4)结构锁：结构修改锁；结构稳定锁。 (5)意向锁：意向锁说明SQL Server 2000有在资源上获得共享锁或者排它锁的意向， 包括： ①共享意向锁。指明事务试图在某一资源上获得共享锁。 ②排它意向锁。指明事务试图在某一资源上获得排它锁。 ③共享排它意向锁。指明事务试图在一些资源上获得共享锁，而在其他一些资源上获得排它锁。

9.3.2 锁的粒度 根据不同的情况，SQL Server 2000中的锁可以灵活地运用在不同的资源层次（也就是粒度）上。锁的粒度越大，被锁定的数据越多，数据的并行性就越低。 锁的粒度可以分为以下几种。 (1)RID行标识符：锁定表中的单行数据。 (2)键值：锁定索引中的单行数据。 (3)页面：锁定一个数据页面或者索引页面，页面的大小为 8KB。 (4)区域：锁定一组连续的数据页面或者索引页面。 (5)表：锁定整个表。 (6)数据库：锁定整个数据库。

9.3.3 死锁 在多用户环境中，当多个用户分别锁定不同的资源，而又在等待其他用户释放已锁定资源时，有可能出现无限制等待的情况，称为死锁 。 9.3.3 死锁 在多用户环境中，当多个用户分别锁定不同的资源，而又在等待其他用户释放已锁定资源时，有可能出现无限制等待的情况，称为死锁 。 设置事务优先级语句的语法格式为： SET DEADLOCK_PRIORITY {low|normal} 设置事务请求锁定的最长等待时间语句的语法格式为： SET LOCK_timeout <时间长度>

9.3.4 检索锁信息 sp_lock [[@spid1=]‘<进程ID1>’][,[@spid2=]‘<进程ID2>’]。 其中，“进程ID1”和“进程ID2”是来自master.dbo.sysprocesses的SQL Server 2000进程ID号，数据类型为int，默认值为NULL。如果没有指定进程ID号，则显示所有锁的信息。 例9-5 显示所有锁的信息。 脚本： USE master GO sp_lock

9.3.5 使用锁时的注意事项 (1)遵守事务指导原则。 9.3.5 使用锁时的注意事项 (1)遵守事务指导原则。 (2)对应用程序进行强度测试。强度测试是指大量用户执行相同操作，实际执行操作的用户数量应为应用程序可能有的最多用户数。 (3)允许用户中止长时间运行的查询 (4)在查询期间禁止用户输入以减少查询的运行时间 (5)当一个查询在运行时，它将在资源上保持—个某种类型的锁。 (6)虽然必要时能够改变查询和对象的锁，但实际应用中应该尽可能让SQL Server 2000来管理锁。

9.4 处理错误— @@ERROR 用户或者应用程序在访问数据库时，可能会出现使用违背数据库要求的访问方式，即非正常的数据访问或者操作，这时可能导致意外的发生。 SQL Server 2000具有完备的错误处理功能，能够完成： (1)判断错误是否发生。 (2)通知用户发生了错误。 (3)决定操作过程。 (4)恢复或放弃修改。

9.4.1 错误的产生 例9-6 插入一行非法的选课数据。

9.4.2 错误的捕获 1.@@ERROR 如果为0则一切正常。 如果这个值不为0，则表示已经发生了一个错误。 2.@@ERROR的使用 9.4.2 错误的捕获 1.@@ERROR 如果为0则一切正常。 如果这个值不为0，则表示已经发生了一个错误。 2.@@ERROR的使用 IF @@ERROR<>0 BEGIN --错误处理部分 END 可以通过@@ERROR来检查是否发生了特定的错误。

9.4.3 错误的处理 一般情况下，在错误发生后应该采取如下一些错误处理方法： (1)放弃任务。 (2)立即退出或尝试继续执行。 9.4.3 错误的处理 一般情况下，在错误发生后应该采取如下一些错误处理方法： (1)放弃任务。 (2)立即退出或尝试继续执行。 (3)向用户发送消息解释错误原因。

9.4.4 错误处理实例 例9-7 编写存储过程，插入学生选课信息。 脚本： 9.4.4 错误处理实例 例9-7 编写存储过程，插入学生选课信息。 脚本： 例9-8 表s中存在学号为“1001”的学生信息，表c中存在课程号为“c001”的课程信息，且表sc中不存在学号为“1001”、课程号为“c001”的学生信息，执行以下脚本将显示成功信息。 exec SCInsert '1001', 'c001'

9.4.4 错误处理实例 例9-9 表S中不存在学号为“1001”的学生信息，或表C中不存在课程号为“C001”的课程信息，执行上述存储过程的情况。

实训 错误的捕获和处理 实验名称：错误的捕获和处理 目的要求：掌握错误捕获及处理的方法 操作步骤： (1)启动查询分析器。 实训 错误的捕获和处理 实验名称：错误的捕获和处理 目的要求：掌握错误捕获及处理的方法 操作步骤： (1)启动查询分析器。 (2)根据要求创建存储过程。存储过程名要求为<班级> _<学号>_cdelete，有一个参数@cno，表示课程号。该存储过程的功能是：删除表c中课程号等于@cno的课程信息，并要求具有错误捕获和处理的能力。 (3)执行存储过程，分析结果。