17 交易處理與鎖定 17-1 交易的基礎 17-2 交易處理 17-3 並行控制 17-4 資料鎖定 17-5 死結問題.

Presentation on theme: "17 交易處理與鎖定 17-1 交易的基礎 17-2 交易處理 17-3 並行控制 17-4 資料鎖定 17-5 死結問題."— Presentation transcript:

1

2 17 交易處理與鎖定 17-1 交易的基礎 17-2 交易處理 17-3 並行控制 17-4 資料鎖定 17-5 死結問題

3 17-1 交易的基礎 交易簡介 交易狀態 交易的四大特性

4 交易簡介-說明 交易是一組資料庫單元操作的集合，而且這個集合是一個不可分割的邏輯單位（Logical Unit），不是全部執行完，就是通通不執行。事實上，組成交易的資料庫單元操作（Atomic Database Actions）只有兩種，如下所示： 讀取（Read）：從資料庫讀取資料。 寫入（Write）：將資料寫入資料庫。 換句話說，交易是一系列資料庫讀取和寫入操作，只不過我們將這一系列操作視為一個無法分割的邏輯單位。

5 交易簡介-執行結果 交易的執行結果只有兩種情況，不是認可交易，就是回復交易，不過，一旦認可交易，就不能再回復交易，其說明如下所示： 認可交易（Commit）：表示交易中的所有資料庫單元操作，真正將更改寫入資料庫，成為資料庫的長存資料，而且不會再取消更改。 回復交易（Rollback）：如果交易尚未認可，我們可以取消交易，也就是取消所有已執行的資料庫單元操作，將它回復到執行交易前的狀態。

6 交易狀態-種類(圖例) 資料庫管理系統執行整個交易的過程可以分成數種「交易狀態」（Transaction State），如下圖所示：

7 交易狀態-種類(說明1) 啟動狀態（Active State）：當交易開始執行時，就是進入啟動狀態的初始狀態，依序執行交易的讀取或寫入等資料庫單元操作。 部分認可交易狀態（Partially Committed State）：當交易的最後一個資料庫單元操作執行完後，也就是交易結束，就進入部分認可交易狀態。 認可交易狀態（Committed State）：在成功完成交易進入部分認可交易狀態後，還需要確認系統沒有錯誤，可以真正將資料寫入資料庫，如此可以進入認可交易狀態，表示交易造成的資料庫更改，將真正寫入資料庫，而且不會再取消更改。

8 交易狀態-種類(說明2) 失敗狀態（Failed State）：當發現交易不能繼續執行下去時，交易就進入失敗狀態，準備執行回復交易。 放棄或中止狀態（Aborted or Terminated State）：交易需要回復到交易前的狀態，在取消所有寫入資料庫單元操作影響的資料後，就進入此狀態。簡單的說，資料庫管理系統如同根本沒有執行過此交易。

9 17-1-2 交易狀態- 交易停止執行的原因1 交易成功
交易成功就是正常結束交易的執行，它是指交易的資料庫單元操作全部執行完成。以交易狀態來說，如果交易從啟動狀態開始，可以到達認可交易狀態，就表示交易成功。

10 17-1-2 交易狀態- 交易停止執行的原因2 交易失敗
交易失敗是送出放棄指令（Abort或Rollback）來結束交易的執行。以交易狀態來說，就是到達放棄或中止狀態。交易失敗分為兩種，如下所示： 放棄交易：交易本身因為條件錯誤、輸入錯誤資料或使用者操作而送出放棄指令（Abort或Rollback）來放棄交易的執行，正確的說，此時的交易是進入放棄狀態。 中止交易：因為系統負載問題或死結（Deadlock）情況，由資料庫管理系統送出放棄指令，讓交易進入中止狀態。

11 17-1-2 交易狀態- 交易停止執行的原因3 交易未完成
交易有可能因為系統錯誤、硬體錯誤或當機而停止交易的執行，因為沒有送出放棄指令，此時交易是尚未完成的中斷狀態，即只執行到一半就被迫中斷執行。 因為資料庫管理系統並不允許此情況發生，所以在重新啟動後，其回復處理（Recovery）機制會從中斷點開始，重新執行交易至交易成功或失敗來結束交易的執行。

12 17-1-3 交易的四大特性-1 資料庫系統的交易需要滿足四項基本特性，以英文字頭的縮寫稱為ACID交易，如下所示：
單元性（Atomicity）：將交易過程的所有資料庫單元操作視為同一項工作，不是全部執行完，就是通通不執行，將它視為一個不能分割的邏輯單位。 一致性（Consistency）：指當交易更改或更新資料庫的資料後，在交易之前和之後，資料庫的資料仍然需要滿足完整性限制條件，維持資料的一致性。

13 交易的四大特性-2 隔離性（Isolation）：指當執行多個交易時，雖然交易是並行執行，不過，各交易之間應該滿足獨立性。也就是說，一個交易不會影響到其他交易的執行結果，或被其他交易所干擾。 永久性（Durability）：當交易完成執行認可交易（Commit）後，其執行操作所更動的資料已經永久改變，資料庫管理系統不只需要將資料從資料庫緩衝區實際寫入儲存裝置，而且不會因任何錯誤，而導致資料的流失。

14 17-2 交易處理 17-2-1 SQL Server的交易模式 17-2-2 T-SQL語言的交易處理 17-2-3 巢狀交易
交易儲存點

15 SQL Server的交易模式-說明 SQL Server的交易處理是以連線為單位，在每一個連線都可以建立交易，SQL Server預設將每一個單獨的T-SQL指令敘述都視為是一個交易，當然，我們也可以自行組合多個T-SQL指令敘述來建立交易。 SQL Server預設的交易模式是自動認可交易（Autocommit Transactions），也就是將每一個T-SQL指令敘述都視為是一個交易。在SQL Server共提供有三種交易模式（Transactions Mode）。

16 17-2-1 SQL Server的交易模式- 自動認可交易模式
自動認可交易模式（Autocommit Transactions Mode）是SQL Server預設的交易模式，每一個單獨的T-SQL指令敘述自動都視為是一個交易，如果敘述有錯誤，就自動回復交易，否則自動認可交易。 如果單一T-SQL指令敘述會更改到多筆記錄，只需有一筆無法更改，就會自動執行回復交易，回復到執行此指令前的資料庫狀態。

17 17-2-1 SQL Server的交易模式- 明顯交易模式
明顯交易模式（Explicit Transactions Mode）就是自行使用BEGIN TRAN、COMMIT TRAN和ROLLBACK TRAIN指令組合多個T-SQL指令敘述來建立交易。

18 17-2-1 SQL Server的交易模式- 隱含交易模式
隱含交易模式（Implicit Transactions Mode）的起動需要指定IMPLICIT_TRANSACTIONS選項，如下所示： SET IMPLICIT_TRANSACTIONS ON 上述指令設定SQL Server進入隱含交易模式，表示交易開始執行，直到執行COMMIT TRAN或ROLLBACK TRAN為止，就會自動進入下一個交易。

19 17-2-2 T-SQL語言的交易處理-說明 以SQL Server明顯交易模式來說，我們需要自行組合多個T-SQL指令敘述來建立交易。
在T-SQL語言提供多個指令來進行交易處理，可以讓我們組織T-SQL指令敘述集合成為一個交易，並且控制交易的執行結果是認可交易，還是回復交易。

20 17-2-2 T-SQL語言的交易處理-情況 執行T-SQL指令需要使用明顯交易模式的情況，如下：
如果執行多個T-SQL操作指令INSERT、UPDATE和DELETE時，這些指令更新的資料是有關聯的，例如：新增訂單資料和訂單明細的項目。 如果執行T-SQL操作指令INSERT、UPDATE和DELETE後，馬上執行SELECT查詢指令，而且查詢結果的欄位資料，就是操作指令更新的資料時。 當我們將記錄從一個資料表搬移至另一個資料庫時，或當更新外來鍵參考時。

21 17-2-2 T-SQL語言的交易處理-指令 T-SQL交易處理的主要指令有三個，其說明如下表所示： 交易指令 說明
BEGIN TRAN[SACTION] 標示開始執行一個交易，它是交易的起點 COMMIT [TRAN[SACTION]] 標示交易的終點，將交易更改的資料都實際寫入資料庫，以便執行下一個交易 ROLLBACK [TRAN[SACTION]] 放棄交易且將資料庫回復到交易前的狀態

22 17-2-2 T-SQL語言的交易處理-範例1a SQL指令碼檔：Ch17_2_2.sql
SELECT * INTO 學生備份 FROM 學生 GO SELECT * INTO 班級備份 FROM 班級 BEGIN TRAN DELETE 班級備份WHERE 學號 = 'S001'

23 17-2-2 T-SQL語言的交易處理-範例1b IF @@ROWCOUNT > 5 BEGIN ROLLBACK TRAN
PRINT ‘回復刪除操作!' END ELSE DELETE 學生備份 WHERE 學號 = 'S001' COMMIT TRAN PRINT ‘認可刪除操作!'

24 巢狀交易-說明 巢狀交易（Nested Transactions）在BEGIN TRAN建立的交易中，擁有其他BEGIN TRAN建立的交易。 巢狀交易的主要目的是針對預存和觸發程序，因為有巢狀交易才可以在程序中建立交易，和在上一層交易中呼叫預存和觸發程序，只是在預存和觸發程序的交易就成為了內層交易。 在SQL

25 17-2-3 巢狀交易- COMMIT和ROLLBACK指令
COMMIT TRAN和ROLLBACK COMMIT ROLLBACK TRAN：不論在哪一層執行ROLLBACK

26 17-2-3 巢狀交易-範例1a SQL指令碼檔：Ch17_2_3.sql 使用巢狀交易刪除【學生備份】和【班級備份】資料表，如下所示：
BEGIN TRAN PRINT 'Outer Transaction = ' + CONVERT(varchar, DELETE 班級備份 PRINT 'Inner Transaction = ' +

27 17-2-3 巢狀交易-範例1b DELETE 學生備份 COMMIT TRAN
PRINT 'Commited Transaction = ' + CONVERT(varchar, ROLLBACK TRAN PRINT 'Rolled Back Transaction = ' +

28 交易儲存點-語法 交易儲存點（Save Points）可以在交易中指定交易儲存點的標籤，而在ROLLBACK TRAN回復交易時，就可以指定回復到哪一個交易儲存點，也就是只回復部分交易的內容。 在交易建立交易儲存點是使用SAVE TRAN指令，其語法如下所示： SAVE TRAN[SACTION] 交易儲存點名稱 ROLLBACK TRAN指令可以指定是回復至哪一個交易儲存點，其語法如下所示： ROLLBACK TRAN[SACTION] 交易儲存點名稱

29 17-2-4 交易儲存點-範例1a SQL指令碼檔：Ch17_2_4.sql
在交易中建立兩個交易儲存點，以便借著刪除【學生備份】資料表的記錄資料來測試如何只回復部分交易的內容，如下所示： BEGIN TRAN int DELETE 學生備份 WHERE 學號 = 'S001' SAVE TRAN 交易儲存點1 DELETE 學生備份 WHERE 學號 = 'S002' SAVE TRAN 交易儲存點2 DELETE 學生備份 WHERE 學號 = 'S003' = COUNT(*) FROM 學生備份

30 17-2-4 交易儲存點-範例1b PRINT 'Records: ' + CONVERT(varchar, @count)
ROLLBACK TRAN 交易儲存點2 = COUNT(*) FROM 學生備份 ROLLBACK TRAN 交易儲存點1 COMMIT TRAN

31 17-3 並行控制 並行控制的三種問題 並行控制機制 交易的隔離性等級

32 17-3 並行控制-說明 「並行控制」（Concurrency Control）可以讓多位使用者同時存取資料庫，也就是並行執行（Concurrent Executions）多個交易，而在各交易間彼此並不會影響，也就是不會發生一個存，一個取的存取衝突問題。 雖然資料庫的多位使用者是並行的執行交易，但是，每位使用者都會認為自已是在使用其專屬的資料庫。

33 17-3 並行控制-優點 有效提高CPU和磁碟讀寫效率：因為多個交易是並行執行，當一個交易使用CPU執行運算時，其他交易就可以使用磁碟I/O進行資料讀寫，提高系統資源的使用率。 減少平均的回應時間：因為多個交易是並行執行，交易不用等待其他長時間交易結束後才能執行，減少每一個交易的平均回應時間。

34 17-3-1 並行控制的三種問題- 遺失更新問題(說明)
遺失更新（Lost Update）問題是指交易已經更新的資料被另一個交易所覆寫，換句話說，整個交易等於白忙一場。 例如：交易A和B同時存取飛機訂位資料庫航班編號CI101的機位數，目前機位數尚餘50個，交易A希望預訂5個機位，交易B預訂4個機位。

35 17-3-1 並行控制的三種問題- 遺失更新問題(圖例)

36 17-3-1 並行控制的三種問題- 遺失更新問題(圖例說明)
N是目前尚餘的機位數，交易A和B分別在t1和t2時間點讀取N=50，在時間點t3交易A減掉訂位數5後寫回資料庫，此時的機位數尚餘 = 45個，接著時間點t4交易B在減掉訂位數4後，寫回資料庫，機位數尚餘 = 46個，最後分別在t5和t6時間點認可交易。 最後飛機訂位資料庫的機位數是46個，交易A等於沒有執行，因為交易A更新的機位數已經被交易B覆寫。

37 17-3-1 並行控制的三種問題- 未認可交易相依問題(說明)
未認可交易相依（Uncommitted Dependency）問題是指存取已經被另一個交易更新，但尚未認可交易的中間結果資料。 例如：交易A和B存取同一筆學生的記錄資料，交易A因為成績登記錯誤，需要從70分改為80分，交易B因為題目出錯，整班每位學生的成績都加5分。

38 17-3-1 並行控制的三種問題- 未認可交易相依問題(圖例)

39 17-3-1 並行控制的三種問題- 未認可交易相依問題(圖例說明)
交易A在t1和t2時間點讀取S = 70後改為S = 80，但是在交易A尚未認可交易前，時間點t3到t5交易B讀取記錄S = 80，加5分後寫入和認可交易，接著時間點t6交易A發生錯誤所以回復交易，成績改回70分。 因為交易B讀取的是尚未認可交易的中間結果資料，雖然交易B認為已完成交易，但實際是最後學生的成績不但沒有加5分，而且還原到最原始的70分。

40 17-3-1 並行控制的三種問題- 不一致分析問題(說明)
不一致分析（Inconsistent Analysis）問題也稱為「不一致取回」（Inconsistent Retrievals）問題，這是因為並行執行多個交易，造成其中一個交易讀取到資料庫中不一致的資料。 例如：交易A和B存取同一位客戶在銀行的X和Y兩個帳戶，交易前兩個帳戶餘額分別為500和300元，交易A可以計算兩個帳戶的存款總額，交易B從帳戶X轉帳150元至帳戶Y。

41 17-3-1 並行控制的三種問題- 不一致分析問題(圖例)

42 17-3-1 並行控制的三種問題- 不一致分析問題(圖例說明)
交易A在t1時間點取得X帳戶的餘額500元，然後交易A在尚未認可交易前，交易B在t2和t3時間點讀取X和Y帳戶餘額500和300元，在時間點t4到t5執行轉帳150元，時間點t6交易B認可交易，目前X和Y帳戶的餘額分別是350和450元。 接著t7時間點交易A取得Y帳戶的餘額450元，然後計算帳戶存款總額為950元，最後交易A在時間點t8認可交易。因為交易A讀取的資料有部份是來自交易B更新前的帳戶餘額（帳戶X=500），這些是資料庫中不一致資料，所以造成計算結果的存款總額成為950元，而不是800元。

43 17-3-2 並行控制機制-1 悲觀並行控制（Pessimistic Concurrency Control）
悲觀並行控制是使用鎖定（Locking）來同步交易的執行，鎖定是將交易欲處理的資料暫時設定成專屬資料，只有目前的交易允許存取，可以防止其他交易存取相同的資料，避免產生存取衝突問題。 悲觀並行控制是假設並行執行的多個交易會存取相同資料，發生存取衝突問題，所以當並行執行多個交易時，整個交易過程都會持續鎖定資料，在鎖定期間的其他交易並不能存取此資料，確保資料不會被其他交易更改，直到解除鎖定為止。

44 17-3-2 並行控制機制-2 樂觀並行控制（Optimistic Concurrency Control）
樂觀並行控制是假設資料衝突問題並不常發生，所以從鎖定改為偵測和解決存取衝突問題。多個並行交易在讀取資料並不會鎖定，只有在更改資料時，系統才會檢查是否有其他交易讀取或更改資料，如果有，就產生錯誤，當交易檢查發生錯誤，就在回復交易後，重新啟動交易來解決存取衝突問題。

45 交易的隔離性等級-說明 SQL Server支援多種並行控制機制，我們可以指定交易的隔離性等級（Isolation Level）來選擇交易使用的並行控制種類。 隔離性等級可以決定有多少個交易能夠同時執行，也就是說，它可以決定一個交易與其他交易間的隔離性（Isolation）有多高，在SQL Server可以使用更多鎖定行為來進行交易間的並行控制，以避免產生並行控制的問題。

46 交易的隔離性等級-語法 在T-SQL語言是使用SET TRANSACT ISOLATION LEVEL指令來指定交易的隔離性等級，其基本語法如下所示： SET TRANSACT ISOLATION LEVEL 隔離性等級名稱

47 17-3-3 交易的隔離性等級-等級名稱 隔離性等級名稱 說明 READ UNCOMMITTED
隔離性最低的等級，交易就算尚未執行認可交易，也允許其他交易讀取，換句話說，讀取的資料並不一定正確，而且有可能讀取到尚未認可交易的中間結果資料 SNAPSHOT 交易就算尚未執行認可交易，也允許其他交易讀取，不過讀取的是交易前的舊資料，雖然不是最新的資料，但不會造成資料庫資料的不一致 READ COMMITTED 交易一定要在執行認可交易後，才允許其他交易讀取，可以避免讀取到尚未認可交易的中間結果資料 REPEATABLE READ 交易在尚未認可交易前，不論讀取幾次的結果都相同。例如：交易A讀取資料x = 100後，交易B讀取變更相同資料x = 200後認可交易，此時如果交易A再次讀取x，x的值仍然是100，而不是交易B更改後的200 SERIALIZABLE 隔離性最高的等級，將交易使用的所有資料都進行鎖定，交易執行順序需要等到前一個交易認可交易後，才能執行下一個交易

48 17-3-3 交易的隔離性等級-範例1a SQL指令碼檔：Ch17_3_3.sql
指定【教務系統】資料庫的隔離性等級為REPEATABLE READ後，表示交易中讀取的資料，不允許其他交易來更改，建立交易來更新員工和教授資料，如下所示： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN UPDATE 員工 SET 薪水 = 65000, 保險 = 3000 WHERE 身份證字號 = 'Y '

49 17-3-3 交易的隔離性等級-範例1b IF @@ERROR = 0 BEGN UPDATE 教授 SET 職稱 = '副教授'
WHERE 教授編號 = 'I014' COMMIT TRAN ELSE ROLLBACK TRAN END

50 17-4 資料鎖定 鎖定層級 鎖定模式 鎖定模式相容性

51 17-4 資料鎖定 SQL Server可以使用鎖定（Locking）方法來處理多交易執行的並行控制，支援多種資料鎖定模式和鎖定層級來控制交易的進行。 資料鎖定是當交易A執行資料讀取（Read）或寫入（Write）的資料庫單元操作前，需要先將資料鎖定（Lock）。若同時有交易B存取相同的資料，因為資料已經被鎖定，所以交易B需要等待，直到交易A解除資料鎖定（Unlock）。

52 鎖定層級 鎖定層級（Lock Level）也稱為「鎖定顆粒度」（Lock Granularity），這是指鎖定時，鎖定資源的範圍大小，其說明如下表所示： 資源 說明 資料庫（Database） 鎖定整個資料庫 資料表（Table） 鎖定整個資料表，包含索引 範圍（Extend） 鎖定連續的8頁分頁，即範圍 分頁（Page） 鎖定分頁的資料，一分頁有8KB 鍵（Key） 鎖定建立索引的欄位或複合欄位 記錄識別碼（RID） 記錄識別碼，可以鎖定資料表中的單筆記錄

53 17-4-2 鎖定模式-種類 鎖定模式 說明 共用鎖定（Shared Lock）
使用在不變更或更新資料的讀取作業，例如：SELECT指令。共用鎖定的資料依然允許其他交易的共用鎖定，但不允許獨佔鎖定 更新鎖定（Update Lock） 使用在可更新的資源上，可以防止當多個交易在讀取、鎖定和後來可能更新資源時發生死結問題，更新鎖定和共用鎖定可以並存，但並不允許其他交易的更新或獨佔鎖定 獨佔鎖定（Exclusive Lock） 使用在資料修改動作，例如：INSERT、UPDATE或 DELETE操作指令。可以確保不對相同資源同時進行多重更新操作，獨佔鎖定的資料並不允許其他交易的任何鎖定 意圖共用鎖定（Intent Shared Lock） 準備使用共用鎖定來讀取資源中的部分內容 意圖獨佔鎖定（Intent Exclusive Lock） 準備使用獨佔鎖定來更新資源中的部分內容 共用與意圖獨佔鎖定（Shared with Intent Exclusive Lock） 準備使用共用鎖定來鎖定資源的全部內容，而且使用獨佔鎖定來更新資源中的部分內容

54 鎖定模式-鎖定模式的使用 基本上，讀取資料是使用共用鎖定；更新資料操作是使用獨佔鎖定。更新鎖定比較特殊，它是使用在更新操作第1部分的讀取階段來鎖定資源，等到第二階段更新時，更新鎖定會鎖定提昇（Lock Promotion）至獨佔鎖定，來避免產生死結問題。 意圖鎖定（Intent Lock）是SQL Server準備請求共用或獨佔鎖定前使用的鎖定。例如：使用意圖共用鎖定（Intent Shared Lock）鎖定資料表，表示它準備使用共用鎖定來鎖定資料表的分頁或記錄，可以防止其他交易請求此資料表的獨佔鎖定。

55 17-4-3 鎖定模式相容性 鎖定模式相容性是指對於同一個資源有哪幾種鎖定模式是可以並存的。 目前存在的鎖定模式 請求的鎖定模式 IS S
U IX SIX X 意圖共用鎖定(IS) 可 不可 共用鎖定(S) 更新鎖定(U) 意圖獨佔鎖定(IX) 共用與意圖獨佔鎖定(SIX) 獨佔鎖定(X)

56 17-5 死結問題 死結的基礎 指定死結的優先順序 預防死結的程式技巧

57 死結的基礎 死結是因為多個交易相互鎖定對方需要的資料，以至交易被卡死，進而導致多個交易都無法繼續執行的情況。例如：並行控制的更新遺失問題就一定會產生死結，如下圖所示：

58 指定死結的優先順序 當死結問題發生時，SQL Server會自動偵測死結，並且允許其中一個交易認可交易，但另一個交易則是強迫回復交易，和產生錯誤碼1205。 基本上，對於產生死結的兩個交易來說，系統強迫回復哪一個交易並不一定，不過，我們可以替交易設定DEADLOCK_PRIORITY選項來指定死結的優先順序，其語法如下所示： SET DEADLOCK_PRIORITY {LOW | NORMAL}

59 17-5-3 預防死結的程式技巧-1 使用較低的隔離性等級
較低的隔離性等級可以讓更多交易能夠並行執行，減少資源被相互鎖定的可能性。一般來說，預設的READ COMMITTED隔離性等級已經可以滿足大部分需求，請保留交易時間短的交易來使用更高的隔離性等級。

60 17-5-3 預防死結的程式技巧-2 不要讓交易時間太長
交易時候愈短的交易，表示鎖定資源的時間也愈短，如此可以降低相互鎖定資源而發生死結的可能性。在建立交易時，請儘可能將SELECT指令置於交易外，而且在交易過程中，不要輸出多餘訊息和要求使用者輸入資料。

61 17-5-3 預防死結的程式技巧-3 使用相同順序來更新資料
如果需要建立多個交易來更新相同資源的多個資料表，請注意！每一個交易的資料表存取順序需要相同，如此可以避免交易相互鎖定資源進而產生死結。

62 17-5-3 預防死結的程式技巧-4 取得獨佔鎖定來執行大量資料變更
如果需要更新資料表中的大量記錄（例如：超過百萬筆記錄），請不要在尖峰時段執行此操作，或儘可能以擁有資料庫獨佔權限的使用者來執行大量資料的變更。