1、第9章 事務管理與并發控制 事務的概念（1.7）事務的概念事務的性質事務管理的基本命令事務舉例事務是構成單一邏輯工作單元的操作集合。l買賣交易一手交錢一手交貨l訂票查詢、訂位、(交錢)、出票（往返票？）l轉帳轉出、轉入為什么需要事務的概念呢？恢復的需要并發操作的需要l買賣交易一手交錢一手交貨l訂票查詢、訂位、(交錢)、出票（往返票？）l轉帳轉出、轉入James Gray1998年獲得圖靈獎在數據庫技術、特別是事務處理方面做出了杰出貢獻。解決了諸如完整性、安全性、并發控制等一系列技術難題。事務的性質 原子性（Atomicity） 一致性（Consistency） 隔離性（Isolation）持久

2、性（Durability） 事務的這些性質通常稱為ACID特性 原子性 事務的原子性強調了一個事務是一個邏輯工作單元，是一個整體，是不可分割的。一個事務所包含的操作要么全部做，要么全部不做。l買賣交易一手交錢一手交貨l訂票查詢、訂位、(交錢)、出票（往返票？）l轉帳轉出、轉入 一致性 一個事務執行一項數據庫操作，事務將使數據庫從一種一致性的狀態變換成另一種一致性狀態。 在事務執行前，總是假設數據庫是一致的，那么當事務成功執行后，數據庫肯定仍然是一致的。 l買賣交易一手交錢一手交貨l訂票查詢、訂位、(交錢)、出票（往返票？）l轉帳轉出、轉入（帳目平衡）隔離性 如果每個事務單獨執行能保持原子性和一

3、致性，這些事務并發執行也能保持原子性和一致性，則是事務的隔離性。l并發記帳？持久性 事務的持久性是指一旦事務成功完成，該事務對數據庫所施加的所有更新都是永久的。l在ATM取錢后銀行系統突然發生故障？ ACID性質DBMS的功能如果讓用戶程序來實現結果會怎樣？事務的生命周期事務的結束提交(Commit)或者撤銷(Rollback)事務在活動中的狀態l活動狀態事務初始時l部分提交狀態命令執行完、但未提交l失敗狀態發現正常操作不能進行l撤銷狀態撤銷事務，恢復到事務前的狀態l提交狀態成功完成后事務的生命周期命令執行完只要事務沒有提交事務就沒有結束，還有可能轉變到失敗狀態?；顒訝顟B失敗狀態提交狀態撤銷狀

4、態部分提交狀態SQL對事務的支持 開始事務 結束事務 事務保存點 隱含事務與自動提交 開始事務 使用BEGIN TRANSACTION命令顯式說明一個事務開始，它說明了對數據庫進行操作的一個單元的起始點。在事務完成之前出現任何操作錯誤和故障，都可以撤銷事務，使事務回退到這個起始點。 結束事務 結束事務有兩種情況，即成功或失?。簂成功結束事務的命令是COMMIT TRANSACTION，它的作用是提交或確認事務已經完成，所以該命令也稱作事務提交。 l失敗的事務需撤銷，撤消事務的命令是ROLLBACK TRANSACTION，即撤消在該事務中對數據庫所做的更新操作，使數據庫回退到事務的起始點。 舉

5、例：如下程序的結果？BEGIN TRANSACTIONCREATE TABLE TestTran (Cola INT PRIMARY KEY, Colb CHAR(3)INSERT INTO TestTran VALUES (1, aaa)COMMIT TRANSACTIONBEGIN TRANSACTIONINSERT INTO TestTran VALUES (2, bbb)ROLLBACK TRANSACTIONBEGIN TRANSACTIONINSERT INTO TestTran VALUES (3, ccc)COMMIT TRANSACTION事務保存點 SQL標準還支持“事務保

6、存點”技術，所謂事務保存點就是在事務的過程中插入若干標記，這樣當發現事務中有操作錯誤時，可以不撤消整個事務，只撤消部分事務，即將事務回退到某個事務保存點。 事務保存點SQL Server支持事務保存點技術，設置保存點的命令是SAVE TRANSACTION，具體格式是： SAVE TRANSACTION savepoint_name撤消部分事務或回退到事務保存點的命令也是ROLLBACK TRANSACTION，具體格式是： ROLLBACK TRANSACTION savepoint_name假設有訂票事務每個中間結點都可以當做一個保存點。北京西安成都蘭州昆明銀川呼市北京舉例：如下程序執行的

7、結果？BEGIN TRANSACTIONCREATE TABLE TestTran (Cola INT PRIMARY KEY, Colb CHAR(3)INSERT INTO TestTran VALUES (1, aaa)SAVE TRANSACTION transpoint1INSERT INTO TestTran VALUES (2, bbb)ROLLBACK TRANSACTION transpoint1INSERT INTO TestTran VALUES (3, ccc)COMMIT TRANSACTION注意：事務和程序是兩個概念。一般來講，一個事務和程序是兩個概念。一般來講，

8、一個程序中包含多個事務。一個事務可以是一程序中包含多個事務。一個事務可以是一條條SQLSQL語句語句 、一組、一組SQLSQL語句或整個程序。語句或整個程序。 隱含事務與自動提交事務的執行模式顯式事務隱式事務自動提交事務顯式事務用BEGIN TRANSACTION命令開始一個事務直到用COMMIT或ROLLBACK命令結束事務隱式事務當執行如下命令時都會自動開始一個事務lALTER，INSERT，CREATE，OPEN，DELETE，REVOKE、DROP、SELECT、FETCH、GRANT、UPDATE直到用COMMIT或ROLLBACK命令結束事務，然后又準備開始一個新的事務。自動提交事

9、務每條單獨的語句都是一個事務，一條語句執行成功則提交事務，執行失敗則撤銷事務。隱含事務與自動提交事務的執行模式 SQL標準規定事務的開始是隱含的，在發出COMMIT（提交事務）或ROLLBACK（撤消事務）命令之前，該事務將一直保持有效。一個事務被提交或撤消之后，又將自動啟動下一個新事務。 隱含事務的設置與取消設置隱含事務方式的命令是： SET IMPLICIT_TRANSACTIONS ON取消隱含事務方式的命令是： SET IMPLICIT_TRANSACTIONS OFF隱含事務與自動提交當 是 隱 含 事 務 方 式 時 ， 不 需 要 用 B E G I N TRANSACTION命

10、令顯式的啟動或開始一個事務，但需要用COMMIT或ROLLBACK命令結束事務；當是非隱含事務方式時，如果沒有用BEGIN TRANSACTION命令顯式的啟動或開始一個事務，則每條操作數據庫的語句都將作為獨立的事務被自動提交或撤消，這時候不需要、也不能執行COMMIT或ROLLBACK命令。思考題UPDATE score SET 成績=成績 + 5如果score表有1000條記錄，這條語句在完成更新時只有1條記錄違背了完整性約束，結果會怎么樣？隱含事務舉例 SET IMPLICIT_TRANSACTIONS ONINSERT INTO 倉儲.倉庫 VALUES(WH20,成都,888)COM

11、MITINSERT INTO 倉儲.倉庫 VALUES(WH21,重慶,888)ROLLBACK結果？非隱含事務與自動提交舉例 SET IMPLICIT_TRANSACTIONS OFFINSERT INTO 倉儲.倉庫 VALUES(WH21,重慶,888)ROLLBACK結果？顯式事務舉例 SET IMPLICIT_TRANSACTIONS OFFBEGIN TRANSACTIONINSERT INTO 倉儲.倉庫 VALUES(WH23,貴陽,888)ROLLBACK結果？使用TRYCATCH控制事務 BEGIN TRY BEGIN TRANSACTION - 假設事務的所有語句成功執行

12、則提交事務 COMMIT TRANSACTIONEND TRYBEGIN CATCH - TRY語句塊中出現錯誤則轉到此 - 檢測錯誤并做出處理END CATCHTRYCATCH舉例SET XACT_ABORT ONBEGIN TRY BEGIN TRANSACTION INSERT INTO 倉儲.倉庫 VALUES(WH17,成都,332) INSERT INTO 倉儲.倉庫 VALUES(WH17,貴陽,380) COMMIT TRANSACTIONEND TRYBEGIN CATCH IF (XACT_STATE() = -1 BEGIN PRINT N事務不能提交，撤銷事務！ ROL

13、LBACK TRANSACTION ENDEND CATCH并發控制干擾問題可串行性封鎖死鎖“活死鎖”隔離級別封鎖與隔離級別并發事務中的不一致問題干擾問題丟失更新問題 未提交依賴問題 不一致分析問題 幻象讀問題 丟失更新問題舉例（假設） 旅客A 通過網絡購票，要買一張15日北京南到上海虹橋的G1次高速列車的一等座車票，旅客A得到剩余票信息； 幾乎在同時，旅客B 也通過網絡購票，也要買一張15日北京南到上海虹橋的G1次高速列車的一等座車票， 用戶B從另一臺計算機查到了同樣的剩余票信息； 旅客A買了一張15日G1次7車廂5號車票，旅客A提交訂單、系統更新剩余票信息并將它存入數據庫； 這時旅客B不知

14、道旅客A已經購買了15日G1次7車廂5號車票，使旅客B也也提交了一張15日G1次7車廂5號車票，系統再次更新剩余票信息并將它存入數據庫（重復了旅客A提交訂單時已經做過的更新）。 總的效果：15日G1次7車廂5號車票賣了兩次。其原因是：允許了旅客B在過時的信息基礎上去更新數據庫，而沒有迫使他去看最新的信息。 丟失更新問題 用SQL術語描述丟失更新問題未提交依賴問題 未提交依賴問題也稱為讀“臟”（Dirty Read）數據問題，查詢一個已經被其他事務更新、但尚未提交的元組，將會引起未提交依賴問題。 “臟臟”數據數據是指其它事務未提交的尚沒有寫回數據庫中的數據。是指其它事務未提交的尚沒有寫回數據庫中

15、的數據。不一致分析問題 不一致分析問題也稱為不可重復讀問題，很多應用可能需要校驗功能，這時往往需要連續兩次或多次讀數據進行校驗和分析，結果由于其他事務的干擾，使得前后結果不一致，從而產生校驗錯誤（即不一致的分析）。 幻象讀問題 幻象讀問題與不一致分析問題有關，當事務A讀數據時，事務B在對同一個關系進行插入或刪除操作，這時事務A再讀同一條件的元組時，會發現神秘地多出了一些元組或丟失了一些元組，把這種現象稱作幻象讀。 可串行性 可串行性通?？醋魇嵌鄠€事務并發執行的正確性準則。如果多個事務并發執行和這些事務按某種順序串行執行的效果是等價的，稱作并發事務的可串行性或可串行化?？纱行缘呐袛喔鲉蝹€事務如

16、能將數據庫從一個正確狀態轉變為另一個正確狀態，則認為該事務是正確的；按任何一個串行順序依次執行多個事務也是正確的（這里的串行順序假定各個事務間彼此獨立、不交叉）； 事務的交叉執行過程是正確的，當且僅當其與串行執行過程等價，則事務是可串行化的。 可串行性描述的是事務的隔離性。并發控制并發操作破壞了事務的隔離性。并發控制就是要用正確的方式調度并發操作，使一個用戶事務的執行不受其他事務的干擾，從而避免造成數據的不一致性。并發控制的主要技術是封鎖(Locking)。實現并發控制(隔離性)的基本思路實現隔離性的基本思路當需要查詢或更新數據時，先對數據進行封鎖，以避免來自其他事務的干擾，即隔離其他事務。針

17、對不同的干擾問題可以有不同的封鎖機制。 以丟失更新問題為例，實施封鎖的基本思想是：當一個用戶對一個表或記錄進行更新時，封鎖該表或記錄，使其他用戶不能在同一時刻更新相同的表或記錄，迫使其他用戶在更新后的基礎上（而不是在更新前的基礎上）再實施另外的更新操作。 封鎖的基本思路實施封鎖以后的時間序列封鎖與事務吞吐量事務吞吐量：單位時間內可以完成的事務數量。封鎖將降低事務吞吐量。如何在保證事務隔離性的前提下提高事務吞吐量？如何在保證事務隔離性的前提下提高事務吞吐量必要的封鎖策略。根據不同的需要采取不同的封鎖策略。封鎖機制 共享封鎖 獨占封鎖 更新封鎖 共享封鎖 共享封鎖是為讀操作設置的一種封鎖，所以也稱

18、作讀封鎖，或簡稱S鎖，目的是想讀到一組不變的數據，也就是在讀數據的過程中，不允許其他用戶對該數據進行任何修改操作。這種封鎖可以保證最大的并發性，任何數量的用戶都可以同時對同樣的數據施加這種共享鎖。已經實施共享鎖的表拒絕來自其他事務的獨占封鎖和更新封鎖。 獨占封鎖 獨占封鎖也叫排他封鎖，它是為修改操作設置的一種封鎖，也稱為寫封鎖，或簡稱為X鎖，這是最嚴格的一類封鎖。當需要對表實施插入、刪除或修改操作時，應該使用獨占封鎖。已經實施獨占封鎖的表，拒絕來自其他用戶的任何封鎖，但不拒絕一般的查詢操作。 共享封鎖和獨占封鎖是基本鎖共享封鎖和共享封鎖是相容的；獨占封鎖與任何封鎖均不相容。更新封鎖 當需要對一

19、個記錄或一組記錄進行更新時（只是修改，不包括插入和刪除）使用更新封鎖，該封鎖的目的是防止其他用戶在同一時刻修改同一記錄。已經實施更新封鎖的記錄，拒絕來自其他用戶的任何封鎖，但不拒絕一般的查詢操作。 鎖的釋放 有些封鎖在執行完相應操作后就自動釋放封鎖，有些封鎖則保持到事務結束（提交或撤消）時才釋放（無論如何，所有的封鎖都會在事務結束時自動釋放）。 SQL Server中與封鎖有關的命令 SQL Server的封鎖操作是在相關語句的“WITH ()”子句中完成的，該短語可以在SELECT、INSERT、UPDATE和DELETE等語句中指定表級鎖定的方式和范圍。SQL Server中與封鎖有關的命

20、令常用的封鎖關鍵詞有：lTABLOCK：對表施行共享封鎖，在讀完數據后立刻釋放封鎖，此類封鎖可以避免讀“臟”數據，但不具有可重復讀的特性。 lHOLDLOCK：與TABLOCK一起使用，可將共享鎖保留到事務完成，而不是在讀完數據后立即釋放鎖，這樣可以保證數據的可重復獨特性。 lNOLOCK：不進行封鎖，此關鍵詞僅應用于SELECT語句，這樣可能會讀取未提交事務的數據，即有可能發生“臟”讀。 lTABLOCKX：對表實施獨占封鎖。 lUPDLOCK：對表中的指定元組實施更新封鎖；這時其他事務可以對同一表中的其他元組也實施更新封鎖，但是不允許對表實施共享封鎖和獨占封鎖。 舉例DECLARE d d

21、atetime, t char(6), s char(2), n char(10)BEGIN TRANSACTIONSELECT n=座位號 FROM R WITH (UPDLOCK)WHERE 日期 = d AND 車次 = t AND 座別 = s AND 狀態 IS NULLIF UPDATE R SET 狀態 = Y WHERE 座位號 = n AND 日期 = d AND 車次 = t AND 座別 = sCOMMIT TRANSACTIONELSEROLLBACK TRANSACTION死鎖 封鎖不當會產生死鎖產生死鎖的原因右圖示意了兩個并發事務所發生事件的序列，假設程序A為了完成

22、某個事務需要封鎖倉庫和職工兩個關系，而幾乎在同一時刻并發執行的程序B為完成另一個事務也需要封鎖職工和倉庫關系，這兩個程序正好按照如圖所示的交錯序列執行命令，結果兩個程序都為了等待對方釋放數據資源而產生死鎖。 發生死鎖兩個或多個事務等待被封鎖資源形成環路時就會發生死鎖。ABC避免死鎖避免死鎖 相同順序法 l所有的用戶程序約定都按相同的順序來封鎖表 一次封鎖法 l為了完成一個事務，一次性封鎖所需要的全部表 兩階段封鎖協議 l所有事務都必須將對數據的封鎖分為封鎖和釋放兩個階段 避免死鎖的封鎖 兩階段封鎖協議 第一階段稱為擴展階段，這一階段獲得各種類型的封鎖，但是不能釋放任何封鎖。第二階段稱為收縮階段

23、，這一階段釋放各種類型的封鎖，一旦開始釋放封鎖，則不能再申請任何類型的封鎖。 注意，兩階段封鎖協議和一次封鎖法的異同之處。一次封鎖法遵守兩階段封鎖協議；但是兩階段封鎖協議并不要求一次封鎖所有需要封鎖的數據。兩階段封鎖協議仍有可能發生死鎖。 舉例：遵守兩階段鎖協議遵守兩階段鎖協議LOCK A LOCK A LOCK B LOCK B LOCK C LOCK C UNLOCK B UNLOCK B UNLOCK A UNLOCK A UNLOCK C UNLOCK C 沒遵守兩階段鎖協議沒遵守兩階段鎖協議 LOCK A LOCK A UNLOCK A UNLOCK A LOCK B LOCK B

24、LOCK C LOCK C UNLOCK C UNLOCK C UNLOCK B UNLOCK B 發現死鎖超時法 l即一個事務在等待的時間超過了規定的時限后就認為發生了死鎖。 l這種方法非常不可靠，如果設置的等待時限長，則不能及時發現死鎖；如果設置的等待時限短，則可能會將沒有發生死鎖的事務誤判為死鎖。 發現死鎖等待圖法 l即通過有向圖判定事務是否是可串行化的，如果是則說明沒有發生死鎖，否則說明發生了死鎖。l具體思路是：用節點來表示正在運行的事務，用有向邊來表示事務之間的等待關系，如右圖所示，如果有向圖中發現回路，則說明發生了死鎖。 解決死鎖發現死鎖后解決死鎖的一般策略是：自動使“年輕”的事務

25、（即完成工作量少的事務）先退回去，然后讓“年老”的事務（即完成工作量多的事務）先執行，等“年老”的事務完成并釋放封鎖后，“年輕”的事務再重新執行。 “活鎖”沒有發生死鎖也有可能某個事務永遠處于等待狀態“活鎖”T1：事務A共享封鎖資源S,成功T2：事務B試圖獨占封鎖資源S，等待T3：事務C在A釋放封鎖S之前，共享封鎖S成功T4：事務D在C釋放封鎖S之前，共享封鎖S成功結果事務B無限期等待避免“活鎖”避免“活鎖”按隊列實施封鎖lT1：事務A共享封鎖資源SlT2：事務B試圖獨占封鎖資源S，等待lT3：事務C在A釋放封鎖S之前申請對S的共享封鎖，由于事務B已經在等待S，所以事務C排隊等待封鎖與隔離級別

26、封鎖的目的是隔離事務，保證事務之間不互相干擾；不同的封鎖機制和事務管理方式實際對應著不同的隔離級別。隔離級別可劃分為：l未提交讀l提交讀l可重復讀l可串行化未提交讀( READ UNCOMMITTED )事務隔離的最低級別，僅可保證不讀取物理損壞的數據，甚至不介意讀“臟”數據。對應封鎖的NOLOCK，即讀之前不加任何封鎖。 提交讀（READ COMMITTED） 默認級別，可以保證不讀取“臟”數據。對應TABLOCK封鎖，即讀之前加共享封鎖，讀完之后即自動釋放封鎖?？芍貜妥x（REPEATABLE READ） 可以保證讀一致性，避免不一致分析問題。 對應TABLOCK+HOLDLOCK封鎖，即讀

27、之前加共享封鎖，并將封鎖保持到事務結束?？纱谢⊿ERIALIZABLE） 事務隔離的最高級別，事務之間完全隔離；如果事務在可串行化隔離級別上運行，則可以保證任何并發重疊事務均是串行的。根據情況對應TABLOCKX或UPDLOCK封鎖，保證增、刪、改操作不受其他事務干擾。 設置隔離級別的命令 SET TRANSACTION ISOLATION LEVEL READ COMMITTED| READ UNCOMMITTED| REPEATABLE READ| SERIALIZABLE 封鎖與隔離級別 可以通過指定隔離級別或對數據資源實施封鎖達到事務隔離的目的；封鎖是實現并發操作的傳統方法（在SQL標準中沒有提及封鎖），適當的運用封鎖并保證高并發操作性能是一件非常復雜的工作，這需要用戶深入了解各種封鎖的相容性，并設計封鎖的調度策略；SQL標準中規定了事務的隔離級別，即未提交讀、提交讀、可重復讀和可串行化，隔離級別解決了并發事務可能產生的丟失更新問題、未提交依賴問題、不一致分析問題和幻象讀問題，其中為了避免丟失更新問題，事務必須運行在可重復讀或可串行化隔離級別。 用戶可以根據事務的需要設定隔離級別，結果由數據庫管理系統控制封鎖和進行并發操作調度。 封鎖與隔離級