SQLServer事務(wù)和并發(fā)控制.ppt

1、大型數(shù)據(jù)庫(kù)系統(tǒng)管理、設(shè)計(jì)與實(shí)例分析基于SQL Server,唐善成 通信學(xué)院 ,第8章 SQL Server 事務(wù)和并發(fā)控制,8.1 事務(wù) 8.2 事務(wù)的分類和控制 8.3 并發(fā)控制 8.4 事務(wù)處理實(shí)例分析 8.5 分布式事務(wù) 8.6 并發(fā)控制,8.1 事務(wù),8.1.1 事務(wù)的概念 8.1.2 事務(wù)對(duì)并發(fā)控制和保障數(shù)據(jù)完整的重要性,8.1.1 事務(wù)的概念,事務(wù)是一個(gè)用戶定義的完整的工作單元，一個(gè)事務(wù)內(nèi)的所有語(yǔ)句被作為整體執(zhí)行，要么全部執(zhí)行，要么全部不執(zhí)行。遇到錯(cuò)誤時(shí)，可以回滾事務(wù)，取消事務(wù)內(nèi)所做的所有改變，從而保證數(shù)據(jù)庫(kù)中數(shù)據(jù)的一致性和可恢復(fù)性,1、事務(wù)的特性(ACID,原子性(Atomic

2、ity)：事務(wù)是數(shù)據(jù)庫(kù)的邏輯工作單位，事務(wù)中的操作要么都做，要么都不做。 一致性(Consistency)：事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變到另一個(gè)一致性狀態(tài)。 隔離性(Isolation)：一事務(wù)的執(zhí)行不能被其它事務(wù)干擾。 持續(xù)性（永久性） (Durability) ：指事務(wù)一旦提交，則其對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就應(yīng)該是永久的,2、事務(wù)和批的區(qū)別,編程時(shí)，一定要區(qū)分事務(wù)和批的差別： 批是一組整體編譯的SQL語(yǔ)句，事務(wù)是一組作為單個(gè)邏輯工作單元執(zhí)行的SQL語(yǔ)句。 批語(yǔ)句的組合發(fā)生在編譯時(shí)刻，事務(wù)中語(yǔ)句的組合發(fā)生在執(zhí)行時(shí)刻。 當(dāng)在編譯時(shí)，批中某個(gè)語(yǔ)句存在語(yǔ)法錯(cuò)誤，系統(tǒng)將取消整個(gè)批中所有

3、語(yǔ)句執(zhí)行，而在運(yùn)行時(shí)刻，如果事務(wù)中某個(gè)數(shù)據(jù)修改違反約束、規(guī)則等，系統(tǒng)默認(rèn)只回退到產(chǎn)生該錯(cuò)誤的語(yǔ)句。 如果批中產(chǎn)生一個(gè)運(yùn)行時(shí)錯(cuò)誤，系統(tǒng)默認(rèn)只回退到產(chǎn)生該錯(cuò)誤的語(yǔ)句。但當(dāng)打開XACT_ABORT選項(xiàng)為ON時(shí)，可以系統(tǒng)自動(dòng)回滾產(chǎn)生該錯(cuò)誤的當(dāng)前事務(wù)。 一個(gè)事務(wù)中也可以擁有多個(gè)批，一個(gè)批里可以有多個(gè)SQL語(yǔ)句組成的事務(wù)，事務(wù)內(nèi)批的多少不影響事務(wù)的提交或回滾操作,2、事務(wù)和批的區(qū)別,SET XACT_ABORT 指定當(dāng) Transact-SQL 語(yǔ)句產(chǎn)生運(yùn)行時(shí)錯(cuò)誤時(shí)，Microsoft SQL Server 是否自動(dòng)回滾當(dāng)前事務(wù)。 語(yǔ)法 SET XACT_ABORT ON | OFF 當(dāng) SET XACT

4、_ABORT 為 ON 時(shí)，如果 Transact-SQL 語(yǔ)句產(chǎn)生運(yùn)行時(shí)錯(cuò)誤，整個(gè)事務(wù)將終止并回滾。為 OFF 時(shí)，只回滾產(chǎn)生錯(cuò)誤的 Transact-SQL 語(yǔ)句，而事務(wù)將繼續(xù)進(jìn)行處理。編譯錯(cuò)誤（如語(yǔ)法錯(cuò)誤）不受 SET XACT_ABORT 的影響。 對(duì)于大多數(shù) OLE DB 提供程序（包括 SQL Server），隱性或顯式事務(wù)中的數(shù)據(jù)修改語(yǔ)句必須將 XACT_ABORT 設(shè)置為 ON。唯一不需要該選項(xiàng)的情況是提供程序支持嵌套事務(wù)時(shí),2、事務(wù)和批的區(qū)別,下例導(dǎo)致在含有其它 Transact-SQL 語(yǔ)句的事務(wù)中發(fā)生違反外鍵錯(cuò)誤。在第一個(gè)語(yǔ)句集中產(chǎn)生錯(cuò)誤，但其它語(yǔ)句均成功執(zhí)行且事務(wù)成功提

5、交。在第二個(gè)語(yǔ)句集中，SET XACT_ABORT 設(shè)置為 ON。這導(dǎo)致語(yǔ)句錯(cuò)誤使批處理終止，并使事務(wù)回滾。 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)

6、INSERT INTO t2 VALUES (2) /* Foreign key error */ INSERT INTO t2 VALUES (3) COMMIT TRAN GO,2、事務(wù)和批的區(qū)別,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 ins

7、ert 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,2、事務(wù)和批的區(qū)別,問(wèn)題：如何知道XACT_ABORT為ON還是OFF呢？ XACT_ABORT的默認(rèn)值是什么,2、事務(wù)和批的區(qū)別,問(wèn)

8、題：如何知道XACT_ABORT為ON還是OFF呢？ DBCC USEROPTIONS: it will show as set if its ON. If its OFF then it will not show DBCC USEROPTIONS 返回當(dāng)前連接的活動(dòng)（設(shè)置）的 SET 選項(xiàng)。 看看VB的例子：運(yùn)行DBCC（順便看一下聯(lián)機(jī)幫助文檔）并查看結(jié)果,2、事務(wù)和批的區(qū)別,SET XACT_ABORT的默認(rèn)值是什么？ 對(duì)每個(gè)連接來(lái)說(shuō)， SET XACT_ABORT的默認(rèn)值是OFF 看看VB的例子和查詢分析器,8.1.2 事務(wù)對(duì)并發(fā)控制和保障數(shù)據(jù)完整的重要性,1事務(wù)與并發(fā)控制的關(guān)系 如果

9、在用戶并發(fā)訪問(wèn)期間沒(méi)有保證多個(gè)事務(wù)正確的交叉運(yùn)行，用戶操作相同的數(shù)據(jù)時(shí)可能會(huì)產(chǎn)生一些意想不到的問(wèn)題。包括： （1）丟失修改或被覆蓋 （火車票） （2）讀臟數(shù)據(jù) （銀行利息） （3）不能重復(fù)讀 （學(xué)生成績(jī)） （4）幻影讀,2事務(wù)對(duì)保障數(shù)據(jù)一致和完整性的作用 故障輕則造成運(yùn)行事務(wù)非正常中斷，影響數(shù)據(jù)庫(kù)中數(shù)據(jù)的正確性，重則破壞數(shù)據(jù)庫(kù)，使數(shù)據(jù)庫(kù)中全部或部分?jǐn)?shù)據(jù)丟失 。 影響事務(wù)正常運(yùn)行的故障有： （1）事務(wù)內(nèi)部的故障 （2）系統(tǒng)故障 （3）介質(zhì)故障 （4）計(jì)算機(jī)病毒,8.1.2 事務(wù)對(duì)并發(fā)控制和保障數(shù)據(jù)完整的重要性,8.2 事務(wù)的分類和控制,8.2.1 事務(wù)的分類 8.2.2 事務(wù)控制,8.2.1 事

10、務(wù)的分類,SQL Server 的事務(wù)模式可分為顯式事務(wù)、隱式事務(wù)和自動(dòng)事務(wù)三種。 1) 顯式事務(wù) 顯式事務(wù)是指由用戶執(zhí)行T-sql事務(wù)語(yǔ)句而定義的事務(wù)，這類事務(wù)又稱做用戶定義事務(wù)。定義事務(wù)的語(yǔ)句包括： BEGIN TRANSACTION:標(biāo)識(shí)一個(gè)事務(wù)的開始，即啟動(dòng)事務(wù)。 COMMIT TRANSACTION、COMMIT WORK：標(biāo)識(shí)一個(gè)事務(wù)的結(jié)束，事務(wù)內(nèi)所修改的數(shù)據(jù)被永久保存到數(shù)據(jù)庫(kù)中。 ROLLBACK TRANSACTION、ROLLBACK WORK：標(biāo)識(shí)一個(gè)事務(wù)的結(jié)束，說(shuō)明事務(wù)執(zhí)行過(guò)程中遇到錯(cuò)誤，事務(wù)內(nèi)所修改的數(shù)據(jù)被回滾到事務(wù)執(zhí)行前的狀態(tài),2) 隱式事務(wù) 在隱式事務(wù)模式下，在當(dāng)前

11、事務(wù)提交或回滾后，SQL Server自動(dòng)開始下一個(gè)事務(wù)。所以，隱式事務(wù)不需要使用BEGIN TRANSACTION語(yǔ)句啟動(dòng)事務(wù)，而只需要用戶使用ROLLBACK TRANSACTION、ROLLBACK WORK、COMMIT TRANSACTION、COMMIT WORK等語(yǔ)句提交或回滾事務(wù)。在提交或回滾后，SQL Server自動(dòng)開始下一個(gè)事務(wù)。 執(zhí)行SET IMPLICIT_TRANSACTIONS ON語(yǔ)句可使SQL Server進(jìn)入隱式事務(wù)模式。 在隱式事務(wù)模式下，當(dāng)執(zhí)行下面任意一個(gè)語(yǔ)句時(shí)，可使SQL Server重新啟動(dòng)一個(gè)事務(wù)： 所有CREATE語(yǔ)句 ALTER TABLE 所

12、有DROP語(yǔ)句 TRUNCATE TABLE GRANT REVOKE INSERT UPDATE DELETE SELECT OPEN FETCH 需要關(guān)閉隱式事務(wù)模式時(shí)，調(diào)用SET語(yǔ)句關(guān)閉IMPLICIT_TRANSACTIONS OFF連接選項(xiàng)即可,3) 自動(dòng)事務(wù)模式 在自動(dòng)事務(wù)模式下，當(dāng)一個(gè)語(yǔ)句被成功執(zhí)行后，它被自動(dòng)提交，而當(dāng)它執(zhí)行過(guò)程中產(chǎn)生錯(cuò)誤時(shí)，被自動(dòng)回滾。自動(dòng)事務(wù)模式是SQL Server的默認(rèn)事務(wù)管理模式，當(dāng)與SQL Server建立連接后，直接進(jìn)入自動(dòng)事務(wù)模式，直到使用BEGIN TRANSACTION語(yǔ)句開始一個(gè)顯式事務(wù)，或者打開IMPLICIT_TRANSACTIONS

13、連接選項(xiàng)進(jìn)入隱式事務(wù)模式為止。 而當(dāng)顯式事務(wù)被提交或IMPLICIT_TRANSACTIONS 被關(guān)閉后，SQL Server又進(jìn)入自動(dòng)事務(wù)管理模式。 看看VB的例子,示例,BEGIN TRAN demo SELECT * FROM Student INSERT INTO Student VALUES(9711112,張三, ) SELECT * FROM Student ROLLBACK - 回滾整個(gè)事務(wù) 或：COMMIT - 提交事務(wù),8.2.2 事務(wù)控制,SQL Server中有關(guān)事務(wù)的處理語(yǔ)句有,Save transaction示例（略）1,下例更改分給 The Gourmet Mic

14、rowave 的兩位作者的版稅。數(shù)據(jù)庫(kù)將會(huì)在兩個(gè)更新間不一致，因此必須將它們分組為用戶定義的事務(wù)。 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

15、, 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

16、 in the books price would affect the authors royalty earnings. *,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 = title

17、author.title_id /* The transaction is rolled back to the savepoint with the ROLLBACK TRANSACTION statement. */ ROLLBACK TRANSACTION percentchanged COMMIT TRANSACTION /* End of royaltychange. *,說(shuō)明： 在定義一個(gè)事務(wù)時(shí)，BEGIN TRANSACTION語(yǔ)句應(yīng)與COMMIT TRANSACTION語(yǔ)句或ROLLBACK TRANSACTION成對(duì)出現(xiàn)。在SQL Server中，事務(wù)定義語(yǔ)句可以嵌套，但實(shí)際

18、上只有最外層的BEGIN TRANSACTION語(yǔ)句和COMMIT TRANSACTION語(yǔ)句才能建立和提交事務(wù)；在回滾事務(wù)時(shí)，也只能使用最外層定義的事務(wù)名或存儲(chǔ)點(diǎn)標(biāo)記，而不能使用內(nèi)層定義的事務(wù)名。 事務(wù)嵌套常用在存儲(chǔ)過(guò)程或觸發(fā)器內(nèi)，它們可以使用BEGIN TRANSACTION 。COMMIT TRANSACTION對(duì)來(lái)相互調(diào)用,說(shuō)明： 事務(wù)處理過(guò)程中的錯(cuò)誤： 如果服務(wù)器錯(cuò)誤使事務(wù)無(wú)法成功完成，則SQL Server自動(dòng)回滾該事務(wù)，并釋放該事務(wù)所占有的所有資源； 如果客戶端與SQL Server的網(wǎng)絡(luò)連接中斷，那么當(dāng)網(wǎng)絡(luò)告知SQL Server該中斷時(shí)，將回滾該連接所有未完成的事務(wù)； 如果客

19、戶端應(yīng)用程序失敗或客戶計(jì)算機(jī)崩潰或重啟，也會(huì)中斷該連接，當(dāng)SQL Server該中斷時(shí)，將回滾該連接所有未完成的事務(wù)； 如果客戶從該應(yīng)用程序注銷，所有未完成的事務(wù)也會(huì)被回滾。 可以用聯(lián)網(wǎng)的兩臺(tái)計(jì)算機(jī)測(cè)試VB程序,8.3.1 編寫有效事務(wù)的指導(dǎo)原則 8.3.2 避免并發(fā)問(wèn)題,8.3 編寫有效事務(wù)的建議,8.3.1 編寫有效事務(wù)的指導(dǎo)原則,1不要在事務(wù)處理期間輸入數(shù)據(jù) 2瀏覽數(shù)據(jù)時(shí)，盡量不要打開事務(wù) 3保持事務(wù)盡可能的短 4靈活地使用更低的事務(wù)隔離級(jí)別 5在事務(wù)中盡量使訪問(wèn)的數(shù)據(jù)量最小,8.3.2 避免并發(fā)問(wèn)題,為了防止并發(fā)問(wèn)題，應(yīng)該小心地管理隱性事務(wù)。在使用隱性事務(wù)時(shí)，COMMIT或ROLLBA

20、CK之后的下一個(gè)Transact-SQL語(yǔ)句會(huì)自動(dòng)啟動(dòng)一個(gè)新事務(wù)。這可能在應(yīng)用程序?yàn)g覽數(shù)據(jù)時(shí)，甚至在要求用戶輸入時(shí)，打開新的事務(wù)。所以，在完成保護(hù)數(shù)據(jù)修改所需要的最后一個(gè)事務(wù)之后和再次需要一個(gè)事務(wù)來(lái)保護(hù)數(shù)據(jù)修改之前，應(yīng)該關(guān)閉隱性事務(wù),8.4 事務(wù)處理實(shí)例分析,例8-1】使用事務(wù)的三種模式進(jìn)行表的處理，分批執(zhí)行，觀察執(zhí)行的過(guò)程。 USE 教學(xué)管理 GO SELECT times=0, * FROM student -檢查當(dāng)前表中的結(jié)果 GO -SQL Server首先處于自動(dòng)事務(wù)管理模式 INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,

21、sdepa) VALUES(S060101,*19880510*,關(guān)漢青,男,西安,計(jì)算機(jī),信息學(xué)院) SELECT times=1, * FROM student -顯示S060101被插入。 GO,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060101,*19880510*,關(guān)漢青,男,西安,計(jì)算機(jī),信息學(xué)院) -服務(wù)器: 消息 2627，級(jí)別 14，狀態(tài) 1，行 1 -違反了 PRIMARY KEY 約束 PK_Student_75A278F5。不能在對(duì)象 Student 中插入重復(fù)鍵。 -語(yǔ)

22、句已終止。 SELECT times=2, * FROM student -顯示數(shù)據(jù)沒(méi)有變化。 GO BEGIN TRANSACTION -進(jìn)入顯式事務(wù)模式 INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060106,*19880510*,關(guān)漢青,男,西安,計(jì)算機(jī),信息學(xué)院) SELECT times=3,* FROM student -顯示S060106被插入,ROLLBACK TRANSACTION GO SELECT times=4,* FROM student -因?yàn)閳?zhí)行了回滾，插入的S0601

23、06被撤消。 GO SET IMPLICIT_TRANSACTIONS ON -進(jìn)入隱式事務(wù)模式 INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060107,*19880510*,關(guān)漢青,男,西安,計(jì)算機(jī),信息學(xué)院) SELECT times=5,* FROM student -顯示S060107被插入 ROLLBACK GO SELECT times=6,* FROM student -因?yàn)閳?zhí)行了回滾，插入的S060107被撤消。 GO,DELETE FROM student WHERE sno=S0

24、60101 -刪除第1個(gè)插入 SELECT times=7,* FROM student -顯示S060101不存在 ROLLBACK GO SELECT times=8,* FROM student -因?yàn)榛貪L，使刪除作廢，所以S060101又重新顯示存在。 GO SET IMPLICIT_TRANSACTIONS OFF -隱式事務(wù)模式結(jié)束，又進(jìn)入自動(dòng)模式 DELETE FROM student WHERE sno=S060101 -刪除第1個(gè)插入 SELECT times=9,* FROM student -自動(dòng)模式執(zhí)行成功被自動(dòng)提交，顯示S060101被刪除不存在,例8-2】定義事務(wù)，

25、使事務(wù)回滾到指定的保存點(diǎn)，分批執(zhí)行，觀察執(zhí)行的過(guò)程,USE 教學(xué)管理 GO SELECT times=0, * FROM student -檢查當(dāng)前表中的結(jié)果 GO BEGIN TRANSACTION demo INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060106,*19880510*,關(guān)漢青,男,西安,計(jì)算機(jī),信息學(xué)院) SAVE TRANSACTION save_demo,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)

26、VALUES(S060107,*19890818*,楊理華,女,運(yùn)城,計(jì)算機(jī),信息學(xué)院) SELECT times=1, * FROM student -顯示S060106和S060107都被插入。 GO ROLLBACK TRANSACTION save_demo -回滾部分事務(wù) SELECT times=2, * FROM student -顯示S060107被撤消不存在。 GO ROLLBACK TRANSACTION -回滾整個(gè)事務(wù) SELECT times=3, * FROM student -顯示S060106被撤消不存在,例8-3】創(chuàng)建數(shù)據(jù)表stu_test3，生成三個(gè)級(jí)別的嵌套

27、時(shí)務(wù)，并提交該嵌套事務(wù)。觀察變量TRANCOUNT的值的變化,USE 教學(xué)管理 -選擇數(shù)據(jù)庫(kù)必須單獨(dú)在一個(gè)批中 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*,關(guān)漢青,男,西安,計(jì)算機(jī),信息學(xué)院) GO BEGIN TRANSACTION inside2 SELECT TRANC

28、OUNT -變量TRANCOUNT的值為2,INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060107,*19890818*,楊理華,女,運(yùn)城,計(jì)算機(jī),信息學(xué)院) GO BEGIN TRANSACTION inside3 SELECT TRANCOUNT -變量TRANCOUNT的值為3 INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060108,*19870818*,陳向前,男,北京,計(jì)算機(jī),信息學(xué)院) GO

29、COMMIT TRANSACTION inside3 SELECT TRANCOUNT -變量TRANCOUNT的值減為2 GO,COMMIT TRANSACTION inside2 SELECT TRANCOUNT -變量TRANCOUNT的值減為1 GO COMMIT TRANSACTION inside1 SELECT TRANCOUNT -變量TRANCOUNT的值減為0 GO,例8-4】在教學(xué)管理數(shù)據(jù)的STUDENT表中先刪除一條記錄，然后再插入一條記錄，通過(guò)測(cè)試錯(cuò)誤值確定提交還是回滾,USE 教學(xué)管理 GO DECLARE del_error int, ins_error int

30、- 開始一個(gè)事務(wù) BEGIN TRAN - 刪除一個(gè)學(xué)生 DELETE STUDENT WHERE SNO = S060308 - 為刪除語(yǔ)句設(shè)置一個(gè)接受錯(cuò)誤數(shù)值的變量 SELECT del_error = ERROR,再執(zhí)行插入語(yǔ)句 INSERT INTO STUDENT VALUES(S060308,*19890526*,張丹寧,男, 130*12,寧波,電子商務(wù),信息學(xué)院, 162) -為插入語(yǔ)句設(shè)置一個(gè)接受錯(cuò)誤數(shù)值的變量 SELECT ins_error = ERROR -測(cè)試錯(cuò)誤變量中的值 IF del_error = 0 AND ins_error = 0 BEGIN -成功，提交

31、事務(wù) COMMIT TRAN END,ELSE BEGIN - 有錯(cuò)誤發(fā)生，回滾事務(wù) IF del_error 0 PRINT 錯(cuò)誤發(fā)生在刪除語(yǔ)句 IF ins_error 0 PRINT 錯(cuò)誤發(fā)生在插入語(yǔ)句 ROLLBACK TRAN END GO 可以將插入的S060308改為SS060308再執(zhí)行一次該程序，觀察結(jié)果有什么不同,8.5 分布式事務(wù),8.5.1 分布式事務(wù)的兩階段提交 8.5.2 分布式事務(wù)的處理過(guò)程 8.5.3 分布式事務(wù)實(shí)例分析,8.5 分布式事務(wù),在大型應(yīng)用領(lǐng)域，經(jīng)常需要時(shí)務(wù)跨服務(wù)器進(jìn)行數(shù)據(jù)操作，這樣的事務(wù)被稱作分布式事務(wù)。所以分布式事務(wù)要能夠在多個(gè)服務(wù)器上執(zhí)行。 按

32、照關(guān)于分布式事務(wù)處理的X/Open XA規(guī)范，分布式事務(wù)的處理過(guò)程規(guī)定為兩個(gè)階段，就是通常說(shuō)的兩階段提交。 為了簡(jiǎn)化應(yīng)用程序?qū)Ψ植际绞聞?wù)的處理工作，系統(tǒng)提供了一個(gè)事務(wù)管理器來(lái)協(xié)調(diào)各個(gè)不同服務(wù)器對(duì)事務(wù)的處理操作，它就是MS DTC（ Distributed Transaction Coordinator ），既事務(wù)管理協(xié)調(diào)器。我們?cè)诘谝徽潞?jiǎn)單介紹過(guò),8.5.1 分布式事務(wù)的兩階段提交,1）準(zhǔn)備階段：當(dāng)分布式事務(wù)管理器接受到提交請(qǐng)求后，它向所有參與與該事務(wù)的SQL Server 服務(wù)器發(fā)出準(zhǔn)備命令。每個(gè)服務(wù)器接受到準(zhǔn)備命令后，做好接受處理事務(wù)的準(zhǔn)備工作，并將準(zhǔn)備工作狀態(tài)返回給事務(wù)管理器。 （2）提

33、交階段：當(dāng)事務(wù)管理器接受到所有服務(wù)器成功準(zhǔn)備好的信息后，它向這些服務(wù)器發(fā)出提交命令。之后所有服務(wù)器進(jìn)行提交。如果所有服務(wù)器均能成功提交事務(wù)，管理器向應(yīng)用程序報(bào)告分布式事務(wù)成功提交，如若有任一個(gè)服務(wù)器未能提交，事務(wù)管理器將向所有服務(wù)器發(fā)出回滾事務(wù)命令，并向應(yīng)用程序報(bào)告事務(wù)提交失敗,8.5.2 分布式事務(wù)的處理過(guò)程,1）T-SQL程序或應(yīng)用程序執(zhí)行 BEGIN DISTRIBUTED TRANSACTION語(yǔ)句啟動(dòng)一個(gè)分布式事務(wù)。此后，該服務(wù)器就成為分布式服務(wù)器的管理服務(wù)器。 （2）應(yīng)用程序?qū)︽溄臃?wù)器執(zhí)行分布式查詢或執(zhí)行遠(yuǎn)程服務(wù)器上的存儲(chǔ)過(guò)程。 （3）分布式事務(wù)管理服務(wù)器自動(dòng)調(diào)用MS DTC，使

34、鏈接服務(wù)器或遠(yuǎn)程服務(wù)器參加分布式事務(wù)處理。 （4）T-SQL應(yīng)用程序執(zhí)行COMMIT或ROLLBACK語(yǔ)句時(shí)，分布式事務(wù)管理服務(wù)器通過(guò)調(diào)用MS DTC來(lái)管理兩階段提交，使鏈接或遠(yuǎn)程服務(wù)器提交或回滾事務(wù),8.5.3 分布式事務(wù)實(shí)例分析,1分布式事務(wù)語(yǔ)法格式 語(yǔ)法格式： BEGIN DISTRIBUTED TRANSANCTION transanctin_name| transanctin_variable 參數(shù)說(shuō)明： transanctin_name| transanctin_variable事務(wù)名稱或事務(wù)名變量,2、分布式事務(wù)實(shí)例,例8-6】有兩個(gè)服務(wù)器LinkServer1和LinkServ

35、er2。在LinkServer2服務(wù)器上建立存儲(chǔ)過(guò)程student_insert_new，其功能是向LinkServer1上的教學(xué)管理數(shù)據(jù)庫(kù)的student表插入一個(gè)新行。 -先創(chuàng)建鏈接（遠(yuǎn)程）服務(wù)器（參見第2章） -在第一臺(tái)運(yùn)行SQL Server的服務(wù)器上運(yùn)行下列代碼： EXEC sp_addlinkedserver LinkServer1, ,SQLOLEDB,本地服務(wù)器名或ip地址 -例如zufe-mxh EXEC sp_addlinkedserver LinkServer2, ,SQLOLEDB,遠(yuǎn)程服務(wù)器名或ip地址 -例如172.19.2.156 EXEC sp_configur

36、e remote access, 1 -系統(tǒng)默認(rèn)是1，一般不需要設(shè)置,RECONFIGURE -設(shè)置LinkServer1的rpc輸出屬性，使得允許調(diào)用鏈接服務(wù)器上的存儲(chǔ)過(guò)程。 EXEC sp_serveroption LinkServer1,rpc out,true GO -停止并重新啟動(dòng)第一臺(tái) SQL Server。 -確保使用 SQL Server 身份驗(yàn)證登錄。在第二臺(tái) SQL Server 上運(yùn)行下列代碼。 EXEC sp_addlinkedserver LinkServer2, ,SQLOLEDB,本地服務(wù)器名或ip地址 -例如172.19.2.156 EXEC sp_addlin

37、kedserver LinkServer1, ,SQLOLEDB,遠(yuǎn)程服務(wù)器名或ip地址 -例如zufe-mxh,EXEC sp_configure remote access, 1 -系統(tǒng)默認(rèn)是1，一般不需要設(shè)置 RECONFIGURE -設(shè)置LinkServer2的rpc輸出屬性，使得允許調(diào)用鏈接服務(wù)器上的存儲(chǔ)過(guò)程。 EXEC sp_serveroption LinkServer2,rpc out,true GO -在第二個(gè)服務(wù)器上添加新的遠(yuǎn)程登錄ID（LinkServer1），以便允許遠(yuǎn)程服務(wù)器LinkServer1連接并執(zhí)行遠(yuǎn)程過(guò)程調(diào)用。 -假設(shè)登錄LinkServer2和LinkSe

38、rver1的用戶都是sa，并且有相同的口令。 EXEC sp_addremotelogin LinkServer1, sa, sa GO -停止并重新啟動(dòng)第二臺(tái) SQL Server,在LinkServer2上創(chuàng)建存儲(chǔ)過(guò)程（見第12章）。 -假設(shè)該服務(wù)器上面有數(shù)據(jù)庫(kù)教學(xué)練習(xí)。 Use 教學(xué)練習(xí) GO CREATE procedure student_insert_new AS INSERT LinkServer1.教學(xué)管理.dbo.student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060112,*19870818*,許少

39、文,男,湖州,計(jì)算機(jī),信息學(xué)院) GO -在第一臺(tái)服務(wù)器上啟動(dòng)DTC開始分布式事務(wù) -使用 sa 登錄，現(xiàn)在就可以在第一臺(tái) SQL Server 上執(zhí)行第二臺(tái) SQL Server 上的存儲(chǔ)過(guò)程,USE 教學(xué)管理 GO BEGIN DISTRIBUTED TRANSACTION insert_tran -開始分布式事務(wù) -在LinkServer1服務(wù)器上實(shí)行對(duì)表student的插入 INSERT student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa) VALUES(S060111,*19870818*,陳東生,男,上海,計(jì)算機(jī),信息學(xué)院) G

40、O -LinkServer1服務(wù)器自動(dòng)調(diào)用MS DTC使得LinkServer2服務(wù)器執(zhí)行存儲(chǔ)過(guò)程student_insert_new對(duì)表student的插入。 EXECUTE LinkServer2.教學(xué)練習(xí).dbo.student_insert_new COMMIT TRANSACTION -提交事務(wù),8.6 并發(fā)控制,8.6.1 SQL Server鎖的粒度及模式 8.6.2 封鎖協(xié)議 8.6.3 事務(wù)隔離 8.6.4 死鎖處理,8.6 并發(fā)控制,在大型分布式數(shù)據(jù)庫(kù)應(yīng)用程序中，對(duì)數(shù)據(jù)庫(kù)的并發(fā)訪問(wèn)操作是一個(gè)普遍存在的問(wèn)題。SQL Server使用資源鎖定的方法管理用戶的并發(fā)操作。 SQL

41、Server 2000提供了兩種并發(fā)控制方法： 樂(lè)觀并發(fā)控制：該方法假想用戶之間不太可能發(fā)生資源沖突（事實(shí)上不是不可能），所以允許用戶在不鎖定任何資源的情況下執(zhí)行事務(wù)。只有當(dāng)用戶試圖修改數(shù)據(jù)時(shí)才檢查資源是否沖突。該方法需要使用游標(biāo)，游標(biāo)中介紹。 悲觀并發(fā)控制：該方法根據(jù)需要在事務(wù)的持續(xù)時(shí)間內(nèi)鎖定資源，從而確保事務(wù)的完整性和數(shù)據(jù)庫(kù)的一致性。這是SQL Server 2000默認(rèn)的并發(fā)控制方法。下面予以介紹,8.6.1 SQL Server鎖的粒度及模式,1、SQL Server 鎖的粒度 1）RID：行標(biāo)識(shí)符，鎖定表中單行數(shù)據(jù)。 2）鍵值：具有索引的行數(shù)據(jù)。 3）頁(yè)面：一個(gè)數(shù)據(jù)頁(yè)面或索引頁(yè)面。

42、4）區(qū)域：一組連續(xù)的8個(gè)數(shù)據(jù)頁(yè)面或索引頁(yè)面。 5）表： 整個(gè)表，包括其所有的數(shù)據(jù)和索引。 6）數(shù)據(jù)庫(kù)：一個(gè)完整的數(shù)據(jù)庫(kù)。 可以根據(jù)事務(wù)所執(zhí)行的任務(wù)來(lái)靈活選擇所鎖定的資源粒度,2、資源鎖定模式基本鎖,1）共享鎖：用于只讀數(shù)據(jù)操作，它允許多個(gè)并發(fā)事務(wù)對(duì)資源鎖定進(jìn)行讀取，但禁止其他事務(wù)對(duì)鎖定資源的修改操作。 2）排它鎖：它鎖定的資源不能被其它并發(fā)事務(wù)再進(jìn)行任何鎖定，所以其它事務(wù)不能讀取和修改。鎖定的資源用于自己的數(shù)據(jù)修改。 一般更新模式是由一個(gè)事務(wù)組成，該事務(wù)先讀取記錄，要獲取資源的共享鎖，然后修改記錄，此操作要求鎖轉(zhuǎn)換為排它鎖。如果兩個(gè)事務(wù)都獲得了資源上的共享鎖，然后試圖同時(shí)更新數(shù)據(jù)，這樣肯定有一

43、個(gè)事務(wù)要將共享鎖轉(zhuǎn)化為排它鎖，因?yàn)橐粋€(gè)事務(wù)的排它鎖與其它事務(wù)的共享鎖不兼容，發(fā)生鎖等待。另一個(gè)事務(wù)也會(huì)出現(xiàn)這個(gè)問(wèn)題，由于兩個(gè)事務(wù)都要轉(zhuǎn)化為排它鎖，并且都等待另一個(gè)事務(wù)釋放共享鎖，因此發(fā)生死鎖,2、資源鎖定模式專用鎖,3）更新鎖：在修改操作的初始化階段用于鎖定可能被修改的資源。一個(gè)數(shù)據(jù)修改事務(wù)在開始時(shí)直接申請(qǐng)更新鎖，每次只有一個(gè)事務(wù)可以獲得資源的更新鎖。 使用更新鎖可以避免上述死鎖，因?yàn)橐淮沃挥幸粋€(gè)事務(wù)可以獲得更新鎖，之后當(dāng)需要繼續(xù)修改數(shù)據(jù)時(shí)，將更新鎖轉(zhuǎn)換為排它鎖，否則將更新鎖轉(zhuǎn)換為共享鎖,4）意向鎖,意向鎖表示如果獲得一個(gè)對(duì)象的鎖，說(shuō)明該結(jié)點(diǎn)的下層對(duì)象正在被加鎖。例如放置在表上的共享意向鎖表示

44、事務(wù)打算在表中的頁(yè)或行上加共享鎖。 意向鎖可以提高性能，因?yàn)橄到y(tǒng)僅在表級(jí)上檢查意向鎖而無(wú)須檢查下層。 意向共享鎖：對(duì)一個(gè)對(duì)象加意向共享鎖，表示將要對(duì)它的下層對(duì)象加共享鎖。 意向排它鎖：對(duì)一個(gè)對(duì)象加意向排它鎖，表示將要對(duì)它的下層對(duì)象加排它鎖。 意向排它共享鎖：對(duì)一個(gè)對(duì)象加意向排它共享鎖，表示對(duì)它加共享鎖，再在它的下層對(duì)象加排它鎖,5）架構(gòu)鎖,架構(gòu)修改鎖：執(zhí)行表的數(shù)據(jù)定義語(yǔ)言（DDL）操作時(shí)使用。 架構(gòu)穩(wěn)定鎖：編譯查詢時(shí)使用。它不阻塞任何事務(wù)鎖，包括排它鎖。 6）大容量更新鎖： 當(dāng)數(shù)據(jù)大容量復(fù)制到表的時(shí)候使用,8.6.2 封鎖協(xié)議,在運(yùn)用X鎖和S鎖對(duì)數(shù)據(jù)對(duì)象加鎖時(shí)，需要約定一些規(guī)則：封鎖協(xié)議（Lo

45、cking Protocol） 何時(shí)申請(qǐng)X鎖或S鎖 持鎖時(shí)間、何時(shí)釋放 不同的封鎖協(xié)議，在不同的程度上為并發(fā)操作的正確調(diào)度提供一定的保證 常用的封鎖協(xié)議：三級(jí)封鎖協(xié)議,1級(jí)封鎖協(xié)議,事務(wù)T在修改數(shù)據(jù)R之前必須先對(duì)其加X(jué)鎖，直到事務(wù)結(jié)束才釋放 正常結(jié)束（COMMIT） 非正常結(jié)束（ROLLBACK） 1級(jí)封鎖協(xié)議可防止丟失修改 在1級(jí)封鎖協(xié)議中，如果是讀數(shù)據(jù)，不需要加鎖的，所以它不能保證可重復(fù)讀和不讀“臟”數(shù)據(jù),1級(jí)封鎖協(xié)議,沒(méi)有丟失修改,沒(méi)有丟失修改,2級(jí)封鎖協(xié)議,1級(jí)封鎖協(xié)議+事務(wù)T在讀取數(shù)據(jù)R前必須先加S鎖，讀完后即可釋放S鎖 2級(jí)封鎖協(xié)議可以防止丟失修改和讀“臟”數(shù)據(jù)。 在2級(jí)封鎖協(xié)議中

46、，由于讀完數(shù)據(jù)后即可釋放S鎖，所以它不能保證可重復(fù)讀,2級(jí)封鎖協(xié)議,不讀“臟”數(shù)據(jù),3級(jí)封鎖協(xié)議,1級(jí)封鎖協(xié)議 + 事務(wù)T在讀取數(shù)據(jù)R之前必須先對(duì)其加S鎖，直到事務(wù)結(jié)束才釋放 3級(jí)封鎖協(xié)議可防止丟失修改、讀臟數(shù)據(jù)和不可重復(fù)讀。 容易造成比較多的死鎖,3級(jí)封鎖協(xié)議,可重復(fù)讀,封鎖協(xié)議小結(jié),8.6.3 事務(wù)隔離,為了避免產(chǎn)生并發(fā)訪問(wèn)問(wèn)題，SQL Server使用不同類型的鎖對(duì)資源進(jìn)行鎖定，從而限制在一個(gè)事務(wù)讀取數(shù)據(jù)期間其他事務(wù)鎖執(zhí)行的操作類型，即對(duì)事務(wù)進(jìn)行隔離。 不同的并發(fā)訪問(wèn)問(wèn)題可以通過(guò)設(shè)置不同的事務(wù)隔離級(jí)別加以解決。事務(wù)的隔離級(jí)別控制一個(gè)事務(wù)與其他事務(wù)的隔離程度，它決定該事務(wù)在讀取數(shù)據(jù)時(shí)對(duì)資源

47、所使用的鎖類型,1）事務(wù)隔離級(jí)別 未提交讀：這是4種隔離級(jí)別中限制最低的級(jí)別，它僅能保證SQL Server不讀取物理?yè)p壞的數(shù)據(jù)。在這種隔離級(jí)別下，讀數(shù)據(jù)時(shí)不發(fā)出共享鎖，也不接受排它鎖，事務(wù)可以對(duì)數(shù)據(jù)執(zhí)行臟讀；在事務(wù)結(jié)束前可以更改數(shù)據(jù)集內(nèi)的數(shù)值，行也可以出現(xiàn)在數(shù)據(jù)集中或從數(shù)據(jù)集消失。但可以防止產(chǎn)生丟失修改。（一級(jí)封鎖協(xié)議） 提交讀：它要求在讀取數(shù)據(jù)時(shí)控制共享鎖以避免發(fā)生臟讀，但數(shù)據(jù)可在事務(wù)結(jié)束前更改，這可能產(chǎn)生不能重復(fù)讀或幻影讀問(wèn)題。 （二級(jí)封鎖協(xié)議） 可重復(fù)讀：鎖定查詢中使用的所有數(shù)據(jù)以防止其他用戶更新，但是其他用戶可以將新的幻影行插入到數(shù)據(jù)集中，新插入的幻影行將出現(xiàn)在當(dāng)前事物的后續(xù)讀取結(jié)果集中?？芍貜?fù)讀能夠