SQL Server事務隔離級別
- 2022 年 9 月 3 日
- 筆記
- SQL Server
事務
定義
事務是作為單個邏輯工作單元執行的一系列操作。 一個邏輯工作單元必須有四個屬性，稱為原子性、一致性、隔離性和持久性 (ACID) 屬性，只有這樣才能成為一個事務。
一個事務可以包含多個操作。
事務特性
- 原子性（atomicity）
事務中的所有操作要麼全部成功，要麼全部失敗，沒有第三種狀態。
- 一致性（consistency）
事務執行前後數據都處於一個最終一致性的狀態；比如庫存扣減前後庫存總量（剩餘庫存+已扣減庫存）總是保持一致。
- 隔離性（isolation）
多事務執行情況下，事務與事務之間相互無感知，相互不影響。
- 持久性（durability）
事務一旦提交，其更改對資料庫中的數據是永久性的，即使資料庫發生故障也必須要保證事務正確執行。
事務分類
- 顯式事務
通過begin transaction標記事務開始，由commit transaction提交事務，rollback transaction回滾事務。
- 隱式事務
使用set implicit_transactions on 語句，將隱式事務模式設置為打開。該模式下不必使用 begin transaction 開啟事務，當一個事務結束後會自動啟用下一個事務，只需使用 commit transaction 提交事務或 Rollback Transaction 回滾事務即可。
- 自動提交事務
SQL Server 的默認模式，它將每條單獨的 T-SQL 語句視為一個事務。如果成功執行，則自動提交。如果錯誤，則自動回滾。相比大家也沒有遇到過update一條記錄多個欄位的時候部分欄位更新成功，部分欄位更新失敗。
事務不隔離產生的問題
- 更新丟失
多個事務更新同一條數據時導致最後更新完成的事務覆蓋掉了之前事務的更新，這就導致了前面事務的更新丟失。
- 臟讀
讀事務讀取到了寫事務還未提交的數據更改，寫事務執行過程中回滾了，導致讀事務前後讀取的數據不一致。
- 不可重複讀
在讀事務範圍內多次執行查詢的結果不一致，因為在讀取事務執行期間有寫事務讀事務讀取的數據進行了修改。
- 幻讀
在讀事務範圍內多次執行查詢的結果記錄條數不一致，因為在讀取事務執行期間有寫事務讀事務讀取的數據範圍進行了添加或刪除。
事務隔離級別
Read Uncommitted（可讀未提交）
允許事務讀取寫事務未提交的更改（也就是允許臟讀），但不允許更改寫事務正在處理的數據。
Read Committed（可讀已提交）
不允許其他事務讀取寫事務未提交的更改（不允許臟讀），但允許更改讀事務正在讀取的數據（允許不可重複讀），這也是SQL SERVER默認的事務隔離級別。
Repeatable Read（可重複讀）
不允許其他事務更改讀事務正在讀取的數據（不允許不可重複讀），但是允許新增或刪除（允許幻讀）。
Serializable（序列化）
要求事務只能一個一個執行，不能並發，但是效率地下，消耗資料庫性能，一般不使用。
其他隔離級別（拷貝的官方文檔）
讀取已提交的快照(READ_COMMITTED_SNAPSHOT)
當 READ_COMMITTED_SNAPSHOT 資料庫選項設置為 ON 時，已提交讀隔離使用行版本控制提供語句級讀取一致性。 讀取操作只需要 SCH-S 表級別的鎖，不需要頁鎖或行鎖。 也就是說，SQL Server資料庫引擎使用行版本控制來呈現每個語句，其中包含在語句開始時存在的數據的事務一致性快照。 不使用鎖來防止其他事務更新數據。 用戶定義的函數可以返回在包含 UDF 的語句開始後提交的數據。
如果 READ_COMMITTED_SNAPSHOT 資料庫選項設置為 OFF（這是默認設置），當前事務運行讀取操作時，已提交讀隔離使用共享鎖來防止其他事務修改行。 共享鎖還會阻止語句在其他事務完成之前讀取由這些事務修改的行。 兩個實現都滿足已提交讀隔離的 ISO 定義。
快照
快照隔離級別使用行版本控制來提供事務級別的讀取一致性。 讀取操作不獲取頁鎖或行鎖，只獲取 SCH-S 表鎖。 讀取其他事務修改的行時，讀取操作將檢索啟動事務時存在的行的版本。 當 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設置為 ON 時，只能對資料庫使用快照隔離。 默認情況下，用戶資料庫的此選項設置為 OFF。
參考鏈接：//www.cnblogs.com/xiongze520/p/14821536.html
//docs.microsoft.com/zh-cn/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16
