SQL Server 數(shù)據(jù)庫(kù)開(kāi)發(fā)經(jīng)典案例教程第11章數(shù)據(jù)庫(kù)事務(wù)

第11章數(shù)據(jù)庫(kù)事務(wù)本章目標(biāo)理解事務(wù)的概念理解事務(wù)的特性、分類在SQLServer中啟動(dòng)、提交和回滾事務(wù)掌握事務(wù)的隔離級(jí)別為什么需要事務(wù)事務(wù)能確保把對(duì)多個(gè)數(shù)據(jù)操作作為一個(gè)單元來(lái)處理例如：銀行轉(zhuǎn)帳問(wèn)題:假設(shè)張三的銀行卡有２０００元，李四的卡有１０００元，共計(jì)３０００元。現(xiàn)在張三要轉(zhuǎn)１０００元給李四，則要更新張三的帳戶，減少余額１０００元，更新李四帳戶余額，增加１０００元。如果整個(gè)流程沒(méi)有出錯(cuò)則最后張三余額１０００元，李四２０００元，共計(jì)３０００元。數(shù)據(jù)和轉(zhuǎn)帳之前是一致的，假設(shè)在減少了張三的余額１０００后，系統(tǒng)掉電或者出現(xiàn)意外李四的帳戶還沒(méi)完成更新。則最終的數(shù)據(jù)２０００元，那怎么解決？事務(wù)的概念及特性事務(wù)提供了一種機(jī)制、是一個(gè)操作序列，它包含了一組數(shù)據(jù)庫(kù)操作命令，并且所有的命令作為一個(gè)整體一起向系統(tǒng)提交或撤消操作請(qǐng)求事務(wù)是作為單個(gè)邏輯工作單元執(zhí)行的一系列操作事務(wù)的四個(gè)特性:原子性（Atomicity）：事務(wù)能確保把對(duì)多個(gè)數(shù)據(jù)修改作為一個(gè)單元來(lái)處理，也就是原子操作。一致性（Consistency）：當(dāng)事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致?tīng)顟B(tài)隔離性（Isolation）:對(duì)數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的。這表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴或影響其他事務(wù)持久性（Durability）：當(dāng)事務(wù)完成之后，它對(duì)于系統(tǒng)的影響是永久性的。事務(wù)的分類事務(wù)分類顯式事務(wù)隱性事務(wù)自動(dòng)提交事務(wù)顯式事務(wù):顯式事務(wù)是顯式地定義其開(kāi)始和結(jié)束的事務(wù)

BEGINTRANSACTION…數(shù)據(jù)庫(kù)操作如:插入記錄

…數(shù)據(jù)庫(kù)操作如:刪除記錄COMMITTRANSACTION事務(wù)的分類隱性事務(wù):通過(guò)Transact-SQL的SETIMPLICIT_TRANSACTIONSON語(yǔ)句，將隱性事務(wù)模式設(shè)置為打開(kāi)。

SETIMPLICIT_TRANSACTIONSONGO/*第一次執(zhí)行Insert語(yǔ)句的時(shí)候?qū)⒆詣?dòng)啟動(dòng)一個(gè)隱性事務(wù)*/INSERTINTOImpTranVALUES(1,'aaa')INSERTINTOImpTranVALUES(2,'bbb')GO/*提交第一個(gè)事務(wù)*/COMMITTRANSACTIONGO/*執(zhí)行SELECT語(yǔ)句將啟動(dòng)第二個(gè)隱性事務(wù)*/SELECTCOUNT(*)FROMImpTranGOINSERTINTOImpTranVALUES(3,'ccc')GOSELECT*FROMImpTranGO/*提交第二個(gè)事務(wù)*/COMMITTRANSACTIONGOSETIMPLICIT_TRANSACTIONSOFFGO事務(wù)的分類自動(dòng)提交事務(wù):所有Transact-SQL語(yǔ)句在完成時(shí)，都會(huì)提交或回滾。如果一條語(yǔ)句成功完成，則將其提交，如果遇到任何錯(cuò)誤，則將其回滾;默認(rèn)操作模式用T-SQL表示事務(wù)Transact-SQL使用下列語(yǔ)句來(lái)管理事務(wù)開(kāi)始事務(wù)：BEGINTRANSACTION提交事務(wù)：COMMITTRANSACTION回滾（撤消）事務(wù)：ROLLBACKTRANSACTION下列變量在事務(wù)處理中非常有用。@@ERROR@@TRANCOUNT事務(wù)的應(yīng)用示例：客戶購(gòu)買(mǎi)圖書(shū)USEBookShopDbGO--一個(gè)客戶在銷售員編號(hào)為的用戶處購(gòu)買(mǎi)了三種書(shū)，生成銷售記錄---SETNOCOUNTON--不顯示受影響的行數(shù)信息GOBEGINTRANSACTION--開(kāi)始事務(wù)DECLARE@errINT--保存錯(cuò)誤編號(hào)PRINT'客戶開(kāi)始買(mǎi)書(shū),事務(wù)開(kāi)始'SET@err=0--插入銷售主表SalesMasterINSERTINTOSalesMasterVALUES(GETDATE(),'001')--聲明局部變量保存SalesMasterIDSET@err=@err+@@ERRORDECLARE@SalesMasterIDINT--獲取最新的SalesMasterIDSELECT@SalesMasterID=@@IDENTITY--購(gòu)買(mǎi)三本書(shū)INSERTINTOSalesDetails

VALUES(@SalesMasterID,'B0001',1,0.9,45)SET@err=@err+@@ERRORINSERTINTOSalesDetails

VALUES(@SalesMasterID,'B0003',1,1,118)SET@err=@err+@@ERRORINSERTINTOSalesDetails

VALUES(@SalesMasterID,'B0004',2,0.8,28)SET@err=@err+@@ERROR--購(gòu)買(mǎi)完成,根據(jù)錯(cuò)誤編號(hào)決定是否執(zhí)行成功IF@err=0BEGINPRINT'購(gòu)買(mǎi)成功，事務(wù)結(jié)束'COMMITTRANSACTIONENDELSEBEGINROLLBACKTRANSACTIONPRINT'購(gòu)買(mǎi)失敗，事務(wù)回滾'ENDGO事務(wù)的隔離級(jí)別事務(wù)的隔離級(jí)別是指一個(gè)事務(wù)必須和其他事務(wù)在進(jìn)行資源或者數(shù)據(jù)更改時(shí)相隔離的程度較低的隔離級(jí)別可以增強(qiáng)許多用戶同時(shí)訪問(wèn)數(shù)據(jù)的能力，但也增加了用戶可能遇到的并發(fā)副作用（例如臟讀或丟失更新）的數(shù)量。相反，較高的隔離級(jí)別減少了用戶可能遇到的并發(fā)副作用的類型，但需要更多的系統(tǒng)資源，并增加了一個(gè)事務(wù)阻塞其他事務(wù)的可能性SQLServer支持的隔離級(jí)別READUNCOMMITTED未提交讀（隔離事務(wù)的最低級(jí)別）READCOMMITTED已提交讀（數(shù)據(jù)庫(kù)引擎的默認(rèn)級(jí)別）REPEATABLEREAD可重復(fù)讀SERIALIZABLE可序列化（隔離事務(wù)的最高級(jí)別，事務(wù)之間完全隔離）未提交讀--第一個(gè)事務(wù)USEBookShopDbGOSETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;GOBEGINTRANSACTIONGOSELECT*FROMpublisherWHEREPublisherID=1GOUPDATEPublisherSETPublisherName=PublisherName+'2'WHEREPublisherID=1GOSELECT*FROMpublisherWHEREPublisherID=1GOWAITFORDELAY'00:00:15'GOROLLBACKTRANSACTION;GOSELECT*FROMpublisherWHEREPublisherID=1GO--第二個(gè)事務(wù)USEBookShopDbGOSETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED;GOBEGINTRANSACTIONGOSELECT*FROMpublisherWHEREPublisherID=1GOCOMMIT;GO可重復(fù)讀--第一個(gè)事務(wù)USEBookShopDbGOSETTRANSACTIONISOLATIONLEVELREPEATABLEREAD;GOBEGINTRANSACTIONGOSELECT*FROMpublisherWHEREPublisherID=1GOWAITFORDELAY'00:00:15'GOSELECT*FROMpublisherWHEREPublisherID=1GOCOMMITTRANSACTION;GO---第二個(gè)事務(wù)USEBookShopDbGOSETTRANSACTIONISOLATIONLEVELREPEATABLEREAD;GOBEGINTRANSACTIONGOSELECT*FROMpublisherWHEREPublisherID=1GOUPDATEPublisherSETPublisherName=PublisherName+'2'WHEREPublisherID=1GOSELECT*FROMpublisherWHEREPublisherID=1GOCOMMITTRANSACTION;SELECT*FROMpublisherWHEREPublisherID=1GO數(shù)據(jù)庫(kù)死鎖在數(shù)據(jù)庫(kù)的并發(fā)應(yīng)用中,多個(gè)請(qǐng)求同時(shí)向數(shù)據(jù)庫(kù)發(fā)送請(qǐng)求,如果各個(gè)進(jìn)程均占有不會(huì)釋放的資源,但因互相申請(qǐng)被其他進(jìn)程所站用不會(huì)釋放的資源而處于的一種永久等待狀態(tài)。這種狀態(tài)就是常說(shuō)的死鎖死鎖示例USEBookShopDbGOBEGINTRANSACTIONUPDATEPublisherSETPublisherName=PublisherName+'publisher'WHEREPublisherID=1WAITFORDELAY'00:00:20'SELECT*FROMcategoryROLLBACKTRANSACTION等待20秒之后顯示查詢結(jié)果：--第二個(gè)查詢USEBookShopDbGOBEGINTRANSACTIONUPDATEPublisherSETPublisherName=PublisherName+'publisher'WHEREPublisherID=1WAITFORDELAY'00:00:20'SELECT*FROMcategoryROLLBACKTRANSACTION等待20秒之后顯示查詢結(jié)果：死鎖解決方法1.SQLServer自動(dòng)選擇一條SQL語(yǔ)句作犧牲品，我們會(huì)發(fā)現(xiàn)兩個(gè)查詢中第一個(gè)正常執(zhí)行，第二個(gè)則拋出錯(cuò)誤。2.按照同一順序訪問(wèn)資源：調(diào)換查詢二中