第7章事务、存储过程、触发器和游标.ppt

1、第第第第7 7 7 7章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标第第7章章 事务、存储过程、触发器和游标事务、存储过程、触发器和游标7.1事事务务7.2存储过程存储过程7.3触发器及其用途触发器及其用途7.4游标游标第第第第7 7 7 7章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标7.1事事务务1、事务的概念、事务的概念事务是一个用户定义的完整的工作单元，事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要一个事务内的所

2、有语句被作为整体执行，要么全部执行，要么全部不执行。么全部执行，要么全部不执行。第第第第7 7 7 7章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标2 2、事务的特性、事务的特性、事务的特性、事务的特性 原子性：原子性：事务是数据库的逻辑工作单位，事务事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。中的操作要么都做，要么都不做。一致性：一致性：事务执行的结果必须是使数据库从一事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。个一致性状态变到另一个一致性状态。隔离性：隔离性：一事务的执行不能被

3、其它事务干扰一事务的执行不能被其它事务干扰持续性（永久性）：持续性（永久性）：指事务一旦提交，则其对指事务一旦提交，则其对数据库中数据的改变就应该是永久的数据库中数据的改变就应该是永久的第第第第7 7 7 7章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标3 3、事务的分类、事务的分类、事务的分类、事务的分类SQLServer的事务模式可分为的事务模式可分为显式事务显式事务、隐式事务隐式事务和和自动事务自动事务三种。三种。1)显式事务显式事务显式事务是指由用户执行显式事务是指由用户执行T-T-sqlsql事务语句而定义的事

4、事务语句而定义的事务，这类事务又称做用户定义事务。定义事务的语句包括：务，这类事务又称做用户定义事务。定义事务的语句包括：BEGINTRANSACTION:标识一个事务的开始，即启标识一个事务的开始，即启动事务。动事务。COMMITTRANSACTION、COMMITWORK：标标识一个事务的结束，事务内所修改的数据被永久保存到数识一个事务的结束，事务内所修改的数据被永久保存到数据库中。据库中。ROLLBACKTRANSACTION、ROLLBACKWORK：标识一个事务的结束，说明事务执行过程中遇到标识一个事务的结束，说明事务执行过程中遇到错误，事务内所修改的数据被回滚到事务执行前的状态。错

5、误，事务内所修改的数据被回滚到事务执行前的状态。第第第第7 7 7 7章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标2)隐式事务隐式事务在隐式事务模式下，在当前事务提交或回滚后，在隐式事务模式下，在当前事务提交或回滚后，SQLServer自动自动开始下一个事务。所以，隐式事务不需要使用开始下一个事务。所以，隐式事务不需要使用BEGINTRANSACTION语句启动事务，而只需要用户使用语句启动事务，而只需要用户使用ROLLBACKTRANSACTION、ROLLBACKWORK、COMMITTRANSACTION、COM

6、MITWORK等语句提交或回滚事务。在提交或回滚后，等语句提交或回滚事务。在提交或回滚后，SQLServer自动开自动开始下一个事务。始下一个事务。执行执行SET IMPLICIT_TRANSACTIONS ONSET IMPLICIT_TRANSACTIONS ON语句可使语句可使SQL ServerSQL Server进入隐进入隐式事务模式。式事务模式。在隐式事务模式下，当执行下面任意一个语句时，可使在隐式事务模式下，当执行下面任意一个语句时，可使SQLServer重新启动一个事务：重新启动一个事务：所有所有CREATE语句语句ALTERTABLE所有所有DROP语句语句TRUNCATET

7、ABLEGRANTREVOKEINSERTUPDATEDELETESELECTOPENFETCH需要关闭隐式事务模式时，调用需要关闭隐式事务模式时，调用SET语句关闭语句关闭IMPLICIT_TRANSACTIONS连接选项即可。连接选项即可。第第第第7 7 7 7章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标3)自动事务模式自动事务模式在自动事务模式下，当一个语句被成功执行后，它被在自动事务模式下，当一个语句被成功执行后，它被自动提交，而当它执行过程中产生错误时，被自动回滚。自动提交，而当它执行过程中产生错误时，被自动

8、回滚。自动事务模式是自动事务模式是SQLServer的默认事务管理模式，的默认事务管理模式，当与当与SQL ServerSQL Server建立连接后，直接进入自动事务模式，直到使建立连接后，直接进入自动事务模式，直到使用用BEGIN TRANSACTIONBEGIN TRANSACTION语句开始一个显式事务，或者打开语句开始一个显式事务，或者打开IMPLICIT_TRANSACTIONS IMPLICIT_TRANSACTIONS 连接选项进入隐式事务模式为止。连接选项进入隐式事务模式为止。而当显式事务被提交或而当显式事务被提交或IMPLICIT_TRANSACTIONS被被关闭后，关闭后

9、，SQLServer又进入自动事务管理模式。又进入自动事务管理模式。第第第第7 7 7 7章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标示例：示例：BEGINTRANdemoSELECT*FROMStudentINSERTINTOStudentVALUES(9711112,张三张三,)SELECT*FROMStudentROLLBACK-回滚整个事务回滚整个事务或：或：COMMIT-提交事务提交事务第第第第7 7 7 7章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储

10、过程、触发器和游标下面例子说明自动事务模式下各语句的执行情况和下面例子说明自动事务模式下各语句的执行情况和SQL ServerSQL Server对批的处理：对批的处理：-SQLServer处于自动事务管理模式处于自动事务管理模式UsepubsGoCreatetableTB_transaction1(col1INTPRIMARYKEY,col2datetime)GoINSERTTB_transaction1VALUES(1,GETDATE()INSERTTB_transaction1VALUES(1,GETDATE()-违反约束违反约束Go第第第第7 7 7 7章事务、存储过程、触发器和游标章

11、事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标SELECTtimes=1,*fromTB_transaction1GoBegintran-进入显示事务模式进入显示事务模式INSERTTB_transaction1VALUES(2,GETDATE()SELECTtimes=2,*fromTB_transaction1INSERTTB_transaction1VALUE(3,GETDATE()-语法错误语法错误RollbackGoSELECTtimes=2,*fromTB_transaction1-此时，又重新进入自动事务模式此时，又重新进入自动事务模式G

12、o第第第第7 7 7 7章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标SETIMPLICIT_TRANSACTIONSON进入隐含事务模式进入隐含事务模式goinsertTB_transaction1VALUES(3,GETDATE()insertTB_transaction1VALUES(4,GETDATE()rollbackgoselect*fromTB_transaction1deletefromTB_transaction1rollbackselect*fromTB_transaction1setimplicit

13、_transactionsoffdeletefromTB_transaction1rollback-此时该命令不成功，因为已经进入自动事务模式此时该命令不成功，因为已经进入自动事务模式go第第第第7 7 7 7章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标4 4、并发问题与事务隔离并发问题与事务隔离在大型分布式数据库应用程序中，对数据库的并发访问操作是在大型分布式数据库应用程序中，对数据库的并发访问操作是一个普遍存在的问题。一个普遍存在的问题。SQLServer使用资源锁定的方法管理用户的使用资源锁定的方法管理用户的并发

14、操作。如果在用户并发访问期间没有锁定数据库资源，用户操并发操作。如果在用户并发访问期间没有锁定数据库资源，用户操作相同的数据时可能会产生一些意想不到的问题。这些问题包括：作相同的数据时可能会产生一些意想不到的问题。这些问题包括：。丢失修改或被覆盖。丢失修改或被覆盖。读脏数据。读脏数据。不能重复读：。不能重复读：一个事物多次访问同一行数据而每次所读取的一个事物多次访问同一行数据而每次所读取的数据是不同的。数据是不同的。幻影读：。幻影读：是指一个事务多次读取一定范围内的数据行，而前是指一个事务多次读取一定范围内的数据行，而前后两次所读取的数据行是不同后两次所读取的数据行是不同第第第第7 7 7 7

15、章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标为了避免产生并发访问问题，为了避免产生并发访问问题，SQLServer使用使用不不同类型的锁对资源进行锁定，从而限制在一个事务读同类型的锁对资源进行锁定，从而限制在一个事务读取数据期间其他事务锁执行的操作类型，即对事务进取数据期间其他事务锁执行的操作类型，即对事务进行隔离。行隔离。不同的并发访问问题可以通过设置不同的事不同的并发访问问题可以通过设置不同的事务隔离级别加以解决。事务的隔离级别控制一个事务务隔离级别加以解决。事务的隔离级别控制一个事务与其他事务的隔离程度，它决定该

16、事务在读取数据时与其他事务的隔离程度，它决定该事务在读取数据时对资源所使用的锁类型。对资源所使用的锁类型。第第第第7 7 7 7章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标章事务、存储过程、触发器和游标SQL-92标准定义了以下标准定义了以下4种隔离级别：种隔离级别：未提交读：未提交读：这是这是4种隔离级别中限制最低的级别，它仅能保证种隔离级别中限制最低的级别，它仅能保证SQLServer不读取物理损坏的数据。在这种隔离级别下，不发出共享不读取物理损坏的数据。在这种隔离级别下，不发出共享锁，也不接受排它锁，事务可以对数据执行未提交读或脏读；在事务锁，也不接受排它锁，事务可以对数据执行未提交读或脏读；在事务结束前可以更改数据集内的数值，行也可以出现在数据集中或从数据结束前可以更改数据集内的数值，行也可以出现在数据集中或从数据集消失。集消失。提交读：提交读：它要求在读取数据时控制共享锁以避免发生脏读，它要求在读取数据时控制共享锁以避免发生脏读，但数据可在事务结束前更改，这可能产生不能重复读或幻影读问题。但数据可在事务结束前更改，这可能产生不能重