SQLServer事务和并发控制ppt课件

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,SQLServer事务和并发控制课件,*,大型数据库系统管理、设计与实例分析基于,SQL Server,SQLServer事务和并发控制课件,第8章 SQL Server 事务和并发控制,8.1,事务,8.2,事务的分类和控制,8.3,并发控制,8.4,事务处理实例分析,8.5,分布式事务,8.6,并发控制,SQLServer事务和并发控制课件,8.1 事务,8.1.1,事务的概念,8.1.2,事务对并发控制和保障数据完整的重要性,SQLServer事务和并发控制课件,8.1.1 事务的概念,事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。遇到错误时，可以回滚事务，取消事务内所做的所有改变，从而保证数据库中数据的一致性和可恢复性。,SQLServer事务和并发控制课件,1、事务的特性,(ACID),原子性(Atomicity)：,事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。,一致性(Consistency)：,事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。,隔离性(Isolation)：,一事务的执行不能被其它事务干扰。,持续性（永久性） (Durability) ：,指事务一旦提交，则其对数据库中数据的改变就应该是永久的,SQLServer事务和并发控制课件,2、事务和批的区别,编程时，一定要区分事务和批的差别：,批是一组整体编译的,SQL,语句，事务是一组作为单个逻辑工作单元执行的,SQL,语句。,批语句的组合发生在编译时刻，事务中语句的组合发生在执行时刻。,当在编译时，批中某个语句存在语法错误，系统将取消整个批中所有语句执行，而在运行时刻，如果事务中某个数据修改违反约束、规则等，系统默认只回退到产生该错误的语句。,如果批中产生一个运行时错误，系统默认只回退到产生该错误的语句。但当打开,XACT_ABORT,选项为,ON,时，可以系统自动回滚产生该错误的当前事务。,一个事务中也可以拥有多个批，一个批里可以有多个,SQL,语句组成的事务，事务内批的多少不影响事务的提交或回滚操作。,SQLServer事务和并发控制课件,2、事务和批的区别,SET XACT_ABORT,指定当 Transact-SQL 语句产生运行时错误时，Microsoft SQL Server 是否自动回滚当前事务。,语法,SET XACT_ABORT ON | OFF ,当 SET XACT_ABORT 为 ON 时，如果 Transact-SQL 语句产生运行时错误，整个事务将终止并回滚。为 OFF 时，只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。编译错误（如语法错误）不受 SET XACT_ABORT 的影响。,对于大多数 OLE DB 提供程序（包括 SQL Server），隐性或显式事务中的数据修改语句必须将 XACT_ABORT 设置为 ON。唯一不需要该选项的情况是提供程序支持嵌套事务时。,SQLServer事务和并发控制课件,2、事务和批的区别,下例导致在含有其它 Transact-SQL 语句的事务中发生违反外键错误。在第一个语句集中产生错误，但其它语句均成功执行且事务成功提交。在第二个语句集中，SET XACT_ABORT 设置为 ON。这导致语句错误使批处理终止，并使事务回滚。,CREATE TABLE t1 (a int PRIMARY KEY),CREATE TABLE t2 (a int REFERENCES t1(a),GO,INSERT INTO t1 VALUES (1),INSERT INTO t1 VALUES (3),INSERT INTO t1 VALUES (4),INSERT INTO t1 VALUES (6),GO,SET XACT_ABORT OFF,GO,BEGIN TRAN,INSERT INTO t2 VALUES (1),INSERT INTO t2 VALUES (2) /* Foreign key error */,INSERT INTO t2 VALUES (3),COMMIT TRAN,GO,SQLServer事务和并发控制课件,2、事务和批的区别,SET XACT_ABORT ON,GO,BEGIN TRAN,INSERT INTO t2 VALUES (4),INSERT INTO t2 VALUES (5) /* Foreign key error */,INSERT INTO t2 VALUES (6),COMMIT TRAN,GO,/* Select shows only keys 1 and 3 added.,Key 2 insert failed and was rolled back, but,XACT_ABORT was OFF and rest of transaction,succeeded.,Key 5 insert error with XACT_ABORT ON caused,all of the second transaction to roll back. */,SELECT *,FROM t2,GO,DROP TABLE t2,DROP TABLE t1,GO,SQLServer事务和并发控制课件,2、事务和批的区别,问题：如何知道XACT_ABORT为ON还是OFF呢？,XACT_ABORT的默认值是什么？,SQLServer事务和并发控制课件,2、事务和批的区别,问题：如何知道XACT_ABORT为ON还是OFF呢？,DBCC USEROPTIONS:,it will show as set if its ON. If its OFF then it will not show,DBCC USEROPTIONS,返回,当前连接,的活动（设置）的 SET 选项。,看看VB的例子：运行,DBCC（顺便看一下联机帮助文档）,并查看结果,SQLServer事务和并发控制课件,2、事务和批的区别,SET XACT_ABORT的默认值是什么？,对每个连接来说， SET XACT_ABORT的默认值是,OFF,看看VB的例子和查询分析器,SQLServer事务和并发控制课件,8.1.2 事务对并发控制和保障数据完整的重要性,1事务与并发控制的关系,如果在用户并发访问期间没有保证多个事务正确的交叉运行，用户操作相同的数据时可能会产生一些意想不到的问题。包括：,（1）丢失修改或被覆盖 （火车票）,（2）读脏数据 （银行利息）,（3）不能重复读 （学生成绩）,（4）幻影读,SQLServer事务和并发控制课件,2事务对保障数据一致和完整性的作用,故障轻则造成运行事务非正常中断，影响数据库中数据的正确性，重则破坏数据库，使数据库中全部或部分数据丢失 。,影响事务正常运行的故障有：,（1）事务内部的故障,（2）系统故障,（3）介质故障,（4）计算机病毒,8.1.2 事务对并发控制和保障数据完整的重要性,SQLServer事务和并发控制课件,8.2 事务的分类和控制,8.2.1,事务的分类,8.2.2,事务控制,SQLServer事务和并发控制课件,8.2.1 事务的分类,SQL Server 的事务模式可分为,显式事务,、,隐式事务,和,自动事务,三种。,1) 显式事务,显式事务是指由用户执行T-sql事务语句而定义的事务，这类事务又称做用户定义事务。定义事务的语句包括,：,BEGIN TRANSACTION:,标识一个事务的开始，即启动事务。,COMMIT TRANSACTION、COMMIT WORK：,标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。,ROLLBACK TRANSACTION、ROLLBACK WORK：,标识一个事务的结束，说明事务执行过程中遇到错误，事务内所修改的数据被回滚到事务执行前的状态。,SQLServer事务和并发控制课件,2) 隐式事务,在隐式事务模式下，在当前事务提交或回滚后，SQL Server自动开始下一个事务。所以，隐式事务不需要使用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,INSERT UPDATE DELETE,SELECT OPEN FETCH,需要关闭隐式事务模式时，调用SET语句关闭IMPLICIT_TRANSACTIONS OFF连接选项即可。,SQLServer事务和并发控制课件,3) 自动事务模式,在自动事务模式下，当一个语句被成功执行后，它被自动提交，而当它执行过程中产生错误时，被自动回滚。自动事务模式是SQL Server的,默认,事务管理模式，,当与SQL Server建立连接后，直接进入自动事务模式，直到使用BEGIN TRANSACTION语句开始一个显式事务，或者打开IMPLICIT_TRANSACTIONS 连接选项进入隐式事务模式为止,。,而当显式事务被提交或,IMPLICIT_TRANSACTIONS,被关闭后，SQL Server又进入自动事务管理模式。,看看VB的例子,SQLServer事务和并发控制课件,示例：,BEGIN TRAN demo,SELECT * FROM Student,INSERT INTO Student,VALUES(9711112,张三, ),SELECT * FROM Student,ROLLBACK,-,回滚整个事务,或：COMMIT,-,提交事务,SQLServer事务和并发控制课件,8.2.2 事务控制,SQL Server中有关事务的处理语句有：,命令名,作用,格式,BEGIN TRANSACTION,说明一个事务开始,BEGIN TRANsaction ,COMMIT TRANSACTION,说明一个事务结束，它的作用是提交或确认事务已经完成,COMMIT TRANsaction ,SAVE TRANSACTION,用于在事务中设置一个保存点，目的是在撤消事务时可以只撤消部分事务，以提高系统的效率,SAVE TRANsaction ,ROLLBACK TRANSACTION,说明要撤消事务，即撤消在该事务中对数据库所做的更新操作，使数据库回退到BEGIN TRANSACTION或保存点之前的状态,ROLLBACK TRANsaction | ,SQLServer事务和并发控制课件,Save transaction示例（略）1：,下例更改分给 The Gourmet Microwave 的两位作者的版税。数据库将会在两个更新间不一致，因此必须将它们分组为用户定义的事务。,BEGIN TRANSACTION royaltychange,UPDATE titleauthor,SET royaltyper = 65,FROM titleauthor, titles,WHERE royaltyper = 75,AND titleauthor.title_id = titles.title_id,AND title = The Gourmet Microwave,UPDATE titleauthor,SET royaltyper = 35,FROM titleauthor, titles,WHERE royaltyper = 25,AND titleauthor.title_id = titles.title_id,AND title = The Gourmet Microwave,SAVE TRANSACTION percentchanged,/*,After having updated the royaltyper entries for the two authors, the,user inserts the savepoint percentchanged, and then determines how a,10-percent increase in the books price would affect the authors royalty earnings.,*/,SQLServer事务和并发控制课件,Save transaction示例（略）2：,UPDATE titles,SET price = price * 1.1,WHERE title = The Gourmet Microwave,SELECT (price * royalty * ytd_sales) * royaltyper,FROM titles, titleauthor,WHERE title = The Gourmet Microwave,AND titles.title_id = titleauthor.title_id,/*,The transaction is rolled back to the savepoint,with the ROLLBACK TRANSACTION statement.,*/,ROLLBACK TRANSACTION percentchanged,COMMIT TRANSACTION,/* End of royaltychange. */,SQLServer事务和并发控制课件,说明：,在定义一个事务时，BEGIN TRANSACTION语句应与COMMIT TRANSACTION语句或ROLLBACK TRANSACTION成对出现。在SQL Server中，事务定义语句可以嵌套，但实际上只有最外层的BEGIN TRANSACTION语句和COMMIT TRANSACTION语句才能建立和提交事务；在回滚事务时，也只能使用最外层定义的事务名或存储点标记，而不能使用内层定义的事务名。,事务嵌套常用在存储过程或触发器内，它们可以使用,BEGIN TRANSACTION 。COMMIT TRANSACTION,对来相互调用。,SQLServer事务和并发控制课件,说明：,事务处理过程中的错误：,如果服务器错误使事务无法成功完成，则,SQL Server,自动回滚该事务，并释放该事务所占有的所有资源；,如果客户端与,SQL Server,的网络连接中断，那么当网络告知,SQL Server,该中断时，将回滚该连接所有未完成的事务；,如果客户端应用程序失败或客户计算机崩溃或重启，也会中断该连接，当,SQL Server,该中断时，将回滚该连接所有未完成的事务；,如果客户从该应用程序注销，所有未完成的事务也会被回滚。,可以用联网的两台计算机测试,VB,程序。,SQLServer事务和并发控制课件,8.3.1,编写有效事务的指导原则,8.3.2,避免并发问题,8.3,编写有效事务的建议,SQLServer事务和并发控制课件,8.3.1,编写有效事务的指导原则,1,不要在事务处理期间输入数据,2,浏览数据时，尽量不要打开事务,3,保持事务尽可能的短,4,灵活地使用更低的事务隔离级别,5,在事务中尽量使访问的数据量最小,SQLServer事务和并发控制课件,8.3.2,避免并发问题,为了防止并发问题，应该小心地管理隐性事务。在使用隐性事务时，,COMMIT,或,ROLLBACK,之后的下一个,Transact-SQL,语句会自动启动一个新事务。这可能在应用程序浏览数据时，甚至在要求用户输入时，打开新的事务。所以，在完成保护数据修改所需要的最后一个事务之后和再次需要一个事务来保护数据修改之前，应该关闭隐性事务。,SQLServer事务和并发控制课件,8.4,事务处理实例分析,【例,8-1,】使用事务的三种模式进行表的处理，分批执行，观察执行的过程。,USE,教学管理,GO,SELECT times=0, * FROM student,-,检查当前表中的结果,GO,-SQL Server,首先处于自动事务管理模式,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060101,*19880510*,关汉青,男,西安,计算机,信息学院,),SELECT times=1, * FROM student,-,显示,S060101,被插入。,GO,SQLServer事务和并发控制课件,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060101,*19880510*,关汉青,男,西安,计算机,信息学院,),-,服务器,:,消息,2627,，级别,14,，状态,1,，行,1,-,违反了,PRIMARY KEY,约束,PK_Student_75A278F5,。不能在对象,Student,中插入重复键。,-,语句已终止。,SELECT times=2, * FROM student,-,显示数据没有变化。,GO,BEGIN TRANSACTION,-,进入显式事务模式,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060106,*19880510*,关汉青,男,西安,计算机,信息学院,),SELECT times=3,* FROM student,-,显示,S060106,被插入,SQLServer事务和并发控制课件,ROLLBACK TRANSACTION,GO,SELECT times=4,* FROM student,-,因为执行了回滚，插入的,S060106,被撤消。,GO,SET IMPLICIT_TRANSACTIONS ON,-,进入隐式事务模式,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060107,*19880510*,关汉青,男,西安,计算机,信息学院,),SELECT times=5,* FROM student,-,显示,S060107,被插入,ROLLBACK,GO,SELECT times=6,* FROM student,-,因为执行了回滚，插入的,S060107,被撤消。,GO,SQLServer事务和并发控制课件,DELETE FROM student WHERE sno=S060101,-,删除第,1,个插入,SELECT times=7,* FROM student,-,显示,S060101,不存在,ROLLBACK,GO,SELECT times=8,* FROM student,-,因为回滚，使删除作废，所以,S060101,又重新显示存在。,GO,SET IMPLICIT_TRANSACTIONS OFF,-,隐式事务模式结束，又进入自动模式,DELETE FROM student WHERE sno=S060101,-,删除第,1,个插入,SELECT times=9,* FROM student,-,自动模式执行成功被自动提交，显示,S060101,被删除不存在。,SQLServer事务和并发控制课件,【例,8-2,】定义事务，使事务回滚到指定的保存点，分批执行，观察执行的过程。,USE,教学管理,GO,SELECT times=0, * FROM student,-,检查当前表中的结果,GO,BEGIN TRANSACTION demo,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060106,*19880510*,关汉青,男,西安,计算机,信息学院,),SAVE TRANSACTION save_demo,SQLServer事务和并发控制课件,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060107,*19890818*,杨理华,女,运城,计算机,信息学院,),SELECT times=1, * FROM student,-,显示,S060106,和,S060107,都被插入。,GO,ROLLBACK TRANSACTION save_demo,-,回滚部分事务,SELECT times=2, * FROM student,-,显示,S060107,被撤消不存在。,GO,ROLLBACK TRANSACTION,-,回滚整个事务,SELECT times=3, * FROM student,-,显示,S060106,被撤消不存在。,SQLServer事务和并发控制课件,【例,8-3,】创建数据表,stu_test3,，生成三个级别的嵌套时务，并提交该嵌套事务。观察变量,TRANCOUNT,的值的变化。,USE,教学管理,-,选择数据库必须单独在一个批中,GO,SELECT TRANCOUNT,-,变量,TRANCOUNT,的值为,0,BEGIN TRANSACTION inside1,SELECT TRANCOUNT,-,变量,TRANCOUNT,的值为,1,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060106,*19880510*,关汉青,男,西安,计算机,信息学院,),GO,BEGIN TRANSACTION inside2,SELECT TRANCOUNT,-变量TRANCOUNT的值为2,SQLServer事务和并发控制课件,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060107,*19890818*,杨理华,女,运城,计算机,信息学院,),GO,BEGIN TRANSACTION inside3,SELECT TRANCOUNT,-变量TRANCOUNT的值为3,INSERT,student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060108,*19870818*,陈向前,男,北京,计算机,信息学院),GO,COMMIT TRANSACTION inside3,SELECT TRANCOUNT,-变量TRANCOUNT的值减为2,GO,SQLServer事务和并发控制课件,COMMIT TRANSACTION inside2,SELECT TRANCOUNT,-,变量,TRANCOUNT,的值减为,1,GO,COMMIT TRANSACTION inside1,SELECT TRANCOUNT,-,变量,TRANCOUNT,的值减为,0,GO,SQLServer事务和并发控制课件,【例,8-4,】在教学管理数据的,STUDENT,表中先删除一条记录，然后再插入一条记录，通过测试错误值确定提交还是回滚。,USE,教学管理,GO,DECLARE del_error int, ins_error int,-,开始一个事务,BEGIN TRAN,-,删除一个学生,DELETE STUDENT WHERE SNO = S060308,-,为删除语句设置一个接受错误数值的变量,SELECT del_error = ERROR,SQLServer事务和并发控制课件,-,再执行插入语句,INSERT INTO STUDENT,VALUES(S060308,*19890526*,张丹宁,男,130*12,宁波,电子商务,信息学院, 162),-,为插入语句设置一个接受错误数值的变量,SELECT ins_error = ERROR,-,测试错误变量中的值,IF del_error = 0 AND ins_error = 0,BEGIN,-,成功，提交事务,COMMIT TRAN,END,SQLServer事务和并发控制课件,ELSE,BEGIN,-,有错误发生，回滚事务,IF del_error 0,PRINT ,错误发生在删除语句,IF ins_error 0,PRINT ,错误发生在插入语句,ROLLBACK TRAN,END,GO,可以将插入的,S060308,改为,SS060308,再执行一次该程序，观察结果有什么不同？,SQLServer事务和并发控制课件,8.5,分布式事务,8.5.1,分布式事务的两阶段提交,8.5.2,分布式事务的处理过程,8.5.3,分布式事务实例分析,SQLServer事务和并发控制课件,8.5 分布式事务,在大型应用领域，经常需要时务跨服务器进行数据操作，这样的事务被称作分布式事务。所以分布式事务要能够在多个服务器上执行。,按照关于分布式事务处理的X/Open XA规范，分布式事务的处理过程规定为两个阶段，就是通常说的两阶段提交。,为了简化应用程序对分布式事务的处理工作，系统提供了一个事务管理器来协调各个不同服务器对事务的处理操作，它就是MS DTC（,Distributed Transaction Coordinator,），既事务管理协调器。我们在第一章简单介绍过。,SQLServer事务和并发控制课件,8.5.1,分布式事务的两阶段提交,（1）准备阶段：当分布式事务管理器接受到提交请求后，它向所有参与与该事务的SQL Server 服务器发出准备命令。每个服务器接受到准备命令后，做好接受处理事务的准备工作，并将准备工作状态返回给事务管理器。,（2）提交阶段：当事务管理器接受到所有服务器成功准备好的信息后，它向这些服务器发出提交命令。之后所有服务器进行提交。,如果所有服务器均能成功提交事务，管理器向应用程序报告分布式事务成功提交，如若有任一个服务器未能提交，事务管理器将向所有服务器发出回滚事务命令，并向应用程序报告事务提交失败。,SQLServer事务和并发控制课件,8.5.2 分布式事务的处理过程,（1）T-SQL程序或应用程序执行,BEGIN DISTRIBUTED TRANSACTION语句启动一个分布式事务。此后，该服务器就成为分布式服务器的管理服务器。,（2）应用程序对,链接服务器,执行分布式查询或执行,远程服务器,上的存储过程。,（3）分布式事务管理服务器自动调用MS DTC，使链接服务器或远程服务器参加分布式事务处理。,（4）T-SQL应用程序执行COMMIT或ROLLBACK语句时，分布式事务管理服务器通过调用MS DTC来管理两阶段提交，使链接或远程服务器提交或回滚事务。,SQLServer事务和并发控制课件,8.5.3,分布式事务实例分析,1,分布式事务语法格式,语法格式：,BEGIN DISTRIBUTED TRANSANCTION,transanctin_name| transanctin_variable,参数说明：,transanctin_name| transanctin_variable,事务名称或事务名变量。,SQLServer事务和并发控制课件,2、分布式事务实例,【例,8-6,】有两个服务器,LinkServer1,和,LinkServer2,。在,LinkServer2,服务器上建立存储过程,student_insert_new,，其功能是向,LinkServer1,上的教学管理数据库的,student,表插入一个新行。,-,先创建链接（远程）服务器（参见第,2,章）,-,在第一台运行,SQL Server,的服务器上运行下列代码：,EXEC sp_addlinkedserver LinkServer1, ,SQLOLEDB,本地服务器名或,ip,地址,-,例如,zufe-mxh,EXEC sp_addlinkedserver LinkServer2, ,SQLOLEDB,远程服务器名或,ip,地址,-,例如,172.19.2.156,EXEC sp_configure remote access, 1,-,系统默认是,1,，一般不需要设置,SQLServer事务和并发控制课件,RECONFIGURE,-,设置,LinkServer1,的,rpc,输出属性，使得允许调用链接服务器上的存储过程。,EXEC sp_serveroption LinkServer1,rpc out,true,GO,-停止并重新启动第一台 SQL Server。,-确保使用 SQL Server 身份验证登录。在第二台 SQL Server 上运行下列代码。,EXEC sp_addlinkedserver LinkServer2, ,SQLOLEDB,本地服务器名或ip地址,-例如172.19.2.156,EXEC sp_addlinkedserver LinkServer1, ,SQLOLEDB,远程服务器名或ip地址,-例如zufe-mxh,SQLServer事务和并发控制课件,EXEC sp_configure remote access, 1,-系统默认是1，一般不需要设置,RECONFIGURE,-设置LinkServer2的rpc输出属性，使得允许调用链接服务器上的存储过程。,EXEC sp_serveroption LinkServer2,rpc out,true,GO,-在第二个服务器上添加新的远程登录ID（LinkServer1），以便允许远程服务器LinkServer1连接并执行远程过程调用。,-假设登录LinkServer2和LinkServer1的用户都是sa，并且有相同的口令。,EXEC sp_addremotelogin LinkServer1, sa, sa,GO,-,停止并重新启动第二台,SQL Server,。,SQLServer事务和并发控制课件,-在LinkServer2上创建存储过程（见第12章）。,-假设该服务器上面有数据库教学练习。,Use 教学练习,GO,CREATE procedure student_insert_new,AS,INSERT LinkServer1.教学管理.dbo.student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060112,*19870818*,许少文,男,湖州,计算机,信息学院),GO,-在第一台服务器上启动DTC开始分布式事务,-,使用,sa,登录，现在就可以在第一台,SQL Server,上执行第二台,SQL Server,上的存储过程。,SQLServer事务和并发控制课件,USE,教学管理,GO,BEGIN DISTRIBUTED TRANSACTION insert_tran,-,开始分布式事务,-,在,LinkServer1,服务器上实行对表,student,的插入,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060111,*19870818*,陈东生,男,上海,计算机,信息学院,),GO,-LinkServer1,服务器自动调用,MS DTC,使得,LinkServer2,服务器执行存储过程,student_insert_new,对表,student,的插入。,EXECUTE LinkServer2.,教学练习,.dbo.student_insert_new,COMMIT TRANSACTION,-,提交事务,SQLServer事务和并发控制课件,8.6,并发控制,8.6.1 SQL Server,锁的粒度及模式,8.6.2,封锁协议,8.6.3,事务隔离,8.6.4,死锁处理,SQLServer事务和并发控制课件,8.6 并发控制,在大型分布式数据库应用程序中，对数据库的并发访问操作是一个普遍存在的问题。SQL Server使用资源锁定的方法管理用户的并发操作。 SQL Server 2000提供了两种并发控制方法：,乐观并发控制：该方法假想用户之间不太可能发生资源冲突（事实上不是不可能），所以允许用户在不锁定任何资源的情况下执行事务。只有当用户试图修改数据时才检查资源是否冲突。该方法需要使用游标，游标中介绍。,悲观并发控制：该方法根据需要在事务的持续时间内锁定资源，从而确保事务的完整性和数据库的一致性。这是SQL Server 2000默认的并发控制方法。下面予以介绍。,SQLServer事务和并发控制课件,8.6.1 SQL Server,锁的粒度及模式,1、SQL Server 锁的粒度,1）RID：行标识符，锁定表中单行数据。,2）键值：具有索引的行数据。,3）页面：一个数据页面或索引页面。,4）区域：一组连续的8个数据页面或索引页面。,5）表： 整个表，包括其所有的数据和索引。,6）数据库：一个完整的数据库。,可以根据事务所执行的任务来灵活选择所锁定的资源粒度。,SQLServer事务和并发控制课件,2、资源锁定模式基本锁,1）共享锁：用于只读数据操作，,它允许多个并发事务对资源锁定进行读取,，但禁止其他事务对锁定资源的修改操作。,2）排它锁：,它锁定的资源不能被其它并发事务再进行任何锁定，,所以其它事务不能读取和修改。锁定的资源用于自己的数据修改。,一般更新模式是由一个事务组成，该事务先读取记录，要获取资源的共享锁，然后修改记录，此操作要求锁转换为排它锁。如果两个事务都获得了资源上的共享锁，然后试图同时更新数据，这样肯定有一个事务要将共享锁转化为排它锁，因为一个事务的排它锁与其它事务的共享锁不兼容，发生锁等待。另一个事务也会出现这个问题，由于两个事务都要转化为排它锁，并且都等待另一个事务释放共享锁，因此发生死锁。,SQLServer事务和并发控制课件,2、资源锁定模式专用锁,3）更新锁：在修改操作的初始化阶段用于锁定可能被修改的资源。一个数据修改事务在开始时直接申请更新锁，,每次只有一个事务可以获得资源的更新锁。,使用更新锁可以避免上述死锁，因为一次只有一个事务可以获得更新锁，之后当需要继续修改数据时，将更新锁转换为排它锁，否则将更新锁转换为共享锁。,SQLServer事务和并发控制课件,4）意向锁,意向锁表示如果获得一个对象的锁，说明该结点的下层对象正在被加锁。例如放置在表上的共享意向锁表示事务打算在表中的页或行上加共享锁。,意向锁可以提高性能，因为系统仅在表级上检查意向锁而无须检查下层。,意向共享锁：对一个对象加意向共享锁，表示将要对它的下层对象加共享锁。,意向排它锁：对一个对象加意向排它锁，表示将要对它的下层对象加排它锁。,意向排它共享锁：对一个对象加意向排它共享锁，表示对它加共享锁，再在它的下层对象加排它锁。,SQLServer事务和并发控制课件,5）架构锁,架构修改锁：执行表的数据定义语言（DDL）操作时使用。,架构稳定锁：编译查询时使用。它不阻塞任何事务锁，包括排它锁。,6）大容量更新锁：,当数据大容量复制到表的时候使用。,SQLServer事务和并发控制课件,8.6.2,封锁协议,在运用X锁和S锁对数据对象加锁时，需要约定一些规则：封锁协议（Locking Protocol）,何时申请X锁或S锁,持锁时间、何时释放,不同的封锁协议，在,不同的程度上,为并发操作的正确调度提供一定的保证,常用的封锁协议：三级封锁协议,SQLServer事务和并发控制课件,1级封锁协议,事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放,正常结束（COMMIT）,非正常结束（ROLLBACK）,1级封锁协议可防止丢失修改,在1级封锁协议中，如果是读数据，不需要加锁的，所以它不能保证可重复读和不读“脏”数据。,SQLServer事务和并发控制课件,1级封锁协议,T,1,T,2,Xlock A,获得,读A=16,AA-1,写回A=15,Commit,Unlock A,Xlock A,等待,等待,等待,等待,获得Xlock A,读A=15,AA-1,写回A=14,Commit,Unlock A,没有丢失修改,没有丢失修改,SQLServer事务和并发控制课件,2级封锁协议,1级封锁协议+事务T在读取数据R前必须,先加S锁,，读完后即可释放S锁,2级封锁协议可以防止丢失修改和读“脏”数据。,在2级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读。,SQLServer事务和并发控制课件,2级封锁协议,T,1,T,2,Xlock C,读C= 100,CC*2,写回C=200,ROLLBACK,(C恢复为100),Unlock C,Slock C,等待,等待,等待,等待,获得Slock C,读C=100,Commit C,Unlock C,不读“脏”数据,SQLServer事务和并发控制课件,3级封锁协议,1级封锁协议 + 事务T在读取数据R之前必须先对其加S锁，直到,事务结束才释放,3级封锁协议可防止丢失修改、读脏数据和不可重复读。,容易造成比较多的死锁,SQLServer事务和并发控制课件,3级封锁协议,T,1,T,2,Slock A,读A=50,Slock B,读B=100,求和=150, 读A=50,读B=100,求和=150,Commit,Unlock A,Unlock B,Xlock B,等待,等待,等待,等待,等待,等待,等待,等待,获得Xlock B,读B=100,BB*2,写回B=200,Commit,Unlock B,可重复读,SQLServer事务和并发控制课件,封锁协议小结,SQLServer事务和并发控制课件,8.6.3,事务隔离,为了避免产生并发访问问题，SQL Server使用,不同类型的锁对资源进行锁定，从而限制在一个事务读取数据期间其他事务锁执行的操作类型，即对事务进行隔离。,不同的并发访问问题可以通过设置不同的事务隔离级别加以解决。事务的隔离级别控制一个事务与其他事务的隔离程度，它决定该事务在读取数据时对资源所使用的锁类型。,SQLServer事务和并发控制课件,1）事务隔离级别,未提交读：,这是4种隔离级别中限制最低的级别，它仅能保证SQL Server不读取物理损坏的数据。在这种隔离级别下，读数据时不发出共享锁，也不接受排它锁，事务可以对数据执行脏读；在事务结束前可以更改数据集内的数值，行也可以出现在数据集中或从数据集消失。但可以防止产生丢失修改。（一级封锁协议）,提交读：,它要求在读取数据时控制共享锁以避免发生脏读，但数据可在事务结束前更改，这可能产生不能重复读或幻影读问题。 （二级封锁协议）,可重复读：,锁定查询中使用的所有数据以防止其他用户更新，但是其他用户可以将新的幻影行插入到数据集中，新插入的幻影行将出现在当前事物的后续读取结果集中。可重复读能够避免产生脏读和非重复读问题，但仍可能导致幻影读问题。 （三级封锁协议）,可串行读：,这是事务隔离的最高级别，它使事务之间完全隔离，所以将导致并发级别较低。在这种隔离级别下，SQL Server在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或向数据集内插入数据行，从而避免出现脏读、非重复读或幻影读等并发问题。 （四级封锁协议）,SQLServer事务和并发控制课件,2）事务隔离级别对不同类型的行为,隔离级别,丢失修改 脏数据 不可重读 幻影,未提交读,否 是 是 是,提交读,否 否 是 是,可重复读,否 否 否 是,可串行读,否 否 否 否,SQLServer事务和并发控制课件,3）设置事务隔离级别,调用T-SQL中的SET TRANSACTION INOLATION LEVEL语句可以调整事务的隔离级别，以控制由该连接所发出的所有SELECT语句的默认事务锁定行为。该语句的语法格式为：,SET TRANSACTION ISOLATION LEVEL,READ UNCOMMITTED,|READ COMMITTED,|REPEATABLE READ,|SERIALIZABLE,例如：将事务隔离级别设置为REPEATABLE READ,SET TRANSACTION ISOLATION LEVEL,REPEATABLE READ,注意：一旦设定，系统就会按这种隔离级别自动进行并发处理。,SQLServer事务和并发控制课件,设置表级锁,表级锁是由程序员进行的，可以对SELECT、INSERT、UPDATE、DELETE语句进行精确控制。,表级锁定不是系统自动完成，而是需要程序员自己根据事务的要求进行锁定，然后，系统按程序员在程序中的锁定予以执行。,一般来说，读操作需要共享锁，写操作需要排它锁。如果需要更精确，还需要一些其它专用锁。具体设置有：,设置共享锁,设置排它锁,设置专用锁,SQLServer事务和并发控制课件,设置共享锁,共享锁用于读操作。一个资源可获得多个共享锁。,使用,HOLDLOCK,设置共享锁。,例：,USE Northwind,GO,BEGIN TRANSANCTION T1,SELECT OrderID,OrderDate FROM Orders,WITH,(HOLDLOCK),SELECT COUNT(OrderID) FROM Orders,COMMIT,SQLServer事务和并发控制课件,设置排它锁,对于INSERT、UPDATE和DELETE语句使用排它锁。在并发事务中，只有一个事务能够获得资源的排它锁。,使用,TABLOCKX,设置排它锁。,例：,USE Northwind,GO,BEGIN TRANSANCTION T1,INSERT INTO Orders WITH (,TABLOCKX,),(CustomerID,OrderDate),VALUES (ALEF,2005-01-01),COMMIT,SQLServer事务和并发控制课件,设置其它专用锁,方法同上。,NOLOCK：,不要发出共享锁和排它锁。仅适用SELECT语句。,READPAST：,跳过已经锁定的行。仅适用SELECT语句。,TABLOCK：,使用大容量更新锁。,PAGLOCK：,使用页级锁。,ROWLOCK：,使用行级锁，不使用页级和表级锁。,UPDLOCK：,读取表时使用更新锁。,XLOCK：,适用于不同粒度的排它锁。,SQLServer事务和并发控制课件,8.6.4,死锁处理,SQL Server能够自动定期搜索和处理死锁问题。当检测到有死锁时， SQL Server回滚被中断的事务，并向应用程序返回1205号错误信息，未被中断的事务则继续执行。在数据库应用程序扑捉到1205号错误，可以对死锁现象做后续处理。,为减少死锁次数，在设计应用程序时：,1）尽量使一个事务在一个批内，并且要短；,2）不要在事务处理期间和用户交互；,3）灵活使用较低级别的事务隔离；,4）在事务处理期间要使访问数据量最小。,SQLServer事务和并发控制课件,Thank you very much!,谢谢您的光临！,下一章,SQLServer事务和并发控制课件,