《08第8章SQL_Server事务和并发控制.ppt》由会员分享，可在线阅读，更多相关《08第8章SQL_Server事务和并发控制.ppt（74页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、大型数据大型数据库系系统管理、管理、设计与与实例分析例分析基于基于SQL Server 唐善成通信学院第第8 8章章 SQL Server SQL Server 事务和并发控制事务和并发控制 8.1 事务 8.2 事务的分类和控制 8.3 并发控制 8.4 事务处理实例分析 8.5 分布式事务 8.6 并发控制 8.1 8.1 事务事务8.1.1 事务的概念 8.1.2 事务对并发控制和保障数据完整的重要性 8.1.1 事务的概念事务的概念事务是一个用户定义的完整的工作单元，一个事务内的所有语句被作为整体执行，要么全部执行，要么全部不执行。遇到错误时，可以回滚事务，取消事务内所做的所有改变，从
2、而保证数据库中数据的一致性和可恢复性。1、事务的特性、事务的特性(ACID)原子性(Atomicity)：事务是数据库的逻辑工作单位，事务中的操作要么都做，要么都不做。一致性(Consistency)：事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。隔离性(Isolation)：一事务的执行不能被其它事务干扰。持续性（永久性）(Durability)：指事务一旦提交，则其对数据库中数据的改变就应该是永久的 2、事务和批的区别、事务和批的区别编程时，一定要区分事务和批的差别：1.批是一组整体编译的SQL语句，事务是一组作为单个逻辑工作单元执行的SQL语句。2.批语句的组合发生在
3、编译时刻，事务中语句的组合发生在执行时刻。3.当在编译时，批中某个语句存在语法错误，系统将取消整个批中所有语句执行，而在运行时刻，如果事务中某个数据修改违反约束、规则等，系统默认只回退到产生该错误的语句。4.如果批中产生一个运行时错误，系统默认只回退到产生该错误的语句。但当打开XACT_ABORT选项为ON时，可以系统自动回滚产生该错误的当前事务。一个事务中也可以拥有多个批，一个批里可以有多个SQL语句组成的事务，事务内批的多少不影响事务的提交或回滚操作。2、事务和批的区别、事务和批的区别SET XACT_ABORT指定当 Transact-SQL 语句产生运行时错误时，Microsoft S
4、QL 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。唯一不需要该选项的情况是提供程序支持嵌套事务时。2、事务和批的区别、事务和批的区别下例导致在含
5、有其它 Transact-SQL 语句的事务中发生违反外键错误。在第一个语句集中产生错误，但其它语句均成功执行且事务成功提交。在第二个语句集中，SET XACT_ABORT 设置为 ON。这导致语句错误使批处理终止，并使事务回滚。CREATE TABLE t1(a int PRIMARY KEY)CREATE TABLE t2(a int REFERENCES t1(a)GOINSERT INTO t1 VALUES(1)INSERT INTO t1 VALUES(3)INSERT INTO t1 VALUES(4)INSERT INTO t1 VALUES(6)GOSET XACT_ABOR
6、T OFFGOBEGIN TRANINSERT INTO t2 VALUES(1)INSERT INTO t2 VALUES(2)/*Foreign key error*/INSERT INTO t2 VALUES(3)COMMIT TRANGO2、事务和批的区别、事务和批的区别SET XACT_ABORT ONGOBEGIN TRANINSERT INTO t2 VALUES(4)INSERT INTO t2 VALUES(5)/*Foreign key error*/INSERT INTO t2 VALUES(6)COMMIT TRANGO/*Select shows only keys
7、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 t2GODROP TABLE t2DROP TABLE t1GO2、事务和批的区别、事务和批的区别问题：如何知道XACT_ABORT为ON还是OFF呢？XACT_ABORT的
8、默认值是什么？2、事务和批的区别、事务和批的区别问题：如何知道XACT_ABORT为ON还是OFF呢？DBCC USEROPTIONS:it will show as set if its ON.If its OFF then it will not showDBCC USEROPTIONS返回当前连接的活动（设置）的 SET 选项。看看VB的例子：运行DBCC（顺便看一下联机帮助文档）并查看结果2、事务和批的区别、事务和批的区别SET XACT_ABORT的默认值是什么？对每个连接来说，SET XACT_ABORT的默认值是OFF看看VB的例子和查询分析器8.1.2 事务对并发控制和保障事务
9、对并发控制和保障数据完整的重要性数据完整的重要性 1事务与并发控制的关系 如果在用户并发访问期间没有保证多个事务正确的交叉运行，用户操作相同的数据时可能会产生一些意想不到的问题。包括：（1）丢失修改或被覆盖（火车票）（2）读脏数据（银行利息）（3）不能重复读（学生成绩）（4）幻影读 2事务对保障数据一致和完整性的作用 故障轻则造成运行事务非正常中断，影响数据库中数据的正确性，重则破坏数据库，使数据库中全部或部分数据丢失。影响事务正常运行的故障有：（1）事务内部的故障（2）系统故障（3）介质故障（4）计算机病毒 8.1.2 事务对并发控制和保障事务对并发控制和保障数据完整的重要性数据完整的重要性
10、 8.2 事务的分类和控制事务的分类和控制 8.2.1 事务的分类 8.2.2 事务控制 8.2.1 事务的分类事务的分类SQL Server 的事务模式可分为显式事务、隐式事务和自动事务三种。1)显式事务显式事务是指由用户执行T-sql事务语句而定义的事务，这类事务又称做用户定义事务。定义事务的语句包括：BEGIN TRANSACTION:标识一个事务的开始，即启动事务。COMMIT TRANSACTION、COMMIT WORK：标识一个事务的结束，事务内所修改的数据被永久保存到数据库中。ROLLBACK TRANSACTION、ROLLBACK WORK：标识一个事务的结束，说明事务执行
11、过程中遇到错误，事务内所修改的数据被回滚到事务执行前的状态。2)隐式事务在隐式事务模式下，在当前事务提交或回滚后，SQL Server自动开始下一个事务。所以，隐式事务不需要使用BEGIN TRANSACTION语句启动事务，而只需要用户使用ROLLBACK TRANSACTION、ROLLBACK WORK、COMMIT TRANSACTION、COMMIT WORK等语句提交或回滚事务。在提交或回滚后，SQL Server自动开始下一个事务。执行SET IMPLICIT_TRANSACTIONS ON语句可使SQL Server进入隐式事务模式。在隐式事务模式下，当执行下面任意一个语句时，
12、可使SQL Server重新启动一个事务：所有CREATE语句 ALTER TABLE 所有DROP语句 TRUNCATE TABLE GRANT REVOKE INSERT UPDATE DELETE SELECT OPEN FETCH需要关闭隐式事务模式时，调用SET语句关闭IMPLICIT_TRANSACTIONS OFF连接选项即可。3)自动事务模式在自动事务模式下，当一个语句被成功执行后，它被自动提交，而当它执行过程中产生错误时，被自动回滚。自动事务模式是SQL Server的默认默认事务管理模式，当与SQL Server建立连接后，直接进入自动事务模式，直到使用BEGIN TRAN
13、SACTION语句开始一个显式事务，或者打开IMPLICIT_TRANSACTIONS 连接选项进入隐式事务模式为止。而当显式事务被提交或而当显式事务被提交或IMPLICIT_TRANSACTIONS 被关闭后，被关闭后，SQL Server又进入自动事务管理模式。又进入自动事务管理模式。看看看看VB的例子的例子示例：BEGIN TRAN demoSELECT*FROM StudentINSERT INTO Student VALUES(9711112,张三,)SELECT*FROM StudentROLLBACK -回滚整个事务或：COMMIT -提交事务8.2.2 事务控制事务控制 SQL
14、 Server中有关事务的处理语句有：命令名作用格式BEGIN TRANSACTION说明一个事务开始 BEGIN TRANsaction COMMIT TRANSACTION说明一个事务结束，它的作用是提交或确认事务已经完成 COMMIT TRANsaction SAVE TRANSACTION用于在事务中设置一个保存点，目的是在撤消事务时可以只撤消部分事务，以提高系统的效率 SAVE TRANsaction ROLLBACK TRANSACTION 说明要撤消事务，即撤消在该事务中对数据库所做的更新操作，使数据库回退到BEGIN TRANSACTION或保存点之前的状态 ROLLBACK
15、TRANsaction|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 t
16、itleauthor SET royaltyper=35 FROM titleauthor,titles WHERE royaltyper=25 AND titleauthor.title_id=titles.title_id AND title=The Gourmet MicrowaveSAVE TRANSACTION percentchanged/*After having updated the royaltyper entries for the two authors,the user inserts the savepoint percentchanged,and then det
17、ermines how a 10-percent increase in the books price would affect the authors royalty earnings.*/Save transaction示例（略）2：UPDATE titles SET price=price*1.1 WHERE title=The Gourmet MicrowaveSELECT(price*royalty*ytd_sales)*royaltyper FROM titles,titleauthor WHERE title=The Gourmet Microwave AND titles.t
18、itle_id=titleauthor.title_id/*The transaction is rolled back to the savepointwith the ROLLBACK TRANSACTION statement.*/ROLLBACK TRANSACTION percentchangedCOMMIT TRANSACTION/*End of royaltychange.*/说明：在定义一个事务时，BEGIN TRANSACTION语句应与COMMIT TRANSACTION语句或ROLLBACK TRANSACTION成对出现。在SQL Server中，事务定义语句可以嵌套，
19、但实际上只有最外层的BEGIN TRANSACTION语句和COMMIT TRANSACTION语句才能建立和提交事务；在回滚事务时，也只能使用最外层定义的事务名或存储点标记，而不能使用内层定义的事务名。事务嵌套常用在存储过程或触发器内，它们可以使用BEGIN TRANSACTION。COMMIT TRANSACTION对来相互调用。说明：事务处理过程中的错误：1.如果服务器错误使事务无法成功完成，则SQL Server自动回滚该事务，并释放该事务所占有的所有资源；2.如果客户端与SQL Server的网络连接中断，那么当网络告知SQL Server该中断时，将回滚该连接所有未完成的事务；3.
20、如果客户端应用程序失败或客户计算机崩溃或重启，也会中断该连接，当SQL Server该中断时，将回滚该连接所有未完成的事务；4.如果客户从该应用程序注销，所有未完成的事务也会被回滚。可以用联网的两台计算机测试可以用联网的两台计算机测试VB程序。程序。8.3.1 编写有效事务的指导原则 8.3.2 避免并发问题 8.3 编写有效事务的建议编写有效事务的建议 8.3.1 编写有效事务的指导原则编写有效事务的指导原则 1不要在事务处理期间输入数据 2浏览数据时，尽量不要打开事务 3保持事务尽可能的短 4灵活地使用更低的事务隔离级别 5在事务中尽量使访问的数据量最小 8.3.2 避免并发问题避免并发问
21、题 为了防止并发问题，应该小心地管理隐性事务。在使用隐性事务时，COMMIT或ROLLBACK之后的下一个Transact-SQL语句会自动启动一个新事务。这可能在应用程序浏览数据时，甚至在要求用户输入时，打开新的事务。所以，在完成保护数据修改所需要的最后一个事务之后和再次需要一个事务来保护数据修改之前，应该关闭隐性事务。8.4 事务处理实例分析事务处理实例分析【例8-1】使用事务的三种模式进行表的处理，分批执行，观察执行的过程。USE 教学管理GOSELECT times=0,*FROM student-检查当前表中的结果GO-SQL Server首先处于自动事务管理模式INSERT stu
22、dent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES(S060101,*19880510*,关汉青,男,西安,计算机,信息学院)SELECT times=1,*FROM student-显示S060101被插入。GOINSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES(S060101,*19880510*,关 汉青,男,西 安,计算机,信息学院)-服务器:消息 2627，级别 14，状态 1，行 1-违反了 PRIMARY KEY 约束 PK_Studen
23、t_75A278F5。不能在对象 Student 中插入重复键。-语句已终止。SELECT times=2,*FROM student -显示数据没有变化。GOBEGIN TRANSACTION -进入显式事务模式INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES(S060106,*19880510*,关 汉青,男,西 安,计算机,信息学院)SELECT times=3,*FROM student -显示S060106被插入ROLLBACK TRANSACTION GOSELECT times=4,*FROM s
24、tudent -因因为为执执行行了了回回滚滚，插插入入的的S060106被撤消。被撤消。GOSET IMPLICIT_TRANSACTIONS ON -进入隐式事务模式进入隐式事务模式INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES(S060107,*19880510*,关关汉汉青青,男男,西西安安,计算机计算机,信息学院信息学院)SELECT times=5,*FROM student -显示显示S060107被插入被插入ROLLBACK GOSELECT times=6,*FROM student -因因为
25、为执执行行了了回回滚滚，插插入入的的S060107被撤消。被撤消。GODELETE FROM student WHERE sno=S060101 -删除第删除第1个插入个插入SELECT times=7,*FROM student -显示显示S060101不存在不存在ROLLBACKGO SELECT times=8,*FROM student -因为回滚，使删除作废，所因为回滚，使删除作废，所以以S060101又重新显示存在。又重新显示存在。GOSET IMPLICIT_TRANSACTIONS OFF -隐式事务模式结束，又隐式事务模式结束，又进入自动模式进入自动模式 DELETE FRO
26、M student WHERE sno=S060101-删除第删除第1个插入个插入SELECT times=9,*FROM student -自动模式执行成功自动模式执行成功被自动提交，显示被自动提交，显示S060101被删除不存在。被删除不存在。【例例8-2】定义事务，使事务回滚到指定的保存点，分批执行，观察执行定义事务，使事务回滚到指定的保存点，分批执行，观察执行的过程。的过程。USE 教学管理GOSELECT times=0,*FROM student -检查当前表中的结果GOBEGIN TRANSACTION demoINSERT student(sno,sssn,sname,ssex
27、,smtel,scity,smajor,sdepa)VALUES(S060106,*19880510*,关汉青,男,西安,计算机,信息学院)SAVE TRANSACTION save_demo INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES(S060107,*19890818*,杨杨理理华华,女女,运运城城,计算机计算机,信息学院信息学院)SELECT times=1,*FROM student -显显示示S060106和和S060107都被插入。都被插入。GOROLLBACK TRANSACTION sav
28、e_demo -回滚部分事务回滚部分事务SELECT times=2,*FROM student -显显示示S060107被被撤撤消不存在。消不存在。GOROLLBACK TRANSACTION -回滚整个事务回滚整个事务SELECT times=3,*FROM student -显示显示S060106被撤消不存在。被撤消不存在。【例例8-3】创建数据表创建数据表stu_test3，生成三个级别的嵌套事务，并提交该生成三个级别的嵌套事务，并提交该嵌套事务。观察变量嵌套事务。观察变量TRANCOUNT的值的变化。的值的变化。USE 教学管理 -选择数据库必须单独在一个批中GOSELECT TRA
29、NCOUNT -变量TRANCOUNT的值为0BEGIN TRANSACTION inside1 SELECT TRANCOUNT -变量TRANCOUNT的值为1INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES(S060106,*19880510*,关汉青,男,西安,计算机,信息学院)GOBEGIN TRANSACTION inside2 SELECT TRANCOUNT -变量TRANCOUNT的值为2 INSERT student(sno,sssn,sname,ssex,smtel,scity,smajo
30、r,sdepa)VALUES(S060107,*19890818*,杨杨理理华华,女女,运运城城,计算机计算机,信息学院信息学院)GOBEGIN TRANSACTION inside3 SELECT TRANCOUNT -变变量量TRANCOUNT的的值为值为3INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES(S060108,*19870818*,陈向前陈向前,男男,北京北京,计算机计算机,信息学院信息学院)GOCOMMIT TRANSACTION inside3 SELECT TRANCOUNT -变量变量T
31、RANCOUNT的值减为的值减为2GOCOMMIT TRANSACTION inside2 SELECT TRANCOUNT-变量变量TRANCOUNT的值减为的值减为1GOCOMMIT TRANSACTION inside1 SELECT TRANCOUNT-变量变量TRANCOUNT的值减为的值减为0 GO【例例8-4】在教学管理数据的在教学管理数据的STUDENT表中先删除一条记录，然后再表中先删除一条记录，然后再插入一条记录，通过测试错误值确定提交还是回滚。插入一条记录，通过测试错误值确定提交还是回滚。USE 教学管理GODECLARE del_error int,ins_error
32、int-开始一个事务BEGIN TRAN-删除一个学生DELETE STUDENT WHERE SNO=S060308-为删除语句设置一个接受错误数值的变量SELECT del_error=ERROR-再执行插入语句再执行插入语句INSERT INTO STUDENT VALUES(S060308,*19890526*,张丹宁张丹宁,男男,130*12,宁波宁波,电子商务电子商务,信息学院信息学院,162)-为插入语句设置一个接受错误数值的变量为插入语句设置一个接受错误数值的变量SELECT ins_error=ERROR-测试错误变量中的值测试错误变量中的值IF del_error=0 AN
33、D ins_error=0 BEGIN -成功，提交事务成功，提交事务 COMMIT TRAN ENDELSE BEGIN -有错误发生，回滚事务有错误发生，回滚事务 IF del_error 0 PRINT 错误发生在删除语句错误发生在删除语句 IF ins_error 0 PRINT 错误发生在插入语句错误发生在插入语句 ROLLBACK TRAN END GO 可以将插入的可以将插入的S060308改为改为SS060308再执行再执行一次该程序，观察结果有什么不同？一次该程序，观察结果有什么不同？8.5 分布式事务分布式事务 8.5.1 分布式事务的两阶段提交 8.5.2 分布式事务的处
34、理过程 8.5.3 分布式事务实例分析 8.5 分布式事务在大型应用领域，经常需要事务跨服务器进行数据操作，这样的事务被称作分布式事务。所以分布式事务要能够在多个服务器上执行。按照关于分布式事务处理的X/Open XA规范，分布式事务的处理过程规定为两个阶段，就是通常说的两阶段提交。为了简化应用程序对分布式事务的处理工作，系统提供了一个事务管理器来协调各个不同服务器对事务的处理操作，它就是MS DTC（Distributed Transaction Coordinator），既事务管理协调器。我们在第一章简单介绍过。8.5.1 分布式事务的两阶段提交分布式事务的两阶段提交（1）准备阶段：当分布
35、式事务管理器接受到提交请求后，它向所有参与该事务的SQL Server 服务器发出准备命令。每个服务器接受到准备命令后，做好接受处理事务的准备工作，并将准备工作状态返回给事务管理器。（2）提交阶段：当事务管理器接受到所有服务器成功准备好的信息后，它向这些服务器发出提交命令。之后所有服务器进行提交。如果所有服务器均能成功提交事务，管理器向应用程序报告分布式事务成功提交，如若有任一个服务器未能提交，事务管理器将向所有服务器发出回滚事务命令，并向应用程序报告事务提交失败。8.5.2 分布式事务的处理过程分布式事务的处理过程（1）T-SQL程序或应用程序执行BEGIN DISTRIBUTED TRAN
36、SACTION语句启动一个分布式事务。此后，该服务器就成为分布式服务器的管理服务器。（2）应用程序对链接服务器执行分布式查询或执行远程服务器上的存储过程。（3）分布式事务管理服务器自动调用MS DTC，使链接服务器或远程服务器参加分布式事务处理。（4）T-SQL应用程序执行COMMIT或ROLLBACK语句时，分布式事务管理服务器通过调用MS DTC来管理两阶段提交，使链接或远程服务器提交或回滚事务。8.5.3 分布式事务实例分析分布式事务实例分析 1分布式事务语法格式语法格式：BEGIN DISTRIBUTED TRANSANCTION transanctin_name|transancti
37、n_variable参数说明：transanctin_name|transanctin_variable事务名称或事务名变量。2、分布式事务实例【例8-6】有两个服务器LinkServer1和LinkServer2。在LinkServer2服务器上建立存储过程student_insert_new，其功能是向LinkServer1上的教学管理数据库的student表插入一个新行。-先创建链接（远程）服务器（参见第2章）-在第一台运行SQL Server的服务器上运行下列代码：EXEC sp_addlinkedserver LinkServer1,SQLOLEDB,本地服务器名或ip地址 -例如z
38、ufe-mxhEXEC sp_addlinkedserver LinkServer2,SQLOLEDB,远程服务器名或ip地址 -例如172.19.2.156EXEC sp_configure remote access,1 -系统默认是1，一般不需要设置RECONFIGURE-设置设置LinkServer1的的rpc输出属性，使得允许调用链接服务器上的存输出属性，使得允许调用链接服务器上的存储过程。储过程。EXEC sp_serveroption LinkServer1,rpc out,trueGO -停止并重新启动第一台停止并重新启动第一台 SQL Server。-确保使用确保使用 SQL
39、 Server 身份验证登录。在第二台身份验证登录。在第二台 SQL Server 上运行上运行下列代码。下列代码。EXEC sp_addlinkedserver LinkServer2,SQLOLEDB,本地服务器名或本地服务器名或ip地址地址 -例如例如172.19.2.156 EXEC sp_addlinkedserver LinkServer1,SQLOLEDB,远程服务器名或远程服务器名或ip地址地址 -例如例如zufe-mxh EXEC sp_configure remote access,1 -系系统统默默认认是是1，一一般不需要设置般不需要设置RECONFIGURE-设设置置L
40、inkServer2的的rpc输输出出属属性性，使使得得允允许许调调用用链链接接服服务务器器上上的的存存储储过过程。程。EXEC sp_serveroption LinkServer2,rpc out,trueGO-在在第第二二个个服服务务器器上上添添加加新新的的远远程程登登录录ID（LinkServer1），以以便便允允许许远远程服务器程服务器LinkServer1连接并执行远程过程调用。连接并执行远程过程调用。-假假设设登登录录LinkServer2和和LinkServer1的的用用户户都都是是sa，并并且且有有相相同同的的口令。口令。EXEC sp_addremotelogin Link
41、Server1,sa,saGO-停止并重新启动第二台停止并重新启动第二台 SQL Server。-在在LinkServer2上创建存储过程（见第上创建存储过程（见第12章）。章）。-假设该服务器上面有数据库假设该服务器上面有数据库教学练习教学练习。Use 教学练习教学练习GOCREATE procedure student_insert_new ASINSERT LinkServer1.教学管理教学管理.dbo.student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES(S060112,*19870818*,许少文许少文,男男,湖州湖
42、州,计算机计算机,信息学院信息学院)GO-在第一台服务器上启动在第一台服务器上启动DTC开始分布式事务开始分布式事务-使用使用 sa 登录，现在就可以在第一台登录，现在就可以在第一台 SQL Server 上执行第二台上执行第二台 SQL Server 上的存储过程。上的存储过程。USE 教学管理教学管理GOBEGIN DISTRIBUTED TRANSACTION insert_tran -开始分布式事务开始分布式事务-在在LinkServer1服务器上实行对表服务器上实行对表student的插入的插入INSERT student(sno,sssn,sname,ssex,smtel,scit
43、y,smajor,sdepa)VALUES(S060111,*19870818*,陈陈东东生生,男男,上上海海,计算机计算机,信息学院信息学院)GO-LinkServer1服服务务器器自自动动调调用用MS DTC使使得得LinkServer2服服务务器器执执行行存存储过程储过程student_insert_new对表对表student的插入。的插入。EXECUTE LinkServer2.教学练习教学练习.dbo.student_insert_newCOMMIT TRANSACTION -提交事务提交事务 8.6 并发控制并发控制 8.6.1 SQL Server锁的粒度及模式 8.6.2 封
44、锁协议 8.6.3 事务隔离 8.6.4 死锁处理 8.6 并发控制并发控制在大型分布式数据库应用程序中，对数据库的并发访问操作是一个普遍存在的问题。SQL Server使用资源锁定的方法管理用户的并发操作。SQL Server 2000提供了两种并发控制方法：乐观并发控制：该方法假想用户之间不太可能发生资源冲突（事实上不是不可能），所以允许用户在不锁定任何资源的情况下执行事务。只有当用户试图修改数据时才检查资源是否冲突。该方法需要使用游标，游标中介绍。悲观并发控制：该方法根据需要在事务的持续时间内锁定资源，从而确保事务的完整性和数据库的一致性。这是SQL Server 2000默认的并发控制
45、方法。下面予以介绍。8.6.1 SQL Server锁的粒度及模式 1、SQL Server 锁的粒度1）RID：行标识符，锁定表中单行数据。2）键值：具有索引的行数据。3）页面：一个数据页面或索引页面。4）区域：一组连续的8个数据页面或索引页面。5）表：整个表，包括其所有的数据和索引。6）数据库：一个完整的数据库。可以根据事务所执行的任务来灵活选择所锁定的资源粒度。2、资源锁定模式、资源锁定模式基本锁基本锁1）共享锁：用于只读数据操作，它允许多个并发事务对资源锁定进行读取，但禁止其他事务对锁定资源的修改操作。2）排它锁：它锁定的资源不能被其它并发事务再进行任何锁定，所以其它事务不能读取和修改
46、。锁定的资源用于自己的数据修改。一般更新模式是由一个事务组成，该事务先读取记录，要获取资源的共享锁，然后修改记录，此操作要求锁转换为排它锁。如果两个事务都获得了资源上的共享锁，然后试图同时更新数据，这样肯定有一个事务要将共享锁转化为排它锁，因为一个事务的排它锁与其它事务的共享锁不兼容，发生锁等待。另一个事务也会出现这个问题，由于两个事务都要转化为排它锁，并且都等待另一个事务释放共享锁，因此发生死锁。2、资源锁定模式、资源锁定模式专用锁专用锁3）更新锁：在修改操作的初始化阶段用于锁定可能被修改的资源。一个数据修改事务在开始时直接申请更新锁，每次只有一个事务可以获得资源的更新锁。使用更新锁可以避免
47、上述死锁，因为一次只有一个事务可以获得更新锁，之后当需要继续修改数据时，将更新锁转换为排它锁，否则将更新锁转换为共享锁。4）意向锁）意向锁意向锁表示如果获得一个对象的锁，说明该结点的下层对象正在被加锁。例如放置在表上的共享意向锁表示事务打算在表中的页或行上加共享锁。意向锁可以提高性能，因为系统仅在表级上检查意向锁而无须检查下层。意向共享锁：对一个对象加意向共享锁，表示将要对它的下层对象加共享锁。意向排它锁：对一个对象加意向排它锁，表示将要对它的下层对象加排它锁。意向排它共享锁：对一个对象加意向排它共享锁，表示对它加共享锁，再在它的下层对象加排它锁。5）架构锁）架构锁架构修改锁：执行表的数据定义
48、语言（DDL）操作时使用。架构稳定锁：编译查询时使用。它不阻塞任何事务锁，包括排它锁。6）大容量更新锁：当数据大容量复制到表的时候使用。8.6.2 封锁协议封锁协议 在运用X锁和S锁对数据对象加锁时，需要约定一些规则：封锁协议（Locking Protocol）何时申请X锁或S锁持锁时间、何时释放 不同的封锁协议，在不同的程度上为并发操作的正确调度提供一定的保证常用的封锁协议：三级封锁协议1级封锁协议级封锁协议事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放正常结束（COMMIT）非正常结束（ROLLBACK）1级封锁协议可防止丢失修改在1级封锁协议中，如果是读数据，不需要加锁的，所
49、以它不能保证可重复读和不读“脏”数据。1级封锁协议级封锁协议T1T2 Xlock A 获得 读A=16AA-1 写回A=15 Commit Unlock AXlock A等待等待等待等待获得Xlock A读A=15AA-1写回A=14CommitUnlock A没有丢失修改没有丢失修改 没有丢失修改没有丢失修改2级封锁协议级封锁协议1级封锁协议+事务T在读取数据R前必须先加S锁，读完后即可释放S锁2级封锁协议可以防止丢失修改和读“脏”数据。在2级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读。2级封锁协议级封锁协议T1T2 Xlock C 读C=100 CC*2 写回C=200
50、 ROLLBACK (C恢复为100)Unlock C Slock C等待等待等待等待获得Slock C读C=100Commit CUnlock C不读不读“脏脏”数据数据 3级封锁协议级封锁协议1级封锁协议+事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放3级封锁协议可防止丢失修改、读脏数据和不可重复读。容易造成比较多的死锁3级封锁协议级封锁协议T1T2 Slock A 读A=50 Slock B 读B=100 求和=150 读A=50 读B=100 求和=150 Commit Unlock A Unlock B Xlock B等待等待等待 等待等待等待等待等待获得Xlock B读