SQLServer事务和并发控制课件

大型数据库系统管理、设计与实例分析

——基于SQLServer

SQLServer事务和并发控制课件大型数据库系统管理、设计与实例分析

——基于SQLServ1第8章SQLServer事务和并发控制8.1事务8.2事务的分类和控制8.3并发控制8.4事务处理实例分析

8.5分布式事务8.6并发控制

SQLServer事务和并发控制课件第8章SQLServer事务和并发控制8.128.1事务8.1.1事务的概念8.1.2事务对并发控制和保障数据完整的重要性SQLServer事务和并发控制课件8.1事务8.1.1事务的概念SQLServer事38.1.1事务的概念事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库中数据的一致性和可恢复性。SQLServer事务和并发控制课件8.1.1事务的概念事务是一个用户定义的完整的工作单元，41、事务的特性(ACID)原子性(Atomicity)：事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。

一致性(Consistency)：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。隔离性(Isolation)：一事务的执行不能被其它事务干扰。持续性（永久性）(Durability)：指事务一旦提交，则其对数据库中数据的改变就应该是永久的

SQLServer事务和并发控制课件1、事务的特性(ACID)原子性(Atomicity)：事务52、事务和批的区别编程时，一定要区分事务和批的差别：批是一组整体编译的SQL语句，事务是一组作为单个逻辑工作单元执行的SQL语句。批语句的组合发生在编译时刻，事务中语句的组合发生在执行时刻。当在编译时，批中某个语句存在语法错误，系统将取消整个批中所有语句执行，而在运行时刻，如果事务中某个数据修改违反约束、规则等，系统默认只回退到产生该错误的语句。如果批中产生一个运行时错误，系统默认只回退到产生该错误的语句。但当打开XACT_ABORT选项为ON时，可以系统自动回滚产生该错误的当前事务。一个事务中也可以拥有多个批，一个批里可以有多个SQL语句组成的事务，事务内批的多少不影响事务的提交或回滚操作。SQLServer事务和并发控制课件2、事务和批的区别编程时，一定要区分事务和批的差别：SQLS62、事务和批的区别SETXACT_ABORT指定当Transact-SQL语句产生运行时错误时，Microsoft®SQLServer™是否自动回滚当前事务。语法SETXACT_ABORT{ON|OFF}当SETXACT_ABORT为ON时，如果Transact-SQL语句产生运行时错误，整个事务将终止并回滚。为OFF时，只回滚产生错误的Transact-SQL语句，而事务将继续进行处理。编译错误（如语法错误）不受SETXACT_ABORT的影响。对于大多数OLEDB提供程序（包括SQLServer），隐性或显式事务中的数据修改语句必须将XACT_ABORT设置为ON。唯一不需要该选项的情况是提供程序支持嵌套事务时。SQLServer事务和并发控制课件2、事务和批的区别SETXACT_ABORTSQLServ72、事务和批的区别下例导致在含有其它Transact-SQL语句的事务中发生违反外键错误。在第一个语句集中产生错误，但其它语句均成功执行且事务成功提交。在第二个语句集中，SETXACT_ABORT设置为ON。这导致语句错误使批处理终止，并使事务回滚。CREATETABLEt1(aintPRIMARYKEY)CREATETABLEt2(aintREFERENCESt1(a))GOINSERTINTOt1VALUES(1)INSERTINTOt1VALUES(3)INSERTINTOt1VALUES(4)INSERTINTOt1VALUES(6)GOSETXACT_ABORTOFFGOBEGINTRANINSERTINTOt2VALUES(1)INSERTINTOt2VALUES(2)/*Foreignkeyerror*/INSERTINTOt2VALUES(3)COMMITTRANGOSQLServer事务和并发控制课件2、事务和批的区别下例导致在含有其它Transact-SQ82、事务和批的区别SETXACT_ABORTONGOBEGINTRANINSERTINTOt2VALUES(4)INSERTINTOt2VALUES(5)/*Foreignkeyerror*/INSERTINTOt2VALUES(6)COMMITTRANGO/*Selectshowsonlykeys1and3added.Key2insertfailedandwasrolledback,butXACT_ABORTwasOFFandrestoftransactionsucceeded.Key5inserterrorwithXACT_ABORTONcausedallofthesecondtransactiontorollback.*/SELECT*FROMt2GODROPTABLEt2DROPTABLEt1GOSQLServer事务和并发控制课件2、事务和批的区别SETXACT_ABORTONSQLS92、事务和批的区别问题：如何知道XACT_ABORT为ON还是OFF呢？XACT_ABORT的默认值是什么？SQLServer事务和并发控制课件2、事务和批的区别问题：如何知道XACT_ABORT为ON还102、事务和批的区别问题：如何知道XACT_ABORT为ON还是OFF呢？DBCCUSEROPTIONS:itwillshowassetifit'sON.Ifit'sOFFthenitwillnotshow

DBCCUSEROPTIONS返回当前连接的活动（设置）的SET选项。看看VB的例子：运行DBCC（顺便看一下联机帮助文档）并查看结果SQLServer事务和并发控制课件2、事务和批的区别问题：如何知道XACT_ABORT为ON还112、事务和批的区别SETXACT_ABORT的默认值是什么？对每个连接来说，SETXACT_ABORT的默认值是OFF看看VB的例子和查询分析器SQLServer事务和并发控制课件2、事务和批的区别SETXACT_ABORT的默认值是什么128.1.2事务对并发控制和保障

数据完整的重要性1．事务与并发控制的关系如果在用户并发访问期间没有保证多个事务正确的交叉运行，用户操作相同的数据时可能会产生一些意想不到的问题。包括：（1）丢失修改或被覆盖（火车票）（2）读脏数据（银行利息）（3）不能重复读（学生成绩）（4）幻影读SQLServer事务和并发控制课件8.1.2事务对并发控制和保障

数据完整的重要性1．事132．事务对保障数据一致和完整性的作用故障轻则造成运行事务非正常中断，影响数据库中数据的正确性，重则破坏数据库，使数据库中全部或部分数据丢失。影响事务正常运行的故障有：（1）事务内部的故障（2）系统故障（3）介质故障（4）计算机病毒8.1.2事务对并发控制和保障

数据完整的重要性SQLServer事务和并发控制课件2．事务对保障数据一致和完整性的作用8.1.2事务对并148.2事务的分类和控制8.2.1事务的分类8.2.2事务控制SQLServer事务和并发控制课件8.2事务的分类和控制8.2.1事务的分类SQL158.2.1事务的分类SQLServer的事务模式可分为显式事务、隐式事务和自动事务三种。1)显式事务显式事务是指由用户执行T-sql事务语句而定义的事务，这类事务又称做用户定义事务。定义事务的语句包括：BEGINTRANSACTION:标识一个事务的开始，即启动事务。COMMITTRANSACTION、COMMITWORK：标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。ROLLBACKTRANSACTION、ROLLBACKWORK：标识一个事务的结束，说明事务执行过程中遇到错误，事务内所修改的数据被回滚到事务执行前的状态。SQLServer事务和并发控制课件8.2.1事务的分类SQLServer的事务模式可分162)隐式事务在隐式事务模式下，在当前事务提交或回滚后，SQLServer自动开始下一个事务。所以，隐式事务不需要使用BEGINTRANSACTION语句启动事务，而只需要用户使用ROLLBACKTRANSACTION、ROLLBACKWORK、COMMITTRANSACTION、COMMITWORK等语句提交或回滚事务。在提交或回滚后，SQLServer自动开始下一个事务。执行SETIMPLICIT_TRANSACTIONSON语句可使SQLServer进入隐式事务模式。在隐式事务模式下，当执行下面任意一个语句时，可使SQLServer重新启动一个事务：

所有CREATE语句ALTERTABLE所有DROP语句TRUNCATETABLEGRANTREVOKEINSERTUPDATEDELETESELECTOPENFETCH需要关闭隐式事务模式时，调用SET语句关闭IMPLICIT_TRANSACTIONSOFF连接选项即可。SQLServer事务和并发控制课件2)隐式事务SQLServer事务和并发控制课件173)自动事务模式在自动事务模式下，当一个语句被成功执行后，它被自动提交，而当它执行过程中产生错误时，被自动回滚。自动事务模式是SQLServer的默认事务管理模式，当与SQLServer建立连接后，直接进入自动事务模式，直到使用BEGINTRANSACTION语句开始一个显式事务，或者打开IMPLICIT_TRANSACTIONS连接选项进入隐式事务模式为止。而当显式事务被提交或IMPLICIT_TRANSACTIONS被关闭后，SQLServer又进入自动事务管理模式。看看VB的例子SQLServer事务和并发控制课件3)自动事务模式SQLServer事务和并发控制课件18示例：BEGINTRANdemo SELECT*FROMStudent INSERTINTOStudentVALUES(‘9711112’,’张三’,…) SELECT*FROMStudent ROLLBACK--回滚整个事务或： COMMIT--提交事务SQLServer事务和并发控制课件示例：BEGINTRANdemoSQLServer198.2.2事务控制

SQLServer中有关事务的处理语句有：命令名作用格式BEGINTRANSACTION说明一个事务开始BEGINTRANsaction[<事务名>]COMMITTRANSACTION说明一个事务结束，它的作用是提交或确认事务已经完成COMMITTRANsaction[<事务名>]SAVETRANSACTION用于在事务中设置一个保存点，目的是在撤消事务时可以只撤消部分事务，以提高系统的效率SAVETRANsaction<保存点>ROLLBACKTRANSACTION说明要撤消事务，即撤消在该事务中对数据库所做的更新操作，使数据库回退到BEGINTRANSACTION或保存点之前的状态ROLLBACKTRANsaction[<事务名>|<保存点>]SQLServer事务和并发控制课件8.2.2事务控制SQLServer中有关事务的处理20Savetransaction示例（略）1：下例更改分给TheGourmetMicrowave的两位作者的版税。数据库将会在两个更新间不一致，因此必须将它们分组为用户定义的事务。BEGINTRANSACTIONroyaltychangeUPDATEtitleauthorSETroyaltyper=65FROMtitleauthor,titlesWHEREroyaltyper=75ANDtitleauthor.title_id=titles.title_idANDtitle='TheGourmetMicrowave'UPDATEtitleauthorSETroyaltyper=35FROMtitleauthor,titlesWHEREroyaltyper=25ANDtitleauthor.title_id=titles.title_idANDtitle='TheGourmetMicrowave'SAVETRANSACTIONpercentchanged/*Afterhavingupdatedtheroyaltyperentriesforthetwoauthors,theuserinsertsthesavepointpercentchanged,andthendetermineshowa10-percentincreaseinthebook'spricewouldaffecttheauthors'royaltyearnings.*/SQLServer事务和并发控制课件Savetransaction示例（略）1：下例更改分给21Savetransaction示例（略）2：UPDATEtitlesSETprice=price*1.1WHEREtitle='TheGourmetMicrowave'SELECT(price*royalty*ytd_sales)*royaltyperFROMtitles,titleauthorWHEREtitle='TheGourmetMicrowave'ANDtitles.title_id=titleauthor.title_id/*ThetransactionisrolledbacktothesavepointwiththeROLLBACKTRANSACTIONstatement.*/ROLLBACKTRANSACTIONpercentchangedCOMMITTRANSACTION/*Endofroyaltychange.*/SQLServer事务和并发控制课件Savetransaction示例（略）2：SQLServ22说明：在定义一个事务时，BEGINTRANSACTION语句应与COMMITTRANSACTION语句或ROLLBACKTRANSACTION成对出现。在SQLServer中，事务定义语句可以嵌套，但实际上只有最外层的BEGINTRANSACTION语句和COMMITTRANSACTION语句才能建立和提交事务；在回滚事务时，也只能使用最外层定义的事务名或存储点标记，而不能使用内层定义的事务名。事务嵌套常用在存储过程或触发器内，它们可以使用BEGINTRANSACTION。。。COMMITTRANSACTION对来相互调用。SQLServer事务和并发控制课件说明：SQLServer事务和并发控制课件23说明：事务处理过程中的错误：如果服务器错误使事务无法成功完成，则SQLServer自动回滚该事务，并释放该事务所占有的所有资源；如果客户端与SQLServer的网络连接中断，那么当网络告知SQLServer该中断时，将回滚该连接所有未完成的事务；如果客户端应用程序失败或客户计算机崩溃或重启，也会中断该连接，当SQLServer该中断时，将回滚该连接所有未完成的事务；如果客户从该应用程序注销，所有未完成的事务也会被回滚。可以用联网的两台计算机测试VB程序。SQLServer事务和并发控制课件说明：SQLServer事务和并发控制课件248.3.1编写有效事务的指导原则

8.3.2避免并发问题

8.3编写有效事务的建议

SQLServer事务和并发控制课件8.3.1编写有效事务的指导原则8.3编写有效事务258.3.1编写有效事务的指导原则

1．不要在事务处理期间输入数据

2．浏览数据时，尽量不要打开事务

3．保持事务尽可能的短

4．灵活地使用更低的事务隔离级别

5．在事务中尽量使访问的数据量最小

SQLServer事务和并发控制课件8.3.1编写有效事务的指导原则1．不要在事务处理期间268.3.2避免并发问题

为了防止并发问题，应该小心地管理隐性事务。在使用隐性事务时，COMMIT或ROLLBACK之后的下一个Transact-SQL语句会自动启动一个新事务。这可能在应用程序浏览数据时，甚至在要求用户输入时，打开新的事务。所以，在完成保护数据修改所需要的最后一个事务之后和再次需要一个事务来保护数据修改之前，应该关闭隐性事务。

SQLServer事务和并发控制课件8.3.2避免并发问题为了防止并发问题，应该小心地管理278.4事务处理实例分析

【例8-1】使用事务的三种模式进行表的处理，分批执行，观察执行的过程。

USE教学管理GOSELECTtimes=0,*FROMstudent--检查当前表中的结果GO--SQLServer首先处于自动事务管理模式INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060101','******19880510***','关汉青','男','','西安','计算机','信息学院')SELECTtimes=1,*FROMstudent--显示'S060101'被插入。GOSQLServer事务和并发控制课件8.4事务处理实例分析【例8-1】使用事务的三种模式进28INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060101','******19880510***','关汉青','男','','西安','计算机','信息学院')--服务器:消息2627，级别14，状态1，行1--违反了PRIMARYKEY约束'PK__Student__75A278F5'。不能在对象'Student'中插入重复键。--语句已终止。SELECTtimes=2,*FROMstudent--显示数据没有变化。GOBEGINTRANSACTION --进入显式事务模式INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060106','******19880510***','关汉青','男','','西安','计算机','信息学院')SELECTtimes=3,*FROMstudent--显示'S060106'被插入SQLServer事务和并发控制课件INSERTstudent(sno,sssn,sname,29ROLLBACKTRANSACTIONGOSELECTtimes=4,*FROMstudent--因为执行了回滚，插入的'S060106'被撤消。GOSETIMPLICIT_TRANSACTIONSON--进入隐式事务模式INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060107','******19880510***','关汉青','男','','西安','计算机','信息学院')SELECTtimes=5,*FROMstudent--显示'S060107'被插入ROLLBACK GOSELECTtimes=6,*FROMstudent--因为执行了回滚，插入的'S060107'被撤消。GOSQLServer事务和并发控制课件ROLLBACKTRANSACTIONSQLServer30DELETEFROMstudentWHEREsno='S060101'--删除第1个插入SELECTtimes=7,*FROMstudent--显示'S060101'不存在ROLLBACKGO SELECTtimes=8,*FROMstudent--因为回滚，使删除作废，所以'S060101'又重新显示存在。GOSETIMPLICIT_TRANSACTIONSOFF --隐式事务模式结束，又进入自动模式

DELETEFROMstudentWHEREsno='S060101'--删除第1个插入SELECTtimes=9,*FROMstudent--自动模式执行成功被自动提交，显示'S060101'被删除不存在。SQLServer事务和并发控制课件DELETEFROMstudentWHEREsno=31【例8-2】定义事务，使事务回滚到指定的保存点，分批执行，观察执行的过程。

USE教学管理GOSELECTtimes=0,*FROMstudent--检查当前表中的结果GOBEGINTRANSACTIONdemoINSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060106','******19880510***','关汉青','男','','西安','计算机','信息学院')SAVETRANSACTIONsave_demoSQLServer事务和并发控制课件【例8-2】定义事务，使事务回滚到指定的保存点，分批执行，观32INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060107','******19890818***','杨理华','女','','运城','计算机','信息学院')SELECTtimes=1,*FROMstudent--显示'S060106'和'S060107'都被插入。GOROLLBACKTRANSACTIONsave_demo--回滚部分事务SELECTtimes=2,*FROMstudent--显示'S060107'被撤消不存在。GOROLLBACKTRANSACTION--回滚整个事务SELECTtimes=3,*FROMstudent--显示'S060106'被撤消不存在。

SQLServer事务和并发控制课件INSERTstudent(sno,sssn,sname,33【例8-3】创建数据表stu_test3，生成三个级别的嵌套时务，并提交该嵌套事务。观察变量@@TRANCOUNT的值的变化。

USE教学管理

--选择数据库必须单独在一个批中GOSELECT@@TRANCOUNT--变量@@TRANCOUNT的值为0BEGINTRANSACTIONinside1SELECT@@TRANCOUNT--变量@@TRANCOUNT的值为1INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060106','******19880510***','关汉青','男','','西安','计算机','信息学院')GOBEGINTRANSACTIONinside2SELECT@@TRANCOUNT--变量@@TRANCOUNT的值为2

SQLServer事务和并发控制课件【例8-3】创建数据表stu_test3，生成三个级别的嵌套34INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060107','******19890818***','杨理华','女','','运城','计算机','信息学院')GOBEGINTRANSACTIONinside3SELECT@@TRANCOUNT--变量@@TRANCOUNT的值为3INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES(‘S060108’,‘******19870818***’,‘陈向前’,‘男’,‘’,‘北京’,‘计算机’,‘信息学院’)GOCOMMITTRANSACTIONinside3SELECT@@TRANCOUNT--变量@@TRANCOUNT的值减为2GOSQLServer事务和并发控制课件INSERTstudent(sno,sssn,sname,35COMMITTRANSACTIONinside2SELECT@@TRANCOUNT--变量@@TRANCOUNT的值减为1GOCOMMITTRANSACTIONinside1SELECT@@TRANCOUNT--变量@@TRANCOUNT的值减为0GOSQLServer事务和并发控制课件COMMITTRANSACTIONinside236【例8-4】在教学管理数据的STUDENT表中先删除一条记录，然后再插入一条记录，通过测试错误值确定提交还是回滚。

USE教学管理GODECLARE@del_errorint,@ins_errorint--开始一个事务BEGINTRAN--删除一个学生DELETESTUDENTWHERESNO='S060308'--为删除语句设置一个接受错误数值的变量SELECT@del_error=@@ERRORSQLServer事务和并发控制课件【例8-4】在教学管理数据的STUDENT表中先删除一条记录37--再执行插入语句INSERTINTOSTUDENTVALUES('S060308','******19890526***','张丹宁','男','130***12','宁波','电子商务','信息学院',162)--为插入语句设置一个接受错误数值的变量SELECT@ins_error=@@ERROR--测试错误变量中的值IF@del_error=0AND@ins_error=0BEGIN

--成功，提交事务COMMITTRANENDSQLServer事务和并发控制课件--再执行插入语句SQLServer事务和并发控制课件38ELSEBEGIN

--有错误发生，回滚事务

IF@del_error<>0PRINT'错误发生在删除语句'IF@ins_error<>0PRINT'错误发生在插入语句'ROLLBACKTRANENDGO

可以将插入的‘S060308’改为‘SS060308’再执行一次该程序，观察结果有什么不同？

SQLServer事务和并发控制课件ELSESQLServer事务和并发控制课件398.5分布式事务

8.5.1分布式事务的两阶段提交

8.5.2分布式事务的处理过程

8.5.3分布式事务实例分析

SQLServer事务和并发控制课件8.5分布式事务8.5.1分布式事务的两阶段提交408.5分布式事务在大型应用领域，经常需要时务跨服务器进行数据操作，这样的事务被称作分布式事务。所以分布式事务要能够在多个服务器上执行。按照关于分布式事务处理的X/OpenXA规范，分布式事务的处理过程规定为两个阶段，就是通常说的两阶段提交。为了简化应用程序对分布式事务的处理工作，系统提供了一个事务管理器来协调各个不同服务器对事务的处理操作，它就是MSDTC（DistributedTransactionCoordinator），既事务管理协调器。我们在第一章简单介绍过。SQLServer事务和并发控制课件8.5分布式事务在大型应用领域，经常需要时务跨服务器进行418.5.1分布式事务的两阶段提交（1）准备阶段：当分布式事务管理器接受到提交请求后，它向所有参与与该事务的SQLServer服务器发出准备命令。每个服务器接受到准备命令后，做好接受处理事务的准备工作，并将准备工作状态返回给事务管理器。（2）提交阶段：当事务管理器接受到所有服务器成功准备好的信息后，它向这些服务器发出提交命令。之后所有服务器进行提交。如果所有服务器均能成功提交事务，管理器向应用程序报告分布式事务成功提交，如若有任一个服务器未能提交，事务管理器将向所有服务器发出回滚事务命令，并向应用程序报告事务提交失败。SQLServer事务和并发控制课件8.5.1分布式事务的两阶段提交（1）准备阶段：当分布式428.5.2分布式事务的处理过程（1）T-SQL程序或应用程序执行BEGINDISTRIBUTEDTRANSACTION语句启动一个分布式事务。此后，该服务器就成为分布式服务器的管理服务器。（2）应用程序对链接服务器执行分布式查询或执行远程服务器上的存储过程。（3）分布式事务管理服务器自动调用MSDTC，使链接服务器或远程服务器参加分布式事务处理。（4）T-SQL应用程序执行COMMIT或ROLLBACK语句时，分布式事务管理服务器通过调用MSDTC来管理两阶段提交，使链接或远程服务器提交或回滚事务。SQLServer事务和并发控制课件8.5.2分布式事务的处理过程（1）T-SQL程序或应用程438.5.3分布式事务实例分析

1．分布式事务语法格式语法格式：BEGINDISTRIBUTEDTRANSANCTION[transanctin_name|@transanctin_variable]参数说明：transanctin_name|@transanctin_variable事务名称或事务名变量。

SQLServer事务和并发控制课件8.5.3分布式事务实例分析1．分布式事务语法格式SQ442、分布式事务实例【例8-6】有两个服务器LinkServer1和LinkServer2。在LinkServer2服务器上建立存储过程student_insert_new，其功能是向LinkServer1上的教学管理数据库的student表插入一个新行。

--先创建链接（远程）服务器（参见第2章）--在第一台运行SQLServer的服务器上运行下列代码：

EXECsp_addlinkedserver'LinkServer1','','SQLOLEDB','本地服务器名或ip地址'--例如'zufe-mxh'EXECsp_addlinkedserver'LinkServer2','','SQLOLEDB','远程服务器名或ip地址'--例如'172.19.2.156'EXECsp_configure'remoteaccess',1--系统默认是1，一般不需要设置SQLServer事务和并发控制课件2、分布式事务实例【例8-6】有两个服务器LinkServe45RECONFIGURE--设置'LinkServer1'的rpc输出属性，使得允许调用链接服务器上的存储过程。EXECsp_serveroption'LinkServer1','rpcout','true'GO--停止并重新启动第一台SQLServer。--确保使用SQLServer身份验证登录。在第二台SQLServer上运行下列代码。EXECsp_addlinkedserver‘LinkServer2’,‘’,‘SQLOLEDB’,‘本地服务器名或ip地址’

--例如'172.19.2.156'EXECsp_addlinkedserver‘LinkServer1’,‘’,‘SQLOLEDB’,‘远程服务器名或ip地址’

--例如'zufe-mxh'

SQLServer事务和并发控制课件RECONFIGURESQLServer事务和并发控制课件46EXECsp_configure'remoteaccess',1--系统默认是1，一般不需要设置RECONFIGURE--设置'LinkServer2'的rpc输出属性，使得允许调用链接服务器上的存储过程。EXECsp_serveroption'LinkServer2','rpcout','true'GO--在第二个服务器上添加新的远程登录ID（LinkServer1），以便允许远程服务器LinkServer1连接并执行远程过程调用。--假设登录LinkServer2和LinkServer1的用户都是'sa'，并且有相同的口令。EXECsp_addremoteloginLinkServer1,sa,saGO--停止并重新启动第二台SQLServer。

SQLServer事务和并发控制课件EXECsp_configure'remoteacce47--在LinkServer2上创建存储过程（见第12章）。--假设该服务器上面有数据库‘教学练习’。Use教学练习GOCREATEprocedurestudent_insert_newASINSERTLinkServer1.教学管理.dbo.student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060112','******19870818***',''许少文','男','','湖州','计算机','信息学院')GO--在第一台服务器上启动DTC开始分布式事务--使用sa登录，现在就可以在第一台SQLServer上执行第二台SQLServer上的存储过程。

SQLServer事务和并发控制课件--在LinkServer2上创建存储过程（见第12章）。S48USE教学管理GOBEGINDISTRIBUTEDTRANSACTIONinsert_tran--开始分布式事务--在LinkServer1服务器上实行对表student的插入INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060111','******19870818***','陈东生','男','','上海','计算机','信息学院')GO--LinkServer1服务器自动调用MSDTC使得LinkServer2服务器执行存储过程student_insert_new对表student的插入。EXECUTELinkServer2.教学练习.dbo.student_insert_newCOMMITTRANSACTION--提交事务

SQLServer事务和并发控制课件USE教学管理SQLServer事务和并发控制课件498.6并发控制

8.6.1SQLServer锁的粒度及模式

8.6.2封锁协议

8.6.3事务隔离

8.6.4死锁处理

SQLServer事务和并发控制课件8.6并发控制8.6.1SQLServer锁的粒508.6并发控制在大型分布式数据库应用程序中，对数据库的并发访问操作是一个普遍存在的问题。SQLServer使用资源锁定的方法管理用户的并发操作。SQLServer2000提供了两种并发控制方法：▲乐观并发控制：该方法假想用户之间不太可能发生资源冲突（事实上不是不可能），所以允许用户在不锁定任何资源的情况下执行事务。只有当用户试图修改数据时才检查资源是否冲突。该方法需要使用游标，游标中介绍。▲悲观并发控制：该方法根据需要在事务的持续时间内锁定资源，从而确保事务的完整性和数据库的一致性。这是SQLServer2000默认的并发控制方法。下面予以介绍。SQLServer事务和并发控制课件8.6并发控制在大型分布式数据库应用程序中，对数据库的并发518.6.1SQLServer锁的粒度及模式

1、SQLServer锁的粒度1）RID：行标识符，锁定表中单行数据。2）键值：具有索引的行数据。3）页面：一个数据页面或索引页面。4）区域：一组连续的8个数据页面或索引页面。5）表：整个表，包括其所有的数据和索引。6）数据库：一个完整的数据库。可以根据事务所执行的任务来灵活选择所锁定的资源粒度。SQLServer事务和并发控制课件8.6.1SQLServer锁的粒度及模式1、SQL522、资源锁定模式—基本锁1）共享锁：用于只读数据操作，它允许多个并发事务对资源锁定进行读取，但禁止其他事务对锁定资源的修改操作。2）排它锁：它锁定的资源不能被其它并发事务再进行任何锁定，所以其它事务不能读取和修改。锁定的资源用于自己的数据修改。一般更新模式是由一个事务组成，该事务先读取记录，要获取资源的共享锁，然后修改记录，此操作要求锁转换为排它锁。如果两个事务都获得了资源上的共享锁，然后试图同时更新数据，这样肯定有一个事务要将共享锁转化为排它锁，因为一个事务的排它锁与其它事务的共享锁不兼容，发生锁等待。另一个事务也会出现这个问题，由于两个事务都要转化为排它锁，并且都等待另一个事务释放共享锁，因此发生死锁。SQLServer事务和并发控制课件2、资源锁定模式—基本锁1）共享锁：用于只读数据操作，它允许532、资源锁定模式—专用锁3）更新锁：在修改操作的初始化阶段用于锁定可能被修改的资源。一个数据修改事务在开始时直接申请更新锁，每次只有一个事务可以获得资源的更新锁。使用更新锁可以避免上述死锁，因为一次只有一个事务可以获得更新锁，之后当需要继续修改数据时，将更新锁转换为排它锁，否则将更新锁转换为共享锁。SQLServer事务和并发控制课件2、资源锁定模式—专用锁3）更新锁：在修改操作的初始化阶段用544）意向锁意向锁表示如果获得一个对象的锁，说明该结点的下层对象正在被加锁。例如放置在表上的共享意向锁表示事务打算在表中的页或行上加共享锁。意向锁可以提高性能，因为系统仅在表级上检查意向锁而无须检查下层。①意向共享锁：对一个对象加意向共享锁，表示将要对它的下层对象加共享锁。②意向排它锁：对一个对象加意向排它锁，表示将要对它的下层对象加排它锁。③意向排它共享锁：对一个对象加意向排它共享锁，表示对它加共享锁，再在它的下层对象加排它锁。SQLServer事务和并发控制课件4）意向锁意向锁表示如果获得一个对象的锁，说明该结点的下层对555）架构锁①架构修改锁：执行表的数据定义语言（DDL）操作时使用。②架构稳定锁：编译查询时使用。它不阻塞任何事务锁，包括排它锁。6）大容量更新锁：当数据大容量复制到表的时候使用。SQLServer事务和并发控制课件5）架构锁①架构修改锁：执行表的数据定义语568.6.2封锁协议

在运用X锁和S锁对数据对象加锁时，需要约定一些规则：封锁协议（LockingProtocol）何时申请X锁或S锁持锁时间、何时释放不同的封锁协议，在不同的程度上为并发操作的正确调度提供一定的保证常用的封锁协议：三级封锁协议SQLServer事务和并发控制课件8.6.2封锁协议在运用X锁和S锁对数据对象加锁时，需571级封锁协议事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放正常结束（COMMIT）非正常结束（ROLLBACK）1级封锁协议可防止丢失修改在1级封锁协议中，如果是读数据，不需要加锁的，所以它不能保证可重复读和不读“脏”数据。SQLServer事务和并发控制课件1级封锁协议事务T在修改数据R之前必须先对其加X锁，直到事务581级封锁协议T1T2①

XlockA获得②

读A=16

③A←A-1写回A=15CommitUnlockA④

⑤

XlockA等待等待等待等待获得XlockA读A=15A←A-1写回A=14CommitUnlockA

没有丢失修改没有丢失修改SQLServer事务和并发控制课件1级封锁协议T1T2①

XlockA

没有丢失修改没592级封锁协议1级封锁协议+事务T在读取数据R前必须先加S锁，读完后即可释放S锁2级封锁协议可以防止丢失修改和读“脏”数据。在2级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读。SQLServer事务和并发控制课件2级封锁协议1级封锁协议+事务T在读取数据R前必须先加S锁，602级封锁协议T1T2①

XlockC读C=100C←C*2写回C=200②

③ROLLBACK(C恢复为100)UnlockC④

⑤

SlockC等待等待等待等待获得SlockC读C=100CommitCUnlockC不读“脏”数据

SQLServer事务和并发控制课件2级封锁协议T1T2①XlockC不读“脏”数据SQL613级封锁协议1级封锁协议+事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放3级封锁协议可防止丢失修改、读脏数据和不可重复读。容易造成比较多的死锁SQLServer事务和并发控制课件3级封锁协议1级封锁协议+事务T在读取数据R之前必须先对623级封锁协议T1T2①

SlockA读A=50SlockB读B=100求和=150②

③读A=50读B=100求和=150CommitUnlockAUnlockB④

⑤

XlockB等待等待等待等待等待等待等待等待获得XlockB读B=100B←B*2写回B=200CommitUnlockB

可重复读

SQLServer事务和并发控制课件3级封锁协议T1T2①

SlockA

可重复读SQL63封锁协议小结SQLServer事务和并发控制课件封锁协议小结SQLServer事务和并发控制课件648.6.3事务隔离

为了避免产生并发访问问题，SQLServer使用不同类型的锁对资源进行锁定，从而限制在一个事务读取数据期间其他事务锁执行的操作类型，即对事务进行隔离。不同的并发访问问题可以通过设置不同的事务隔离级别加以解决。事务的隔离级别控制一个事务与其他事务的隔离程度，它决定该事务在读取数据时对资源所使用的锁类型。SQLServer事务和并发控制课件8.6.3事务隔离为了避免产生并发访问问题，SQLS651）事务隔离级别未提交读：这是4种隔离级别中限制最低的级别，它仅能保证SQLServer不读取物理损坏的数据。在这种隔离级别下，读数据时不发出共享锁，也不接受排它锁，事务可以对数据执行脏读；在事务结束前可以更改数据集内的数值，行也可以出现在数据集中或从数据集消失。但可以防止产生丢失修改。（一级封锁协议）提交读：它要求在读取数据时控制共享锁以避免发生脏读，但数据可在事务结束前更改，这可能产生不能重复读或幻影读问题。（二级封锁协议）可重复读：锁定查询中使用的所有数据以防止其他用户更新，但是其他用户可以将新的幻影行插入到数据集中，新插入的幻影行将出现在当前事物的后续读取结果集中。可重复读能够避免产生脏读和非重复读问题，但仍可能导致幻影读问题。（三级封锁协议）可串行读：这是事务隔离的最高级别，它使事务之间完全隔离，所以将导致并发级别较低。在这种隔离级别下，SQLServer在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或向数据集内插入数据行，从而避免出现脏读、非重复读或幻影读等并发问题。（四级封锁协议）SQLServer事务和并发控制课件1）事务隔离级别SQLServer事务和并发控制课件662）事务隔离级别对不同类型的行为隔离级别丢失修改脏数据不可重读幻影未提交读否是是是提交读否否是是可重复读否否否是可串行读否否否否SQLServer事务和并发控制课件2）事务隔离级别对不同类型的行为隔离级别673）设置事务隔离级别调用T-SQL中的SETTRANSACTIONINOLATIONLEVEL语句可以调整事务的隔离级别，以控制由该连接所发出的所有SELECT语句的默认事务锁定行为。该语句的语法格式为：SETTRANSACTIONISOLATIONLEVEL{READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE}例如：将事务隔离级别设置为REPEATABLEREADSETTRANSACTIONISOLATIONLEVELREPEATABLEREAD注意：一旦设定，系统就会按这种隔离级别自动进行并发处理。SQLServer事务和并发控制课件3）设置事务隔离级别调用T-SQL中的SET68设置表级锁表级锁是由程序员进行的，可以对SELECT、INSERT、UPDATE、DELETE语句进行精确控制。表级锁定不是系统自动完成，而是需要程序员自己根据事务的要求进行锁定，然后，系统按程序员在程序中的锁定予以执行。一般来说，读操作需要共享锁，写操作需要排它锁。如果需要更精确，还需要一些其它专用锁。具体设置有：设置共享锁设置排它锁设置专用锁SQLServer事务和并发控制课件设置表级锁表级锁是由程序员进行的，可以对SELE69设置共享锁共享锁用于读操作。一个资源可获得多个共享锁。使用HOLDLOCK设置共享锁。例：USENorthwindGOBEGINTRANSANCTIONT1SELECTOrderID,OrderDateFROMOrdersWITH(HOLDLOCK)SELECTCOUNT(OrderID)FROMOrdersCOMMITSQLServer事务和并发控制课件设置共享锁共享锁用于读操作。一个资源可获得70设置排它锁对于INSERT、UPDATE和DELETE语句使用排它锁。在并发事务中，只有一个事务能够获得资源的排它锁。使用TABLOCKX设置排它锁。例：USENorthwindGOBEGINTRANSANCTIONT1INSERTINTOOrdersWITH(TABLOCKX)(CustomerID,OrderDate)VALUES(‘ALEF’,’2005-01-01’)COMMITSQLServer事务和并发控制课件设置排它锁对于INSERT、UPDATE和DELETE71设置其它专用锁方法同上。NOLOCK：不要发出共享锁和排它锁。仅适用SELECT语句。READPAST：跳过已经锁定的行。仅适用SELECT语句。TABLOCK：使用大容量更新锁。PAGLOCK：使用页级锁。ROWLOCK：使用行级锁，不使用页级和表级锁。UPDLOCK：读取表时使用更新锁。XLOCK：适用于不同粒度的排它锁。SQLServer事务和并发控制课件设置其它专用锁方法同上。SQLServer事务和728.6.4死锁处理

SQLServer能够自动定期搜索和处理死锁问题。当检测到有死锁时，SQLServer回滚被中断的事务，并向应用程序返回1205号错误信息，未被中断的事务则继续执行。在数据库应用程序扑捉到1205号错误，可以对死锁现象做后续处理。为减少死锁次数，在设计应用程序时：1）尽量使一个事务在一个批内，并且要短；2）不要在事务处理期间和用户交互；3）灵活使用较低级别的事务隔离；4）在事务处理期间要使访问数据量最小。SQLServer事务和并发控制课件8.6.4死锁处理SQLServer能够自动定期搜索73Thankyouverymuch!谢谢您的光临！下一章SQLServer事务和并发控制课件Thankyouverymuch!谢谢您的光临！下一章74大型数据库系统管理、设计与实例分析

——基于SQLServer

SQLServer事务和并发控制课件大型数据库系统管理、设计与实例分析

——基于SQLServ75第8章SQLServer事务和并发控制8.1事务8.2事务的分类和控制8.3并发控制8.4事务处理实例分析

8.5分布式事务8.6并发控制

SQLServer事务和并发控制课件第8章SQLServer事务和并发控制8.1768.1事务8.1.1事务的概念8.1.2事务对并发控制和保障数据完整的重要性SQLServer事务和并发控制课件8.1事务8.1.1事务的概念SQLServer事778.1.1事务的概念事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库中数据的一致性和可恢复性。SQLServer事务和并发控制课件8.1.1事务的概念事务是一个用户定义的完整的工作单元，781、事务的特性(ACID)原子性(Atomicity)：事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。

一致性(Consistency)：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。隔离性(Isolation)：一事务的执行不能被其它事务干扰。持续性（永久性）(Durability)：指事务一旦提交，则其对数据库中数据的改变就应该是永久的

SQLServer事务和并发控制课件1、事务的特性(ACID)原子性(Atomicity)：事务792、事务和批的区别编程时，一定要区分事务和批的差别：批是一组整体编译的SQL语句，事务是一组作为单个逻辑工作单元执行的SQL语句。批语句的组合发生在编译时刻，事务中语句的组合发生在执行时刻。当在编译时，批中某个语句存在语法错误，系统将取消整个批中所有语句执行，而在运行时刻，如果事务中某个数据修改违反约束、规则等，系统默认只回退到产生该错误的语句。如果批中产生一个运行时错误，系统默认只回退到产生该错误的语句。但当打开XACT_ABORT选项为ON时，可以系统自动回滚产生该错误的当前事务。一个事务中也可以拥有多个批，一个批里可以有多个SQL语句组成的事务，事务内批的多少不影响事务的提交或回滚操作。SQLServer事务和并发控制课件2、事务和批的区别编程时，一定要区分事务和批的差别：SQLS802、事务和批的区别SETXACT_ABORT指定当Transact-SQL语句产生运行时错误时，Microsoft®SQLServer™是否自动回滚当前事务。语法SETXACT_ABORT{ON|OFF}当SETXACT_ABORT为ON时，如果Transact-SQL语句产生运行时错误，整个事务将终止并回滚。为OFF时，只回滚产生错误的Transact-SQL语句，而事务将继续进行处理。编译错误（如语法错误）不受SETXACT_ABORT的影响。对于大多数OLEDB提供程序（包括SQLServer），隐性或显式事务中的数据修改语句必须将XACT_ABORT设置为ON。唯一不需要该选项的情况是提供程序支持嵌套事务时。SQLServer事务和并发控制课件2、事务和批的区别SETXACT_ABORTSQLServ812、事务和批的区别下例导致在含有其它Transact-SQL语句的事务中发生违反外键错误。在第一个语句集中产生错误，但其它语句均成功执行且事务成功提交。在第二个语句集中，SETXACT_ABORT设置为ON。这导致语句错误使批处理终止，并使事务回滚。CREATETABLEt1(aintPRIMARYKEY)CREATETABLEt2(aintREFERENCESt1(a))GOINSERTINTOt1VALUES(1)INSERTINTOt1VALUES(3)INSERTINTOt1VALUES(4)INSERTINTOt1VALUES(6)GOSETXACT_ABORTOFFGOBEGINTRANINSERTINTOt2VALUES(1)INSERTINTOt2VALUES(2)/*Foreignkeyerror*/INSERTINTOt2VALUES(3)COMMITTRANGOSQLServer事务和并发控制课件2、事务和批的区别下例导致在含有其它Transact-SQ822、事务和批的区别SETXACT_ABORTONGOBEGINTRANINSERTINTOt2VALUES(4)INSERTINTOt2VALUES(5)/*Foreignkeyerror*/INSERTINTOt2VALUES(6)COMMITTRANGO/*Selectshowsonlykeys1and3added.Key2insertfailedandwasrolledback,butXACT_ABORTwasOFFandrestoftransactionsucceeded.Key5inserterrorwithXACT_ABORTONcausedallofthesecondtransactiontorollback.*/SELECT*FROMt2GODROPTABLEt2DROPTABLEt1GOSQLServer事务和并发控制课件2、事务和批的区别SETXACT_ABORTONSQLS832、事务和批的区别问题：如何知道XACT_ABORT为ON还是OFF呢？XACT_ABORT的默认值是什么？SQLServer事务和并发控制课件2、事务和批的区别问题：如何知道XACT_ABORT为ON还842、事务和批的区别问题：如何知道XACT_ABORT为ON还是OFF呢？DBCCUSEROPTIONS:itwillshowassetifit'sON.Ifit'sOFFthenitwillnotshow

DBCCUSEROPTIONS返回当前连接的活动（设置）的SET选项。看看VB的例子：运行DBCC（顺便看一下联机帮助文档）并查看结果SQLServer事务和并发控制课件2、事务和批的区别问题：如何知道XACT_ABORT为ON还852、事务和批的区别SETXACT_ABORT的默认值是什么？对每个连接来说，SETXACT_ABORT的默认值是OFF看看VB的例子和查询分析器SQLServer事务和并发控制课件2、事务和批的区别SETXACT_ABORT的默认值是什么868.1.2事务对并发控制和保障

数据完整的重要性1．事务与并发控制的关系如果在用户并发访问期间没有保证多个事务正确的交叉运行，用户操作相同的数据时可能会产生一些意想不到的问题。包括：（1）丢失修改或被覆盖（火车票）（2）读脏数据（银行利息）（3）不能重复读（学生成绩）（4）幻影读SQLServer事务和并发控制课件8.1.2事务对并发控制和保障

数据完整的重要性1．事872．事务对保障数据一致和完整性的作用故障轻则造成运行事务非正常中断，影响数据库中数据的正确性，重则破坏数据库，使数据库中全部或部分数据丢失。影响事务正常运行的故障有：（1）事务内部的故障（2）系统故障（3）介质故障（4）计算机病毒8.1.2事务对并发控制和保障

数据完整的重要性SQLServer事务和并发控制课件2．事务对保障数据一致和完整性的作用8.1.2事务对并888.2事务的分类和控制8.2.1事务的分类8.2.2事务控制SQLServer事务和并发控制课件8.2事务的分类和控制8.2.1事务的分类SQL898.2.1事务的分类SQLServer的事务模式可分为显式事务、隐式事务和自动事务三种。1)显式事务显式事务是指由用户执行T-sql事务语句而定义的事务，这类事务又称做用户定义事务。定义事务的语句包括：BEGINTRANSACTION:标识一个事务的开始，即启动事务。COMMITTRANSACTION、COMMITWORK：标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。ROLLBACKTRANSACTION、ROLLBACKWORK：标识一个事务的结束，说明事务执行过程中遇到错误，事务内所修改的数据被回滚到事务执行前的状态。SQLServer事务和并发控制课件8.2.1事务的分类SQLServer的事务