数据库原理与 SQL Server 2000

第 10 章 ( 续 ) 事务

保证数据完整性—事务 问题：如果在修改了数据库中的数据之后又立即意识到

不该做这些修改，应该怎样处理 ?

最简单的办法，就是重新输入这些数据。但是，如果已经修改了多个表中的一些数据时，想要重新输入，就比较困难了。

另一种方法是自动地将数据恢复到它们修改之前的原始状态。为了解决这样的问题， SQL Server 2000 提供了事务和锁来保证数据的一致性和完整性。

事务的属性

事务是构成单一逻辑单元的操作的集合。事务必须满足 4 个要求，称为 ACID 属性，即

原子性、一致性、隔离性和持久性。

一个简单的事务例子银行转账：事务T 从A帐户过户100元到B帐户 READ(A)

A=A-100

WRITE(A)

READ(B)

B=B+100

WRITE(B)

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

2. 一致性

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

3. 隔离性

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

4. 永久性

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

SQL Server 2000 的事务分为显式事务、隐式事务、自动事务。

显式事务 显式事务就是用户使用“事务处理语句”定义的事务。 事务处理语句主要包括：(1)BEGIN TRANSACTION

作用是启动一个事务，它标志着一个事务的开始。(2)COMMIT TRANSACTION 和 COMMIT WORK

作用是提交事务。在事务中对数据库所做的修改，将在此时进行提交，它标志着事务的结束。

(3)ROLLBACK TRANSACTION 和 ROLLBACK WORK

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

例例 10-2 10-2 提交事务。提交事务。 脚本：

例例 10-3 10-3 回滚事务。回滚事务。脚本：脚本：

隐式事务

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

例例 9-4 9-4 隐式事务实例。隐式事务实例。 脚本：

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

例： update sc set score=score+15

使用事务时的注意事项

(1) 事务应尽可能短。(2) 定义有效的锁策略。 锁可以防止用户读取已经被修改但还没有提交的数据。(3) 避免用户在事务中输入数据。(4) 在浏览数据时避免打开事务。 这有助于减少锁定问题。除此之外，事务的最大作用在于

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

维护数据一致性—锁

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

锁的类型 (1)排它锁：不允许读取、修改锁定资源。 (2)共享锁：允许读取锁定资源。 (3)更新锁：锁定资源。

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

死锁

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

设置事务请求锁定的最长等待时间语句的语法格式为： SET LOCK_timeout < 时间长度 >

检索锁信息 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 GO

使用锁时的注意事项

(1)遵守事务指导原则。(2) 对应用程序进行强度测试。强度测试是指大量用户执

行相同操作，实际执行操作的用户数量应为应用程序可能有的最多用户数。

(3)允许用户中止长时间运行的查询(4) 在查询期间禁止用户输入以减少查询的运行时间(5) 当一个查询在运行时，它将在资源上保持—个某种类型的锁。

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

处理错误— @@ERROR 用户或者应用程序在访问数据库时，可能会出现使用违背数据库要求的访问方式，即非正常的数据访问或者操作，这时可能导致意外的发生。 SQL Server 2000具有完备的错误处理功能，能够完成：

(1)判断错误是否发生。(2) 通知用户发生了错误。(3) 决定操作过程。(4) 恢复或放弃修改。

错误的产生

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

错误的捕获

1.@@ERROR

如果为 0 则一切正常。 如果这个值不为 0 ，则表示已经发生了一个错误。 2.@@ERROR 的使用 IF @@ERROR<>0

BEGIN

-- 错误处理部分 END

可以通过 @@ERROR 来检查是否发生了特定的错误。

错误的处理

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

错误处理实例 例例 10-7 10-7 编写存储过程，插入学生选课信息。编写存储过程，插入学生选课信息。 脚本：

例例 10-8 10-8 表表 ss 中存在学号为“中存在学号为“ 10011001”” 的学生信的学生信息，表息，表 cc 中存在课程号为“中存在课程号为“ c001c001”” 的课程信息，的课程信息，且表且表 scsc 中不存在学号为“中不存在学号为“ 10011001”” 、课程号为“、课程号为“ cc001001”” 的学生信息，执行以下脚本将显示成功信息。的学生信息，执行以下脚本将显示成功信息。 脚本： exec SCInsert '1001', 'c001'

例例 10-9 10-9 表表 SS 中不存在学号为“中不存在学号为“ 1001”1001” 的学生信息，或的学生信息，或表表 CC 中不存在课程号为“中不存在课程号为“ C001”C001” 的课程信息，执行上述存的课程信息，执行上述存储过程的情况。储过程的情况。

错误处理实例

实训 错误的捕获和处理 实验名称：错误的捕获和处理目的要求：掌握错误捕获及处理的方法操作步骤：(1) 启动查询分析器。(2)根据要求创建存储过程。存储过程名要求为 <班级 >

_<学号 >_cdelete ，有一个参数 @cno ，表示课程号。该存储过程的功能是：删除表 c 中课程号等于 @cno 的课程信息，并要求具有错误捕获和处理的能力。

(3) 执行存储过程，分析结果。