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

第7章事务、存储过程、触发器和游标第7章事务、存储过程、触发器和游标7.1事务7.2存储过程7.3触发器及其用途7.4游标第7章事务、存储过程、触发器和游标7.1事务1、事务的概念事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。第7章事务、存储过程、触发器和游标2、事务的特性原子性：事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。隔离性：一事务的执行不能被其它事务干扰持续性（永久性）：指事务一旦提交，则其对数据库中数据的改变就应该是永久的第7章事务、存储过程、触发器和游标3、事务的分类SQLServer的事务模式可分为显式事务、隐式事务和自动事务三种。1)显式事务显式事务是指由用户执行T-sql事务语句而定义的事务，这类事务又称做用户定义事务。定义事务的语句包括：BEGINTRANSACTION:标识一个事务的开始，即启动事务。COMMITTRANSACTION、COMMITWORK：标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。ROLLBACKTRANSACTION、ROLLBACKWORK：标识一个事务的结束，说明事务执行过程中遇到错误，事务内所修改的数据被回滚到事务执行前的状态。第7章事务、存储过程、触发器和游标2)隐式事务在隐式事务模式下，在当前事务提交或回滚后，SQLServer自动开始下一个事务。所以，隐式事务不需要使用BEGINTRANSACTION语句启动事务，而只需要用户使用ROLLBACKTRANSACTION、ROLLBACKWORK、COMMITTRANSACTION、COMMITWORK等语句提交或回滚事务。在提交或回滚后，SQLServer自动开始下一个事务。执行SETIMPLICIT_TRANSACTIONSON语句可使SQLServer进入隐式事务模式。在隐式事务模式下，当执行下面任意一个语句时，可使SQLServer重新启动一个事务：所有CREATE语句ALTERTABLE所有DROP语句TRUNCATETABLEGRANTREVOKEINSERTUPDATEDELETESELECTOPENFETCH需要关闭隐式事务模式时，调用SET语句关闭IMPLICIT_TRANSACTIONS连接选项即可。第7章事务、存储过程、触发器和游标3)自动事务模式在自动事务模式下，当一个语句被成功执行后，它被自动提交，而当它执行过程中产生错误时，被自动回滚。自动事务模式是SQLServer的默认事务管理模式，当与SQLServer建立连接后，直接进入自动事务模式，直到使用BEGINTRANSACTION语句开始一个显式事务，或者打开IMPLICIT_TRANSACTIONS连接选项进入隐式事务模式为止。而当显式事务被提交或IMPLICIT_TRANSACTIONS被关闭后，SQLServer又进入自动事务管理模式。第7章事务、存储过程、触发器和游标示例：BEGINTRANdemoSELECT*FROMStudentINSERTINTOStudentVALUES(‘9711112’,’张三’,…)SELECT*FROMStudentROLLBACK--回滚整个事务或：COMMIT--提交事务第7章事务、存储过程、触发器和游标下面例子说明自动事务模式下各语句的执行情况和SQLServer对批的处理：--SQLServer处于自动事务管理模式UsepubsGoCreatetableTB_transaction1(col1INTPRIMARYKEY,col2datetime)GoINSERTTB_transaction1VALUES(1,GETDATE())INSERTTB_transaction1VALUES(1,GETDATE())--违反约束Go第7章事务、存储过程、触发器和游标SELECTtimes=1,*fromTB_transaction1GoBegintran--进入显示事务模式INSERTTB_transaction1VALUES(2,GETDATE())SELECTtimes=2,*fromTB_transaction1INSERTTB_transaction1VALUE(3,GETDATE())--语法错误RollbackGoSELECTtimes=2,*fromTB_transaction1--此时，又重新进入自动事务模式Go第7章事务、存储过程、触发器和游标SETIMPLICIT_TRANSACTIONSON—进入隐含事务模式goinsertTB_transaction1VALUES(3,GETDATE())insertTB_transaction1VALUES(4,GETDATE())rollbackgoselect*fromTB_transaction1deletefromTB_transaction1rollbackselect*fromTB_transaction1setimplicit_transactionsoffdeletefromTB_transaction1rollback--此时该命令不成功，因为已经进入自动事务模式go第7章事务、存储过程、触发器和游标4、并发问题与事务隔离在大型分布式数据库应用程序中，对数据库的并发访问操作是一个普遍存在的问题。SQLServer使用资源锁定的方法管理用户的并发操作。如果在用户并发访问期间没有锁定数据库资源，用户操作相同的数据时可能会产生一些意想不到的问题。这些问题包括：。丢失修改或被覆盖。读脏数据。不能重复读：一个事物多次访问同一行数据而每次所读取的数据是不同的。。幻影读：是指一个事务多次读取一定范围内的数据行，而前后两次所读取的数据行是不同第7章事务、存储过程、触发器和游标为了避免产生并发访问问题，SQLServer使用不同类型的锁对资源进行锁定，从而限制在一个事务读取数据期间其他事务锁执行的操作类型，即对事务进行隔离。不同的并发访问问题可以通过设置不同的事务隔离级别加以解决。事务的隔离级别控制一个事务与其他事务的隔离程度，它决定该事务在读取数据时对资源所使用的锁类型。第7章事务、存储过程、触发器和游标SQL-92标准定义了以下4种隔离级别：未提交读：这是4种隔离级别中限制最低的级别，它仅能保证SQLServer不读取物理损坏的数据。在这种隔离级别下，不发出共享锁，也不接受排它锁，事务可以对数据执行未提交读或脏读；在事务结束前可以更改数据集内的数值，行也可以出现在数据集中或从数据集消失。提交读：它要求在读取数据时控制共享锁以避免发生脏读，但数据可在事务结束前更改，这可能产生不能重复读或幻影读问题。可重复读：锁定查询中使用的所有数据以防止其他用户更新，但是其他用户可以将新的幻影行插入到数据集中，新插入的幻影行将出现在当前事物的后续读取结果集中。可重复读能够避免产生脏读和非重复读问题，但仍可能导致幻影读问题。可串行读：这是事务隔离的最高级别，它使事务之间完全隔离，所以将导致并发级别较低。在这种隔离级别下，SQLServer在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或向数据集内插入数据行，从而避免出现脏读、非重复读或幻影读等并发问题。第7章事务、存储过程、触发器和游标调用Transact-SQL中的SETTRANSACTIONINOLATIONLEVEL语句可以调整事务的隔离级别，以控制由该连接所发出的所有SELECT语句的默认事务锁定行为。该语句的语法格式为：SETTRANSACTIONISOLATIONLEVEL{READUNCOMMITTED|READCOMMITTED|REPEATEDREAD|SERIALIZATION}第7章事务、存储过程、触发器和游标5.事务处理语句SQLServer中有关事务的处理语句有：命令名作用格式BEGINTRANSACTION说明一个事务开始BEGINTRANsaction[事务名]COMMITTRANSACTION说明一个事务结束，它的作用是提交或确认事务已经完成COMMITTRANsaction[事务名]SAVETRANSACTION用于在事务中设置一个保存点，目的是在撤消事务时可以只撤消部分事务，以提高系统的效率SAVETRANsaction保存点ROLLBACKTRANSACTION说明要撤消事务，即撤消在该事务中对数据库所做的更新操作，使数据库回退到BEGINTRANSACTION或保存点之前的状态ROLLBACKTRANsaction[事务名|保存点]第7章事务、存储过程、触发器和游标关于事务保存点保存点提供了一种机制，用于回滚部分事务。在应用程序中，使用SAVETRANSACTIONsavepoint_name语句设置保存点，使用ROLLBACKTRANSACTIONsavepoint_name将事务回滚到保存点。第7章事务、存储过程、触发器和游标关于嵌套事务：说明：在定义一个事务时，BEGINTRANSACTION语句应与COMMITTRANSACTION语句或ROLLBACKTRANSACTION成对出现。在SQLServer中，显示事务定义语句可以嵌套.一个嵌套的事务是一系列子事务ti的集合,T={t1,t2,…,tn},这些子事务中的每一个又可以是拥有它自己的事务.T能够决定子事务ti的启动和终止,反过来,如果T中的一个子事务ti终止,它强制T终止;如拖ti提交,这一动作并不能使ti成为永久的,如果T终止(回滚),那么ti的提交将被撤消.也就是说，SQLServer忽略内部事务的提交，根据最外部事务结束时采取的操作，将提交或者回滚事务。如果提交外部事务，则内层嵌套的事务也会提交。如果回滚外部事务，则不论此前是否提交过内层事务，所有内层事务都将回滚。第7章事务、存储过程、触发器和游标但实际上只有最外层的BEGINTRANSACTION语句和COMMITTRANSACTION语句才能建立和提交事务；在回滚事务时，也只能使用最外层定义的事务名或存储点标记，而不能使用内层定义的事务名。事务嵌套常用在存储过程或触发器内，它们可以使用BEGINTRANSACTION。。。COMMITTRANSACTION对来相互调用。第7章事务、存储过程、触发器和游标6.事务处理实例例1、UsepubsgoBEGINTRANSACTIONdemoSELECT*FROMdiscountsINSERTdiscountsVALUES(‘demo1’,null,null,null,20.0)SAVETRANSACTIONsave_demoINSERTdiscountsVALUES(‘demo2’,null,null,null,20.0)SELECT*FROMdiscountsROLLBACKTRANSACTIONsave_demo—回滚部分事务SELECT*FROMdiscountsROLLBACKTRANSACTION—回滚整个事务SELECT*FROMdiscounts第7章事务、存储过程、触发器和游标例2、事务嵌套usepubsgoBEGINTRANSACTIONdemoSELECT*FROMdiscountsINSERTdiscountsVALUES('demo1',null,null,null,20.0)beginTRANSACTIONdemo1INSERTdiscountsVALUES('demo2',null,null,null,20.0)SELECT*FROMdiscountsROLLBACKTRANSACTIONdemo1SELECT*FROMdiscountsROLLBACKTRANSACTION--出错，因为相应事务已经被回滚SELECT*FROMdiscounts第7章事务、存储过程、触发器和游标例3、USEpubsGOCREATETABLETB_transaction2(col1