第九章 保证数据完整性

null数据库原理与SQL Server数据库原理与SQL Server第9章 保证数据完整性第9章 保证数据完整性第9章 保证数据完整性9.1 构造Transact-SQL执行单元 —批处理 9.2 保证数据完整性—事务 9.3 维护数据一致性—锁 9.4 处理错误— @@ERROR 实训 错误的捕获和处理 9.1 构造Transact-SQL执行单元 —批处理 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 事务的属性9.2.1 事务的属性事务是构成单一逻辑单元的操作的集合。 事务必须满足4个要求，称为ACID属性，即原子性、一致性、隔离性和持久性。1. 原子性1. 原子性 如果事务成功，SQL Server 2000确保在事务中所有的数据修改作为一个整体。如果事务没有成功，就不会有任何修改发生。也就是说，SQL Server 2000能确保事务的原子性。事务要想取得成功，事务中的每一个操作（语句）都必须成功。如果其中的任何一个操作失败了，则整个事务就会失败，而且自事务开始所做的任何修改都会被撤销。 2. 一致性2. 一致性 SQL Server 2000能保证事务的一致性。一致性意味着全部数据都保持在一致的状态。在一个事务开始之前，数据库处于一致的状态，当事务结束后，不管它是成功还是失败，数据库还应该处于一致的状态。3. 隔离性3. 隔离性 如果有两个或者多个事务，这些事务必须按照一定的顺序先后执行，而不能在执行一个事务的同时，又穿插执行另外的一个事务，也就是说，多事务并发执行时，应保证执行的结果是正确的，如同单用户环境一样。这可以通过锁来实现。4. 永久性4. 永久性事务一旦完成，它对数据库所进行的修改将被永久保存，即使以后系统发生故障，也应该保留这个事务执行的痕迹。 SQL Server 2000的事务分为显式事务、隐式事务、自动事务和分布式事务。9.2.2 显式事务9.2.2 显式事务 显式事务就是用户使用“事务处理语句”定义的事务。 事务处理语句主要包括： (1)BEGIN TRANSACTION 作用是启动一个事务，它标志着一个事务的开始。 (2)COMMIT TRANSACTION和COMMIT WORK 作用是提交事务。在事务中对数据库所做的修改，将在此时进行提交，它标志着事务的结束。 (3)ROLLBACK TRANSACTION和ROLLBACK WORK 作用是回滚事务。通常如果在事务的执行过程中发生了错误，需要执行这个语句，放弃事务中对数据库所做的修改，使数据库恢复到事务开始之前的状态。null例9-2 提交事务。 脚本： 例9-3 回滚事务。 脚本：9.2.3 隐式事务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 自动事务9.2.4 自动事务 自动事务是SQL Server 2000默认的事务处理模式。 在这种模式下，如果任何一个语句执行成功，则它对数据库所做的修改马上被自动提交，反之如果失败，则自动回滚。9.2.5 分布式事务 9.2.5 分布式事务 SQL Server 2000可以通过网络实现跨服务器的数据操作，这种事务称为“分布式事务”。分布式事务有非常强大的功能，但必须通过网络来传送数据，因此出错的几率也就大大增加了。为了解决这个问题，分布式事务的处理被分成两个阶段：准备阶段和提交阶段，也就是所谓的两阶段提交。 (1)准备阶段。 (2)提交阶段。分布式事务处理过程分布式事务处理过程(1)使用BEGIN DISTRIBUTED TRANSACTION语句启动一个分布式事务。此时该服务器成为本事务管理服务器。 (2)应用程序执行分布式查询或执行远程服务器上的存储过程。 (3)事务管理器调用MS DTC，通知远程服务器开始参与该分布式事务。 (4)应用程序执行提交事务或回滚事务的语句来结束事务。此时事务管理器将调用MS DTC来管理两阶段提交过程，本服务器和远程服务器提交或回滚事务。 9.2.6 使用事务时的注意事项9.2.6 使用事务时的注意事项(1)事务应尽可能短。 (2)定义有效的锁策略。 锁可以防止用户读取已经被修改但还没有提交的数据。 (3)避免用户在事务中输入数据。 (4)在浏览数据时避免打开事务。 这有助于减少锁定问题。除此之外，事务的最大作用在于修改数据，而不是检索数据。 (5)减少事务中所访问的数据量。 事务处理中往往会锁定数据，因此能够减少其他用户可能遇到的锁问题，提高数据库的并发性。 9.3 维护数据一致性—锁 9.3 维护数据一致性—锁 SQL Server 2000使用锁来防止多个用户在同一时间内对同一数据进行修改，并能防止一个用户查询正在被另一个用户修改的数据，防止可能发生的数据混乱。锁有助于保证数据库逻辑上的一致性。9.3.1 锁的类型 9.3.1 锁的类型 (1)排它锁：不允许读取、修改锁定资源。 (2)共享锁：允许读取锁定资源。 (3)更新锁：锁定资源。 (4)结构锁：结构修改锁；结构稳定锁。 (5)意向锁：意向锁说明SQL Server 2000有在资源上获得共享锁或者排它锁的意向， 包括： ①共享意向锁。指明事务试图在某一资源上获得共享锁。 ②排它意向锁。指明事务试图在某一资源上获得排它锁。 ③共享排它意向锁。指明事务试图在一些资源上获得共 享锁，而在其他一些资源上获得排它锁。 9.3.2 锁的粒度 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 检索锁信息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 GO9.3.5 使用锁时的注意事项 9.3.5 使用锁时的注意事项 (1)遵守事务指导原则。 (2)对应用程序进行强度测试。强度测试是指大量用户执行相同操作，实际执行操作的用户数量应为应用程序可能有的最多用户数。 (3)允许用户中止长时间运行的查询 (4)在查询期间禁止用户输入以减少查询的运行时间 (5)当一个查询在运行时，它将在资源上保持—个某种类型的锁。 (6)虽然必要时能够改变查询和对象的锁，但实际应用中应该尽可能让SQL Server 2000来管理锁。 9.4 处理错误— @@ERROR 9.4 处理错误— @@ERROR 用户或者应用程序在访问数据库时，可能会出现使用违背数据库要求的访问方式，即非正常的数据访问或者操作，这时可能导致意外的发生。 SQL Server 2000具有完备的错误处理功能，能够完成： (1)判断错误是否发生。 (2)通知用户发生了错误。 (3)决定操作过程。 (4)恢复或放弃修改。9.4.1 错误的产生9.4.1 错误的产生例9-6 插入一行非法的选课数据。 9.4.2 错误的捕获9.4.2 错误的捕获 1.@@ERROR 如果为0则一切正常。 如果这个值不为0，则表示已经发生了一个错误。 2.@@ERROR的使用 IF @@ERROR<>0 BEGIN --错误处理部分 END 可以通过@@ERROR来检查是否发生了特定的错误。9.4.3 错误的处理9.4.3 错误的处理 一般情况下，在错误发生后应该采取如下一些错误处理方法： (1)放弃任务。 (2)立即退出或尝试继续执行。 (3)向用户发送消息解释错误原因。9.4.4 错误处理实例 9.4.4 错误处理实例 例9-7 编写存储过程，插入学生选课信息。 脚本： 例9-8 表s中存在学号为“1001”的学生信息，表c中存在课程号为“c001”的课程信息，且表sc中不存在学号为“1001”、课程号为“c001”的学生信息，执行以下脚本将显示成功信息。 脚本： exec SCInsert '1001', 'c001'9.4.4 错误处理实例 9.4.4 错误处理实例 例9-9 表S中不存在学号为“1001”的学生信息，或表C中不存在课程号为“C001”的课程信息，执行上述存储过程的情况。 实训 错误的捕获和处理 实训 错误的捕获和处理 实验名称：错误的捕获和处理 目的要求：掌握错误捕获及处理的方法 操作步骤： (1)启动查询 分析 定性数据统计分析pdf销售业绩分析模板建筑结构震害分析销售进度分析表京东商城竞争战略分析 器。 (2)根据要求创建存储过程。存储过程名要求为<班级> _<学号>_cdelete，有一个参数@cno，表示课程号。该存储过程的功能是：删除表c中课程号等于@cno的课程信息，并要求具有错误捕获和处理的能力。 (3)执行存储过程，分析结果。