如果轉載，請注明博文來源： www.cnblogs.com/xinysu/ ，版權歸 部落格園 蘇家小蘿蔔 所有。望各位支援！
MySQL通過MVCC和鎖來實作并發控制，在4個隔離級别中，讀寫資料方式及加鎖方式有所不同，以滿足不同的業務需求。
而在MSSQL中，也是通過鎖和MVCC的行版本來實作并發控制。
每個事務中，鎖的類型、級别、加鎖、釋放的情況，由事務的隔離級别控制，在MSSQL中，有6個隔離級别，不同的隔離級别對鎖的應用不一樣。而這兩個隔離級别中，有2個應用 MVCC的機制，也就是 快照類的隔離級别：Read Commmitted Snapshot 跟 Snapshot。
1 并發控制理論
在MSSQL中，經常用到的并發控制理論是 悲觀并發控制跟樂觀并發控制。
1.1 悲觀并發控制
悲觀并發，預設在事務操作過程中，一定會有其他事務跟它争奪資源，是以在事務操作過程中，會根據不同的情況對資料添加鎖，避免操作期間其他事務對該資料的修改或讀取，保證資料的一緻性。
悲觀并發控制，由于納入了鎖機制，很大程度會影響到并發規模。主要應用于資料頻繁修改、并且復原事務的成本要大于鎖資料的成本 的系統中。
1.2 樂觀并發控制
樂觀控制，預設事務在讀取資料的時候，其他事務并沒有在操作這些資料，是以不會加鎖，直接修改資料，修改後檢視讀取資料期間是否有其他使用者也修改了資料，如果有，則復原本身的修改事務。
樂觀并發控制，應用于資料修改不頻繁、并且 復原事務成本要小于鎖資料成本 的系統中。
2 隔離級别
在每一個事務中，都指定了一個隔離級别，該隔離級别定義了這個事務跟其他事務之間的隔離程度。
在MSSQL中，有6種隔離級别，4個正常隔離級别跟2個快照隔離級别：Read UnCommitted、Read Committed、Read Commmitted (行版本)、Read Repeattable、Snapshot跟Read Serializeble。Read Commmitted (行版本)跟Snapshot 可能接觸情況比較少，不過仍會說明。
在MySQL中，預設的隔離級别是RR，而在SQL SERVER中，預設的隔離級别是RC，讀已送出。
2.1 隔離級别說明
如何設定整個資料庫的預設隔離級别？
資料不一緻的說明詳見之前博文：http://www.cnblogs.com/xinysu/p/7260227.html 中的第四章：資料不一緻情況。
下文中說S鎖，并不是全部加鎖過程（MSSQL中還是IS鎖的申請）。
- Read UnCommitted
- 簡稱 RU，讀未送出記錄，始終是讀最新記錄
- 可能存在髒讀、不可重複讀、幻讀等問題
- 讀的過程不加S鎖，等同于 SELECT * FROM tbname with(nolock)
- 簡稱 RC ，讀已送出記錄
- 可能存在不可重複讀、幻讀等問題
- 讀的過程加 S鎖，無論事務是否結束，SELECT 語句一旦結束，立馬釋放S鎖，不會等到事務結束才釋放鎖，遵循的是 Strict 2-PL
- 簡稱 RCSI
- 應用MVCC原理，版本讀，讀已送出記錄，但是讀取到的不一定是最新的記錄
- 同個事務中，讀取資料都是同一個版本
- 不存在髒讀、不可重複讀問題，可能存在幻讀問題
- 行版本控制隔離級别 中的版本資料，不存在與資料庫本身，而是存在 tempdb ，下文會較長的描述這一隔離級别
- 簡稱 RR ，可重複讀記錄
- 可能存在幻讀等問題
- 讀的過程加S鎖，直到事務結束，才釋放S鎖，遵循的是 Stong Strict 2-PL
- 簡稱 SI
- 下文會較長的描述這一隔離級别
- 簡稱 RS，序列化讀記錄
- 不存在 髒讀、不可重複讀、幻讀等問題
- 讀的過程中除了添加S鎖，還添加範圍鎖；修改資料的過程中，除了添加 X 鎖，也會添加範圍鎖，避免在符合條件的資料在操作過程中，有其他符合條件的資料INSERT進來
- 并發度最差，除非明确業務需求及性能影響才使用，曾經遇到過某個短信業務的架構預設使用這個隔離級别，上線後爆發死鎖上K個，馬上分析緊急修複....
2.2 Read Commmitted Snapshot Isolation 與 Snapshot Isolation
Read Commmitted Snapshot Isolation 使用行版本控制語句級的快照，在事務中當資料發生修改或者删除時，調用寫入複制機制，保證寫入的行資料的舊版本滿足事務操作前的一緻性。 RCSI 保證的是語句級的 讀一緻性。
Snapshot Isolation 使用行版本控制事務級的快照，當事務開始的時候，調用寫入複制機制。 SI 保證的是事務級 的讀取一緻性。
如何管理行版本資訊呢？
兩者的行版本的資訊均存儲在tempdb資料庫内，并非存儲在本身的資料庫，這就要求tempdb要有足夠的空間存儲版本資訊，如果tempdb空間不足，則行版本寫入失敗，造成該隔離級别無法正常使用。
存儲引擎對使用 RCSI 或者 SI 隔離級别的事務，在 SI事務開始的時候，配置設定一個事務序列号 XLN，每次配置設定遞增1，以此實作事務級的一緻性，這裡注意 RCSI的 事務序列号 并不是一個事務一個序列号，而是事務内每條SQL一個事務序列号，以此來實作語句級别的快照。這兩個隔離級别下，需要維護所有執行過資料修改的邏輯副本（即行版本），這些邏輯副本存儲在tempdb内，每個邏輯副本（行版本）都有标記本次的事務的事務序列号XLN。即 最新的行值存儲在目前的資料庫中，而曆史行版本資訊包括最新版本，存儲在tempdb中。這裡注意一下，事務内的修改資料寫行版本資訊的時候，先寫入到緩存池中，在重新整理到tempdb檔案，避免性能造成太大的影響。
這個時候，可能會問？那豈不是tempdb要存儲非常多的曆史版本資料，有沒有删除機制呢？
這個是有的，一方面，行版本資訊不會即時删除，因為要保證基于行版本控制隔離級别下運作的事務要求，保證并行的事務如果正在使用tempdb的行版本資訊 不會受到影響。另一方面，資料庫的存儲引擎 會跟蹤最早可用的事務序列号，然後定期删除比序列号更小的 XLN的所有行版本。
如何讀取行版本資訊呢？
兩個快照隔離級别下的 的事務讀資料的時候，不會擷取正在讀取資料上的共享鎖，是以不會堵塞正在修改的事務，由于減少了鎖的申請及數量，可以提供其DB并發能力。不過會擷取所在表格的架構鎖，如果表格正在發現架構修改（如列增加修改等），則會被堵塞。
如何讀取合适的行版本，RCSI 跟 SI 之間是有差別的。
RCSI：每次啟動語句時，送出所有資料，同時讀取tempdb中的最新事務序列，這使 RCSI 下事務内的每個語句 都可以檢視每個語句啟動時存在的最新資料的快照，也就是 事務内多個SQL查詢間隙中有其他事務修改了資料，那麼同個事務的多次相同SQL查詢結果就會出現不一緻的情況。
SI：每次啟動事務時，送出所有資料，讀取 最接近但低于 本身的 快照事務序列号，也就是 事務内的多個SQL 查詢，讀到的資料都是同一個版本，即使多次查詢間隙有其他事務修改資料，讀到的結果也是一緻的。
如何修改行版本資訊呢 ？
在使用 RCSI 事務中，使用阻塞性掃描（其中讀取資料值時将在資料行上采用更新鎖（U 鎖）完成選擇要更新的行，滿足條件的行記錄将更新更新鎖到排它鎖，注意，這裡掃描的不是tempdb裡邊的行版本資訊，而是實際資料庫裡邊的最新行記錄，修改資料的機制跟 RC 相同。 如果資料行不符合更新條件，則在該行上将釋放更新鎖，同時鎖定下一行并對其進行掃描。持有鎖之後，則進行資料更新，事務結束後，釋放鎖。
在使用 SI 事務中，對資料修改采用樂觀方法：使用行版本的資料，進行資料修改，直到資料修改完成是，才擷取實際資料上的鎖， 當資料行符合更新标準時，則送出修改的資料行。 如果資料行已在快照事務以外修改，則将出現更新沖突，同時快照事務也将終止。 更新沖突由資料庫引擎處理，無法禁用更新沖突檢測。
從簡單的SQL來分析，WHERE條件均為主鍵（僅為個人測試推測）：
- 同個事務，多次 SELECT * FROM tbname WHERE id=2
- RCSI，在同個事務中，每個SQL啟動的時候，送出資料到tempdb表格（個人推測，應該是會配置設定一個類似hash字元串之類的，如果同個事務中的多次查詢結果一緻，應該不用在每個SQL開始的時候，重複送出行版本到tempdb），從tempdb中讀取最新版本資訊，如果tempdb沒有版本資訊，則從 資料庫中讀取，并把讀取到的記錄存儲在 tempdb。會存在同個事務中，多次讀取資料結果不一緻的情況。
- SI，在同個事務中，同個事務内的相同SQL 從tempdb中讀取距離目前事務最新的版本，整個事務内部的SQL都是用這個版本資料，如果tempdb沒有版本資訊，則從 資料庫中讀取，并把讀取到的記錄存儲在 tempdb。同個事務中，不會存在 多次讀取資料結果不一緻的情況。
- UPDATE tbname SET colname='xinysu' WHERE id=18
- RCSI，直接讀取資料庫中的資料，根據主鍵加上X鎖，更新資料，這個操作跟 RC 隔離級别是一樣的。
- SI，讀取 行版本 資料，在行版本上選擇需要更新的行，修改成功後把資料 修改到實際的資料庫中去，如果 實際資料庫中的資料在這段操作期間已被其他事務修改了數值，則會出現更新沖突，該事務将報錯停止。即，SI 在 UPDATE 的時候，有更新沖突檢測。
- 為啥要先在行版本上更新，最後在更新到實際資料上？
- 假設一個UPDATE運作需要3s，但是隻更新了1條行記錄，如果直接在實際資料上更新，則需要鎖定掃描記錄3s，最後更新，中間會堵塞到其他事務對該資料的查詢，但是如果在行版本上更新，則不需要鎖住 實際資料，最後更新1行記錄的時候，非常快，避免長時間的堵塞，提高并發能力。
| 屬性 | 使用行版本控制的已送出讀隔離級别 | 快照隔離級别 |
| 資料庫級選項啟動 | READ_COMMITTED_SNAPSHOT | ALLOW_SNAPSHOT_ISOLATION |
| 事務設定 | 使用預設的已送出讀隔離級别，或運作 SET TRANSACTION ISOLATION LEVEL 語句來指定 READ COMMITTED 隔離級别 | SET TRANSACTION ISOLATION LEVEL 來在事務啟動前指定 SNAPSHOT 隔離級别 |
| 行版本處理 | 在每條語句啟動前送出的所有資料。 | 在每個事務啟動前送出的所有資料。 |
| 更新處理 | 從行版本恢複到實際的資料，以選擇要更新的行并使用選擇的資料行上的更新鎖。 擷取要修改的實際資料行上的排他鎖。 沒有更新沖突檢測。 | 使用行版本選擇要更新的行。 嘗試擷取要修改的實際資料行上的排他鎖，如果資料已被其他事務修改，則出現更新沖突，同時快照事務也将終止。 |
| 更新沖突檢測 | 無 | 內建支援。 無法禁用。 |
3 隔離級别測試
檢視目前會話的資料庫隔離級别：DBCC USEROPTIONS ，檢視[set options] = 'isolation level'，即可檢視目前事務的隔離級别。
資料不一緻的說明詳見之前博文：http://www.cnblogs.com/xinysu/p/7260227.html 中的第四章：資料不一緻情況。
2-PL鎖申請釋放的說明詳見之前博文：http://www.cnblogs.com/xinysu/p/7260227.html 中的第3章：資料不一緻情況。
設定資料庫隔離級别：
- RU，事務開始的時候，設定 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
- RC，事務開始的時候，設定 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
- RCSI，整個資料庫級設定 READ_COMMITTED_SNAPSHOT 為ON，注意，設定的這個的時候需要擷取資料庫的獨占權，也就是目前不允許有使用者線程連接配接資料庫，否者這個設定SQL會一直處于堵塞情況。如果目前資料庫的預設隔離級别是 RC，則設定後，預設為RCSI，否者，需要在事務開始的時候，設定 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
- 資料庫設定：目前資料庫下，執行 ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON
- 事務設定：SET TRANSACTION ISOLATION LEVEL READ COMMITTED
- RR，事務開始的時候，設定 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
- RS，事務開始的時候，設定 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
- SI，整個資料庫級設定 ALLOW_SNAPSHOT_ISOLATION 為ON，同時設定事務的隔離級别為 SNAPSHOT。注意，這裡的 ALLOW_SNAPSHOT_ISOLATION 設定也是需要擷取資料的獨占鎖。
- 資料庫設定：目前資料庫下，執行 ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON
- 事務設定：SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
測試過程中，分為3個表格：無索引、有索引、有唯一索引。
CREATE TABLE tb_no_index ( id int primary key not null identity(1,1), age int not null, name varchar(100) );
CREATE TABLE tb_index ( id int primary key not null identity(1,1), age int not null, name varchar(100) );
CREATE TABLE tb_unique_index ( id int primary key not null identity(1,1), age int not null,name varchar(100) );
CREATE INDEX IX_age ON tb_index(age)
CREATE INDEX IX_unique_age ON tb_index(age)
INSERT INTO tb_no_index(age) values(2),(9),(21),(4),(7),(25);
INSERT INTO tb_index(age) values(2),(9),(21),(4),(7),(25);
INSERT INTO tb_unique_index(age) values(2),(9),(21),(4),(7),(25); 3.1 Read Uncommitted
- 資料不一緻情況測試截圖
-
MSSQL-并發控制-2-Isolation - RU測試結論
- 在RU隔離級别下
- 不會出現更新丢失情況（鎖機制），但是會出現 髒讀、不可重複讀及幻讀的情況。
- 讀不加行鎖，可以讀未送出資料
3.2 Read Committed
- 資料不一緻情況測試截圖
-
MSSQL-并發控制-2-Isolation - 讀情況測試
-
MSSQL-并發控制-2-Isolation
-
- RC測試結論
- 在RC隔離級别下
- 不會出現更新丢失情況（鎖機制）、髒讀現象，但是會出現 不可重複讀及幻讀的情況
- 讀需要申請鎖，故不會出現髒讀情況
- 遵循 強2-PL模式，事務内的讀鎖讀完即刻釋放，寫鎖等到事務送出的時候才釋放。
3.3 Read Commit Snapshot Isolation
- 測試環境設定
- 實作設定資料庫隔離級别為：
MSSQL-并發控制-2-Isolation - 檢查目前會話的預設隔離級别：
MSSQL-并發控制-2-Isolation -
-
MSSQL-并發控制-2-Isolation
-
- 更新沖突測試
-
MSSQL-并發控制-2-Isolation
-
- RCSI 測試結論
- 讀不加鎖，但申請表格的架構鎖，讀行版本資料
- 不存在丢失更新、髒讀情況，但是存在不可重複讀及幻讀情況
- 沒有更新沖突檢測，RCSI跟RC的更新處理方式一樣
3.4 Read Reaptable
-
MSSQL-并發控制-2-Isolation - RR測試結論
- 讀加S鎖，事務結束後才釋放S鎖
- 不存在丢失更新、髒讀及不可重複讀情況，但是存在幻讀情況
3.5 Read Serializable
-
-
MSSQL-并發控制-2-Isolation
-
- RS 測試結論
- 增加了範圍鎖
- 不存在丢失更新、髒讀、不可重複讀、幻讀情況
- 并發能力最差
3.6 Snapshot Isolation
-
-
MSSQL-并發控制-2-Isolation
-
-
MSSQL-并發控制-2-Isolation - SI 測試結論
- 不存在 丢失更新、髒讀、幻讀等資料不一緻情況
- 讀不加鎖，為讀行版本資料
- 具有沖突監測，無法禁用，如果使用這個隔離級别，程式要做更新沖突的復原處理
4 總結
| 隔離級别 | 說明 | 髒讀 | 不可重複讀 | 幻影 | 并發控制模型 |
| 未送出讀 | YES | 悲觀 | |||
| 已送出讀 | NO | ||||
| 已送出讀（快照） | 樂觀 | ||||
| 可重複讀 | |||||
| 快照 | |||||
| 可串行化 |
如果轉載，請注明博文來源： www.cnblogs.com/xinysu/ ，版權歸 部落格園 蘇家小蘿蔔 所有。望各位支援！