第 18 章 交易與鎖定

本章重點 18-1 交易簡介 18-2 進行交易的 3 種模式 18-3 巢狀交易與 @@TRANCOUNT 18-4 交易儲存點的設定與回復 18-5 分散式交易 18-6 交易的隔離等級 18-7 資料鎖定 18-8 鎖定的死結問題

18-1 交易簡介

交易簡介

交易簡介

交易簡介 交易通常是由 BEGIN TRAN 敘述開始, 一直執行到 COMMIT TRAN 或 ROLLBACK TRAN 敘述時才結束 (指令中的 TRAN 也可改寫為TRANSACTION, 意義相同)。 當執行完交易中的最後一項資料庫操作後, 若沒有任何錯誤, 我們可用 COMMIT TRAN 表示確認交易。

交易簡介 每執行完一項資料庫的操作後, 要立即檢查@@ERROR 及 @@ROWCOUNT, 否則再執行下一項資料庫操作時, 這二個系統變數將會被新的值取代。 無論是執行 COMMIT TRAN 或 ROLLBACK TRAN 而結束交易後, 如果後面還有其他未執行的敘述, 則還會繼續執行這些敘述, 直到批次結束為止。

交易簡介

交易簡介 SQL Server 自動 ROLLBACK 的時機 ROLLBACK 的原理 交易的 4 大特性

SQL Server 自動 ROLLBACK 的時機 當 SQL Server 因停電、當機、或使用者關機等因素而突然結束時, 那麼在重新啟動時會自動ROLLBACK 所有未確認 (COMMIT) 的交易。 如果在交易途中因網路問題、前端程式當機、使用者登出等因素, 而造成連線中斷, 那麼 SQL Server 也會自動 ROLLBACK 該連線正在進行中但尚未確認 (COMMIT) 的交易。

SQL Server 自動 ROLLBACK 的時機 當交易中發生嚴重錯誤而使交易所屬的批次被中止時, SQL Server 會自動 ROLLBACK 尚未確認(COMMIT) 的相關交易。 如果執行 “SET XACT_ABORT ON” 敘述, 則當有任何執行錯誤 (Runtime error) 發生時, 例如違反某項資料表的條件約束, 就會終止批次並自動回復交易。此選項預設為 OFF。

ROLLBACK 的原理 ROLLBACK 的原理, 其實就是利用一個暫時的交易記錄檔來回復異動資料。在交易開始時, SQL Server 會將要更改的相關資料一一鎖定並進行更改, 同時也會建立一個暫時的交易記錄檔, 來存放交易中更改資料的過程及內容。

交易的 4 大特性 單元性 (Atomicity)：整個交易中的敘述會視為一個執行單元, 要就全部成功,不然就全部取消。 一致性 (Consistency) ：在交易完成後,資料庫的內容必須全部更新妥當。 隔離性 (Isolation)：在交易中所使用到的資料, 必須與其他同時在進行的交易做適度隔離。 永久性 (Durability) ：交易一旦確認之後,其所做的資料修改將視為永久性的,無法再用ROLLBACK 回復了。

18-2 進行交易的 3 種模式 外顯交易 (Explicit transactions)：以 BEGIN TRAN 來開始交易, 而以 COMMIT TRAN 或 ROLLBACK TRAN 等敘述來結束交易。

進行交易的 3 種模式

進行交易的 3 種模式 自動認可交易 (Autocommit transactions)：有許多 SQL 敘述在執行時都會自動進行交易,即稱為自動認可交易。

會影響到資料庫內容的敘述

進行交易的 3 種模式 隱含交易 (Implicit transactions)： 當我們執行 “SET IMPLICIT_TRANSACTIONS ON” 敘述後, 只要執行到會影響資料庫內容的敘述 (如上表), 系統即進入隱含交易模式：

18-3 巢狀交易與 @@TRANCOUNT

巢狀交易與 @@TRANCOUNT

巢狀交易與 @@TRANCOUNT

巢狀交易與 @@TRANCOUNT

巢狀交易與 @@TRANCOUNT 交易計數：@@TRANCOUNT

巢狀交易與 @@TRANCOUNT

巢狀交易與 @@TRANCOUNT

18-4 交易儲存點的設定與回復

交易儲存點的設定與回復

18-5 分散式交易

分散式交易

分散式交易 MSDTC 預設並沒有開啟『網路 DTC 存取』的功能, 因此透過網路執行分散式交易時, 可能會顯示"協力電腦異動管理員已經停用了對遠端/網路異動的支援。"

分散式交易

分散式交易

分散式交易 如果有使用防火牆, 那麼還要設定允許 MSDTC 程式通過防火牆。 底下以 Windows 內建的防火牆為例：

分散式交易

分散式交易 使用分散式交易

分散式交易

分散式交易 分散式交易的運作流程 本地 (使用中的) SQL Server 會執行交易中的敘述, 並將對外的查詢或預存程序送至遠端伺服器處理。另外, 也會將相關的遠端伺服器名單送至MSDTC。 當執行到交易中的 COMMIT 或 ROLLBACK 敘述時, 則會將控制權交給 MSDTC 做後續的處理。

分散式交易 如果是要 ROLLBACK, 則 MSDTC 會通知相關伺服器進行 ROLLBACK。若是要 COMMIT, 則會進入『2 階段的確認』(Two-phase commit, 2PC)： 準備階段: MSDTC 送出準備確認的訊息給所有參與的伺服器, 然後各伺服器依各自的執行狀況傳回成功或失敗訊息給 MSDTC。 確認階段: 只要 MSDTC 收到了任何一個伺服器傳來的失敗訊息, 便會通知所有的伺服器都進行 ROLLBACK, 並將此訊息通知前端應用程式。

18-6 交易的隔離等級 Read uncommitted：完全沒有隔離效果, 即使要讀取的資料已被其他交易使用且尚未 COMMIT 也沒關係。 Read committed：不允許讀取尚未 COMMIT 的資料, 因此該資料後來被更動的機率就比較小,而且也不會讀取到交易尚未完成的資料。 Repeatable read：在交易中所讀取到的資料將不允許別人更改或刪除, 以保證在交易中每次都可以讀取到相同的內容。

18-6 交易的隔離等級 Snapshot：在交易進行前會先建立資料快照 (Snapshot),而在交易期間所讀取的資料則均來自快照,因此即使實際資料又被別人異動過 (新增/修改/刪除),也不影響該交易中讀取資料的一致性。 Serializable：此等級會將要使用的資料表全部鎖定, 不允許別人來修改、刪除、或新增資料。 40

交易的隔離等級

交易的隔離等級

交易的隔離等級

交易的隔離等級

交易的隔離等級

18-7 資料鎖定 樂觀與悲觀的並行控制 資料鎖定的種類 各類鎖定的共存性

樂觀與悲觀的並行控制 樂觀的並行控制 (Optimistic Concurrency) 悲觀的並行控制 (Pessimistic Concurrency) 當READ_COMMITTED_SNAPSHOT 為OFF 時, Read committed 會使用共用式鎖定(後詳) 的方式, 來確保不會讀到交易未完成的資料 若READ_COMMITTED_SNAPSHOT 設為ON, 此時Read committed 會改用類似快照的方式保存資料, 而不會鎖定資料, 因此算是樂觀的並行控制

資料鎖定的種類 鎖定的對象

資料鎖定的種類 鎖定的方法 獨佔式鎖定 (Exclusive Lock) 共用式鎖定 (Shared Lock) 更新式鎖定 (Update Lock)

資料鎖定的種類 意圖式鎖定

各類鎖定的共存性

18-8 鎖定的死結問題

鎖定的死結問題 避免死結發生的技巧 使用相同的順序來存取資料 儘量縮短交易的時間 儘量使用較低的隔離等級