SQLServer事務(wù)和并發(fā)控制課件

大型數(shù)據(jù)庫(kù)系統(tǒng)管理、設(shè)計(jì)與實(shí)例分析

——基于SQLServer

SQLServer事務(wù)和并發(fā)控制課件大型數(shù)據(jù)庫(kù)系統(tǒng)管理、設(shè)計(jì)與實(shí)例分析

——基于SQLServ1第8章SQLServer事務(wù)和并發(fā)控制8.1事務(wù)8.2事務(wù)的分類和控制8.3并發(fā)控制8.4事務(wù)處理實(shí)例分析

8.5分布式事務(wù)8.6并發(fā)控制

SQLServer事務(wù)和并發(fā)控制課件第8章SQLServer事務(wù)和并發(fā)控制8.128.1事務(wù)8.1.1事務(wù)的概念8.1.2事務(wù)對(duì)并發(fā)控制和保障數(shù)據(jù)完整的重要性SQLServer事務(wù)和并發(fā)控制課件8.1事務(wù)8.1.1事務(wù)的概念SQLServer事38.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ù)性。SQLServer事務(wù)和并發(fā)控制課件8.1.1事務(wù)的概念事務(wù)是一個(gè)用戶定義的完整的工作單元，41、事務(wù)的特性(ACID)原子性(Atomicity)：事務(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)該是永久的

SQLServer事務(wù)和并發(fā)控制課件1、事務(wù)的特性(ACID)原子性(Atomicity)：事務(wù)52、事務(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è)批中所有語(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)打開(kāi)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ù)的提交或回滾操作。SQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別編程時(shí)，一定要區(qū)分事務(wù)和批的差別：SQLS62、事務(wù)和批的區(qū)別SETXACT_ABORT指定當(dāng)Transact-SQL語(yǔ)句產(chǎn)生運(yùn)行時(shí)錯(cuò)誤時(shí)，Microsoft?SQLServer?是否自動(dòng)回滾當(dāng)前事務(wù)。語(yǔ)法SETXACT_ABORT{ON|OFF}當(dāng)SETXACT_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ò)誤）不受SETXACT_ABORT的影響。對(duì)于大多數(shù)OLEDB提供程序（包括SQLServer），隱性或顯式事務(wù)中的數(shù)據(jù)修改語(yǔ)句必須將XACT_ABORT設(shè)置為ON。唯一不需要該選項(xiàng)的情況是提供程序支持嵌套事務(wù)時(shí)。SQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別SETXACT_ABORTSQLServ72、事務(wù)和批的區(qū)別下例導(dǎo)致在含有其它Transact-SQL語(yǔ)句的事務(wù)中發(fā)生違反外鍵錯(cuò)誤。在第一個(gè)語(yǔ)句集中產(chǎn)生錯(cuò)誤，但其它語(yǔ)句均成功執(zhí)行且事務(wù)成功提交。在第二個(gè)語(yǔ)句集中，SETXACT_ABORT設(shè)置為ON。這導(dǎo)致語(yǔ)句錯(cuò)誤使批處理終止，并使事務(wù)回滾。CREATETABLEt1(aintPRIMARYKEY)CREATETABLEt2(aintREFERENCESt1(a))GOINSERTINTOt1VALUES(1)INSERTINTOt1VALUES(3)INSERTINTOt1VALUES(4)INSERTINTOt1VALUES(6)GOSETXACT_ABORTOFFGOBEGINTRANINSERTINTOt2VALUES(1)INSERTINTOt2VALUES(2)/*Foreignkeyerror*/INSERTINTOt2VALUES(3)COMMITTRANGOSQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別下例導(dǎo)致在含有其它Transact-SQ82、事務(wù)和批的區(qū)別SETXACT_ABORTONGOBEGINTRANINSERTINTOt2VALUES(4)INSERTINTOt2VALUES(5)/*Foreignkeyerror*/INSERTINTOt2VALUES(6)COMMITTRANGO/*Selectshowsonlykeys1and3added.Key2insertfailedandwasrolledback,butXACT_ABORTwasOFFandrestoftransactionsucceeded.Key5inserterrorwithXACT_ABORTONcausedallofthesecondtransactiontorollback.*/SELECT*FROMt2GODROPTABLEt2DROPTABLEt1GOSQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別SETXACT_ABORTONSQLS92、事務(wù)和批的區(qū)別問(wèn)題：如何知道XACT_ABORT為ON還是OFF呢？XACT_ABORT的默認(rèn)值是什么？SQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別問(wèn)題：如何知道XACT_ABORT為ON還102、事務(wù)和批的區(qū)別問(wèn)題：如何知道XACT_ABORT為ON還是OFF呢？DBCCUSEROPTIONS:itwillshowassetifit'sON.Ifit'sOFFthenitwillnotshow

DBCCUSEROPTIONS返回當(dāng)前連接的活動(dòng)（設(shè)置）的SET選項(xiàng)。看看VB的例子：運(yùn)行DBCC（順便看一下聯(lián)機(jī)幫助文檔）并查看結(jié)果SQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別問(wèn)題：如何知道XACT_ABORT為ON還112、事務(wù)和批的區(qū)別SETXACT_ABORT的默認(rèn)值是什么？對(duì)每個(gè)連接來(lái)說(shuō)，SETXACT_ABORT的默認(rèn)值是OFF看看VB的例子和查詢分析器SQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別SETXACT_ABORT的默認(rèn)值是什么128.1.2事務(wù)對(duì)并發(fā)控制和保障

數(shù)據(jù)完整的重要性1．事務(wù)與并發(fā)控制的關(guān)系如果在用戶并發(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）幻影讀SQLServer事務(wù)和并發(fā)控制課件8.1.2事務(wù)對(duì)并發(fā)控制和保障

數(shù)據(jù)完整的重要性1．事132．事務(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ù)完整的重要性SQLServer事務(wù)和并發(fā)控制課件2．事務(wù)對(duì)保障數(shù)據(jù)一致和完整性的作用8.1.2事務(wù)對(duì)并148.2事務(wù)的分類和控制8.2.1事務(wù)的分類8.2.2事務(wù)控制SQLServer事務(wù)和并發(fā)控制課件8.2事務(wù)的分類和控制8.2.1事務(wù)的分類SQL158.2.1事務(wù)的分類SQLServer的事務(wù)模式可分為顯式事務(wù)、隱式事務(wù)和自動(dòng)事務(wù)三種。1)顯式事務(wù)顯式事務(wù)是指由用戶執(zhí)行T-sql事務(wù)語(yǔ)句而定義的事務(wù)，這類事務(wù)又稱做用戶定義事務(wù)。定義事務(wù)的語(yǔ)句包括：BEGINTRANSACTION:標(biāo)識(shí)一個(gè)事務(wù)的開(kāi)始，即啟動(dòng)事務(wù)。COMMITTRANSACTION、COMMITWORK：標(biāo)識(shí)一個(gè)事務(wù)的結(jié)束，事務(wù)內(nèi)所修改的數(shù)據(jù)被永久保存到數(shù)據(jù)庫(kù)中。ROLLBACKTRANSACTION、ROLLBACKWORK：標(biāo)識(shí)一個(gè)事務(wù)的結(jié)束，說(shuō)明事務(wù)執(zhí)行過(guò)程中遇到錯(cuò)誤，事務(wù)內(nèi)所修改的數(shù)據(jù)被回滾到事務(wù)執(zhí)行前的狀態(tài)。SQLServer事務(wù)和并發(fā)控制課件8.2.1事務(wù)的分類SQLServer的事務(wù)模式可分162)隱式事務(wù)在隱式事務(wù)模式下，在當(dāng)前事務(wù)提交或回滾后，SQLServer自動(dòng)開(kāi)始下一個(gè)事務(wù)。所以，隱式事務(wù)不需要使用BEGINTRANSACTION語(yǔ)句啟動(dòng)事務(wù)，而只需要用戶使用ROLLBACKTRANSACTION、ROLLBACKWORK、COMMITTRANSACTION、COMMITWORK等語(yǔ)句提交或回滾事務(wù)。在提交或回滾后，SQLServer自動(dòng)開(kāi)始下一個(gè)事務(wù)。執(zhí)行SETIMPLICIT_TRANSACTIONSON語(yǔ)句可使SQLServer進(jìn)入隱式事務(wù)模式。在隱式事務(wù)模式下，當(dāng)執(zhí)行下面任意一個(gè)語(yǔ)句時(shí)，可使SQLServer重新啟動(dòng)一個(gè)事務(wù)：

所有CREATE語(yǔ)句ALTERTABLE所有DROP語(yǔ)句TRUNCATETABLEGRANTREVOKEINSERTUPDATEDELETESELECTOPENFETCH需要關(guān)閉隱式事務(wù)模式時(shí)，調(diào)用SET語(yǔ)句關(guān)閉IMPLICIT_TRANSACTIONSOFF連接選項(xiàng)即可。SQLServer事務(wù)和并發(fā)控制課件2)隱式事務(wù)SQLServer事務(wù)和并發(fā)控制課件173)自動(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ù)模式是SQLServer的默認(rèn)事務(wù)管理模式，當(dāng)與SQLServer建立連接后，直接進(jìn)入自動(dòng)事務(wù)模式，直到使用BEGINTRANSACTION語(yǔ)句開(kāi)始一個(gè)顯式事務(wù)，或者打開(kāi)IMPLICIT_TRANSACTIONS連接選項(xiàng)進(jìn)入隱式事務(wù)模式為止。而當(dāng)顯式事務(wù)被提交或IMPLICIT_TRANSACTIONS被關(guān)閉后，SQLServer又進(jìn)入自動(dòng)事務(wù)管理模式?？纯碫B的例子SQLServer事務(wù)和并發(fā)控制課件3)自動(dòng)事務(wù)模式SQLServer事務(wù)和并發(fā)控制課件18示例：BEGINTRANdemo SELECT*FROMStudent INSERTINTOStudentVALUES(‘9711112’,’張三’,…) SELECT*FROMStudent ROLLBACK--回滾整個(gè)事務(wù)或： COMMIT--提交事務(wù)SQLServer事務(wù)和并發(fā)控制課件示例：BEGINTRANdemoSQLServer198.2.2事務(wù)控制

SQLServer中有關(guān)事務(wù)的處理語(yǔ)句有：命令名作用格式BEGINTRANSACTION說(shuō)明一個(gè)事務(wù)開(kāi)始BEGINTRANsaction[<事務(wù)名>]COMMITTRANSACTION說(shuō)明一個(gè)事務(wù)結(jié)束，它的作用是提交或確認(rèn)事務(wù)已經(jīng)完成COMMITTRANsaction[<事務(wù)名>]SAVETRANSACTION用于在事務(wù)中設(shè)置一個(gè)保存點(diǎn)，目的是在撤消事務(wù)時(shí)可以只撤消部分事務(wù)，以提高系統(tǒng)的效率SAVETRANsaction<保存點(diǎn)>ROLLBACKTRANSACTION說(shuō)明要撤消事務(wù)，即撤消在該事務(wù)中對(duì)數(shù)據(jù)庫(kù)所做的更新操作，使數(shù)據(jù)庫(kù)回退到BEGINTRANSACTION或保存點(diǎn)之前的狀態(tài)ROLLBACKTRANsaction[<事務(wù)名>|<保存點(diǎn)>]SQLServer事務(wù)和并發(fā)控制課件8.2.2事務(wù)控制SQLServer中有關(guān)事務(wù)的處理20Savetransaction示例（略）1：下例更改分給TheGourmetMicrowave的兩位作者的版稅。數(shù)據(jù)庫(kù)將會(huì)在兩個(gè)更新間不一致，因此必須將它們分組為用戶定義的事務(wù)。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事務(wù)和并發(fā)控制課件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事務(wù)和并發(fā)控制課件Savetransaction示例（略）2：SQLServ22說(shuō)明：在定義一個(gè)事務(wù)時(shí)，BEGINTRANSACTION語(yǔ)句應(yīng)與COMMITTRANSACTION語(yǔ)句或ROLLBACKTRANSACTION成對(duì)出現(xiàn)。在SQLServer中，事務(wù)定義語(yǔ)句可以嵌套，但實(shí)際上只有最外層的BEGINTRANSACTION語(yǔ)句和COMMITTRANSACTION語(yǔ)句才能建立和提交事務(wù)；在回滾事務(wù)時(shí)，也只能使用最外層定義的事務(wù)名或存儲(chǔ)點(diǎn)標(biāo)記，而不能使用內(nèi)層定義的事務(wù)名。事務(wù)嵌套常用在存儲(chǔ)過(guò)程或觸發(fā)器內(nèi)，它們可以使用BEGINTRANSACTION。。。COMMITTRANSACTION對(duì)來(lái)相互調(diào)用。SQLServer事務(wù)和并發(fā)控制課件說(shuō)明：SQLServer事務(wù)和并發(fā)控制課件23說(shuō)明：事務(wù)處理過(guò)程中的錯(cuò)誤：如果服務(wù)器錯(cuò)誤使事務(wù)無(wú)法成功完成，則SQLServer自動(dòng)回滾該事務(wù)，并釋放該事務(wù)所占有的所有資源；如果客戶端與SQLServer的網(wǎng)絡(luò)連接中斷，那么當(dāng)網(wǎng)絡(luò)告知SQLServer該中斷時(shí)，將回滾該連接所有未完成的事務(wù)；如果客戶端應(yīng)用程序失敗或客戶計(jì)算機(jī)崩潰或重啟，也會(huì)中斷該連接，當(dāng)SQLServer該中斷時(shí)，將回滾該連接所有未完成的事務(wù)；如果客戶從該應(yīng)用程序注銷，所有未完成的事務(wù)也會(huì)被回滾?？梢杂寐?lián)網(wǎng)的兩臺(tái)計(jì)算機(jī)測(cè)試VB程序。SQLServer事務(wù)和并發(fā)控制課件說(shuō)明：SQLServer事務(wù)和并發(fā)控制課件248.3.1編寫有效事務(wù)的指導(dǎo)原則

8.3.2避免并發(fā)問(wèn)題

8.3編寫有效事務(wù)的建議

SQLServer事務(wù)和并發(fā)控制課件8.3.1編寫有效事務(wù)的指導(dǎo)原則8.3編寫有效事務(wù)258.3.1編寫有效事務(wù)的指導(dǎo)原則

1．不要在事務(wù)處理期間輸入數(shù)據(jù)

2．瀏覽數(shù)據(jù)時(shí)，盡量不要打開(kāi)事務(wù)

3．保持事務(wù)盡可能的短

4．靈活地使用更低的事務(wù)隔離級(jí)別

5．在事務(wù)中盡量使訪問(wèn)的數(shù)據(jù)量最小

SQLServer事務(wù)和并發(fā)控制課件8.3.1編寫有效事務(wù)的指導(dǎo)原則1．不要在事務(wù)處理期間268.3.2避免并發(fā)問(wèn)題

為了防止并發(fā)問(wèn)題，應(yīng)該小心地管理隱性事務(wù)。在使用隱性事務(wù)時(shí)，COMMIT或ROLLBACK之后的下一個(gè)Transact-SQL語(yǔ)句會(huì)自動(dòng)啟動(dòng)一個(gè)新事務(wù)。這可能在應(yīng)用程序?yàn)g覽數(shù)據(jù)時(shí)，甚至在要求用戶輸入時(shí)，打開(kāi)新的事務(wù)。所以，在完成保護(hù)數(shù)據(jù)修改所需要的最后一個(gè)事務(wù)之后和再次需要一個(gè)事務(wù)來(lái)保護(hù)數(shù)據(jù)修改之前，應(yīng)該關(guān)閉隱性事務(wù)。

SQLServer事務(wù)和并發(fā)控制課件8.3.2避免并發(fā)問(wèn)題為了防止并發(fā)問(wèn)題，應(yīng)該小心地管理278.4事務(wù)處理實(shí)例分析

【例8-1】使用事務(wù)的三種模式進(jìn)行表的處理，分批執(zhí)行，觀察執(zhí)行的過(guò)程。

USE教學(xué)管理GOSELECTtimes=0,*FROMstudent--檢查當(dāng)前表中的結(jié)果GO--SQLServer首先處于自動(dòng)事務(wù)管理模式INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060101','******19880510***','關(guān)漢青','男','','西安','計(jì)算機(jī)','信息學(xué)院')SELECTtimes=1,*FROMstudent--顯示'S060101'被插入。GOSQLServer事務(wù)和并發(fā)控制課件8.4事務(wù)處理實(shí)例分析【例8-1】使用事務(wù)的三種模式進(jìn)28INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060101','******19880510***','關(guān)漢青','男','','西安','計(jì)算機(jī)','信息學(xué)院')--服務(wù)器:消息2627，級(jí)別14，狀態(tài)1，行1--違反了PRIMARYKEY約束'PK__Student__75A278F5'。不能在對(duì)象'Student'中插入重復(fù)鍵。--語(yǔ)句已終止。SELECTtimes=2,*FROMstudent--顯示數(shù)據(jù)沒(méi)有變化。GOBEGINTRANSACTION --進(jìn)入顯式事務(wù)模式INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060106','******19880510***','關(guān)漢青','男','','西安','計(jì)算機(jī)','信息學(xué)院')SELECTtimes=3,*FROMstudent--顯示'S060106'被插入SQLServer事務(wù)和并發(fā)控制課件INSERTstudent(sno,sssn,sname,29ROLLBACKTRANSACTIONGOSELECTtimes=4,*FROMstudent--因?yàn)閳?zhí)行了回滾，插入的'S060106'被撤消。GOSETIMPLICIT_TRANSACTIONSON--進(jìn)入隱式事務(wù)模式INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060107','******19880510***','關(guān)漢青','男','','西安','計(jì)算機(jī)','信息學(xué)院')SELECTtimes=5,*FROMstudent--顯示'S060107'被插入ROLLBACK GOSELECTtimes=6,*FROMstudent--因?yàn)閳?zhí)行了回滾，插入的'S060107'被撤消。GOSQLServer事務(wù)和并發(fā)控制課件ROLLBACKTRANSACTIONSQLServer30DELETEFROMstudentWHEREsno='S060101'--刪除第1個(gè)插入SELECTtimes=7,*FROMstudent--顯示'S060101'不存在ROLLBACKGO SELECTtimes=8,*FROMstudent--因?yàn)榛貪L，使刪除作廢，所以'S060101'又重新顯示存在。GOSETIMPLICIT_TRANSACTIONSOFF --隱式事務(wù)模式結(jié)束，又進(jìn)入自動(dòng)模式

DELETEFROMstudentWHEREsno='S060101'--刪除第1個(gè)插入SELECTtimes=9,*FROMstudent--自動(dòng)模式執(zhí)行成功被自動(dòng)提交，顯示'S060101'被刪除不存在。SQLServer事務(wù)和并發(fā)控制課件DELETEFROMstudentWHEREsno=31【例8-2】定義事務(wù)，使事務(wù)回滾到指定的保存點(diǎn)，分批執(zhí)行，觀察執(zhí)行的過(guò)程。

USE教學(xué)管理GOSELECTtimes=0,*FROMstudent--檢查當(dāng)前表中的結(jié)果GOBEGINTRANSACTIONdemoINSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060106','******19880510***','關(guān)漢青','男','','西安','計(jì)算機(jī)','信息學(xué)院')SAVETRANSACTIONsave_demoSQLServer事務(wù)和并發(fā)控制課件【例8-2】定義事務(wù)，使事務(wù)回滾到指定的保存點(diǎn)，分批執(zhí)行，觀32INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060107','******19890818***','楊理華','女','','運(yùn)城','計(jì)算機(jī)','信息學(xué)院')SELECTtimes=1,*FROMstudent--顯示'S060106'和'S060107'都被插入。GOROLLBACKTRANSACTIONsave_demo--回滾部分事務(wù)SELECTtimes=2,*FROMstudent--顯示'S060107'被撤消不存在。GOROLLBACKTRANSACTION--回滾整個(gè)事務(wù)SELECTtimes=3,*FROMstudent--顯示'S060106'被撤消不存在。

SQLServer事務(wù)和并發(fā)控制課件INSERTstudent(sno,sssn,sname,33【例8-3】創(chuàng)建數(shù)據(jù)表stu_test3，生成三個(gè)級(jí)別的嵌套時(shí)務(wù)，并提交該嵌套事務(wù)。觀察變量@@TRANCOUNT的值的變化。

USE教學(xué)管理

--選擇數(shù)據(jù)庫(kù)必須單獨(dú)在一個(gè)批中GOSELECT@@TRANCOUNT--變量@@TRANCOUNT的值為0BEGINTRANSACTIONinside1SELECT@@TRANCOUNT--變量@@TRANCOUNT的值為1INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060106','******19880510***','關(guān)漢青','男','','西安','計(jì)算機(jī)','信息學(xué)院')GOBEGINTRANSACTIONinside2SELECT@@TRANCOUNT--變量@@TRANCOUNT的值為2

SQLServer事務(wù)和并發(fā)控制課件【例8-3】創(chuàng)建數(shù)據(jù)表stu_test3，生成三個(gè)級(jí)別的嵌套34INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060107','******19890818***','楊理華','女','','運(yùn)城','計(jì)算機(jī)','信息學(xué)院')GOBEGINTRANSACTIONinside3SELECT@@TRANCOUNT--變量@@TRANCOUNT的值為3INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES(‘S060108’,‘******19870818***’,‘陳向前’,‘男’,‘’,‘北京’,‘計(jì)算機(jī)’,‘信息學(xué)院’)GOCOMMITTRANSACTIONinside3SELECT@@TRANCOUNT--變量@@TRANCOUNT的值減為2GOSQLServer事務(wù)和并發(fā)控制課件INSERTstudent(sno,sssn,sname,35COMMITTRANSACTIONinside2SELECT@@TRANCOUNT--變量@@TRANCOUNT的值減為1GOCOMMITTRANSACTIONinside1SELECT@@TRANCOUNT--變量@@TRANCOUNT的值減為0GOSQLServer事務(wù)和并發(fā)控制課件COMMITTRANSACTIONinside236【例8-4】在教學(xué)管理數(shù)據(jù)的STUDENT表中先刪除一條記錄，然后再插入一條記錄，通過(guò)測(cè)試錯(cuò)誤值確定提交還是回滾。

USE教學(xué)管理GODECLARE@del_errorint,@ins_errorint--開(kāi)始一個(gè)事務(wù)BEGINTRAN--刪除一個(gè)學(xué)生DELETESTUDENTWHERESNO='S060308'--為刪除語(yǔ)句設(shè)置一個(gè)接受錯(cuò)誤數(shù)值的變量SELECT@del_error=@@ERRORSQLServer事務(wù)和并發(fā)控制課件【例8-4】在教學(xué)管理數(shù)據(jù)的STUDENT表中先刪除一條記錄37--再執(zhí)行插入語(yǔ)句INSERTINTOSTUDENTVALUES('S060308','******19890526***','張丹寧','男','130***12','寧波','電子商務(wù)','信息學(xué)院',162)--為插入語(yǔ)句設(shè)置一個(gè)接受錯(cuò)誤數(shù)值的變量SELECT@ins_error=@@ERROR--測(cè)試錯(cuò)誤變量中的值IF@del_error=0AND@ins_error=0BEGIN

--成功，提交事務(wù)COMMITTRANENDSQLServer事務(wù)和并發(fā)控制課件--再執(zhí)行插入語(yǔ)句SQLServer事務(wù)和并發(fā)控制課件38ELSEBEGIN

--有錯(cuò)誤發(fā)生，回滾事務(wù)

IF@del_error<>0PRINT'錯(cuò)誤發(fā)生在刪除語(yǔ)句'IF@ins_error<>0PRINT'錯(cuò)誤發(fā)生在插入語(yǔ)句'ROLLBACKTRANENDGO

可以將插入的‘S060308’改為‘SS060308’再執(zhí)行一次該程序，觀察結(jié)果有什么不同？

SQLServer事務(wù)和并發(fā)控制課件ELSESQLServer事務(wù)和并發(fā)控制課件398.5分布式事務(wù)

8.5.1分布式事務(wù)的兩階段提交

8.5.2分布式事務(wù)的處理過(guò)程

8.5.3分布式事務(wù)實(shí)例分析

SQLServer事務(wù)和并發(fā)控制課件8.5分布式事務(wù)8.5.1分布式事務(wù)的兩階段提交408.5分布式事務(wù)在大型應(yīng)用領(lǐng)域，經(jīng)常需要時(shí)務(wù)跨服務(wù)器進(jìn)行數(shù)據(jù)操作，這樣的事務(wù)被稱作分布式事務(wù)。所以分布式事務(wù)要能夠在多個(gè)服務(wù)器上執(zhí)行。按照關(guān)于分布式事務(wù)處理的X/OpenXA規(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ù)的處理操作，它就是MSDTC（DistributedTransactionCoordinator），既事務(wù)管理協(xié)調(diào)器。我們?cè)诘谝徽潞?jiǎn)單介紹過(guò)。SQLServer事務(wù)和并發(fā)控制課件8.5分布式事務(wù)在大型應(yīng)用領(lǐng)域，經(jīng)常需要時(shí)務(wù)跨服務(wù)器進(jìn)行418.5.1分布式事務(wù)的兩階段提交（1）準(zhǔn)備階段：當(dāng)分布式事務(wù)管理器接受到提交請(qǐng)求后，它向所有參與與該事務(wù)的SQLServer服務(wù)器發(fā)出準(zhǔn)備命令。每個(gè)服務(wù)器接受到準(zhǔn)備命令后，做好接受處理事務(wù)的準(zhǔn)備工作，并將準(zhǔn)備工作狀態(tài)返回給事務(wù)管理器。（2）提交階段：當(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ù)提交失敗。SQLServer事務(wù)和并發(fā)控制課件8.5.1分布式事務(wù)的兩階段提交（1）準(zhǔn)備階段：當(dāng)分布式428.5.2分布式事務(wù)的處理過(guò)程（1）T-SQL程序或應(yīng)用程序執(zhí)行BEGINDISTRIBUTEDTRANSACTION語(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)用MSDTC，使鏈接服務(wù)器或遠(yuǎn)程服務(wù)器參加分布式事務(wù)處理。（4）T-SQL應(yīng)用程序執(zhí)行COMMIT或ROLLBACK語(yǔ)句時(shí)，分布式事務(wù)管理服務(wù)器通過(guò)調(diào)用MSDTC來(lái)管理兩階段提交，使鏈接或遠(yuǎn)程服務(wù)器提交或回滾事務(wù)。SQLServer事務(wù)和并發(fā)控制課件8.5.2分布式事務(wù)的處理過(guò)程（1）T-SQL程序或應(yīng)用程438.5.3分布式事務(wù)實(shí)例分析

1．分布式事務(wù)語(yǔ)法格式語(yǔ)法格式：BEGINDISTRIBUTEDTRANSANCTION[transanctin_name|@transanctin_variable]參數(shù)說(shuō)明：transanctin_name|@transanctin_variable事務(wù)名稱或事務(wù)名變量。

SQLServer事務(wù)和并發(fā)控制課件8.5.3分布式事務(wù)實(shí)例分析1．分布式事務(wù)語(yǔ)法格式SQ442、分布式事務(wù)實(shí)例【例8-6】有兩個(gè)服務(wù)器LinkServer1和LinkServer2。在LinkServer2服務(wù)器上建立存儲(chǔ)過(guò)程student_insert_new，其功能是向LinkServer1上的教學(xué)管理數(shù)據(jù)庫(kù)的student表插入一個(gè)新行。

--先創(chuàng)建鏈接（遠(yuǎn)程）服務(wù)器（參見(jiàn)第2章）--在第一臺(tái)運(yùn)行SQLServer的服務(wù)器上運(yùn)行下列代碼：

EXECsp_addlinkedserver'LinkServer1','','SQLOLEDB','本地服務(wù)器名或ip地址'--例如'zufe-mxh'EXECsp_addlinkedserver'LinkServer2','','SQLOLEDB','遠(yuǎn)程服務(wù)器名或ip地址'--例如'172.19.2.156'EXECsp_configure'remoteaccess',1--系統(tǒng)默認(rèn)是1，一般不需要設(shè)置SQLServer事務(wù)和并發(fā)控制課件2、分布式事務(wù)實(shí)例【例8-6】有兩個(gè)服務(wù)器LinkServe45RECONFIGURE--設(shè)置'LinkServer1'的rpc輸出屬性，使得允許調(diào)用鏈接服務(wù)器上的存儲(chǔ)過(guò)程。EXECsp_serveroption'LinkServer1','rpcout','true'GO--停止并重新啟動(dòng)第一臺(tái)SQLServer。--確保使用SQLServer身份驗(yàn)證登錄。在第二臺(tái)SQLServer上運(yùn)行下列代碼。EXECsp_addlinkedserver‘LinkServer2’,‘’,‘SQLOLEDB’,‘本地服務(wù)器名或ip地址’

--例如'172.19.2.156'EXECsp_addlinkedserver‘LinkServer1’,‘’,‘SQLOLEDB’,‘遠(yuǎn)程服務(wù)器名或ip地址’

--例如'zufe-mxh'

SQLServer事務(wù)和并發(fā)控制課件RECONFIGURESQLServer事務(wù)和并發(fā)控制課件46EXECsp_configure'remoteaccess',1--系統(tǒng)默認(rèn)是1，一般不需要設(shè)置RECONFIGURE--設(shè)置'LinkServer2'的rpc輸出屬性，使得允許調(diào)用鏈接服務(wù)器上的存儲(chǔ)過(guò)程。EXECsp_serveroption'LinkServer2','rpcout','true'GO--在第二個(gè)服務(wù)器上添加新的遠(yuǎn)程登錄ID（LinkServer1），以便允許遠(yuǎn)程服務(wù)器LinkServer1連接并執(zhí)行遠(yuǎn)程過(guò)程調(diào)用。--假設(shè)登錄LinkServer2和LinkServer1的用戶都是'sa'，并且有相同的口令。EXECsp_addremoteloginLinkServer1,sa,saGO--停止并重新啟動(dòng)第二臺(tái)SQLServer。

SQLServer事務(wù)和并發(fā)控制課件EXECsp_configure'remoteacce47--在LinkServer2上創(chuàng)建存儲(chǔ)過(guò)程（見(jiàn)第12章）。--假設(shè)該服務(wù)器上面有數(shù)據(jù)庫(kù)‘教學(xué)練習(xí)’。Use教學(xué)練習(xí)GOCREATEprocedurestudent_insert_newASINSERTLinkServer1.教學(xué)管理.dbo.student(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060112','******19870818***',''許少文','男','','湖州','計(jì)算機(jī)','信息學(xué)院')GO--在第一臺(tái)服務(wù)器上啟動(dòng)DTC開(kāi)始分布式事務(wù)--使用sa登錄，現(xiàn)在就可以在第一臺(tái)SQLServer上執(zhí)行第二臺(tái)SQLServer上的存儲(chǔ)過(guò)程。

SQLServer事務(wù)和并發(fā)控制課件--在LinkServer2上創(chuàng)建存儲(chǔ)過(guò)程（見(jiàn)第12章）。S48USE教學(xué)管理GOBEGINDISTRIBUTEDTRANSACTIONinsert_tran--開(kāi)始分布式事務(wù)--在LinkServer1服務(wù)器上實(shí)行對(duì)表student的插入INSERTstudent(sno,sssn,sname,ssex,smtel,scity,smajor,sdepa)VALUES('S060111','******19870818***','陳東生','男','','上海','計(jì)算機(jī)','信息學(xué)院')GO--LinkServer1服務(wù)器自動(dòng)調(diào)用MSDTC使得LinkServer2服務(wù)器執(zhí)行存儲(chǔ)過(guò)程student_insert_new對(duì)表student的插入。EXECUTELinkServer2.教學(xué)練習(xí).dbo.student_insert_newCOMMITTRANSACTION--提交事務(wù)

SQLServer事務(wù)和并發(fā)控制課件USE教學(xué)管理SQLServer事務(wù)和并發(fā)控制課件498.6并發(fā)控制

8.6.1SQLServer鎖的粒度及模式

8.6.2封鎖協(xié)議

8.6.3事務(wù)隔離

8.6.4死鎖處理

SQLServer事務(wù)和并發(fā)控制課件8.6并發(fā)控制8.6.1SQLServer鎖的粒508.6并發(fā)控制在大型分布式數(shù)據(jù)庫(kù)應(yīng)用程序中，對(duì)數(shù)據(jù)庫(kù)的并發(fā)訪問(wèn)操作是一個(gè)普遍存在的問(wèn)題。SQLServer使用資源鎖定的方法管理用戶的并發(fā)操作。SQLServer2000提供了兩種并發(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ù)的一致性。這是SQLServer2000默認(rèn)的并發(fā)控制方法。下面予以介紹。SQLServer事務(wù)和并發(fā)控制課件8.6并發(fā)控制在大型分布式數(shù)據(jù)庫(kù)應(yīng)用程序中，對(duì)數(shù)據(jù)庫(kù)的并發(fā)518.6.1SQLServer鎖的粒度及模式

1、SQLServer鎖的粒度1）RID：行標(biāo)識(shí)符，鎖定表中單行數(shù)據(jù)。2）鍵值：具有索引的行數(shù)據(jù)。3）頁(yè)面：一個(gè)數(shù)據(jù)頁(yè)面或索引頁(yè)面。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)靈活選擇所鎖定的資源粒度。SQLServer事務(wù)和并發(fā)控制課件8.6.1SQLServer鎖的粒度及模式1、SQL522、資源鎖定模式—基本鎖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ù)，這樣肯定有一個(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ā)生死鎖。SQLServer事務(wù)和并發(fā)控制課件2、資源鎖定模式—基本鎖1）共享鎖：用于只讀數(shù)據(jù)操作，它允許532、資源鎖定模式—專用鎖3）更新鎖：在修改操作的初始化階段用于鎖定可能被修改的資源。一個(gè)數(shù)據(jù)修改事務(wù)在開(kāi)始時(shí)直接申請(qǐng)更新鎖，每次只有一個(gè)事務(wù)可以獲得資源的更新鎖。使用更新鎖可以避免上述死鎖，因?yàn)橐淮沃挥幸粋€(gè)事務(wù)可以獲得更新鎖，之后當(dāng)需要繼續(xù)修改數(shù)據(jù)時(shí)，將更新鎖轉(zhuǎn)換為排它鎖，否則將更新鎖轉(zhuǎn)換為共享鎖。SQLServer事務(wù)和并發(fā)控制課件2、資源鎖定模式—專用鎖3）更新鎖：在修改操作的初始化階段用544）意向鎖意向鎖表示如果獲得一個(gè)對(duì)象的鎖，說(shuō)明該結(jié)點(diǎn)的下層對(duì)象正在被加鎖。例如放置在表上的共享意向鎖表示事務(wù)打算在表中的頁(yè)或行上加共享鎖。意向鎖可以提高性能，因?yàn)橄到y(tǒng)僅在表級(jí)上檢查意向鎖而無(wú)須檢查下層。①意向共享鎖：對(duì)一個(gè)對(duì)象加意向共享鎖，表示將要對(duì)它的下層對(duì)象加共享鎖。②意向排它鎖：對(duì)一個(gè)對(duì)象加意向排它鎖，表示將要對(duì)它的下層對(duì)象加排它鎖。③意向排它共享鎖：對(duì)一個(gè)對(duì)象加意向排它共享鎖，表示對(duì)它加共享鎖，再在它的下層對(duì)象加排它鎖。SQLServer事務(wù)和并發(fā)控制課件4）意向鎖意向鎖表示如果獲得一個(gè)對(duì)象的鎖，說(shuō)明該結(jié)點(diǎn)的下層對(duì)555）架構(gòu)鎖①架構(gòu)修改鎖：執(zhí)行表的數(shù)據(jù)定義語(yǔ)言（DDL）操作時(shí)使用。②架構(gòu)穩(wěn)定鎖：編譯查詢時(shí)使用。它不阻塞任何事務(wù)鎖，包括排它鎖。6）大容量更新鎖：當(dāng)數(shù)據(jù)大容量復(fù)制到表的時(shí)候使用。SQLServer事務(wù)和并發(fā)控制課件5）架構(gòu)鎖①架構(gòu)修改鎖：執(zhí)行表的數(shù)據(jù)定義語(yǔ)568.6.2封鎖協(xié)議

在運(yùn)用X鎖和S鎖對(duì)數(shù)據(jù)對(duì)象加鎖時(shí)，需要約定一些規(guī)則：封鎖協(xié)議（LockingProtocol）何時(shí)申請(qǐng)X鎖或S鎖持鎖時(shí)間、何時(shí)釋放不同的封鎖協(xié)議，在不同的程度上為并發(fā)操作的正確調(diào)度提供一定的保證常用的封鎖協(xié)議：三級(jí)封鎖協(xié)議SQLServer事務(wù)和并發(fā)控制課件8.6.2封鎖協(xié)議在運(yùn)用X鎖和S鎖對(duì)數(shù)據(jù)對(duì)象加鎖時(shí)，需571級(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ù)。SQLServer事務(wù)和并發(fā)控制課件1級(jí)封鎖協(xié)議事務(wù)T在修改數(shù)據(jù)R之前必須先對(duì)其加X(jué)鎖，直到事務(wù)581級(jí)封鎖協(xié)議T1T2①

XlockA獲得②

讀A=16

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

⑤

XlockA等待等待等待等待獲得XlockA讀A=15A←A-1寫回A=14CommitUnlockA

沒(méi)有丟失修改沒(méi)有丟失修改SQLServer事務(wù)和并發(fā)控制課件1級(jí)封鎖協(xié)議T1T2①

XlockA

沒(méi)有丟失修改沒(méi)592級(jí)封鎖協(xié)議1級(jí)封鎖協(xié)議+事務(wù)T在讀取數(shù)據(jù)R前必須先加S鎖，讀完后即可釋放S鎖2級(jí)封鎖協(xié)議可以防止丟失修改和讀“臟”數(shù)據(jù)。在2級(jí)封鎖協(xié)議中，由于讀完數(shù)據(jù)后即可釋放S鎖，所以它不能保證可重復(fù)讀。SQLServer事務(wù)和并發(fā)控制課件2級(jí)封鎖協(xié)議1級(jí)封鎖協(xié)議+事務(wù)T在讀取數(shù)據(jù)R前必須先加S鎖，602級(jí)封鎖協(xié)議T1T2①

XlockC讀C=100C←C*2寫回C=200②

③ROLLBACK(C恢復(fù)為100)UnlockC④

⑤

SlockC等待等待等待等待獲得SlockC讀C=100CommitCUnlockC不讀“臟”數(shù)據(jù)

SQLServer事務(wù)和并發(fā)控制課件2級(jí)封鎖協(xié)議T1T2①XlockC不讀“臟”數(shù)據(jù)SQL613級(jí)封鎖協(xié)議1級(jí)封鎖協(xié)議+事務(wù)T在讀取數(shù)據(jù)R之前必須先對(duì)其加S鎖，直到事務(wù)結(jié)束才釋放3級(jí)封鎖協(xié)議可防止丟失修改、讀臟數(shù)據(jù)和不可重復(fù)讀。容易造成比較多的死鎖SQLServer事務(wù)和并發(fā)控制課件3級(jí)封鎖協(xié)議1級(jí)封鎖協(xié)議+事務(wù)T在讀取數(shù)據(jù)R之前必須先對(duì)623級(jí)封鎖協(xié)議T1T2①

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

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

⑤

XlockB等待等待等待等待等待等待等待等待獲得XlockB讀B=100B←B*2寫回B=200CommitUnlockB

可重復(fù)讀

SQLServer事務(wù)和并發(fā)控制課件3級(jí)封鎖協(xié)議T1T2①

SlockA

可重復(fù)讀SQL63封鎖協(xié)議小結(jié)SQLServer事務(wù)和并發(fā)控制課件封鎖協(xié)議小結(jié)SQLServer事務(wù)和并發(fā)控制課件648.6.3事務(wù)隔離

為了避免產(chǎn)生并發(fā)訪問(wèn)問(wèn)題，SQLServer使用不同類型的鎖對(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ì)資源所使用的鎖類型。SQLServer事務(wù)和并發(fā)控制課件8.6.3事務(wù)隔離為了避免產(chǎn)生并發(fā)訪問(wèn)問(wèn)題，SQLS651）事務(wù)隔離級(jí)別未提交讀：這是4種隔離級(jí)別中限制最低的級(jí)別，它僅能保證SQLServer不讀取物理?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ù)讀能夠避免產(chǎn)生臟讀和非重復(fù)讀問(wèn)題，但仍可能導(dǎo)致幻影讀問(wèn)題。（三級(jí)封鎖協(xié)議）可串行讀：這是事務(wù)隔離的最高級(jí)別，它使事務(wù)之間完全隔離，所以將導(dǎo)致并發(fā)級(jí)別較低。在這種隔離級(jí)別下，SQLServer在數(shù)據(jù)集上放置一個(gè)范圍鎖，以防止其他用戶在事務(wù)完成之前更新數(shù)據(jù)集或向數(shù)據(jù)集內(nèi)插入數(shù)據(jù)行，從而避免出現(xiàn)臟讀、非重復(fù)讀或幻影讀等并發(fā)問(wèn)題。（四級(jí)封鎖協(xié)議）SQLServer事務(wù)和并發(fā)控制課件1）事務(wù)隔離級(jí)別SQLServer事務(wù)和并發(fā)控制課件662）事務(wù)隔離級(jí)別對(duì)不同類型的行為隔離級(jí)別丟失修改臟數(shù)據(jù)不可重讀幻影未提交讀否是是是提交讀否否是是可重復(fù)讀否否否是可串行讀否否否否SQLServer事務(wù)和并發(fā)控制課件2）事務(wù)隔離級(jí)別對(duì)不同類型的行為隔離級(jí)別673）設(shè)置事務(wù)隔離級(jí)別調(diào)用T-SQL中的SETTRANSACTIONINOLATIONLEVEL語(yǔ)句可以調(diào)整事務(wù)的隔離級(jí)別，以控制由該連接所發(fā)出的所有SELECT語(yǔ)句的默認(rèn)事務(wù)鎖定行為。該語(yǔ)句的語(yǔ)法格式為：SETTRANSACTIONISOLATIONLEVEL{READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE}例如：將事務(wù)隔離級(jí)別設(shè)置為REPEATABLEREADSETTRANSACTIONISOLATIONLEVELREPEATABLEREAD注意：一旦設(shè)定，系統(tǒng)就會(huì)按這種隔離級(jí)別自動(dòng)進(jìn)行并發(fā)處理。SQLServer事務(wù)和并發(fā)控制課件3）設(shè)置事務(wù)隔離級(jí)別調(diào)用T-SQL中的SET68設(shè)置表級(jí)鎖表級(jí)鎖是由程序員進(jìn)行的，可以對(duì)SELECT、INSERT、UPDATE、DELETE語(yǔ)句進(jìn)行精確控制。表級(jí)鎖定不是系統(tǒng)自動(dòng)完成，而是需要程序員自己根據(jù)事務(wù)的要求進(jìn)行鎖定，然后，系統(tǒng)按程序員在程序中的鎖定予以執(zhí)行。一般來(lái)說(shuō)，讀操作需要共享鎖，寫操作需要排它鎖。如果需要更精確，還需要一些其它專用鎖。具體設(shè)置有：設(shè)置共享鎖設(shè)置排它鎖設(shè)置專用鎖SQLServer事務(wù)和并發(fā)控制課件設(shè)置表級(jí)鎖表級(jí)鎖是由程序員進(jìn)行的，可以對(duì)SELE69設(shè)置共享鎖共享鎖用于讀操作。一個(gè)資源可獲得多個(gè)共享鎖。使用HOLDLOCK設(shè)置共享鎖。例：USENorthwindGOBEGINTRANSANCTIONT1SELECTOrderID,OrderDateFROMOrdersWITH(HOLDLOCK)SELECTCOUNT(OrderID)FROMOrdersCOMMITSQLServer事務(wù)和并發(fā)控制課件設(shè)置共享鎖共享鎖用于讀操作。一個(gè)資源可獲得70設(shè)置排它鎖對(duì)于INSERT、UPDATE和DELETE語(yǔ)句使用排它鎖。在并發(fā)事務(wù)中，只有一個(gè)事務(wù)能夠獲得資源的排它鎖。使用TABLOCKX設(shè)置排它鎖。例：USENorthwindGOBEGINTRANSANCTIONT1INSERTINTOOrdersWITH(TABLOCKX)(CustomerID,OrderDate)VALUES(‘ALEF’,’2005-01-01’)COMMITSQLServer事務(wù)和并發(fā)控制課件設(shè)置排它鎖對(duì)于INSERT、UPDATE和DELETE71設(shè)置其它專用鎖方法同上。NOLOCK：不要發(fā)出共享鎖和排它鎖。僅適用SELECT語(yǔ)句。READPAST：跳過(guò)已經(jīng)鎖定的行。僅適用SELECT語(yǔ)句。TABLOCK：使用大容量更新鎖。PAGLOCK：使用頁(yè)級(jí)鎖。ROWLOCK：使用行級(jí)鎖，不使用頁(yè)級(jí)和表級(jí)鎖。UPDLOCK：讀取表時(shí)使用更新鎖。XLOCK：適用于不同粒度的排它鎖。SQLServer事務(wù)和并發(fā)控制課件設(shè)置其它專用鎖方法同上。SQLServer事務(wù)和728.6.4死鎖處理

SQLServer能夠自動(dòng)定期搜索和處理死鎖問(wèn)題。當(dāng)檢測(cè)到有死鎖時(shí)，SQLServer回滾被中斷的事務(wù)，并向應(yīng)用程序返回1205號(hào)錯(cuò)誤信息，未被中斷的事務(wù)則繼續(xù)執(zhí)行。在數(shù)據(jù)庫(kù)應(yīng)用程序撲捉到1205號(hào)錯(cuò)誤，可以對(duì)死鎖現(xiàn)象做后續(xù)處理。為減少死鎖次數(shù)，在設(shè)計(jì)應(yīng)用程序時(shí)：1）盡量使一個(gè)事務(wù)在一個(gè)批內(nèi)，并且要短；2）不要在事務(wù)處理期間和用戶交互；3）靈活使用較低級(jí)別的事務(wù)隔離；4）在事務(wù)處理期間要使訪問(wèn)數(shù)據(jù)量最小。SQLServer事務(wù)和并發(fā)控制課件8.6.4死鎖處理SQLServer能夠自動(dòng)定期搜索73Thankyouverymuch!謝謝您的光臨！下一章SQLServer事務(wù)和并發(fā)控制課件Thankyouverymuch!謝謝您的光臨！下一章74大型數(shù)據(jù)庫(kù)系統(tǒng)管理、設(shè)計(jì)與實(shí)例分析

——基于SQLServer

SQLServer事務(wù)和并發(fā)控制課件大型數(shù)據(jù)庫(kù)系統(tǒng)管理、設(shè)計(jì)與實(shí)例分析

——基于SQLServ75第8章SQLServer事務(wù)和并發(fā)控制8.1事務(wù)8.2事務(wù)的分類和控制8.3并發(fā)控制8.4事務(wù)處理實(shí)例分析

8.5分布式事務(wù)8.6并發(fā)控制

SQLServer事務(wù)和并發(fā)控制課件第8章SQLServer事務(wù)和并發(fā)控制8.1768.1事務(wù)8.1.1事務(wù)的概念8.1.2事務(wù)對(duì)并發(fā)控制和保障數(shù)據(jù)完整的重要性SQLServer事務(wù)和并發(fā)控制課件8.1事務(wù)8.1.1事務(wù)的概念SQLServer事778.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ù)性。SQLServer事務(wù)和并發(fā)控制課件8.1.1事務(wù)的概念事務(wù)是一個(gè)用戶定義的完整的工作單元，781、事務(wù)的特性(ACID)原子性(Atomicity)：事務(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)該是永久的

SQLServer事務(wù)和并發(fā)控制課件1、事務(wù)的特性(ACID)原子性(Atomicity)：事務(wù)792、事務(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è)批中所有語(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)打開(kāi)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ù)的提交或回滾操作。SQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別編程時(shí)，一定要區(qū)分事務(wù)和批的差別：SQLS802、事務(wù)和批的區(qū)別SETXACT_ABORT指定當(dāng)Transact-SQL語(yǔ)句產(chǎn)生運(yùn)行時(shí)錯(cuò)誤時(shí)，Microsoft?SQLServer?是否自動(dòng)回滾當(dāng)前事務(wù)。語(yǔ)法SETXACT_ABORT{ON|OFF}當(dāng)SETXACT_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ò)誤）不受SETXACT_ABORT的影響。對(duì)于大多數(shù)OLEDB提供程序（包括SQLServer），隱性或顯式事務(wù)中的數(shù)據(jù)修改語(yǔ)句必須將XACT_ABORT設(shè)置為ON。唯一不需要該選項(xiàng)的情況是提供程序支持嵌套事務(wù)時(shí)。SQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別SETXACT_ABORTSQLServ812、事務(wù)和批的區(qū)別下例導(dǎo)致在含有其它Transact-SQL語(yǔ)句的事務(wù)中發(fā)生違反外鍵錯(cuò)誤。在第一個(gè)語(yǔ)句集中產(chǎn)生錯(cuò)誤，但其它語(yǔ)句均成功執(zhí)行且事務(wù)成功提交。在第二個(gè)語(yǔ)句集中，SETXACT_ABORT設(shè)置為ON。這導(dǎo)致語(yǔ)句錯(cuò)誤使批處理終止，并使事務(wù)回滾。CREATETABLEt1(aintPRIMARYKEY)CREATETABLEt2(aintREFERENCESt1(a))GOINSERTINTOt1VALUES(1)INSERTINTOt1VALUES(3)INSERTINTOt1VALUES(4)INSERTINTOt1VALUES(6)GOSETXACT_ABORTOFFGOBEGINTRANINSERTINTOt2VALUES(1)INSERTINTOt2VALUES(2)/*Foreignkeyerror*/INSERTINTOt2VALUES(3)COMMITTRANGOSQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別下例導(dǎo)致在含有其它Transact-SQ822、事務(wù)和批的區(qū)別SETXACT_ABORTONGOBEGINTRANINSERTINTOt2VALUES(4)INSERTINTOt2VALUES(5)/*Foreignkeyerror*/INSERTINTOt2VALUES(6)COMMITTRANGO/*Selectshowsonlykeys1and3added.Key2insertfailedandwasrolledback,butXACT_ABORTwasOFFandrestoftransactionsucceeded.Key5inserterrorwithXACT_ABORTONcausedallofthesecondtransactiontorollback.*/SELECT*FROMt2GODROPTABLEt2DROPTABLEt1GOSQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別SETXACT_ABORTONSQLS832、事務(wù)和批的區(qū)別問(wèn)題：如何知道XACT_ABORT為ON還是OFF呢？XACT_ABORT的默認(rèn)值是什么？SQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別問(wèn)題：如何知道XACT_ABORT為ON還842、事務(wù)和批的區(qū)別問(wèn)題：如何知道XACT_ABORT為ON還是OFF呢？DBCCUSEROPTIONS:itwillshowassetifit'sON.Ifit'sOFFthenitwillnotshow

DBCCUSEROPTIONS返回當(dāng)前連接的活動(dòng)（設(shè)置）的SET選項(xiàng)?？纯碫B的例子：運(yùn)行DBCC（順便看一下聯(lián)機(jī)幫助文檔）并查看結(jié)果SQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別問(wèn)題：如何知道XACT_ABORT為ON還852、事務(wù)和批的區(qū)別SETXACT_ABORT的默認(rèn)值是什么？對(duì)每個(gè)連接來(lái)說(shuō)，SETXACT_ABORT的默認(rèn)值是OFF看看VB的例子和查詢分析器SQLServer事務(wù)和并發(fā)控制課件2、事務(wù)和批的區(qū)別SETXACT_ABORT的默認(rèn)值是什么868.1.2事務(wù)對(duì)并發(fā)控制和保障

數(shù)據(jù)完整的重要性1．事務(wù)與并發(fā)控制的關(guān)系如果在用戶并發(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）幻影讀SQLServer事務(wù)和并發(fā)控制課件8.1.2事務(wù)對(duì)并發(fā)控制和保障

數(shù)據(jù)完整的重要性1．事872．事務(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ù)完整的重要性SQLServer事務(wù)和并發(fā)控制課件2．事務(wù)對(duì)保障數(shù)據(jù)一致和完整性的作用8.1.2事務(wù)對(duì)并888.2事務(wù)的分類和控制8.2.1事務(wù)的分類8.2.2事務(wù)控制SQLServer事務(wù)和并發(fā)控制課件8.2事務(wù)的分類和控制8.2.1事務(wù)的分類SQL898.2.1事務(wù)的分類SQLServer的事務(wù)模式可分為顯式事務(wù)、隱式事務(wù)和自動(dòng)事務(wù)三種。1)顯式事務(wù)顯式事務(wù)是指由用戶執(zhí)行T-sql事務(wù)語(yǔ)句而定義的事務(wù)，這類事務(wù)又稱做用戶定義事務(wù)。定義事務(wù)的語(yǔ)句包括：BEGINTRANSACTION:標(biāo)識(shí)一個(gè)事務(wù)的開(kāi)始，即啟動(dòng)事務(wù)。COMMITTRANSACTION、COMMITWORK：標(biāo)識(shí)一個(gè)事務(wù)的結(jié)束，事務(wù)內(nèi)所修改的數(shù)據(jù)被永久保存到數(shù)據(jù)庫(kù)中。ROLLBACKTRANSACTION、ROLLBACKWORK：標(biāo)識(shí)一個(gè)事務(wù)的結(jié)束，說(shuō)明事務(wù)執(zhí)行過(guò)程中遇到錯(cuò)誤，事務(wù)內(nèi)所修改的數(shù)據(jù)被回滾到事務(wù)執(zhí)行前的狀態(tài)。SQLServer事務(wù)和并發(fā)控制課件8.2.1事務(wù)的分類SQLServer的事務(wù)