第七章 系统实现技术 7.1 事务 7.2 数据库的并发控制 7.3 DB的恢复 7.4数据库的安全性 7.5数据库的完整性

7.1.1 事务的基本概念 定义： 事务是形成一个逻辑工作单位的数据库的操作的汇集。 或： 7.1.1 事务的基本概念 定义： 事务是形成一个逻辑工作单位的数据库的操作的汇集。 或： 是由用户定义的一个DB的操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位。 事务和程序是两个概念，一般的，一个程序可包含多个事务。 程序  事务 7.1 事务

显示定义 commit 提交 事务的定义 roll back回退 默认按缺省自动划分（隐式） 事务以begin transaction 开始 事务以end Transaction结束 事务的定义 7.1.1 roll back回退 事务的基本概念 默认按缺省自动划分（隐式） 默认（隐式）有：ALTER TABLE 、CREATE 、DELETE、 DROP、 FETCH、 INSERT 、OPEN 、SELECT、 UPDATE等

UPDATE S SET SDEPT='计算机‘ WHERE SDEPT='aa' 例：定义一个未提交的事务。 BEGIN TRANSACTION T1 GO UPDATE S SET SDEPT='计算机‘ WHERE SDEPT='aa' ROLLBACK TRAN 7.1.1 事务的基本概念

UPDATE S SET SDEPT='计算机'WHERE SDEPT='aa' COMMIT TRAN 例：定义一个提交的事务。 BEGIN TRANSACTION T1 GO UPDATE S SET SDEPT='计算机'WHERE SDEPT='aa' COMMIT TRAN --注意：打开S表看结果 ROLLBACK TRAN --注意：同样打开S表看结果 7.1.1 事务的基本概念

7.1.2 事务的ACID性质 为了保证DB的完整（正确），事务应具有以下四个性质 1. 原子性（atomicity） 一个事务是不分割的操作序列，要么全做，要么全不做. 2. 一致性（consistency） 事务的执行必须从一个使DB一致的状态到另一个是使DB一致的状态。既：DB不会因事务的执行而遭受破坏。 7.1 事务 如：一公司在银行有两个帐号。从A帐号—>B帐号转10000元，既可定义一个事务。 A-10000 B+10000

一个事务的执行不能被其他事务打扰,在并发事务操作时，系统应保证与这些事务独执行时结果一样。 3. 隔离性（isolation） 一个事务的执行不能被其他事务打扰,在并发事务操作时，系统应保证与这些事务独执行时结果一样。 4. 持久性（durability） 一个事务一旦完成全部操作后，它对DB的所有更新应永久反映在DB中。即使以后系统发现故障，也应保留这个事务的结果。 7.1.2 事务的ACID性质 上述四个性质分别有DBMS相应的子系统实现。 A: 由 DBMS的事务管理子系统实现。 C: 由DBMS测试完整性约束自动完成。 I: 由DBMS的并发控制子系统实现 D: 由DBMS恢复控制子系统实现。

对DB的访问是建立在读，写两个操作的基础上的 . read(x): 把数据X从DB读到内存缓冲区 . write(x): 把数据X从内存缓冲区写回DB （在实际操作时，write(x)未必就写回DB很可能先暂存在系统缓冲区，然后再写回硬盘。先假定write(x)写回磁盘。） 举例说明事务的ACID性质： 7.1.2 例：银行DB有转帐事务T1，从帐号A转50元到帐号B 事务的ACID性质 T1: read(A) A:=A-50 Write(A) Read(B) B:=B+50 Write(B)

由事务的原子性保持事务的一致性，但事务的执行有一定的时间，在某一个时刻会不一致，是正常的 (1) 原子性： 由事务的原子性保持事务的一致性，但事务的执行有一定的时间，在某一个时刻会不一致，是正常的 (2) 一致性： A-50，B+50 A+B的和不变 7.1.2 (3) 隔离性： 事务的ACID性质 在A-50后，突然插入一个事务来计算A+B,那肯定会不对，这就要由DBMS的并发控制来控制。 (4) 持久性： 事务正确执行后，仍长期保存，不能丢失

7.1.3 事务的状态变迁 begin 失败 read/write commit end 提 交 活动 局部提交 transaction 7.1.3 事务的状态变迁 read/write commit begin end 提 交 活动 局部提交 transaction transaction abort 7.1 失败 异常终止 事务 roll back

事务开始执行，即进入“活动”状态，在活动状态执行对DB进行读/写，但“写”并不立即写到硬盘，可暂存在系统缓冲区。 read/write begin commit end 局部提交 提 交 活动 transaction transaction abort 失败 异常中止 7.1.3 roll back 事务的状态变迁 1、活动状态： 事务开始执行，即进入“活动”状态，在活动状态执行对DB进行读/写，但“写”并不立即写到硬盘，可暂存在系统缓冲区。

事务执行完后，进入该状态，但对DB的修改很可能在缓冲区内，所以事务并未真正结束。 read/write begin commit end 局部提交 提 交 活动 transaction transaction abort 失败 异常中止 7.1.3 roll back 事务的状态变迁 2、局部提交 事务执行完后，进入该状态，但对DB的修改很可能在缓冲区内，所以事务并未真正结束。

失败 3、失败 没有运行到事务的最后一个语句就中止 两种 事务的修改未写到硬盘 read/write begin end commit 局部提交 提 交 活动 transaction transaction abort 失败 异常中止 7.1.3 roll back 事务的状态变迁 3、失败 没有运行到事务的最后一个语句就中止 两种 事务的修改未写到硬盘

read/write begin commit end 局部提交 提 交 活动 transaction transaction abort 失败 异常中止 4、异常中止 7.1.3 roll back 在“失败”状态的事务，很可能对硬盘的数据进行了一部分修改，为了保证事务的原子性，应撤消，执行rollback回退到事务执行前的状态，由恢复子系统完成。 事务的状态变迁 重新启动事务: 由硬件等原因造成 在异常中止时: 取消事务： 由事务内逻辑错误

在局部提交后，并发控制系统将检查该事务与并发事务是否发生干扰，通过检查后，系统执行commit。 read/write begin end commit 局部提交 提 交 活动 transaction transaction abort 失败 异常中止 7.1.3 roll back 5、提交 事务的状态变迁 在局部提交后，并发控制系统将检查该事务与并发事务是否发生干扰，通过检查后，系统执行commit。 把对DB的修改的全部写到硬盘，成功结束。 commit 事务结束有两个状态 roll back

7.1.4事务的并发执行 为了提高事务的执行效率，使多个事务并发执行。 定义： 事务的执行次序称为“调度”。 如果多个事务依次执行，则称为事务的串行调度。 如果利用分时的方法同时处理多个事务，则称为事务的并发调度。 7.1 举例说明事务的并发执行，可能会带来DB的不一致性。 事务

例7-1： 设事务T1从帐号A转100元到帐号B T2从帐号A转10%的款到帐号B T2: T1: Read(A) Read(A) A:=A-100 Temp:=A*0.1 Write(A) A:=A-temp 7.1.4 Write(A) Read(B) 事务的并发执行 B:=B+100 Read(B) Write(B) B:=B+temp Write(B) 设A.B的初值为2000，1000

第一种S1：串行：T1、T2 设A.B的初值为2000，1000 A=1710 B=1290 Read(A) A:=A-100 7.1.4 Write(A) Read(B) B:=B+100 Write(B) Temp:=A*0.1 A:=A-tmpe B:=B+temp Write(b) 7.1.4 事务的并发执行 A=1710 B=1290

设A.B的初值为2000，1000 A=1700 B=1300 7.1.4 事务的并发执行 T1 T2 Read(A) Temp:=A*0.1 A:=A-temp W(A) R(B) B:=B+temp W(B) R(A) A:=A-100 B:=B+100 7.1.4 事务的并发执行 A=1700 B=1300

第三种S3：并发调度 设A.B的初值为2000，1000 A=1710 B=1290 7.1.4 事务的并发执行 T1 T2 R(A) A:=A-100 W(A) r(A) Temp=A*0.1 A:=A-temp w(A) r(B) B:=B+100 W(B) R(B) B:=B+temp 7.1.4 事务的并发执行 A=1710 B=1290

设A.B的初值为2000，1000 第四种S4：并发调度 A=1900 B=1200 7.1.4 事务的并发执行 T1 T2 R(A) A:=A-100 Temp=A*0.1 A:=A-temp W(A) R(B) B:=B+100 W(B) B:=B+temp 7.1.4 事务的并发执行 A=1900 B=1200

如果有n个事务，串行调度有n!种不同的有效调度（都正确）并行调度有远远＞n!种调度，且有的并发调度是正确的（和串行调度之一相等即可）。 如何产生一个正确的并发调度，由DB的并发控制系统实现。 7.1.4 事务的并发执行

7.1.5并发事务的可串行化 从上例可以看出，有的并发调度可以保持DB的一致，有的则不行，涉及到一个概念——并发事务的可串行化。 冲突可串行化 两种不同形式的等价调度： 观察可串行化 (了解) 1. 冲突可串行化 7.1 事务 设有两个事务Ti、Tj,其调度为S。 S中有两个相邻语句Ii和Ij(指read，write)分别来自Ti、Tj。

如果Ii和Ij是对不同数据的操作，那么交换Ii和Ij次序，不影响调度执行的结果。 第一： 如果Ii和Ij是对不同数据的操作，那么交换Ii和Ij次序，不影响调度执行的结果。 第二： 如果Ii和Ij是对同一数据的操作，则 (1) Ii=r(Q) Ij=r(Q) 可忽视Ii、Ij的先后次序 即可互换 7.1.5 并发事务的可串行化 (2) Ii=r(Q) Ij=w(Q) Ii、Ij互换后，Ii读Q可能就 不一样 即：不能互换 (3) Ii=w(Q) Ij=r(Q) 不能互换 （同上） (4) Ii=w(Q) Ij=w(Q) 其次序直接影响DB中 值 即：不能互换

Ii和Ij是并发事务Ti和Tj中的read、write语句，并在并发调度中相邻。 定义： Ii和Ij是并发事务Ti和Tj中的read、write语句，并在并发调度中相邻。 当Ii和Ij是对同一数据操作时，并且至少有一个write语句，我们称Ii、Ij是一对“冲突”语句，否则，为“非冲突”语句。 7.1.5 在调度S中，有一对相邻的语句是“非冲突”的，那么它次序可互换，且交换后产生的新调度S’和S等价，即产生相同的执行结果。 并发事务的可串行化 上例上的第三种情况S3（简化只留下r、w语句）

S3 S3-1 T1 T2 R(A) W(A) R(B) W(B) T1 T2 R(A) W(A) R(B) W(B) 7.1.5 并发事务的可串行化

S3-2 S3-3 T1 T2 R(A) W(A) R(B) W(B) T1 T2 R(A) W(A) R(B) W(B) 7.1.5 并发事务的可串行化

同第一种，所以第三种S3和第一种S1是等价的。 T1 T2 R(A) W(A) R(B) W(B) 7.1.5 并发事务的可串行化 同第一种，所以第三种S3和第一种S1是等价的。

如果调度S’从调度S通过交换一系列的非冲突语句得到，那么称S、S’是一对“冲突等价”的调度。 定义： 如果调度S’从调度S通过交换一系列的非冲突语句得到，那么称S、S’是一对“冲突等价”的调度。 如果调度S和某个串行调度是“冲突等价”，那么称调度S是“冲突可串行化”的调度。 例7-2：第一种S1和第二种S2是“冲突等价等价”吗？ 7.1.5 并发事务的可串行化

例7-2：第一种S1和第二种S2是“冲突等价等价”吗？ T1 T2 R(A) W(A) R(B) W(B) 7.1.5 并发事务的可串行化 即：T2的r(A)不可能换到T1的w(A)之前 ，所以两种情不一样。

例7-3：有一并发调度T3、T4是“冲突可串行化”吗？ R(Q) W(Q) T3 T4 7.1.5 两邻语句不能互换即不能变为 并发事务的可串行化 T4 T3 所以不是“冲突可串行化”

例7-4：有一并发调度 设： A=2000 B=1000 T1: 从帐号A转100到帐号B T5: 从帐号B转10 到帐号A R(A) A:=A-100 W(A) R(B) B:=B+100 W(B) B:=B-10 A:=A+10 T1 T5 R(A) A:=A-100 W(A) R(B) B:=B-10 W(B) B:=B+100 A:=A+10 7.1.5 并发事务的可串行化 A=1910 B=1090 A=1910 B=1090

这两种“调度”等价，但不是“冲突可串行化” 可见：“冲突等价”的定义比“调度等价”的定义严格。 即：“冲突等价”一定是“调度等价”。 而“调度等价”不一定是“冲突等价”。 7.1.5 并发事务的可串行化

定义：在同样事务集上的两个调度设S、S’，如果满足下列三个条件，那么称S、S’是“观察等价”的调度。 2. 观察可串行化 比“冲突等价”的定义放宽一些。 定义： 定义：在同样事务集上的两个调度设S、S’，如果满足下列三个条件，那么称S、S’是“观察等价”的调度。 7.1.5 并发事务的可串行化

对每个数据项Q，如果事务Ti在调度S中读了Q的初值，那么事务Ti在调度S’也读Q的初值。 例7-5： T1 T2 R(A) W(A) R(B) W(B) S1 7.1.5 并发事务的可串行化

对每个数据Q，如果在调度S中，事务Tj执行read(Q)操作，读了由事务Ti 产生的Q值,那么在调度S’中，事务Tj也必须执行read(Q)操作，读由事务Ti产生的Q值。 R(A) W(A) R(B) W(B) 7.1.5 并发事务的可串行化

对每个数据项Q，如果在调度S中, 最后执行 W(Q)的事务是Ti，那么在S’中最后执行W(Q)也是Ti。 R(A) W(A) R(B) W(B) 7.1.5 并发事务的可串行化

(1)(2)可以保证在调度中每个事务读到同样的数据值，(1)(2)(3)可以导致DB的状态是一样的。 S3 S1 S2 (1)(2)可以保证在调度中每个事务读到同样的数据值，(1)(2)(3)可以导致DB的状态是一样的。 T1 T2 R(A) W(A) R(B) W(B) T1 T2 R(A) W(A) R(B) W(B) T1 T2 R(A) W(A) R(B) W(B) 7.1.5 并发事务的可串行化

在S1中：T1读A的初始，S2中T1不读A的初始。 所以S1，S2不是观察等价。 B的初始值(S1，S3中) 7.1.5 (2) S1中，T2读由T1产生的A，B值。 并发事务的可串行化 (3) S1中，最后执行W(A)、W(B)是T2。 S3中，最后执行W(A)、W(B)是T2。 所以S1和S3是“观察等价”。

如果调度S与某个串引调度是“观察等价”，那么称调度S是“观察可串行化”的调度。 定义： 如果调度S与某个串引调度是“观察等价”，那么称调度S是“观察可串行化”的调度。 S3为：观察可串行化，也是“冲突可串行化 7.1.5 例7-6: S1 并发事务的可串行化 T3 T4 T6 R(Q) W(Q)

每个“冲突可串行化”的调度都是“观察可串行化”，但 “观察可串行化”的调度不一都是“冲突可串行化”。 T3 T4 T6 R(Q) W(Q) S1是“观察可串行化” 但S1不是“冲突可串行化”。 7.1.5 并发事务的可串行化 结论： 每个“冲突可串行化”的调度都是“观察可串行化”，但 “观察可串行化”的调度不一都是“冲突可串行化”。

小 结 1、事务以及性质 2、事务的并发执行 7.1 事务

作 业： 1. 什么样的 调度是正确的调度？ P 306 8 9 本节结束

7.2 并发操作 7.2.1 DB的并发操作带来的问题 1. 丢失更新的问题。 S1 事务T1对数据A的更新丢失，被T2覆盖。 T1 T2 7.2 并发操作 7.2.1 DB的并发操作带来的问题 1. 丢失更新的问题。 S1 T1 T2 A的初值=16 R(A) 16 A:=A-2 14 A:=A-1 15 W(A) 7.2 数据库的并发控制 事务T1对数据A的更新丢失，被T2覆盖。

T1要验算，A+B的值是否一致。即：D=C？ 2. 不一致分析的问题(读了过时的数据) S2 T1 T2 A =50 B=100 R(A) A=50 R(B) B=100 C:=A+B C=150 B:=B*2 B=200 W(B) D:=A+B D=250 7.2.1 DB的并发操作带来的问题 T1要验算，A+B的值是否一致。即：D=C？

T1 T2 R(A) W(A) A:=A+10 7.2.1 DB的并发操作带来的问题 T2读的是过时的数据。

3. 依赖于未提交更新的问题。(读“脏”数据) S3 T1 T2 C=100 R(C) C:=C*2 C=200 W(C) Rollback 7.2.1 DB的并发操作带来的问题 T2读到了“脏”数据。 这些问题需要用并发控制来完成， 封锁协议技术 时标协议技术 乐观方法

7.2.2 封锁（locking） 封锁是并发控制的一个非常重要的技术。 基本封锁有两种类型： 排它锁(exclusive locks，简称X锁，又称写锁) 共享锁（share locks，简称S锁，又称读锁）。 X锁: 如果事务T对某个数据(数据项、记录 ……DB)实现X封锁，那么其他事务要等T解除X封锁之后，才能对这个数据进行封锁。 7.2 S锁:如果事务Ti对某数据有一个S封锁，那么其他事物Tj也能对这个数据实现S封锁。但不能对其实现X封锁。 数据库的并发控制 Tj Ti X S — N Y

7.2.3 封锁协议 运用X和S锁，对数据加锁时，还要约定一些规则 (如：何时申请、持续时间、何时释放 )， 这些规则称为封锁协议。 1. 一级封锁协议 事务T在修改数据R之前必须先对其加X锁。直到事务结束才释放。 7.2 数据库的并发控制 或： 任何企图更新记录R的事务必须先执行:“Lock-X(R)”操作可获取对该记录进行寻址的能力，并对它获取X封锁。 如果未获取X封锁，那么这个事务进入等待状态，一直到获取X锁，事务才继续。

例7-8： 对丢失更新问题的解决 T1： 对A-2 T2： 对 A-1 7.2.3 封锁协议 T1 T2 A的初值=16 LOCK—X（A） R（A） 16 WAIT A:=A-2 14 W（A） COMMIT UNLOCK（A） A:=A-1 13 7.2.3 封锁协议

对于修改，删除操作，一级封锁协议适用。对于插入操作，可以认为“INSERT”本身就加了X锁。 7.2.3 封锁协议

一级封锁协议 加上事务T在读数据R之前必须先对其加S锁，读完后即释放S锁。 2. 二级封锁协议 一级封锁协议 加上事务T在读数据R之前必须先对其加S锁，读完后即释放S锁。 例: S3 T1 T2 C=100 LOCK－X（ C） R(C) C:=C*2 C=200 W(C) LOCK －S（C） Rollback WAIT UNLOCK（ C） LOCK－S （C） 7.2.3 封锁协议 这就保证了事务不读“脏”数，但由于读完就释放锁，所以S3不能保证可重复读。

一级封锁协议 加上 事务T在读数据R之前必须先对其加S锁，直到事务结束才释放。 3. 三级封锁协议 一级封锁协议 加上 事务T在读数据R之前必须先对其加S锁，直到事务结束才释放。 7.2.3 封锁协议

例: S2 7.2.3 封锁协议 T1 T2 A =50 B=100 LOCK－S （A） R(A) A=50 LOCK－S （B） R(B) B=100 C:=A+B C=150 LOCK－X （B） WAIT D:=A+B D=150 COMMIT UNLOCK（ A） UNLOCK （B） 100 B:=B*2 B=200 W(B) UNLOCK（ B） 7.2.3 封锁协议

三级封锁协议的实施，避免了本节开头提到的三类问题，即避免了丢失更新、读“脏”数和不可重复读。 7.2.3 封锁协议

7.2.4 活锁和死锁 1. 活锁 T1，T2，T3…Tn个事务都要对A进行更新操作，假设T1先获取LOCK-X（A）,待其执行UNLOCK（A）后T3获取LOCK-X（A）…使T2一直长期的处于等待状态，这种状态称为“活锁” 7.2 数据库的并发控制

活锁的预防，采取先来先服务的策略。 7.2 数据库的并发控制 T1 T2 T3 T4 LOCK-X（A） R（A） WAIT UNLOCK（A） UNLOCK-X LOCK –X（A） 7.2 数据库的并发控制 活锁的预防，采取先来先服务的策略。

事务T1、T2都要对A、B进行更新操作，T1等待A的X锁，T2等待B的X锁，每个事务都在等待对方事务解封锁，这种现象“死锁”。 2. 死锁 事务T1、T2都要对A、B进行更新操作，T1等待A的X锁，T2等待B的X锁，每个事务都在等待对方事务解封锁，这种现象“死锁”。 T1 T2 LOCK-X（A） LOCK-X（B） R（A） R（B） WAIT 7.2.4 活锁和死锁

(a) 一次封锁法：要求每个事务必须一次把所 使用的数据全部加锁，否则就不执行。 3. 死锁的预防 (a) 一次封锁法：要求每个事务必须一次把所 使用的数据全部加锁，否则就不执行。 存在的问题： 扩大了封锁的范围，降低了系统的并发度；数据库的数据是动态的，原本不要求封锁的数据在执行过程中，会变成封锁对象，所以很难预料要封锁的对象。 (b) 顺序封锁法：预先对数据对象规定一个封锁次序，所有事务按此规定进行封锁。 7.2.4 活锁和死锁 存在的问题： 数据库的数据是动态的，数据的顺序很难定；原本不要求封锁的数据在执行过程中，会变成封锁对象，所以很难预料要封锁的对象。

(a) 超时法：如果一个事务的等待超过了规定的时限，认为“死锁”。 4. 死锁的诊断与解除 (a) 超时法：如果一个事务的等待超过了规定的时限，认为“死锁”。 存在的问题：时间很难确定,不同的事务大小不一. 时间长: 不能快速发现；时间短，又会出现误判。 (b) 等待图法：（依赖图） 图中每一节点为一事务，箭头表示事务的依赖关系。 T1 若 T1等待T2 T1→T2 7.2.4 T2等待T3 T2→T3 T2 T3等待T1 T3→T1 T3 活锁和死锁 如果依赖图形成一个回路，则出现了“死锁”。并发子系统，定期的检测依赖图。如果出现回路，则选取一代价小的事务将其撤消，其他事务可继续运行下去。

7.2.5 两段封锁 1. 2PL协议（TWO-PHASE LOCKING） 两段锁协议： 加 锁 解 锁 申请锁 释放封锁 增 生 收 敛 7.2.5 两段封锁 1. 2PL协议（TWO-PHASE LOCKING） 两段锁协议： 加 锁 解 锁 申请锁 释放封锁 增 生 收 敛 一级、二级、三级封锁协议都是为了避免并发中的问题而设的，但对并发调度是否正确的唯一标准是可串性化。那么，两段锁协议就是为了保证并发调度可串性化而提供的。 7.2 数据库的并发控制 两段锁协议规定： (1) 在对任何数据进行读写之前，事务要先获得对该数据的封锁 (2)在释放一个封锁之后，事务不再获得任何其他封锁

若一个并发执行的所有事务都遵守2PL，则这些事务的所有并发调度策略都是可串性化的。 例: LOCK-S（A）┄LOCK-S（B）┄LOCK-X（C）┄ UNLOCK B┄UNLOCK A ┄UNLOCK C 是2PL LOCK-S（A）┄LOCK-S（B）┄UNLOCK（A）---LOCK-X（C）┄ 不是2PL 2. 2PL与可串行化调度的关系 可以证明： 若一个并发执行的所有事务都遵守2PL，则这些事务的所有并发调度策略都是可串性化的。 2PL是可串行化的充分条件，不是必要条件。 另外：注意2PL和预防死锁的一次封锁法的异同。一次封锁法是要每个事务一次将所使用的数据全部加锁，否则不能继续执行，因此一次封锁法遵守2PL；而2PL并不要求事务必须一次将所使用的数据全部加锁，因此遵守2PL的事务可能发生死锁。 7.2.5 两段封锁

遵守2PL，这些调度是冲突可串行化的，但并不一定保持无串接调度。 (1) 严格的2PL，要求X锁必须保留到事务COMMIT操作 7.2.5 (2) 精确的2PL，要求所有封锁都必须保持到COMMIT操作 两段封锁

小 结 1、X S 锁 2、封锁协议 7.2 数据库的并发控制

并发操作可能产生哪几类数据的不一致？用什么方法能避免各种不一致的情况？ 什么是封锁协议？不同级别的封锁协议的主要区别是什么？ 作 业 并发操作可能产生哪几类数据的不一致？用什么方法能避免各种不一致的情况？ 什么是封锁协议？不同级别的封锁协议的主要区别是什么？ 什么是活锁？ 什么是死锁？ 试述活锁的产生原因和预防方法。 给出解决死锁的若干方法。 7.2 数据库的并发控制 本节结束

7.3 DB的恢复 7.3.1 数据库系统遭遇的故障 1. 事务故障 逻辑错误：如余款不足，运算溢出… 系统错误：如：死锁 这类故障没有达到事务的COMMIT，恢复操作应该为UNDO。 2. 系统故障 硬、软件错误，引起内存信息丢失，但未破坏外存中的数据。 7.3 DB的恢复 如: 一些尚未完成的事务的结果可能已被送入数据库, 需执行UNDO；有些已完成的事务可能有一部分甚至全留在缓冲区，需执行REDO。

总结各类故障，对数据库的影响有两种可能： （2） 是数据库没有被破坏，但数据可能不正确，这是事务的运行被非正常终止造成的。 3. 磁盘故障 磁盘损坏等，破坏性更大。 4. 计算机病毒 总结各类故障，对数据库的影响有两种可能： （1） 是数据库本身被破坏 （2） 是数据库没有被破坏，但数据可能不正确，这是事务的运行被非正常终止造成的。 7.3.1 DBS遭遇的故障 作为DBMS把DB从一被破坏、不正确的状态恢复到最近一个正确的状态。DBMS的这种能力称为可恢复性。恢复的原理——冗余。

7.3.2 存转数据 如何冗余？系统采用数据转储的方法。 动态转存：在转储期间，可以对数据库进行修改。 数据转储状态 7.3.2 存转数据 如何冗余？系统采用数据转储的方法。 动态转存：在转储期间，可以对数据库进行修改。 数据转储状态 静态转存 ：在系统无运行事务时转储。 海量转储：每次转储全部数据。 数据转储方式 增量转储：只转储上一次转储后的更新的数据。 7.3 上述两种都十分耗资源。 DB的恢复 日志 现在一般有两种恢复机制（技术）： 阴影页技术。

＜Ti BEGIN TRANSACTION＞ ＜Ti COMMIT＞ / ＜Ti ROLLBACK＞ 7.3.3 基于日志的恢复技术 日志 记载DB修改信息的数据结构，称为日志。日志文件是日志记录的汇集。 日志记录的书写形式如下： ＜Ti BEGIN TRANSACTION＞ ＜Ti，Xi，V1，V2＞ . ＜Ti COMMIT＞ / ＜Ti ROLLBACK＞ 7.3 DB的恢复

（1）事务故障和系统故障恢复必须用到日志文件。 2. 日志文件的作用 （1）事务故障和系统故障恢复必须用到日志文件。 （2）在动态转储方式，必须建立日志文件。 （3）在静态转储方式，也可以建立日志文件。 3. 登记日志文件 7.3.3 基于日志的恢复技术 （1）登记的次序严格按并发事务执行的时间顺序。 （2）必须先写日志，后进行数据库的修改。

必须反向扫描，A的值为V1，否则，A的值为V2。 1、事务故障的恢复 7.3.4 恢复策略 1. 事务故障的恢复 （1）反向扫描日志文件，查找事务的更新操作。 （2）对事务进行更新的逆向操作。 （3）继续重复，到事务的开始标记。 例： ＜T1 BEGIN TRANSACTION ＞ ﹤T1，A，V1，V2﹥ ＜T1，B，V3，V4＞ 7.3 DB的恢复 ﹤T1，A，V2，V3﹥ 必须反向扫描，A的值为V1，否则，A的值为V2。

（2）UNDO－LIST反向扫描，进行UNDO （3）REDO－LIST正向扫描，进行REDO 2. 系统故障的恢复 （1）正向扫描日志文件，查找有< BEGIN TRANSACTION >和 <COMMIT>事务。将其事务加入REDO－LIST；只有< BEGIN TRANSACTION >的事务加入UNDO－LIST。 （2）UNDO－LIST反向扫描，进行UNDO （3）REDO－LIST正向扫描，进行REDO 例： T1：A从10→20事务中止 T2：A从10→30提交 恢复策略 7.3.4 ＜T1 BEGIN TRANSACTION ＞ ﹤T1，A，10，20﹥ ＜T2 BEGIN TRANSACTION ＞ ﹤T2，A，10，30﹥ ＜T2，COMMIT＞

（1）REDO-LIST： T2 UNDO-LIST：T1 （2）恢复步骤 ①A=10 反向扫描 UNDO-LIST ②A=30 正向扫描 REDO-LIST 如果： 按A=30 ， A=10的顺序，则是错误的 7.3.4 3、介质故障的恢复 恢复策略 （1）装入最新的数据库的后备副本。 （2）装入相应的日志文件的后备副本。

在DBS运行时，系统保证一个日志，周期地（几分钟）产生一个检查点，在检查点时刻，做 以下事情： 4. 检查点方法 在系统出现故障后，要在整个日志中判断UNDO/REDO的事务，在日志文件中，查找速度慢， 且对已写入磁盘的事务再REDO也无意义,为了提高日志技术效率，一般采用检查点的方法。 在DBS运行时，系统保证一个日志，周期地（几分钟）产生一个检查点，在检查点时刻，做 以下事情： 第一步：将当前日志缓冲中的所有日志记录写入磁盘（稳定存储器）的日志文件中； 第二步：在日志文件中写入一个检查点记录； 7.3.4 恢复策略 第三步：将当前数据缓冲的所有数据记录写入磁盘（稳定存储器）的数据库中； 第四步：把检查点记录在日志文件中的地址写入一个重新开始文件。

（1）从重新开始文件中找到最后一个检查点记录在日志文件中的地址，由该地址在日志文件中找到最后一个检查点。 例： T1 不要REDO T2 REDO T3 UNDO T4 REDO T5 UNDO 检查点 故障点 7.3.4 使用检查点进行恢复的步骤： （1）从重新开始文件中找到最后一个检查点记录在日志文件中的地址，由该地址在日志文件中找到最后一个检查点。 恢复策略

（2）由该检查点记录得到检查点建立时刻所有正在进行的事务清单。 重做表：REDO-LIST 撤消表：UNDO-LIST 构造方法如下： 初始时，两张表为空。 反向扫描日志，考察每个日志记录，直到出现检查点。 对每个﹤Ti COMMIT﹥日志记录，Ti加入REDO-LIST。 对每个﹤Ti BEGIN TRANSACTION ﹥日志记录，如果Ti不在REDO-LIST，则把Ti加入UNDO-LIST。 7.3.4 恢复策略

从检查点记录开始，正向扫描日志，对REDO-LIST表中的每个事务Ti相应的日志记录执行REDO 恢复步骤如下： 再从日志尾反向扫描日志，对撤消表中每个事务Ti相应的日志记录执行UNDO，扫描到撤消表中每个事务的﹤Ti BEGIN TRANSACTION ﹥都找到为止。 在日志记录中查找最近一个检查点记录 从检查点记录开始，正向扫描日志，对REDO-LIST表中的每个事务Ti相应的日志记录执行REDO 7.3.4 恢复策略

适应于小的数据库或很少进行数据更改的数据库。当发生故障时，只还原到上次备份的即时点。 7.3.5 SQL故障还原 1. SQL故障还原模型 1）简单恢复模型 7.3 DB的恢复 适应于小的数据库或很少进行数据更改的数据库。当发生故障时，只还原到上次备份的即时点。

适用于重要数据库。SQL会在日志中记录对数据库的所有修改。 2）完整恢复模型 适用于重要数据库。SQL会在日志中记录对数据库的所有修改。 7.3.5 SQL故障恢复

和完全恢复模型相似，为某些大规模或大容量复制操作提供最佳性能和最小的的日志使用空间。 3）大容量日志恢复模型 和完全恢复模型相似，为某些大规模或大容量复制操作提供最佳性能和最小的的日志使用空间。 7.3.5 SQL故障恢复

7.3.5 SQL故障恢复

2）进行数据库的完整备份、差异备份、日志备份、文件和文件组备份 2. 备份和恢复的流程 1）创建备份设备 2）进行数据库的完整备份、差异备份、日志备份、文件和文件组备份 3) 恢复数据库 7.3.5 SQL故障恢复

sp_addumpdevice '备份设备类型', '备份设备逻辑名','备份设备物理名称' 2）功能 7.3.6 备份与恢复数据库 1. 备份设备 1．建立备份设备 1）格式一 sp_addumpdevice '备份设备类型', '备份设备逻辑名','备份设备物理名称' 2）功能 可以系统使用储过程sp_addumpdevice添加备份设备。 【例】 Exec sp_addumpdevice 'disk','scdb','c:\stu.bak' 7.3 DB的故障

也可以使用Management Studio建立备份设备 7.3.6 备份与恢复数据库

2．查看备份设备的属性 【例】查看备份设备scbk的属性。 7.3.6 备份与恢复数据库

sp_dropdevice[‘设备的逻辑名’][,‘delfile’] 2）功能 3．删除备份设备 1）格式 sp_dropdevice[‘设备的逻辑名’][,‘delfile’] 2）功能 从SQL Server除去数据库设备或备份设备。如果将物理备份设备文件指定为 DELFILE，将会删除物理备份设备文件，否则只删除逻辑设备名。返回0，表示成功删除，返回1表示删除失败。不能在事务内部使用 sp_dropdevice。 【例】 sp_dropdevice 'scdb' 也可以使用Management Studio删除备份设备 7.3.6 备份与恢复数据库

将指定数据库备份到指定备份设备。备份设备可以是逻辑备份设备名或物理备份设备名。 使用Management Studio备份数据库 4.备份数据库 1）格式 BACKUP DATABASE 数据库名 [<文件_或者_文件组> [ ,...n ]] TO <备份设备> [ ,...n ] [ WITH [[ , ] DIFFERENTIAL ] [[ , ] EXPIREDATE = 日期 | RETAINDAYS =天数 ] [[ , ] { INIT | NOINIT } ] [[ , ] NAME = 备份集名称 ] [[ , ] RESTART ][[ , ] STATS [ = percentage ]] ] 2）功能 将指定数据库备份到指定备份设备。备份设备可以是逻辑备份设备名或物理备份设备名。 使用Management Studio备份数据库 7.3.6 备份与恢复数据库

[ FROM <备份设备> [ ,...n ] ] [ WITH PARTIAL [ FILE = 备份文件号 ] 5. 数据库还原 1．还原数据库 1）格式 RESTORE DATABASE 数据库名 <文件_或者_文件组> [ FROM <备份设备> [ ,...n ] ] [ WITH PARTIAL [ FILE = 备份文件号 ] [[ ,] MOVE '逻辑文件名' TO '操作系统文件名'] [,...n ] [[ ,]{ RECOVERY|NORECOVERY|STANDBY={撤消文件名}}] [[ , ] STATS [= percentage ]][[ , ] REPLACE ]] 2）功能 还原数据库。 使用Management Studio恢复数据库 7.3.6 备份与恢复数据库

【例】从SQL Server 实例分离数据库。 方法一：使用Management Studio图形工具 方法二：使用SQL命令 7.3.7 分离和附加数据库 1. 分离数据 分离数据库是指将数据库从 SQL Server 实例中删除，但使数据库在其数据文件和事务日志文件中保持不变。之后，就可以使用这些文件将数据库附加到任何 SQL Server 实例，包括分离该数据库的服务器。 【例】从SQL Server 实例分离数据库。 方法一：使用Management Studio图形工具 方法二：使用SQL命令 sp_detach_db student 7.3.6 备份与恢复数据库

可以附加复制的或分离的 SQL Server 数据库。在 SQL Server 2005 中，数据库包含的全文文件随数据库一起附加。 2.附加数据 可以附加复制的或分离的 SQL Server 数据库。在 SQL Server 2005 中，数据库包含的全文文件随数据库一起附加。 【例】附加数据库student到SQL Server服务器中。 方法一：使用Management Studio 方法二：使用SQL命令 sp_attach_db 'student','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\student.mdf' 7.3.6 备份与恢复数据库

小 结 1、DBS遭遇的故障 2、基于日志的恢复 7.3 DB的恢复

作业 什么是数据库恢复？其基本原理是什么？ 登记日志文件是为什么必须先写日志文件，后写数据库？ 7.3 DB的恢复 本节结束

7.4 数据库的安全性 数据库的安全性——保护数据库以防止不合法的使用所造成的数据泄密、更改或破坏。 不是DBMS所特有的，所有计算机系统都有此问题。 为了保护DBMS，防止DB的破坏，可以设置各种安全措施： 7.4 (1) 环境级：对机房、设备加以保护。 数据库的安全性 (2) 职员级：工作人员应遵守规章、不越权。 (3) 操作系统级：防止未授权的用户从OS处访问DB。 (4) 网络级：注意网络内部的安全。 (5) 数据库系统级：检查用户身份，使用权限是否合法。

Windows身份验证模式（Windows身份验证） 混合模式（Windows身份验证和SQL Server 2005身份验证） 7.4.1 数据库安全访问控制 身份验证模式 Windows身份验证模式（Windows身份验证） 混合模式（Windows身份验证和SQL Server 2005身份验证） 7.4 数据库的安全性

选中服务器，右击属性，在安全性选项卡上选中 SQL Server和Windows 7.4.1 数据库的安全访问控制 选中服务器，右击属性，在安全性选项卡上选中 SQL Server和Windows

在SQL Server 2005中，用户访问数据库需要下面的设置： 1）在服务器上建立服务器登录帐户。 2. 权限验证 在SQL Server 2005中，用户访问数据库需要下面的设置： 1）在服务器上建立服务器登录帐户。 2）在数据库中创建帐户、角色、设置访问许可。 3）设置语句和对象许可。 7.4.1 数据库的安全访问控制

sp_grantlogin [@loginame=] '域\用户' 功能 7.4.2 安全登录帐户管理 1. 建立Windows登录帐户 格式一 sp_grantlogin [@loginame=] '域\用户' 功能 （1）将Windows用户或组帐户添加到 SQL Server 2005中，以便使用 Windows 身份验证连接到SQL Server 2005。 （2）“域\用户”是要添加的 Windows 用户或组的名称。 格式二：在Management Studio中 7.4 数据库的安全性

sp_revokelogin[@loginame=] '域\用户‘ 功能： 2. 取消Windows登录帐户 格式一： sp_revokelogin[@loginame=] '域\用户‘ 功能： 在SQL Server 2005中删除Windows用户或组的登录帐号。存储过程sp_revokelogin返回值为0表示成功删除，返回值为1表示删除失败。 格式二：使用Management Studio。 7.4.2 安全登录账户管理

sp_addlogin[@loginame=] '域\用户' [,[@passwd =] '登录密码'] 3．创建SQL Server 2005登录帐户 1)格式 sp_addlogin[@loginame=] '域\用户' [,[@passwd =] '登录密码'] [,[@defdb=] '数据库名'] 2)功能 （1） 创建SQL Server 2005登录帐号，使用户可以使用SQL Server 2005 身份验证连接SQL Server 2005。 （2）存储过程sp_addlogin 执行后，登录密码被加密并存储在系统表中。数据库名为默认连接到的数据库。 7.4.2 安全登录账户管理

【例】为用户“S09001”创建一个 SQL Server 2005 登录帐户，密码为“123456” 。 EXEC sp_addlogin 'S09001', '123456' 方法二：使用Management Studio创建帐户。 7.4.2 安全登录账户管理

7.4.2 安全登录账户管理

【例】创建没有密码和默认数据库的登录帐号user1，密码为NULL，默认数据库master。 SQL语句如下： EXEC sp_addlogin 's09003' 7.4.2 安全登录账户管理

sp_droplogin[@loginame=] '域\用户' 2）功能 4．删除SQL Server 2005登录帐户 1）格式 sp_droplogin[@loginame=] '域\用户' 2）功能 （1）使用sp_droplogin 可删除指定的用 sp_addlogin 添加的SQL Server 2005 登录帐户。 （2）不能删除记录在任何数据库中的用户登录帐户。必须首先使用 sp_dropuser 删除该用户。此外，不能删除系统管理员 (sa) 登录帐户，不能删除当前正在使用并且被连接到 SQL Server 2005 的登录。 7.4.2 安全登录账户管理

方法二：使用Management Studio创建帐户 【例】 方法一：使用SQL语句 EXEC sp_droplogin 's09001' 方法二：使用Management Studio创建帐户 7.4.2 安全登录账户管理

sp_helplogins[[@loginamepattern=] '域\用户' 2）功能 5 、查看用户 1）格式 sp_helplogins[[@loginamepattern=] '域\用户' 2）功能 查看指定用户是否具有连接SQL Server 2005的权限，及访问数据库的权限。 【例】查询“s09001”帐户信息 EXEC sp_helplogins ' s09001' 7.4.2 安全登录账户管理

登录名能登录SQL Server，但不能打开数据库student。 应该在数据库student的安全性—用户—新建用户。 然后登录。但不能对表操作。 还要对用户授权。 7.4.2 安全登录账户管理

7.4.3 权限 用户使用DB的方式称为权限。 1、权限概述 权限有三种类型 (1)GRANT：授予用户有访问权限 7.4.3 权限 用户使用DB的方式称为权限。 1、权限概述 权限有三种类型 (1)GRANT：授予用户有访问权限 (2)REVOKE：撤消已经授予、或撤消已经拒绝的权限 (3)DENY：拒绝用户有访问权限 7.4 数据库的安全性

GRANT SELECT ON TABLE S TO “s09001” 例：把查询S 表的权限授给所有用户。 2. SQL中的安全机制 1） 用户授权 格式：GRANT <权限1>[，<权限2> …] [ON <对象类型> <对象名>]TO<用户1> [，<用户2> ]…[WITH GRANT OPTION] 例：把查询S 表的权限授给s09001。 GRANT SELECT ON TABLE S TO “s09001” 例：把查询S 表的权限授给所有用户。 7.4.3 GRANT SELECT ON TABLE S TO PUBLIC 权限 例：把插入SC 表的权限授给s09002，并允许U3授给其他用户。 GRANT INSERT ON TABLE S TO “s09002”WITH GRANT OPTION

REVOKE SELECT ON TABLE S FROM ‘s09001’ 2) 用户撤权 格式：REVOKE <权限1>[，<权限2> …] [ON <对象类型> <对象名>]FROM<用户1> [，<用户2> ]… 例1：收回s09001查询S 表的权限。 REVOKE SELECT ON TABLE S FROM ‘s09001’ 例2：收回所有用户查询S 表的权限。 REVOKE SELECT ON TABLE S FROM PUBLIC 例3：收回s09002插入SC 表的权限。 7.4.3 REVOKE INSERT ON TABLE S FROM s09002 权限 这时，如果U3又把该权限授给其他用户，则其他用户的这个权限也被收回。

角色可以把各个用户汇集成一个单元，以便进行权限的管理。对于一个角色授予、拒绝或废除权限，该角色的任何成员的权限也被授权、拒绝或废除。 7.4.4 数据库角色管理 角色可以把各个用户汇集成一个单元，以便进行权限的管理。对于一个角色授予、拒绝或废除权限，该角色的任何成员的权限也被授权、拒绝或废除。 固定角色 1.固定服务器角色 2. 固定数据库角色 7.4 数据库的安全性

7.4.4 数据库角色管理

7.4.4 数据库角色管理

sp_addrole[@rolename=] '新角色的名称' [,[@ownername=]’ 新角色的所有者’] 2）功能 1．创建数据库角色 1）格式 sp_addrole[@rolename=] '新角色的名称' [,[@ownername=]’ 新角色的所有者’] 2）功能 （1）在当前数据库创建新的SQL Server 2005角色，新角色的所有者必须是当前数据库中的某个用户或角色。 （2）可以使用 sp_addrolemember 添加安全帐户为该角色的成员。当使用 GRANT、DENY 或 REVOKE 语句将权限应用于角色时，角色的成员将继承这些权限，就好像将权限直接应用于其帐户一样 7.4.4 数据库角色管理

sp_addapprole[@rolename=] '新角色的名称' [,'密码'] 2．创建应用程序角色 1）格式 sp_addapprole[@rolename=] '新角色的名称'　[,'密码'] 2）功能 （1）在当前数据库中，创建一个应用程序角色，'密码'为激活口令。 （2）可以使用sp_setapprole激活应用程序角色，可以使用sp_dropapprole删除应用程序角色， 7.4.4 数据库角色管理

sp_addrolemember[@rolename=] '角色名称' [,[@membername=]'安全帐户'] 2）功能 1．为数据库角色添加成员 1）格式 sp_addrolemember[@rolename=] '角色名称' [,[@membername=]'安全帐户'] 2）功能 （1）将指定安全帐户添加为当前数据库角色的成员。 （2）添加到角色的新安全帐户将继承所有应用到角色的权限。 （3）可以使角色成为另一个角色的成员时，不能创建循环角色。 （4）无法创建新的固定服务器角色。只能在数据库级别上创建角色 7.4.4 数据库角色管理

sp_addsrvrolemember[@loginame=] '域\用户',[@rolename=]'角色名称' ２．为固定服务器角色添加成员 1）格式 sp_addsrvrolemember[@loginame=] '域\用户',[@rolename=]'角色名称' 2）功能 （1）添加固定服务器角色的成员。固定服务器角色如表12.1所列。 （2）[@loginame =] '域\用户'指定添加到固定服务器角色的登录名称。 （3）不能更改 sa 登录的角色成员资格。 7.4.4 数据库角色管理

sp_dropsrvrolemember [ @loginame = ] '域\用户' , [ @rolename = ] '角色名称' ３．删除固定服务器角色成员 1）格式 sp_dropsrvrolemember [ @loginame = ] '域\用户' , [ @rolename = ] '角色名称' 2）功能 （1）从固定服务器角色中删除成员。 （2）[@loginame =] '域\用户'指定将要从固定服务器角色删除的登录的名称。 7.4.4 数据库角色管理

sp_droprolemember [ @rolename = ] '角色名称' , [ @membername = ] '用户安全帐户' ４．删除数据库角色成员 1)．格式 sp_droprolemember [ @rolename = ] '角色名称' ,  [ @membername = ] '用户安全帐户' 2)．功能 从当前数据库中的SQL Server 2005角色中删除安全帐户。 7.4.4 数据库角色管理

sp_addrole [ @rolename = ] '角色名称' 2）功能 （1）向当前数据库添加指定的自定义数据库角色。 5.自定义数据库角色 1）格式 sp_addrole [ @rolename = ] '角色名称' 2）功能 （1）向当前数据库添加指定的自定义数据库角色。 （2）数据库角色的名称不能包含反斜杠 (\)、不能为 NULL 或空字符串 ('')。 【例】向数据库中添加名为教师的自定义数据库角色。 EXEC sp_addrole '教师' 7.4.4 数据库角色管理

sp_droprole [ @rolename = ] '角色名称' 2）功能 6、删除自定义数据库角色 1）格式 sp_droprole [ @rolename = ] '角色名称' 2）功能 （1）从当前数据库删除指定的SQL Server 2005角色。 （2）不能删除仍然带有成员的角色。在删除角色之前，首先必须从该角色删除其所有的成员。不能删除固定角色及 public 角色。 【例】删除SQL Server 2005角色教师。 EXEC sp_droprole '教师' 7.4.4 数据库角色管理

sp_helprolemember ‘角色名称’ 2）功能 （1）sp_helprole列出角色的名称、识别码 7．查询角色信息 1）格式 sp_helprole ‘角色名称’ sp_helprolemember ‘角色名称’ 2）功能 （1）sp_helprole列出角色的名称、识别码 （2）sp_helprolemember列出该角色所有成员及各成员的对象识别码。 【例】列出SQL Server 2005角色“教师” 的所有成员及各成员的对象识别码。 EXEC sp_helprolemember'教师' 7.4.4 数据库角色管理

小 结 1、安全性 2、权限 7.4 数据库的安全性

作 业 P 148 1、 8 7.4 数据库的安全性 本节结束

7.5 数据库的完整性 7.5.1 数据库的完整性 1、数据库的完整性—数据的正确性、相容性。 不正确的表现有： (1) 数据本身就是错误。 7.5.1 数据库的完整性 1、数据库的完整性—数据的正确性、相容性。 不正确的表现有： (1) 数据本身就是错误。 (2) 数据正确，由于操作或程序错误。 7.5 (3) 系统故障，使数据出错。 —恢复机制 数据库的完整性 (4) 事务的并发执行，使数据出错。－并发控制机制 (5) 人为破坏。 —安全性机制

7.5.2 数据库的完整性规则 1. 实体完整性 2. 参照完整性 3. 用户自定义完整性 7.5 数据库的完整性

7.5.3 SQL中的完整性约束 SQL的完整性约束在CREATE和触发器中实现。 1、主键约束 格式： PRIMARY KEY （<属性名1> [，<属性名2>…]） 2、外键约束 7.5 格式： 数据库的完整性 FOREIGN KEY （<属性名1> [，<属性名2>…]） REFRENCES 基本表（<属性名1> [，<属性名2>…]） 3、自定义 NOT NULL UNIQUE 类型 长度 CHECK….

（ SNO CHAE（4） NOT NULL UNIQUE， （ CNO CHAE（4） NOT NULL UNIQUE， 例：建立学生、课程、选课表 CREATE TABLE S （ SNO CHAE（4） NOT NULL UNIQUE， SNAME CHAR（8）， SSEX CHAR（2）， SAGE INT， PRIMARY KEY （SNO）） 7.5.3 SQL中的完整性约束 CREATE TABLE C （ CNO CHAE（4） NOT NULL UNIQUE， CNAME CHAR（8）， PRIMARY KEY （CNO））

FOREIGN KEY(SNO) REFRENCES S(SNO), FOREIGN KEY (CNO)REFRENCES C(CNO)， CREATE TABLE SC （ SNO CHAE(4) NOT NULL， CNO CHAR(4) NOT NULL， GRADE INT， PRIMARY KEY(SNO,CNO), 7.5.3 FOREIGN KEY(SNO) REFRENCES S(SNO), SQL中的完整性约束 FOREIGN KEY (CNO)REFRENCES C(CNO)， CHECK （GRADE IS NULL） OR （GRADE BETWEEN 0 AND 100）)

7.5.4 触发器 SQL Server提供了两种主要机制来强制实施数据库的完整性：约束和触发器。 触发器可以实现比check约束更为复杂的完整性约束。 触发器：是一种特殊的存储过程，它也定义了一组Transact-SQL语句，用于完成某项任务。在用户试图更新触发器保护的数据时自动执行。 7.5.4 触发器

触发器的主要作用是能实现复杂的完整性控制。主要功能有： 1.强化约束：触发器能实现比check子句更为复杂的约束； 2.保证参照完整性 :能够实现主键和外键所不能保证的参照完整性和数据一致性。 3.级联运行：触发器可以侦测数据库内的操作，并自动级联相关的整个数据库对象的各项内容。比如一张表的触发器可以涉及多张表的操作。 7.5.4 触发器

4.跟踪变化：触发器可以侦测数据库内的操作，从而阻止未经许可的更新操作和访问； 5.调用存储过程：为了实现数据库更新，触发器可以调用一个或多个存储过程。 创建触发器时需指定： 名称 在其上定义触发器的表 触发器将何时激发 激活触发器的数据修改语句 7.5.4 触发器

触发器是在单个表或视图上定义，这个表称为触发器表。每个表上可以建若干触发器。 (2) 是自动激活的。 (1) 总是与一个表相连。 触发器是在单个表或视图上定义，这个表称为触发器表。每个表上可以建若干触发器。 (2) 是自动激活的。 当对一个表中数据执行插入、删除、修改操作时，如果对该表上这个操作定义了触发器，则该触发器自动执行，这是不可阻挡的。 (3) 不能直接调用 与标准的存储过程不同，触发器不能直接调用，也不能传递或接受参数。 (4) 是一个事务 触发器和激活它的语句作为一个事务处理，可以从触发器中的任何位置撤销。触发器可以包括ROLLBACK TRANSACTION语句，激活触发器的语句可以看成隐含事务的开始。 7.5.4 触发器

[WITH ENCRYPTION ] --加密存储触发器定义 FOR|AFTER|INSTEAD OF –触发时间 1、 创建触发器 1. 语法格式 CREATE TRIGGER 触发器名 ON 表 --给定建立触发器的表 [WITH ENCRYPTION ] --加密存储触发器定义 FOR|AFTER|INSTEAD OF –触发时间 [INSERT][,][DELETE][,][UPDATE] -触发事件 AS 触发器要完成的操作 --触发后的动作 7.5.4 触发器

AFTER：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。 说明： AFTER：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。 INSTEAD OF：指定执行触发器而不是执行“触发 SQL 语句”，从而替代“触发语句”的操作。对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。 WITH ENCRYPTION：加密存储触发器存放在syscomments中的文本。 7.5.4 触发器

创建触发器的权限默认为表的所有者，且不能将该权限转让给其它用户。 不能在临时表上创建触发器，但在触发器中可引用临时表。 注意： 创建触发器的权限默认为表的所有者，且不能将该权限转让给其它用户。 不能在临时表上创建触发器，但在触发器中可引用临时表。 触发器允许嵌套。最大嵌套数32。 触发器中不允许有下列语句： ALTER DATABASE, CREATE DATABASE, DISK INIT, DISK RESIZE, DROP DATABASE, LOAD DATABASE等等。 7.5.4 触发器

deleted和inserted 表: 当用户对触发器表执行更新操作时，SQL Server自行为每个触发器创建和管理这两个表。它们是逻辑表，存放在内存中，用户不能直接对这两个表进行修改。这两个表的结构与触发器表的结构相同。触发工作完成后，与触发器相关的这两个表将被删除。 7.5.4 触发器

1. inserted 表 inserted表用于存储INSERT和UPDATE语句所影响的行的副本。在一个插入或更新事务处理中，新建行被同时添加到inserted表和触发器表中。 inserted表中的行是触发器表中新行的副本。 2. deleted 表 deleted表用于存储DELETE和UPDATE语句所影响的行的副本。在执行DELETE或UPDATE语句时，行从触发器表中删除，并传送到deleted 表， deleted 表和触发器表一般没有相同的行。 7.5.4 触发器

首先执行INSERT语句的插入操作。将新行插入到触发器表和inserted表中。 插入触发器的执行过程： 首先执行INSERT语句的插入操作。将新行插入到触发器表和inserted表中。 然后执行触发器中的语句。如果执行到ROLLBACK语句，则系统将回滚整个操作。 7.5.4 例7-5-1: 建立一个INSERT触发器。每当在“sc”表中插入一条记录时，检查学号在s表中是否存在，若不存在，拒绝插入，否则允许。 触发器

先建newstudentcourse数据库，s,c,sc表 CREATE TABLE S (SNO CHAR(6), SNAME CHAR(8), SSEX CHAR(2), SAGE SMALLINT, SDEPT CHAR(20)) CREATE TABLE C (CNO CHAR(4), CNAME CHAR(8), CCREDIT SMALLINT) CREATE TABLE SC (SNO CHAR(6), CNO CHAR(4), GRADE INT) 7.5.4 触发器

CREATE TRIGGER sc_insert ON sc FOR INSERT AS BEGIN DECLARE @sno CHAR(6) Select @sno=sno FROM inserted IF NOT EXISTS(SELECT * FROM s WHERE sno=@sno) PRINT '该生不存在！' ROLLBACK TRAN END 7.5.4 触发器

INSERT INTO sc(sno,cno,grade) VALUES('s10002', 'c001',88) 测试： INSERT INTO sc(sno,cno,grade) VALUES('s10002', 'c001',88) VALUES('s10004', 'c001',88) 7.5.4 触发器

例7-5-2: 建立一个触发器,学号必须是数字字符。 create trigger s_sno_numeric on s for insert, update as begin declare @sno char(6) select @sno=inserted.sno from inserted if isnumeric(@sno)=0 print ' 学号必须是数字构成，撤销此插入' delete from s where sno like @sno end 7.5.4 触发器 142

例7-5-3: 建一个触发器，sno必须以S打头。 create trigger s_sno on S for insert, update as begin declare @sno char(10) select @sno=inserted.sno from inserted if @sno not like 's%' print ' 学号必须以S打头，撤销此插入' delete from s where sno like @sno end 测试： insert into s values('s123', 'aaa', '女',20, '计算机') insert into s values('123s', 'bbb', '女',20, '计算机') 7.5.4 触发器 143

例7-5-4: 建立一个触发器,一门功课的选课人数不能大于160. create trigger sc_count on sc for insert as begin declare @cno char(4) select @cno=cno from inserted if 160<(select count(*) from sc where cno=@cno) Print '选课人数已满' rollback transaction end 7.5.4 触发器

首先执行DELETE语句的删除操作。将要删除的记录存放到deleted表中,然后删除触发器表中相应的行。 执行过程: 首先执行DELETE语句的删除操作。将要删除的记录存放到deleted表中,然后删除触发器表中相应的行。 执行触发器中语句。如果执行到ROLLBACK语句，则系统将回滚整个操作。 7.5.4 触发器

例7-5-5:建立一个DELETE触发器，每当在s表中删除一条记录时，也将在sc表中删除相应的记录。 CREATE TRIGGER S_delete ON s FOR DELETE AS BEGIN DECLARE @sno CHAR(6) Select @sno=sno FROM deleted IF EXISTS(SELECT * FROM sc WHERE sno=@sno) DELETE FROM sc WHERE sno=@sno PRINT '成功删除! ' END 7.5.4 触发器

DELETE FROM s WHERE sno= ‘s10001' 测试： DELETE FROM s WHERE sno= ‘s10001' 7.5.4 触发器

首先执行UPDATE语句。将修改前的旧行插入到deleted表中,再修改触发器表中该行信息, 将修改后的新行插入到inserted表。 执行过程： 首先执行UPDATE语句。将修改前的旧行插入到deleted表中,再修改触发器表中该行信息, 将修改后的新行插入到inserted表。 执行触发器中的语句。如果执行到ROLLBACK语句，则系统将回滚整个操作。 7.5.4 触发器

例7-5-6:建立一个UPDATE触发器，每当在sc表中修改一条 记录时，也将在c_avg表中更新相应的记录。 c_avg(cno, avg_grade) create table c_avg (cno char(4) , avg_grade int ) insert into c_avg select cno, avg(grade) as 平均成绩 from sc group By cno 7.5.4 触发器

CREATE TRIGGER sc_update ON sc FOR insert,UPDATE AS DECLARE @cno CHAR(4) Select @cno=inserted.cno FROM inserted IF NOT EXISTS(SELECT * FROM c_avg WHERE cno = @cno) BEGIN INSERT INTO C_avg SELECT cno, AVG(grade) FROM sc WHERE cno= @cno GROUP BY cno PRINT ‘新增加了一条课程记录！ ’ --也可以如下页 END ELSE UPDATE C_avg SET avg_grade=(SELECT AVG(grade) FROM sc WHERE cno= @cno) WHERE cno= @cno PRINT '成功更新课程的平均成绩！ ' 7.5.4 触发器

DECLARE @cno CHAR(4) declare @grade int Select @cno=inserted.cno FROM inserted Select @grade=inserted.grade from inserted IF NOT EXISTS(SELECT * FROM c_avg WHERE cno = @cno) BEGIN INSERT INTO C_avg values(@cno,@grade) PRINT '新增加了一条课程记录！ ' END 7.5.4 触发器

方法1：使用系统过程：Sp_help、Sp_helptext、Sp_depends查看触发器相关信息。 1. 查看触发器 方法1：使用系统过程：Sp_help、Sp_helptext、Sp_depends查看触发器相关信息。 触发器创建后，与存储过程一样，名字存放在sysobjects表，定义文本存放在syscomments中。 2. 修改触发器 方法：在查询分析器中，选定表，在其上打开快捷菜单，选择“修改”菜单命令。 3. 删除触发器 在选定触发器，按“删除”键即可。 或 使用命令：DROP TRIGGER 触发器名 7.5.4 触发器

小 结 1、完整性的含义 2、SQL中的完整性 7.5 数据库的完整性

作 业 P164 1、2、3、6 7.5 数据库的完整性 本章结束

Click to edit company slogan . www.pptbz.com Thank You ! Click to edit company slogan .