Sql Server系列：事務完整性

1、事務的基本概念

　　事務是一系列的工作群組成的邏輯工作單元，這個邏輯工作單元中的所有任務必須作為一個整體要麼全部完成要麼全部失敗。

　　在SQL Server中，不管是否顯式地使用begin transaction標記了事務的開始，每個DDL操作都是一個事務。

　　要把多條命令封裝在一個事務中，只需要使用兩個標記來圈定整個事務的範圍：一個標記於事務開始處，而另一個則位於事務完成處，也就是把事務對資料的修改提交到磁碟的地方。如果封裝在事務內部的代碼檢測到錯誤發生，可以復原或撤銷整個事務。

　　◊ begin transaction

　　◊ commit transaction

　　◊ rollback transaction

BEGIN TRANSACTION    INSERT INTO Product(ProductNo,ProductName) VALUES ('1001','ProductA')    IF @@error <> 0        BEGIN            ROLLBACK TRANSACTION            RAISERROR('error',16,1)            RETURN        END    INSERT INTO Product(ProductNo,ProductName) VALUES ('1002','ProductB')    IF @@error <> 0        BEGIN            ROLLBACK TRANSACTION            RAISERROR('error',16,1)            RETURN        ENDCOMMIT TRANSACTION

 1.1、事務完整性

　　事務完整性使用ACID特性來衡量事務的品質。違反事務完整性的問題有3類：髒讀（dirty read）、不可重複讀取（nonrepeatable read）、幻影行（phantom rows）。要解決這3個問題，需要在事務之間使用不同的完整性或者隔離性層級。

　　1>、ACID屬性

　　一個資料庫產品的品質是通過它所提供的交易處理機制對ACID特性的支援程度來衡量的。ACID是4個相互獨立的特性的首字母縮寫：原子性（atomicity）、一致性（consistency）、隔離性（isolation）、持久性（durability）。

　　◊ 原子性

　　事務必須是原子的，這也就是說，在事務結束的時候，事務中的操作要麼全部完成，要麼全部失敗。如果事務中的某些操作被寫到磁碟，而另外一些則沒有，就違反了原子性。

　　◊ 一致性

　　事務必須保證資料庫的一致性，在事務執行前資料庫應當處於一致狀態，而事務結束的時候，資料庫又會回到一致性狀態。從ACID特性的目的來看，一致性意味著資料庫中的每一行和每個值必須與其所描述的現實保持一致，而且滿足所有約束的要求。如果將訂單行寫到磁碟上，卻沒有寫入相應的訂單明細，則Order與OrderDetail表之間的一致性就被破壞了。

　　◊ 隔離性

　　每個事務都必須與其他事務所產生的結果隔離開來。不管是否有其他的事務正在執行，事務都必須使用它開始啟動並執行那一刻的資料集合執行下去。隔離性是兩個事務之間的屏障。檢驗隔離性的方法之一是看資料庫是否具有這樣的能力，即：在相同的初始資料集合上多次重複執行一組特地的事務集合，而每次都能得到相同的結果。隔離性在多使用者資料庫中更為重要。

　　◊ 持久性

　　事務的持久性指不管系統是否發生了故障，交易處理的結果都是永久的。一旦事務被提交後，它就一直處於已提交狀態。

　　2>、事務缺陷

　　事務之間缺乏足夠的隔離性會表現在以下3個方面：髒讀、不可重複讀取和幻影行。這些事務缺陷是影響事務完整性的隱患。

　　◊ 髒讀（Dirty Reads）

　　事務最明顯的缺陷是在事務提交之前，它對資料所做的修改就為其他事務所見。如果一個事務讀取了另外一個事務尚未提交的更新，就叫做髒讀。

BEGIN TRANSACTION    UPDATE Product SET ProductName='Transaction Dirty Read' WHERE ProductID=1    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED    SELECT ProductName FROM Product WHERE ProductID=1COMMIT TRANSACTION

　　執行結果如下：

　　第一個事務尚未完成對資料的修改，而第二個事務卻能夠讀到修改結果，這就違反了事務完整性。

　　◊ 不可重複讀取（Non-Repeatable Reads）

　　不可重複讀取類似於髒讀，只不過它發生在事務看看到其他事務已經提交的資料更新的情況下。真正的隔離性指一個事務不會影響到另外一個事務。如果隔離性是完全的，那麼一個事務不應該能看到本事務以外的資料更新。在一個事務內進行同樣的讀操作，每次都應該得到相同的結果。如果在兩次讀操作中得到了不同的結果，就意味著出現了不可重複讀取型事務缺陷。

SET TRANSACTION ISOLATION LEVEL READ COMMITTEDBEGIN TRANSACTION    --UPDATE Product SET ProductName='Dirty Read' WHERE ProductID=1    SELECT ProductName FROM Product WHERE ProductID=1    BEGIN TRANSACTION        UPDATE Product SET ProductName='Non-Repeatable Read' WHERE ProductID=1COMMIT TRANSACTIONSELECT ProductName FROM Product WHERE ProductID=1

　　執行結果如下：

　　◊ 幻影行（Phantom Rows）

　　危害最小的事務完整性缺陷是幻影行。和不可重複讀取有些相似，幻影行指的是一個事務的更新結果影響到另外一個事務的情況，但與不可重複讀取不同的是它不僅會影響到另外一個事務所取的結果集中的資料值，而且還能使select語句返回另外一些不同的記錄行。

BEGIN TRANSACTION    SELECT ProductID,ProductNo FROM Product WHERE ProductNo='1000'    BEGIN TRANSACTION        UPDATE Product SET ProductNo='1000' WHERE ProductID=2COMMIT TRANSACTIONSELECT ProductID,ProductNo FROM Product WHERE ProductNo='1000'

　　執行結果如下：

　　在上述所有這些事務缺陷中，髒讀危害最大，不可重複讀取次之，幻影行危害最小。

　　3>、隔離性層級

　　資料庫產品是通過在事務之間建立隔離還處理這3個事務缺陷的。隔離性層級是事務之間隔離帶的高度，可以根據具體需求加以調節以控制允許出現的事務缺陷。

　　ANSI SQL-92定義了4個隔離等級：

　　SQL Server使用鎖來實現隔離性層級。鑒於鎖影響到效能，使用者必須在隔離等級和效能之間進行權衡。SQL Server的預設隔離等級是Read Committed，這對於大多數OLTP項目都是適用的。

　　◊ 層級1——Read Uncommitted

　　最不嚴格的隔離等級是Read Uncommitted，它不能防止任何一種事務缺陷，因為它根本沒有在事務間提供隔離。把SQL Server的隔離等級設為Read Uncommitted，等同於把SQL Server的鎖設定為NOLOCK。這種設定適合報表或唯讀應用程式，因為此時SQL Server只為防止資料崩潰提供足夠的鎖，而不會為行競爭提供足夠的鎖，這對資料經常被更新的系統是不合適的。

　　◊ 層級2——Read Committed

　　Read Committed防止了最嚴重的事務缺陷，而又不會是系統陷入過度鎖爭用的泥潭。基於這個原因，SQL Server將她作為預設的隔離等級，對於絕大多數的OLTP項目來說，它都是一個理想的選擇。

　　◊ 層級3——Repeatable Read

　　Repeatable Read可以防止髒讀和不可重複讀取，它增加了事務的隔離等級，而因此帶來的鎖爭用的壓力沒有Serializable隔離等級那樣的嚴重。

　　◊ 層級4——Serializable

　　這是最嚴格的隔離等級，它防止了全部的事務缺陷，並且通過了在上面的隔離定義中所提到的串列事務測試。這種模式適用於對於絕對的事務完整性的要求比效能更為重要的情況。銀行、賬務系統、高度競爭性的銷售資料庫（例如股票市場）通常會使用Serializable隔離等級。

　　使用Serializable隔離等級相當於把鎖設為HOLDLOCK，這將會使事務在整個執行期間都保持鎖，甚至包含共用鎖定。這種設定雖然提供了完全的事務隔離性，卻會造成惡劣的鎖爭用，並使效能降低。

2、SQL Server的鎖機制

　　SQL Server用鎖來實現事務之間的隔離，這樣可以防止一個事務所操作的資料受到另外一個事務影響。每個所都具有以下3個特性：

　　◊ 粒度（Granularity）——鎖的大小

　　◊ 模式（Mode）——鎖的類型

　　◊ 持續期(Duration)——鎖的隔離模式

2.1、鎖的粒度

　　SQL Server的鎖管理器試圖在鎖大小和數量之間尋求平衡以爭取教好的效能。矛盾的焦點在並發（較小的鎖可以允許更多的事務同時存取資料）和效能（鎖越少速度越快）。為了達到平衡，鎖管理器會動態地從一組鎖切換到另外一組鎖。

　　1>、25個行鎖有可能升級為一個頁鎖。

　　2>、如果在同一個擴充區的其他4個以上的頁面上分布著25個以上被鎖定的行，上述頁鎖和這25個行級鎖就可能升級為一個擴充盤區鎖，因為該擴充盤區上有50%以上的頁面都受到了鎖定的影響。

　　3>、如果有足夠的擴充盤區被鎖住，所有這些鎖就可能升級為一個表鎖。

　　動態調整的鎖策略為SQL Server的開發人員帶來了顯著的益處：

　　◊ 無需任何編程，就可以自動地在效能和並發之間取得最佳平衡；

　　◊ 隨著資料庫的增長，鎖管理器會相應地使用與之匹配的鎖粒度，從而保持資料庫具有良好的效能；

　　◊ 動態鎖定簡化了管理工作。

2.2、鎖模式

　　除了鎖粒度也就是鎖大小的屬性之外，鎖還具有鎖模式屬性，它確定了鎖定的用途。SQL Server具有豐富的鎖模式。

　　1>、鎖爭用

　　在SQL Server中，鎖的相互作用與相容性對事務完整性和效能都有很重要的影響。一些鎖模式會排斥另一些鎖模式。鎖的相容性如下：

 

 

 

 

 

 

 

　　2>、共用鎖定(S)

　　到目前為止，最常用也是最為濫用的鎖就是共用鎖定，它是一個簡單的“讀鎖”。事務得到了共用鎖定就好比是在宣稱“我正在查看這個資料”。通常多個事務可以同時查看同一組資料，當然最終還要取決於隔離模式。

　　3>、排它鎖(X)

　　使用排它鎖意味著事務正在寫資料。對於同一資料，在同一時間只能有一個事務持有排它鎖，其他事務在排它鎖持續期間不能查看該資料。

　　4>、更新鎖定(U)

　　更新鎖定並不是事務執行更新時所使用的鎖，更新鎖定意味著事務即將要使用排它鎖，它當前正在掃描資料，以確定使用排它鎖鎖定的那些行。可以將更新鎖定當作即將轉化為排它鎖的共用鎖定。

　　為了避免死結，在同一個時刻只運行使用一個事務持有更新鎖定。

　　5>、意圖鎖定

　　意圖鎖定是一種用於警示的鎖，它警告其他事務即將要發生一些事情。意圖鎖定的主要目的是提高效能。

2.3、查看鎖

sp_lock