第 10 章補充 交易與鎖定 (節錄自實習課課本第 20 章)

20-2 進行交易的 3 種模式 交易是以連線 (Connection) 為單位, 每個連線都可以有自己的交易。在同一個連線中, 當交易開始時, 所有後續執行的 SQL 敘述都是該交易的成員, 直到交易結束為止。事實上, SQL Server 提供了 3 種進行交易的模式： 外顯交易 (Explicit transactions) 自動認可交易 (Auto-commit transactions) 隱含交易 (Implicit transactions)

外顯交易 (Explicit transactions) 此方式就是我們前面所介紹的, 以 BEGIN TRAN 來開始交易, 而以 COMMIT TRAN 或 ROLLBACK TRAN 等敘述來結束交易。可用來啟動或結束交易的敘述有： WORK 寫不寫都可以

外顯交易 (Explicit transactions) 請注意, 有些無法 ROLLBACK 的敘述不允許使用在交易中, 包括： 此外, 在交易中也不可使用 sp_dboption 預存程序來設定資料庫選項, 或是用任何系統預存程序來更改 master 資料庫的內容。

自動認可交易 (Autocommit transactions) 這是系統預設的交易方式。當我們未明確指定要進行外顯交易時, 每個存取資料的敘述即為一個交易, 因此其執行結果也是只有完全成功 或完全取消 2 種。

隱含交易 (Implicit transactions) 當我們執行 “SET IMPLICIT_TRANSACTIONS ON” 敘述後, 系統即進入隱含交易模式：亦即交易會自動開始, 直到執行 COMMIT TRAN 或 ROLLBACK TRAN 為止。而在此交易結束後, 當執行到下一個存取資料的敘述時, 又會自動開始交易, 直到再次執行結束交易的敘述為止；如此周而復始, 循環不息。由於交易是以連線為單位, 因此您所做的隱含交易設定也只限於目前的連線, 而不會影響到其他連線。

20-4 交易儲存點的設定與回復 有時在交易中發生錯誤時, 我們會希望只要回復一小部份的操作即可, 然後在程式中改用其他方法來完成此項交易。此時即可使用 SAVE TRAN[SACTION] 來設置『交易儲存點』, 然後在必要時使用 ROLLBACK 來回復到所儲存的位置, 而不會中斷交易。

交易儲存點的設定與回復 設定

ROLLBACK TRAN 之後的交易名稱只能是由 SAVE TRAN 或最外層的 BEGIN TRAN 所宣告的交易名稱 交易儲存點的設定與回復 ROLLBACK TRAN 之後的交易名稱只能是由 SAVE TRAN 或最外層的 BEGIN TRAN 所宣告的交易名稱

20-6 交易的隔離等級 在交易的 4 大特性 (ACID) 中, 有一項『隔離性』(Isolation) 是說明交易中所使用的資料, 必須適度地與其他同時進行的交易做隔離。 由於同一時間內可能有許多的交易在存取資料, 因此每個交易在存取資料時必須先將之鎖定, 以免受到其他交易的干擾。隔離等級主要是用來設定交易在『讀取』資料時的隔離狀態 (在修改資料時則一定要做完整鎖定, 因此不必設等級)。 然而, 將資料鎖定的副作用, 就是其他要使用此資料的交易必須排隊等待, 而降低了資料的『並行性』(Concurrency, 就是多個交易可同時進行的特性)。 更嚴重者, 甚至發生每個交易都鎖定了一些資料, 而又在等待一些被其他交易鎖住的資料, 如此就造成了所謂的『死結』(Dead Lock)。

交易的隔離等級 其隔離性由低到高共分為 5 等： Read uncommitted：完全沒有隔離效果, 即使要讀取的資料已被其他交易使用且尚未 COMMIT 也沒關係。因此, 讀取到的資料隨時都可能被別人更改或刪除。 Snapshot：這是 SQL Server 2005 新增加的等級。 在交易進行前先建立資料快照 (Snapshot), 其間若有別的交易要存取該資料, 則會直接傳回 Shapshot的資料 (即原本的資料), 待資料 COMMIT 完成才會將 Snapshot 卸離, 隔離等級較 Read uncommitted 稍高。雖然從 Snapshot 得到的資料很可能不是最新的資料, 但至少資料是正確的, 並不會像 Read uncommitted, 可能得到錯誤資料。

交易的隔離等級 Read committed：不允許讀取尚未 COMMIT 的資料, 因為該資料被更動的機率很大。不過, 在讀取完資料後就和 Read uncommitted 一樣, 不會在乎該資料是否還會被別人更改。因此每次讀取到的資料可能會不相同。 Repeatable read：在交易中所讀取到的資料將不允許別人更改或刪除, 以保證在交易中每次都可以讀取到相同的內容。但別人仍然可以在該資料表中新增記錄。 Serializable：此等級會將要使用的資料表全部鎖定, 不允許別人來修改、刪除、或新增資料。由於必須等交易完成後, 其他交易才能使用這些資料表, 因此 Serializable 的並行性最低, 要使用相同資料的交易必須一個個循序地進行。

交易的隔離等級 在下表中, 我們針對上述 5 種不同程度的交易需求來做比較： SQL Server 預設

交易的隔離等級 更改隔離等級的語法如下： 底下的程式先建立一個預存程序, 然後用它來讀取二家 更改隔離等級後, 其效果將持續到該連線結束為止。 底下的程式先建立一個預存程序, 然後用它來讀取二家 貿易公司的產品平均差價, 接著修改二家公司的產品價格, 使其平均價格相同；由於我們希望在交易中所讀取的資料 不被其他人員修改, 但新增資料則無妨, 因此使用 Repeatable read 隔離等級：

交易的隔離等級範例

交易的隔離等級

20-7 資料鎖定 鎖定 (Lock) 是將指定的資料暫時鎖起來供我們使用, 以防止該資料被別人修改或讀取。 SQL Server 會自動且適時地幫我們處理鎖定資料的工作, 例如在進行交易的過程中, 所有被修改的資料會自動鎖定, 以確保萬一失敗而必須回復時, 不會受到其他使用者的干擾。 使用者自定鎖定語法：在 SQL 敘述後加 WITH [SHARED|EXCLUSIVE|…] LOCK 例如： SELECT * FROM PRODUCT WITH SHARED LOCK

資料鎖定的種類 鎖定除了有不同的對象外, 還可以有 5 種不同的方法： 獨佔式鎖定 (Exclusive Lock)：Exclusive 鎖定可禁止其他交易對資料做存取或鎖定操作。假設當交易 A 對資料提出獨佔式鎖定, 那麼交易 B 對相同資料提出的任何鎖定都會遭到拒絕。 共用式鎖定 (Shared Lock)：Shared 鎖定可將資料設成唯讀的, 並禁止其他交易對該資料做 Exclusive 鎖定, 但卻允許其他交易對資料再做 Shared 鎖定。也就是說, 資料可以同時被許多的交易做 Shared 鎖定並讀取內容, 但不允許做 Exclusive 鎖定或更改內容。 更新式鎖定 (Update Lock)： Update 鎖定可以和 Shared 鎖定共存, 但禁止其他的 Update 鎖定或 Exclusive 鎖定。其實 Update 鎖定的特性和 Shared 鎖定完全一樣 (資料只能唯讀), 但 Update 鎖定在需要更改資料時, 可以自動升級為 Exclusive 鎖定並進行更改, 當然此前題是當時已沒有其他的 Shared 鎖定存在。

資料鎖定的種類 例如有 2 個交易同時對資料做了 Shared 鎖定, 而且都想升級為 Exclusive 鎖定以更改資料, 但由於必須等所有其他的 Shared 鎖定釋放後才能升級, 因此這 2 個交易就一直佔著 Shared 鎖定並癡癡地等待升級, 而造成了死結。要避免這樣的問題, 在讀取資料但稍後能可會更改內容時, 就應該使用 Update 鎖定。 由於每次只能有一個 Update 鎖定存在, 所以一旦成功地鎖定了, 就等於拿到了升級的保證書, 只要耐心等待其他 Shared 鎖定都釋放後, 即可升級為 Exclusive 鎖定並更改資料。

資料鎖定的種類 意圖式鎖定 (Intent Lock)： 表示只想要 (或已經) 鎖定物件中某部份的底層資源。例如意圖式鎖定資料表時, 則表示我們想要 (或已經) 鎖定資料表中的某些資料頁或記錄, 但不是鎖定整個資料表。意圖式鎖定又可分為 3 類：

實務上比課本的理論還複雜，本頁了解就好，不列期末考範圍 各類鎖定的共存性 實務上比課本的理論還複雜，本頁了解就好，不列期末考範圍

20-8 鎖定的死結問題 我們不需要擔心死結的問題, 因為 SQL Server 會定時偵測是否有死結發生。 如果真有死結發生了, 則 SQL Server 會在死結的參與者之中找一個犧牲者, 強迫將其 ROLLBACK 並傳回編號 1205 的錯誤訊息, 然後釋放其鎖定的資源, 以供其他參與者繼續完成交易。

鎖定的死結問題 至於誰會被挑選出來當犧牲者則不一定, 但如果某些交易並不是很重要或很緊急, 那麼我們倒可以將其設定為優先被選的犧牲者, 方法是用 SET 來設定： 當 DEADLOCK PRIORITY 設為 LOW 時, 就表示目前連線中的交易都會優先被選為死結的犧牲者。若再設為 NORMAL 則可取消該連線的優先設定。

避免死結發生的技巧 雖然 SQL Server 會自動偵測並處理死結, 但由於死結會浪費相當多的系統資源, 因此我們應儘量避免。以下是幾個在撰寫程式時的技巧： 使用相同的順序來存取資料：如果每個要存取 A、B、C 三個資料表的交易, 都是以 A→B→C 的順序來存取, 那麼就不會發生死結了, 因為只有第一個鎖定 A 的人才能去鎖定 B, 然後才能鎖定 C, 因此不會有交互 Block 的狀況發生。 儘量縮短交易的時間：時間越短, 佔用資源的時間也越短, 而發生死結的機率自然也就減少了。因此, 整個交易最好能在一個批次中完成 (以減少網路傳輸的次數), 並且在交易中不要與使用者進行溝通 (例如顯示訊息或等待使用者輸入資料等)。 儘量使用較低的隔離等級：較低隔離等級的資料鎖定可以供較多人同時讀取, 因此較不易發生死結。

避免死結發生的技巧