教学网站： http://swpu.fy.chaoxing.com 数据库及应用 授课教师：岳静 Tel：13551817786 E-mail: qiuqiuyj@163.com 教学网站： http://swpu.fy.chaoxing.com 123

内容提要 实现触发器 实现事务 并发控制 第7章

思考 什么是触发器？

触发器 insert 触发器 delete 其他操作 update

触发器的类型 插入触发器 删除触发器 更新触发器 DML触发器 Create Alter Drop DDL触发器

魔表 Cno Cname Ccredit Semester c01 计算机文化学 3 1 c02 VB 2 c03 计算机网络 4 7 2 deleted Cno Cname Ccredit Semester c02 VB 2 3 inserted Cno Cname Ccredit Semester c02 VB 2

创建触发器 触发器： 使用CREATE TRIGGER语句来创建触发器 语法： CREATE TRIGGER trigger_name ON { OBJECT NAME } { FOR | AFTER | INSTEAD OF } { event_type [ ,...n ] | DDL_DATABASE_LEVEL_EVENTS } { AS { sql_statement [ ...n ] } } In this slide, you need to explain creating triggers to the students. Example: CREATE TRIGGER [HumanResources].[trgDepartment] ON [HumanResources].[Department] AFTER UPDATE AS BEGIN UPDATE [HumanResources].[Department] SET [HumanResources].[Department].[ModifiedDate] = GETDATE() FROM inserted WHERE inserted.[DepartmentID] = [HumanResources].[Department].[DepartmentID]; END; CG NOTE: For demonstration of this example, you can use the create_trDepartment.sql file from the Datafiles_for_faculty\Chapter 8 folder in the TIRM CD. In this file, you will find the code to create the trigger as well as to create the trgMagic trigger and the update statement to verify the trigger. FAQ: Q: When does a trigger get executed? After a DML (update, insert, or delete) transaction. Q: If there exists a trigger and a rule, which will get executed first? The rule will get executed first. Additional Inputs The maximum nesting level for triggers is 32. You cannot create triggers on system tables. Triggers unlike stored procedures do not return values or result sets. If multiple business rules need to be applied when a DML operation is underway use multiple triggers for implementing the same. For example, if three columns are being updated and different business rules have to be applied for each, use three different update triggers for each business rule. SQL Server allows recursive triggers. Recursion occurs when the same trigger gets executed again and again. There are two types of recursion, direct and indirect. For example, if an application updates table T3, the trigger TRIG3 defined on the table for update gets executed. This trigger again does an updation on the table T3, thereby, re-executing the trigger TRIG3. This is an example of direct recursion. If an application updates table T3, the trigger TRIG3 defined on the table for update gets executed. This trigger updates another table T4, this executes trigger TRIG4 defined for update on the table. TRIG4 updates table T3 thereby executing TRIG3. This is an example of indirect recursion. To enable recursive triggers for a particular database, issue the following command: sp_dboption <databasename>, ‘recursive triggers’, True.

更新触发器 create trigger trg1 on Course after update as begin select * from Deleted select * from Inserted end

每次修改Course表时，自动将ModifiedDate设为当前时间 思考 每次修改Course表时，自动将ModifiedDate设为当前时间

更新触发器 create trigger trg2 on Course after update as begin update Course set ModifiedDate=getdate() from inserted where Course.cno=inserted.cno end create trigger HumanResources.trgInsertShift on for insert as begin declare @ModifiedDate datetime select @ModifiedDate=ModifiedDate from inserted if @ModifiedDate!=getdate() print 'The Modified Date should be the current date. ' rollback transaction end

后触发器 create trigger trg3 on Course after delete as 若要禁止删除，该怎么办？ 后触发器 create trigger trg3 on Course after delete as print 'Deletion Successful'

删除触发器 create trigger HumanResources.trgDelete on HumanResources.Department after delete as print 'Deletion of the Department is not allowed' rollback transaction return Instaed of delete

管理触发器 改变触发器： 删除触发器： DROP TRIGGER { trigger } ALTER TRIGGER trigger_name { FOR | AFTER } { event_type [ ,...n ] | DDL_DATABASE_LEVEL_EVENTS } { AS { sql_statement [ ...n ] } } 删除触发器： DROP TRIGGER { trigger } In this slide, you need to explain managing the triggers to the students. State that managing trigger includes altering the trigger and deleting a trigger. Example: ALTER TRIGGER HumanResources.trgInsertShift ON HumanResources.Shift FOR INSERT AS DECLARE @ModifiedDate datetime SELECT @ModifiedDate = ModifiedDate FROM Inserted IF (@ModifiedDate != getdate()) BEGIN RAISERROR (’The modified date is not the current date. The transaction cannot be processed.',10, 1) ROLLBACK TRANSACTION END RETURN CG Input: For demonstration use the alter_trgInsertShift.sql file in the datafiles_for_faculty\chapter 8 folder in the TIRM CD. DROP TRIGGER HumanResources.trgMagic

转账 账户A-1000 账户B +1000 Phase 1 Phase 2 正常的情况下，这些操作将顺利进行，最终交易成功，与交易相关的所有数据库信息也成功地更新。但是，如果在这一系列过程中任何一个环节出了差错，例如在更新商品库存信息时发生异常、该顾客银行帐户存款不足等，都将导致交易失败。一旦交易失败，数据库中所有信息都必须保持交易前的状态不变，比如最后一步更新用户信息时失败而导致交易失败，那么必须保证这笔失败的交易不影响数据库的状态--库存信息没有被更新、用户也没有付款，订单也没有生成。否则，数据库的信息将会一片混乱而不可预测。 　　数据库事务正是用来保证这种情况下交易的平稳性和可预测性的技术。

思考 万一交易过程中发生异常，怎么办？ 事务

概念 事务 事务是一种机制、是一种操作序列，它包含了一组数据库操作命令，这组命令要么全部执行，要么全部不执行

事务的特性 一致性 隔离性 持久性 原子性 事务是一个完整的操作； 对于其数据修改，要么全都执行，要么全都不执行。 Atomicity Consistency Isolation Durability 一致性 隔离性 持久性 原子性 事务是一个完整的操作； 对于其数据修改，要么全都执行，要么全都不执行。 事务在完成时，必须使所有的数据都保持一致状态。 对数据进行修改的所有并发事务是彼此隔离的。 事务完成之后，它对于系统的影响是永久性的。即使出现故障也将一直保持。

事务的特性 2个动作， 1是A账号减去100块，2是B账号增加100块钱 ，2个动作不可分割——原子性。 如果当B账号钱没有增加的时候， 那么A账号的钱不应该减少， 保持一致性。 一旦转账成功就不能撤销了，数据将真正写入到表中——永久性。 当我在更新的时候 其他用户不能进行修改, 隔离性。

事务的特征 保证事务的ACID特性是事务处理的重要任务。事务的ACID特性可能遭到破坏的因素有： 多个事务并行运行时，不同事务的操作有交叉情况 事务在运行过程中被强迫停止

事务的两种方式 事 务 隐式事务 显式事务

ISO事务处理模型 UPDATE 支付表 SET 帐户总额 ＝ 帐户总额 － n WHERE 帐户名 ＝ ‘A’ WHERE 帐户名 ＝ ‘B’ COMMIT

特点 事务的开头是隐含的，事务的结束有明确标记 事务结束符 COMMIT：事务成功结束符 ROLLBACK：事务失败结束符 事务提交方式自动提交：每条SQL语句为一个事务指定位置提交；在事务结束符或程序正常结束处提交

T-SQL事务处理模型 Begin transaciton trans_name Commit transaciton trans_name 开始 事务 Begin transaciton trans_name 提交 事务 Commit transaciton trans_name 回滚 事务 RollBack transaciton trans_name

示例 例如：前边的转帐例子用Transact-SQL事务处理模型描述为： BEGIN TRANSACTION UPDATE 支付表 SET 帐户总额 ＝ 帐户总额－n WHERE 帐户名 ＝ ‘A’ UPDATE 支付表 SET 帐户总额 ＝ 帐户总额＋n WHERE 帐户名 ＝ ‘B’ COMMIT

并发控制概述 用户1 用户2 数据 用户3 用户4

并发事务的相互干扰示例 A、B两个订票点恰巧同时办理同一架航班的飞机订票业务。设其操作过程及顺序如下： A订票点（事务A）读出航班目前的机票余额数，假设为10张； B订票点（事务B）读出航班目前的机票余额数，也为10张； A订票点订出6张机票，修改机票余额为10－6＝4，并将4写回到数据库中； B订票点订出5张机票，修改机票余额为10－5＝5，并将5写回到数据库中；

数据不一致 B事务覆盖了A事务对数据库的修改，使数据不可信，此情况就称为数据的不一致，它由并发操作引起 并发操作带来的数据不一致包括：丢失修改、不可重复读、读“脏”数据、产生“幽灵”数据

丢失数据修改

读“脏”数据

不可重复读

产生“幽灵”数据 属于不可重复读的范畴； 指当事务T1按一定条件从数据库中读取了某些数据记录后，事务T2删除了其中的部分记录，或者在其中添加了部分记录，则当T1再次按相同条件读取数据时，发现其中莫名其妙地少了（对删除）或多了（对插入）一些记录； 这样的数据对T1来说就是“幽灵”数据。

并发控制措施 控制目标：事务运行过程中尽可能隔离事务外操作对本事务数据环境的影响 在数据库环境下，并发控制的主要方式是封锁机制，即加锁（Locking） 加锁是一种并行控制技术，限制事务内和事务外对数据的操作

基本的封锁类型 共享锁（S）：指对于读操作（检索）来说，可以多个事务同时获得共享锁，但阻止其它事务对已获得共享锁的数据进行排它封锁 。

共享锁（S） select * from Student where sno=9512101 Sname Ssex Sage Sdept 9512101 李勇 男 19 计算机系 9512102 刘晨 20 9512103 王敏 女 计算机 select * from Student where sno=9512101 select * from Student where sno=9512101

示例 begin tran select * from dbo.Student where Sno=9512101 等待5秒 begin tran select * from dbo.Student where Sno=9512101 waitfor delay '00:00:5' commit tran begin tran select * from dbo.Student where Sno=9512101 commit tran

基本的封锁类型 排它锁(X)：一旦一事务获得了对某一数据的排它锁，则任何其它事务再不能对该数据进行排它封锁，其它事务只能进入等待状态，直到第一个事务撤销了对该数据的封锁。

× 排他锁（X） Update Student set sage=sage+1 where sno=9512101 Sname Ssex Sage Sdept 9512101 李勇 男 19 计算机系 9512102 刘晨 20 9512103 王敏 女 计算机 Update Student set sage=sage+1 where sno=9512101 × Update Student set sage=sage+1 where sno=9512101

示例 begin tran update Student set sage=sage+1 where Sno=9512101 waitfor delay '00:00:5' commit tran 等待5秒 select * from dbo.Student where Sno=9512101

排它锁和共享锁的控制方式 T2 T1 X S 无锁 No Yes

通过以下隔离等级约束锁： READ UNCOMMITTED（读未提交） READ COMMITED （读已提交） REPEATABLE READ （可重复读） SERIALIZABLE （串行化） 为了避免上面出现的几种情况，在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。 ●   未授权读取（Read Uncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。 ●   授权读取（Read Committed）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。 ●   可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻影数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。 ●   序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。        隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、虚读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。 通过前面的介绍已经知道，通过选用不同的隔离等级就可以在不同程度上避免前面所提及的在事务处理中所面临的各种问题。所以，数据库隔离级别的选取就显得尤为重要，在选取数据库的隔离级别时，应该注意以下几个处理的原则：        首先，必须排除“未授权读取”，因为在多个事务之间使用它将会是非常危险的。事务的回滚操作或失败将会影响到其他并发事务。第一个事务的回滚将会完全将其他事务的操作清除，甚至使数据库处在一个不一致的状态。很可能一个已回滚为结束的事务对数据的修改最后却修改提交了，因为“未授权读取”允许其他事务读取数据，最后整个错误状态在其他事务之间传播开来。        其次，绝大部分应用都无须使用“序列化”隔离（一般来说，读取幻影数据并不是一个问题），此隔离级别也难以测量。目前使用序列化隔离的应用中，一般都使用悲观锁，这样强行使所有事务都序列化执行。        剩下的也就是在“授权读取”和“可重复读取”之间选择了。我们先考虑可重复读取。如果所有的数据访问都是在统一的原子数据库事务中，此隔离级别将消除一个事务在另外一个并发事务过程中覆盖数据的可能性（第二个事务更新丢失问题）。这是一个非常重要的问题，但是使用可重复读取并不是解决问题的唯一途径。        假设使用了“版本数据”，Hibernate会自动使用版本数据。Hibernate的一级Session缓存和版本数据已经为你提供了“可重复读取隔离”绝大部分的特性。特别是，版本数据可以防止二次更新丢失的问题，一级Session缓存可以保证持久载入数据的状态与其他事务对数据的修改隔离开来，因此如果使用对所有的数据库事务采用授权读取隔离和版本数据是行得通的。        “可重复读取”为数据库查询提供了更好的效率（仅对那些长时间的数据库事务），但是由于幻影读取依然存在，因此没必要使用它（对于Web应用来说，一般也很少在一个数据库事务中对同一个表查询两次）。        也可以同时考虑选择使用Hibernate的二级缓存，它可以如同底层的数据库事务一样提供相同的事务隔离，但是它可能弱化隔离。假如在二级缓存大量使用缓存并发策略，它并不提供重复读取语义（例如，后面章节中将要讨论的读写，特别是非严格读写），很容易可以选择默认的隔离级别：因为无论如何都无法实现“可重复读取”，因此就更没有必要拖慢数据库了。另一方面，可能对关键类不采用二级缓存，或者采用一个完全的事务缓存，提供“可重复读取隔离”。那么在业务中需要使用到“可重复读取”吗？如果你喜欢，当然可以那样做，但更多的时候并没有必要花费这个代价

实现隔离 使用SET TRANSACTION ISOLATION LEVEL语句实现锁 语法： READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE } [ ;] BEGIN TRANSACTION ……… COMMIT TRANSACTION Example: SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION TR BEGIN TRY UPDATE Person.Contact SET EmailAddress='jolyn@yahoo.com' WHERE ContactID = 1070 UPDATE HumanResources.EmployeeAddress SET AddressID = 32533 WHERE EmployeeID = 1 COMMIT TRANSACTION TR SELECT 'Transaction Executed' END TRY BEGIN CATCH ROLLBACK TRANSACTION TR SELECT 'Transaction Rollbacked' END CATCH Additional Inputs Timeouts can be used to prevent deadlocks. For multiple transactions running simultaneously on a SQL Server, you can define their isolation level to balance between concurrency and data integrity. By choosing the right transaction, isolation level can improve performance of the SQL Server queries. There are four transaction isolation levels: Read Committed This is the default isolation level. Read Uncommitted The restriction in this isolation level is the least as there are no shared or exclusive locks. This allows data updates before the transaction is over. Repeatable Read In this isolation level, rows can be added but existing data cannot be updated. Serializable Data integrity is the highest in this isolation level but concurrency between transactions is very low. Data involved in this transaction isolation level is locked. Transactions with this isolation level execute one by one. The syntax for setting transaction isolation levels is shown below: SET TRANSACTION ISOLATION LEVEL {READ COMMITTED|READ UNCOMMITTED|REPEATABLE READ|SERIALIZABLE} When an INSENSETIVE cursor is created SQL Server stores the result set of the cursor in a temporary table in the tempdb database. This result set does not get updated with changes made to the base table(s). Apart from this the cursor itself is a read-only cursor i.e. it cannot be updated. The following code declares an INSENSETIVE cursor: DECLARE curPublishers CURSOR FOR SELECT * FROM publishers OPEN curPublishers FETCH NEXT FROM curPublishers <<<example needs to be changed>>>

更新某学生信息时，不想其他事务读取未提交的记录，该如何处理? 思考 更新某学生信息时，不想其他事务读取未提交的记录，该如何处理?

示例 连接1： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION SELECT * FROM dbo.Student WAITFOR DELAY '00:00:05' SELECT * FROM Student COMMIT 连接2： UPDATE Student SET Sage = Sage + 10 WHERE Sno= 9512101;

封锁协议 在运用X锁和S锁对数据对象进行加锁时，还需要约定一些规则，如何时申请X锁或S锁、持锁时间、何时释放锁等,这些规则为封锁协议或加锁协议（Locking Protocel） 对封锁方式规定不同的规则，就形成了各种不同级别的封锁协议 不同级别的封锁协议达到的系统一致性级别不同

一级封锁协议 对事务T要修改的数据加X锁，直到事务结束（包括正常结束和非正常结束）时才释放 但不能保证可重复读和不读“脏”数据

一级封锁协议示例

思考 为什么不能保证可重复读和不读“脏”数据 不能保证可重复读？ 不能保证不读“脏数据”？ 读数据 修改 数据 x T1 T2 读出错误数据

二级封锁协议 一级封锁协议加上对事务T对要读取的数据加S锁，读完后即释放S锁 除了可以防止丢失修改外，还可以防止读“脏”数据 但不能保证可重复读数据

二级封锁协议示例

三级封锁协议 一级封锁协议加上事务T对要读取的数据加S锁，并直到事务结束才释放 除了可以防止丢失修改和不读“脏”数据之外，还进一步防止了不可重复读

三级封锁协议示例

不同级别的封锁协议总结

死锁 两个事务相互等待对方先释放资源，则会造成死锁。

预防死锁的方法 一次封锁法 顺序封锁法 一次封锁法是每个事务一次将所有要使用的数据全部加锁 顺序封锁法是预先对数据对象规定一个封锁顺序，所有事务都按这个顺序封锁

解决死锁的方法 设置死锁优选级 为探测死锁的情况，SQL Server扫描在等待锁请求的会话 SQL Server提供SET DEADLOCK_PRIORITY命令来定制死锁

解决死锁的方法 定制LOCK_TIMEOUT SET LOCK_TIMEOUT命令可被用来设置等待被阻塞资源语句的最长时间。

并发调度的可串行性 多个事务的并发执行是正确的，当且仅当其结果与按某一顺序的串行执行的结果相同，则我们称这种调度为可串行化的调度 可串行性是并发事务正确性的准则

事务T1：读B；A=B+1;写回A； 事务T2：读A；B=A+1;写回B； 假设A、B初值均为4，则按T1->T2的顺序执行，其结果为A=5，B=6 若按照T2->T1的顺序执行，其结果为A=6，B=5。 并发调度的执行结果是这种之一，则是正确的。

数据库备份与恢复 数据库故障的种类 数据库备份 数据库恢复

数据库故障的种类 事务内部的故障 系统故障 其它故障 事务故障意味着事务没有达到预期的终点（COMMIT或ROLLBACK），因此，数据库可能处于不正确的状态 系统故障 指造成系统停止运转、系统要重启的故障。例如，硬件错误（CPU故障）、操作系统故障、突然停电等 其它故障 介质故障或由计算机病毒引起的故障或破坏

数据库备份 指定期或不定期地对数据库数据进行复制 可以复制到本地机器上，也可以复制到其它机器上。 备份的介质可以是磁带也可以是磁盘，但通常选用磁带 是保证系统安全的一项重要措施

数据库恢复 1．恢复策略 事务故障的恢复 系统故障的恢复 介质故障的恢复 2．恢复方法 利用备份技术 利用事务日志 利用镜像技术

课堂练习 有3个事务T1、T2、T3，所包含的动作为： T1:A=A+2 T2:A=A*2 T3:A=A*A

小结 实现触发器 实现事务 并发控制

Thank You ! www.themegallery.com