第11章數(shù)據(jù)庫事務

1、1本章目標本章目標 理解事務的概念理解事務的概念 理解事務的特性、分類理解事務的特性、分類 在在SQLServer中啟動、提交和回滾事務中啟動、提交和回滾事務 掌握事務的隔離級別掌握事務的隔離級別2為什么需要事務為什么需要事務 事務能確保把對多個數(shù)據(jù)操作作為一個單元來處理事務能確保把對多個數(shù)據(jù)操作作為一個單元來處理 例如：銀行轉帳例如：銀行轉帳l 問題問題:假設張三的銀行卡有元，李四的卡有假設張三的銀行卡有元，李四的卡有元，共計元。現(xiàn)在張三要轉元給元，共計元。現(xiàn)在張三要轉元給李四，則要更新張三的帳戶，減少余額元，更李四，則要更新張三的帳戶，減少余額元，更新李四帳戶余額，增加元。如果整個流程沒有

2、新李四帳戶余額，增加元。如果整個流程沒有出錯則最后張三余額元，李四元，共出錯則最后張三余額元，李四元，共計元。數(shù)據(jù)和轉帳之前是一致的，假設在減少計元。數(shù)據(jù)和轉帳之前是一致的，假設在減少了張三的余額后，系統(tǒng)掉電或者出現(xiàn)意外李四了張三的余額后，系統(tǒng)掉電或者出現(xiàn)意外李四的帳戶還沒完成更新。則最終的數(shù)據(jù)元，那怎的帳戶還沒完成更新。則最終的數(shù)據(jù)元，那怎么解決？么解決？3事務的概念及特性事務的概念及特性 事務提供了一種機制、是一個操作序列，它包含了一組數(shù)事務提供了一種機制、是一個操作序列，它包含了一組數(shù)據(jù)庫操作命令，并且所有的命令作為一個整體一起向系統(tǒng)據(jù)庫操作命令，并且所有的命令作為一個整體一起向系統(tǒng)提交

3、或撤消操作請求提交或撤消操作請求 事務是作為單個邏輯工作單元執(zhí)行的一系列操作事務是作為單個邏輯工作單元執(zhí)行的一系列操作 事務的四個特性事務的四個特性:l 原子性（原子性（Atomicity）：事務能確保把對多個數(shù)據(jù)修改作為一個單）：事務能確保把對多個數(shù)據(jù)修改作為一個單元來處理，也就是原子操作。元來處理，也就是原子操作。 l 一致性（一致性（Consistency）：當事務完成時，數(shù)據(jù)必須處于一致狀）：當事務完成時，數(shù)據(jù)必須處于一致狀態(tài)態(tài) l 隔離性（隔離性（Isolation）:對數(shù)據(jù)進行修改的所有并發(fā)事務是彼此隔離對數(shù)據(jù)進行修改的所有并發(fā)事務是彼此隔離的。這表明事務必須是獨立的，它不應以任何

4、方式依賴或影響其的。這表明事務必須是獨立的，它不應以任何方式依賴或影響其他事務他事務 l 持久性（持久性（Durability）：當事務完成之后，它對于系統(tǒng)的影響是）：當事務完成之后，它對于系統(tǒng)的影響是永久性的。永久性的。 4事務的分類事務的分類 事務分類事務分類l 顯式事務顯式事務l 隱性事務隱性事務l 自動提交事務自動提交事務 顯式事務顯式事務:顯式事務是顯式地定義其開始和結束的事務顯式事務是顯式地定義其開始和結束的事務 BEGIN TRANSACTION 數(shù)據(jù)庫操作如數(shù)據(jù)庫操作如:插入記錄插入記錄 數(shù)據(jù)庫操作如數(shù)據(jù)庫操作如:刪除記錄刪除記錄COMMIT TRANSACTION 5事務的分

5、類事務的分類 隱性事務隱性事務:通過通過Transact-SQL的的 SET IMPLICIT_TRANSACTIONS ON 語句，將隱性事務模式設置為打開語句，將隱性事務模式設置為打開。 SET IMPLICIT_TRANSACTIONS ONGO/*第一次執(zhí)行第一次執(zhí)行Insert 語句的時候將自動啟動一個隱性事務語句的時候將自動啟動一個隱性事務*/INSERT INTO ImpTran VALUES(1,aaa)INSERT INTO ImpTran VALUES(2,bbb)GO/*提交第一個事務提交第一個事務*/COMMIT TRANSACTIONGO/*執(zhí)行執(zhí)行SELECT語句將

6、啟動第二個隱性事務語句將啟動第二個隱性事務*/SELECT COUNT(*) FROM ImpTranGOINSERT INTO ImpTran VALUES(3,ccc)GOSELECT * FROM ImpTranGO/*提交第二個事務提交第二個事務*/COMMIT TRANSACTIONGOSET IMPLICIT_TRANSACTIONS OFFGO 6事務的分類事務的分類 自動提交事務自動提交事務:所有所有Transact-SQL語句在完成時，都會提語句在完成時，都會提交或回滾。如果一條語句成功完成，則將其提交，如果遇交或回滾。如果一條語句成功完成，則將其提交，如果遇到任何錯誤，則將

7、其回滾到任何錯誤，則將其回滾; 默認操作模式默認操作模式7用用T-SQL表示事務表示事務 Transact-SQL使用下列語句來管理事務使用下列語句來管理事務l 開始事務：開始事務：BEGIN TRANSACTIONl 提交事務：提交事務：COMMIT TRANSACTIONl 回滾（撤消）事務：回滾（撤消）事務：ROLLBACK TRANSACTION 下列變量在事務處理中非常有用。下列變量在事務處理中非常有用。l ERRORl TRANCOUNT8事務的應用事務的應用 示例：示例：客戶購買圖書客戶購買圖書USE BookShopDb GO-一個客戶在銷售員編號為的用戶處購買了三種書，生成銷

8、售記錄一個客戶在銷售員編號為的用戶處購買了三種書，生成銷售記錄-SET NOCOUNT ON -不顯示受影響的行數(shù)信息不顯示受影響的行數(shù)信息GOBEGIN TRANSACTION -開始事務開始事務DECLARE err INT -保存錯誤編號保存錯誤編號PRINT 客戶開始買書客戶開始買書,事務開始事務開始SET err=0-插入銷售主表插入銷售主表SalesMasterINSERT INTO SalesMaster VALUES(GETDATE(),001)-聲明局部變量保存聲明局部變量保存SalesMasterIDSET err=err+ERRORDECLARE SalesMasterI

9、D INT -獲取最新的獲取最新的SalesMasterID9SELECT SalesMasterID=IDENTITY -購買三本書購買三本書INSERT INTO SalesDetailsVALUES(SalesMasterID,B0001,1,0.9,45)SET err=err+ERRORINSERT INTO SalesDetailsVALUES(SalesMasterID,B0003,1,1,118)SET err=err+ERRORINSERT INTO SalesDetailsVALUES(SalesMasterID,B0004,2,0.8,28)SET err=err+ERR

10、OR-購買完成購買完成,根據(jù)錯誤編號決定是否執(zhí)行成功根據(jù)錯誤編號決定是否執(zhí)行成功IF err=0BEGIN PRINT 購買成功，事務結束購買成功，事務結束 COMMIT TRANSACTIONEND ELSE BEGIN ROLLBACK TRANSACTION PRINT 購買失敗，事務回滾購買失敗，事務回滾 ENDGO10事務的隔離級別事務的隔離級別 事務的隔離級別是指一個事務必須和其他事務在進行資源事務的隔離級別是指一個事務必須和其他事務在進行資源或者數(shù)據(jù)更改時相隔離的程度或者數(shù)據(jù)更改時相隔離的程度 較低的隔離級別可以增強許多用戶同時訪問數(shù)據(jù)的能力，較低的隔離級別可以增強許多用戶同時訪

11、問數(shù)據(jù)的能力，但也增加了用戶可能遇到的并發(fā)副作用（例如臟讀或丟失但也增加了用戶可能遇到的并發(fā)副作用（例如臟讀或丟失更新）的數(shù)量。相反，較高的隔離級別減少了用戶可能遇更新）的數(shù)量。相反，較高的隔離級別減少了用戶可能遇到的并發(fā)副作用的類型，但需要更多的系統(tǒng)資源，并增加到的并發(fā)副作用的類型，但需要更多的系統(tǒng)資源，并增加了一個事務阻塞其他事務的可能性了一個事務阻塞其他事務的可能性11SQLServer支持的隔離級別支持的隔離級別 READ UNCOMMITTED未提交讀（隔離事務的最低級別）未提交讀（隔離事務的最低級別） READ COMMITTED已提交讀（數(shù)據(jù)庫引擎的默認級別）已提交讀（數(shù)據(jù)庫引擎

12、的默認級別） REPEATABLE READ可重復讀可重復讀 SERIALIZABLE可序列化（隔離事務的最高級別，事務可序列化（隔離事務的最高級別，事務之間完全隔離）之間完全隔離）12未提交讀未提交讀-第一個事務第一個事務USE BookShopDbGOSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;GOBEGIN TRANSACTIONGOSELECT * FROM publisher WHERE PublisherID=1GOUPDATE Publisher SET PublisherName=PublisherName+2WHERE P

13、ublisherID=1GOSELECT * FROM publisher WHERE PublisherID=1GOWAITFOR DELAY 00:00:15GOROLLBACK TRANSACTION;GOSELECT * FROM publisher WHERE PublisherID=1GO13-第二個事務第二個事務USE BookShopDbGOSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;GOBEGIN TRANSACTIONGOSELECT * FROM publisher WHERE PublisherID=1GOCOMMI

14、T;GO14可重復讀可重復讀-第一個事務第一個事務USE BookShopDbGOSET TRANSACTION ISOLATION LEVEL REPEATABLE READ;GOBEGIN TRANSACTIONGOSELECT * FROM publisher WHERE PublisherID=1GOWAITFOR DELAY 00:00:15GOSELECT * FROM publisher WHERE PublisherID=1GOCOMMIT TRANSACTION;GO15-第二個事務第二個事務USE BookShopDbGOSET TRANSACTION ISOLATION

15、LEVEL REPEATABLE READ;GOBEGIN TRANSACTIONGOSELECT * FROM publisher WHERE PublisherID=1GOUPDATE Publisher SET PublisherName=PublisherName+2WHERE PublisherID=1GOSELECT * FROM publisher WHERE PublisherID=1GOCOMMIT TRANSACTION;SELECT * FROM publisher WHERE PublisherID=1GO16數(shù)據(jù)庫死鎖數(shù)據(jù)庫死鎖 在數(shù)據(jù)庫的并發(fā)應用中在數(shù)據(jù)庫的并發(fā)應用

16、中,多個請求同時向數(shù)據(jù)庫發(fā)送請求多個請求同時向數(shù)據(jù)庫發(fā)送請求,如果各個進程均占有不會釋放的資源如果各個進程均占有不會釋放的資源,但因互相申請被其但因互相申請被其他進程所站用不會釋放的資源而處于的一種永久等待狀態(tài)。他進程所站用不會釋放的資源而處于的一種永久等待狀態(tài)。這種狀態(tài)就是常說的死鎖這種狀態(tài)就是常說的死鎖17死鎖示例死鎖示例USE BookShopDbGOBEGIN TRANSACTIONUPDATE Publisher SET PublisherName=PublisherName+publisherWHERE PublisherID=1WAITFOR DELAY 00:00:20SELE

17、CT * FROM categoryROLLBACK TRANSACTION等待等待20秒之后顯示查詢結果：秒之后顯示查詢結果：18-第二個查詢第二個查詢USE BookShopDbGOBEGIN TRANSACTIONUPDATE Publisher SET PublisherName=PublisherName+publisherWHERE PublisherID=1WAITFOR DELAY 00:00:20SELECT * FROM categoryROLLBACK TRANSACTION等待等待20秒之后顯示查詢結果：秒之后顯示查詢結果：19死鎖解決方法死鎖解決方法 1.SQLServer自動選擇一條自動選擇一條SQL語句作犧牲品，我們會發(fā)語句作犧牲品，我們會發(fā)現(xiàn)兩個查詢中第一個正常執(zhí)行，第二個則拋出錯誤?，F(xiàn)兩個查詢中第一個正常執(zhí)行，第二個則拋出錯誤。 2.按照同一順序訪問資源：調(diào)換查詢二中按照同一順序訪問資源：調(diào)換查詢二中Update語句和語句和Select語句的順序。語句的順序。 3. SELECT 語