《第7章事务、存储过程、触发器和游标》由会员分享，可在线阅读，更多相关《第7章事务、存储过程、触发器和游标（157页珍藏版）》请在金锄头文库上搜索。
1、第7章 事务、存储过程、触发器和游标,7.1 事 务 7.2 存储过程 7.3 触发器及其用途 7.4 游标,7.1 事 务,1、事务的概念 事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。,2、事务的特性,原子性：事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。 一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。 隔离性：一事务的执行不能被其它事务干扰 持续性（永久性）：指事务一旦提交，则其对数据库中数据的改变就应该是永久的,3、事务的分类,SQL Server 的事务模式可分为显式事务、隐式事务和自
2、动事务三种。 1) 显式事务 显式事务是指由用户执行T-sql事务语句而定义的事务，这类事务又称做用户定义事务。定义事务的语句包括： BEGIN TRANSACTION:标识一个事务的开始，即启动事务。 COMMIT TRANSACTION、COMMIT WORK：标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。 ROLLBACK TRANSACTION、ROLLBACK WORK：标识一个事务的结束，说明事务执行过程中遇到错误，事务内所修改的数据被回滚到事务执行前的状态。,2) 隐式事务 在隐式事务模式下，在当前事务提交或回滚后，SQL Server自动开始下一个事务。所以，隐式
3、事务不需要使用BEGIN TRANSACTION语句启动事务，而只需要用户使用ROLLBACK TRANSACTION、ROLLBACK WORK、COMMIT TRANSACTION、COMMIT WORK等语句提交或回滚事务。在提交或回滚后，SQL Server自动开始下一个事务。 执行SET IMPLICIT_TRANSACTIONS ON语句可使SQL Server进入隐式事务模式。在隐式事务模式下，当执行下面任意一个语句时，可使SQL Server重新启动一个事务： 所有CREATE语句 ALTER TABLE 所有DROP语句 TRUNCATE TABLE GRANT REVOKE
4、 INSERT UPDATE DELETE SELECT OPEN FETCH 需要关闭隐式事务模式时，调用SET语句关闭IMPLICIT_TRANSACTIONS 连接选项即可。,3) 自动事务模式 在自动事务模式下，当一个语句被成功执行后，它被自动提交，而当它执行过程中产生错误时，被自动回滚。自动事务模式是SQL Server的默认事务管理模式，当与SQL Server建立连接后，直接进入自动事务模式，直到使用BEGIN TRANSACTION语句开始一个显式事务，或者打开IMPLICIT_TRANSACTIONS 连接选项进入隐式事务模式为止。 而当显式事务被提交或IMPLICIT_TR
5、ANSACTIONS 被关闭后，SQL Server又进入自动事务管理模式。,示例：,BEGIN TRAN demo SELECT * FROM Student INSERT INTO Student VALUES(9711112,张三, ) SELECT * FROM Student ROLLBACK - 回滚整个事务 或：COMMIT - 提交事务,下面例子说明自动事务模式下各语句的执行情况和SQL Server对批的处理： -SQL Server处于自动事务管理模式 Use pubs Go Create table TB_transaction1 (col1 INT PRIMARY KE
6、Y, col2 datetime) Go INSERT TB_transaction1 VALUES(1,GETDATE() INSERT TB_transaction1 VALUES(1,GETDATE()-违反约束 Go,SELECT times=1,* from TB_transaction1 Go Begin tran-进入显示事务模式 INSERT TB_transaction1 VALUES(2,GETDATE() SELECT times=2,* from TB_transaction1 INSERT TB_transaction1 VALUE(3,GETDATE()-语法错误
7、Rollback Go SELECT times=2,* from TB_transaction1 -此时，又重新进入自动事务模式 Go,SET IMPLICIT_TRANSACTIONS ON进入隐含事务模式 go insert TB_transaction1 VALUES(3,GETDATE() insert TB_transaction1 VALUES(4,GETDATE() rollback go select * from TB_transaction1 delete from TB_transaction1 rollback select * from TB_transaction
8、1 set implicit_transactions off delete from TB_transaction1 rollback-此时该命令不成功，因为已经进入自动事务模式 go,4、并发问题与事务隔离,在大型分布式数据库应用程序中，对数据库的并发访问操作是一个普遍存在的问题。SQL Server使用资源锁定的方法管理用户的并发操作。如果在用户并发访问期间没有锁定数据库资源，用户操作相同的数据时可能会产生一些意想不到的问题。这些问题包括： 。丢失修改或被覆盖 。读脏数据 。不能重复读：一个事物多次访问同一行数据而每次所读取的数据是不同的。 。幻影读：是指一个事务多次读取一定范围内的数据
9、行，而前后两次所读取的数据行是不同,为了避免产生并发访问问题，SQL Server使用不同类型的锁对资源进行锁定，从而限制在一个事务读取数据期间其他事务锁执行的操作类型，即对事务进行隔离。不同的并发访问问题可以通过设置不同的事务隔离级别加以解决。事务的隔离级别控制一个事务与其他事务的隔离程度，它决定该事务在读取数据时对资源所使用的锁类型。,SQL-92标准定义了以下4种隔离级别： 未提交读：这是4种隔离级别中限制最低的级别，它仅能保证SQL Server不读取物理损坏的数据。在这种隔离级别下，不发出共享锁，也不接受排它锁，事务可以对数据执行未提交读或脏读；在事务结束前可以更改数据集内的数值，行
10、也可以出现在数据集中或从数据集消失。 提交读：它要求在读取数据时控制共享锁以避免发生脏读，但数据可在事务结束前更改，这可能产生不能重复读或幻影读问题。 可重复读：锁定查询中使用的所有数据以防止其他用户更新，但是其他用户可以将新的幻影行插入到数据集中，新插入的幻影行将出现在当前事物的后续读取结果集中。可重复读能够避免产生脏读和非重复读问题，但仍可能导致幻影读问题。 可串行读：这是事务隔离的最高级别，它使事务之间完全隔离，所以将导致并发级别较低。在这种隔离级别下，SQL Server在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或向数据集内插入数据行，从而避免出现脏读、非重复读或
11、幻影读等并发问题。,调用Transact-SQL中的SET TRANSACTION INOLATION LEVEL语句可以调整事务的隔离级别，以控制由该连接所发出的所有SELECT语句的默认事务锁定行为。该语句的语法格式为： SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED |READ COMMITTED |REPEATED READ |SERIALIZATION ,5. 事务处理语句,SQL Server中有关事务的处理语句有：,关于事务保存点,保存点提供了一种机制，用于回滚部分事务。在应用程序中，使用SAVE TRANSACTION save
12、point_name语句设置保存点，使用ROLLBACK TRANSACTION savepoint_name将事务回滚到保存点。,关于嵌套事务： 说明：在定义一个事务时，BEGIN TRANSACTION语句应与COMMIT TRANSACTION语句或ROLLBACK TRANSACTION成对出现。在SQL Server中，显示事务定义语句可以嵌套.一个嵌套的事务是一系列子事务ti的集合, T=t1,t2,tn,这些子事务中的每一个又可以是拥有它自己的事务.T能够决定子事务ti的启动和终止,反过来,如果T中的一个子事务ti终止,它强制T终止;如拖ti提交,这一动作并不能使ti成为永久的,
13、如果T终止(回滚),那么ti的提交将被撤消. 也就是说，SQL Server忽略内部事务的提交，根据最外部事务结束时采取的操作，将提交或者回滚事务。如果提交外部事务，则内层嵌套的事务也会提交。如果回滚外部事务，则不论此前是否提交过内层事务，所有内层事务都将回滚。,但实际上只有最外层的BEGIN TRANSACTION语句和COMMIT TRANSACTION语句才能建立和提交事务；在回滚事务时，也只能使用最外层定义的事务名或存储点标记，而不能使用内层定义的事务名。事务嵌套常用在存储过程或触发器内，它们可以使用BEGIN TRANSACTION 。COMMIT TRANSACTION对来相互调用
14、。,6. 事务处理实例,例1、 Use pubs go BEGIN TRANSACTION demo SELECT * FROM discounts INSERT discounts VALUES(demo1,null,null,null,20.0) SAVE TRANSACTION save_demo INSERT discounts VALUES(demo2,null,null,null,20.0) SELECT * FROM discounts ROLLBACK TRANSACTION save_demo回滚部分事务 SELECT * FROM discounts ROLLBACK TR
15、ANSACTION回滚整个事务 SELECT * FROM discounts,例2、事务嵌套 use pubs go BEGIN TRANSACTION demo SELECT * FROM discounts INSERT discounts VALUES(demo1,null,null,null,20.0) begin TRANSACTION demo1 INSERT discounts VALUES(demo2,null,null,null,20.0) SELECT * FROM discounts ROLLBACK TRANSACTION demo1 SELECT * FROM di
16、scounts ROLLBACK TRANSACTION-出错，因为相应事务已经被回滚 SELECT * FROM discounts,例3、 USE pubs GO CREATE TABLE TB_transaction2 (col1 int IDENTITY, col2 char(10) GO SET IMPLICIT_TRANSACTIONS ON GO INSERT TB_ transaction2 VALUES(row1) INSERT TB_ transaction2 VALUES(row2) Go COMMIT TRANSACTION PRINT 第一个隐式事务所插入的数据: SELECT * FROM TB_ transaction2 GO INSERT TB_ transaction2 VALUES(row3) GO,PRINT 第一、二个隐式事务所插入的数据： SELECT * FROM TB_ transaction2 GO ROLLBACK TRA
