搜索
第19章SQLServer事务和并发控制1.1事务19.1.1事务的概念事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库中数据的一致性和可恢复性。举一个简单的例子，ATM机。你通过ATM系统转账。你有1000元，对方有1000元。你将把500元从你的账户划到对方账户，最终的结果是你有500元，对方的账户1500元。但在交易时，当你从帐上取走500元后，软件出现故障，没有来得及去给对方存钱，也就是你的账户少了500，而对方并没有增加。这就会导致数据的不一致性存在。而通过事务，就可以实现。1、事务的特性原子性：事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。一致性：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。隔离性：一事务的执行不能被其它事务干扰。持续性（永久性）：指事务一旦提交，则其对数据库中数据的改变就应该是永久的2、事务和批的区别编程时，一定要区分事务和批的差别。1）批是一组整体编译的SQL语句，事务是一组作为单个逻辑工作单元执行的SQL语句。2）批语句的组合发生在编译时刻，事务中语句的组合发生在执行时刻。3）当在编译时，批中某个语句存在语法错误，系统将取消整个批中所有语句执行，而在运行时刻，如果事务中某个数据修改违反约束、规则等，系统默认只回退到产生该错误的语句。一个事务中也可以拥有多个批，一个批里可以有多个SQL语句组成的事务，事务内批的多少不影响事务的提交或回滚操作。19.1.2事务对并发控制和保障数据完整的重要性1．事务与并发控制的关系并发性是用来解决多个用户对同一数据进行操作时的问题。特别是对于网络数据库来说，这个特点更加突出。例如：在线售票系统。假设有一个订票系统，在9：00这个时刻，甲乙同时在2个窗口买票，甲买5张，已买10张。正常情况下剩余票数是5--5-10=35张。如果数据库没有并发手段，则会出现以下两种情况：（1）甲先买上，将剩余票45写入数据库；乙后买上，将40写回数据库，因此剩余票数为40（2）乙先买上，将剩余票40写入数据库；甲后买上，将45写回数据库，因此剩余票数为45如果在用户并发访问期间没有保证多个事务正确的交叉运行，用户操作相同的数据时可能会产生一些意想不到的问题2．事务对保障数据一致和完整性的作用故障轻则造成运行事务非正常中断，影响数据库中数据的正确性，重则破坏数据库，使数据库中全部或部分数据丢失。影响事务正常运行的故障有：（1）事务内部的故障（2）系统故障（3）介质故障（4）计算机病毒19.2事务的分类SQLServer的事务模式可分为显式事务、隐式事务和自动事务三种。1)显式事务显式事务是指由用户执行T-sql事务语句而定义的事务，这类事务又称做用户定义事务。定义事务的语句包括：BEGINTRANSACTION:标识一个事务的开始，即启动事务。COMMITTRANSACTION、COMMITWORK：标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。ROLLBACKTRANSACTION、ROLLBACKWORK：标识一个事务的结束，说明事务执行过程中遇到错误，事务内所修改的数据被回滚到事务执行前的状态。2)隐式事务在隐式事务模式下，在当前事务提交或回滚后，SQLServer自动开始下一个事务。所以，隐式事务不需要使用BEGINTRANSACTION语句启动事务，而只需要用户使用ROLLBACKTRANSACTION、ROLLBACKWORK、COMMITTRANSACTION、COMMITWORK等语句提交或回滚事务。在提交或回滚后，SQLServer自动开始下一个事务。执行SETIMPLICIT_TRANSACTIONSON语句可使SQLServer进入隐式事务模式。在隐式事务模式下，当执行下面任意一个语句时，可使SQLServer重新启动一个事务：所有CREATE语句、ALTERTABLE、所有DROP语句、TRUNCATETABLE、GRANTREVOKE、INSERT、UPDATE、DELETE、SELECT、OPEN、FETCH需要关闭隐式事务模式时，调用SET语句关闭IMPLICIT_TRANSACTIONSOFF连接选项即可。3)自动事务模式在自动事务模式下，当一个语句被成功执行后，它被自动提交，而当它执行过程中产生错误时，被自动回滚。自动事务模式是SQLServer的默认事务管理模式，当与SQLServer建立连接后，直接进入自动事务模式，直到使用BEGINTRANSACTION语句开始一个显式事务，或者打开IMPLICIT_TRANSACTIONS连接选项进入隐式事务模式为止。而当显式事务被提交或IMPLICIT_TRANSACTIONS被关闭后，SQLServer又进入自动事务管理模式。BEGINTRANdemoSELECT*FROMStudentINSERTINTOStudentVALUES('20090101','王明','男','1992-9-23',null,3)SELECT*FROMStudent--ROLLBACK--回滚整个事务COMMIT--提交事务19.3事务处理实例分析【例19-1】使用事务的三种模式进行表的处理，分批执行，观察执行的过程。USEjxglGOSELECTtimes=0,*FROMstudent--检查当前表中的结果GO--SQLServer首先处于自动事务管理模式INSERTstudentVALUES('20090201','关汉青','男','1989-3-17','中国山东滨州',2)SELECTtimes=1,*FROMstudent--显示'20090201'被插入。GOINSERTstudentVALUES('20090201','关汉青','男','1989-3-17','中国山东滨州',2)--服务器:消息2627，级别14，状态1，行1--违反了PRIMARYKEY约束'PK__Student__75A278F5'。不能在对象'Student'中插入重复键。--语句已终止。SELECTtimes=2,*FROMstudent--显示数据没有变化。GOBEGINTRANSACTION--进入显式事务模式INSERTstudentVALUES('20090202','关汉青','男','1989-3-17','中国山东滨州',2)SELECTtimes=3,*FROMstudent--显示'20090202'被插入ROLLBACKTRANSACTIONGOSELECTtimes=4,*FROMstudent--因为执行了回滚，插入的'20090202'被撤消。GOSETIMPLICIT_TRANSACTIONSON--进入隐式事务模式INSERTstudentVALUES('20090203','关汉青','男','1989-3-17','中国山东滨州',2)SELECTtimes=5,*FROMstudent--显示'20090203'被插入ROLLBACKGOSELECTtimes=6,*FROMstudent--因为执行了回滚，插入的'20090203'被撤消。GODELETEFROMstudentWHERE学号='20090201'--删除第个插入SELECTtimes=7,*FROMstudent--显示'20090201'不存在ROLLBACKGOSELECTtimes=8,*FROMstudent--因为回滚，使删除作废，所以'20090201'又重新显示存在。GOSETIMPLICIT_TRANSACTIONSOFF--隐式事务模式结束，又进入自动模式DELETEFROMstudentWHERE学号='20090201'--删除第个插入SELECTtimes=9,*FROMstudent--自动模式执行成功被自动提交，显示'20090201'被删除不存在。【例19-2】定义事务，使事务回滚到指定的保存点，分批执行，观察执行的过程。USEjxglGOSELECTtimes=0,*FROMstudent--检查当前表中的结果GOBEGINTRANSACTIONdemoINSERTstudentVALUES('20090201','关汉青','男','1989-3-17','中国山东滨州',2)SAVETRANSACTIONsave_demoINSERTstudentVALUES('20090202','关汉青','男','1989-3-17','中国山东滨州',2)SELECTtimes=1,*FROMstudent--显示'20090201'和'20090202'都被插入。GOROLLBACKTRANSACTIONsave_demo--回滚部分事务SELECTtimes=2,*FROMstudent--显示'20090202'被撤消不存在。GOROLLBACKTRANSACTION--回滚整个事务SELECTtimes=3,*FROMstudent--显示'20090201'被撤消不存在。【例19-3】创建数据表stu_test3，生成三个级别的嵌套时务，并提交该嵌套事务。观察变量@@TRANCOUNT的值的变化。USEjxgl--选择数据库必须单独在一个批中GOSELECT@@TRANCOUNT--变量@@TRANCOUNT的值为BEGINTRANSACTIONinside1SELECT@@TRANCOUNT--变量@@TRANCOUNT的值为INSERTstudentVALUES('20090201','关汉青','男','1989-3-17','中国山东滨州',2)GOBEGINTRANSACTIONinside2SELECT@@TRANCOUNT--变量@@TRANCOUNT的值为INSERTstudentVALUES('20090202','关汉青','男','1989-3-17','中国山东滨州',2)GOBEGINTRANSACTIONinside3SELECT@@TRANCOUNT--变量@@TRANCOUNT的值为INSERTstudentVALUES('20090203','关汉青','男','1989-3-17','中国山东滨州',2)GOCOMMITTRANSACTIONinside3SELECT@@TRANCOUNT--变量@@TRANCOUNT的值减为GOCOMMITTRANSACTIONinside2SELECT@@TRANCOUNT--变量@@TRANCOUNT的值减为GOCOMMITTRANSACTIONinside1SELECT@@TRANCOUNT--变量@@TRANCOUNT的值减为GO【例19-4】在教学管理数据的STUDENT表中先删除一条记录，然后再插入一条记录，通过测试错误值c确定提交还是回滚。USEjxglGODECLARE@del_errorint,@ins_errorint--开始一个事务BEGINTRAN--删除一个学生DELETEFROMstudentWHERE学号='20090201'--为删除语句设置一个接受错误数值的变量SELECT@del_error=@@ERROR--再执行插入语句INSERTstudentVALUES('20090201','关汉青','男','1989-3-17','中国山东滨州',2)--为插入语句设置一个接受错误数值的变量SELECT@ins_error=@@ERROR--测试错误变量中的值IF@del_error=0AND@ins_error=0BEGINprint'成功，提交事务'COMMITTRANENDELSEBEGINprint'有错误发生，回滚事务'IF@del_error0PRINT'错误发生在删除语句'IF@ins_error0PRINT'错误发生在插入语句'ROLLBACKTRANENDGO19.6并发控