深入sql server中的事務

  

一.         概述... 1

二.         並發訪問的不利影響... 1

1.       髒讀（dirty read）... 1

2.       不可重複讀取（nonrepeatable read）... 1

3.       幻讀（phantom read）... 1

三.         並發訪問的控制機制... 2

1.       鎖... 2

2.       資料列版本設定... 2

四.         隔離等級... 2

五.         事務... 3

1.       事務的模式... 3

1.1.       明確交易（Explicit Transactions）... 3

1.2.       自動認可事務（Autocommit Transactions）... 4

1.3.       隱含交易（Implicit Transactions）... 4

2.       事務的編程... 5

2.1.       Transact-SQL指令碼... 5

2.2.       ADO.NET應用程式介面... 5

 

一. 概述

當多個使用者同時訪問資料庫的同一資源時，叫做並發訪問。如果並發的訪問中有使用者對資料進行修改，很可能就會對其他訪問同一資源的使用者產生不利的影響。可能產生的並發不利影響有以下幾類：髒讀、不可重複讀取和幻讀。

為了避免並發訪問產生的不利影響，sql server設計有兩種並發訪問的控制機制：鎖、資料列版本設定。

二. 並發訪問的不利影響

並發訪問，如果沒有並發存取控制機制，可能產生的不利影響有以下幾種

1. 髒讀（dirty read）

如果一個使用者在更新一條記錄，這時第二個使用者來讀取這條更新了的記錄，但是第一個使用者在更新了記錄後又反悔了，不修改了，復原了剛才的更新。這樣，導致了第二個使用者實際上讀取到了一條根本就沒有存在過的修改後的記錄。如果第一個使用者在修改記錄期間，把所修改的記錄鎖住，在修改完成前別的使用者讀取不到記錄，就能避免這種情況。

2. 不可重複讀取（nonrepeatable read）

第一個使用者在一次事務中讀取同一記錄兩次，第一次讀取一條記錄後，又有第二個使用者來訪問這條記錄，並修改了這條記錄，第一個使用者第二次讀取這條記錄時，得到的是跟第一次不同的資料了。如果第一個使用者在兩次讀取之間鎖住要讀取的記錄，別的使用者不能去修改相應的記錄就能避免這種情況。

3. 幻讀（phantom read）

第一個使用者在一次事務中兩次讀取同樣滿足條件的一批記錄，第一次讀取一批記錄後，又有第二個使用者來訪問這個表，並在這個表中插入或者刪除了一些記錄，第一個使用者第二次以同樣條件讀取這批記錄時，可能得到的結果有些記錄是在第一次讀取時有，第二次的結果中沒有了，或者是第二次讀取的結果中有的記錄在第一次讀取的結果中沒有的。如果第一個使用者在兩次讀取之間鎖住要讀取的記錄，別的使用者不能去修改相應的記錄，也不能增刪記錄，就能避免這種情況。

三. 並發訪問的控制機制

Sql server中提供了兩種並發控制的機制以避免在並發訪問時可能產生的不利影響。這兩種機制是：

1. 鎖

每個事務對所依賴的資源（如行、頁或表）請求不同類型的鎖。鎖可以阻止其他事務以某種可能會導致事務請求鎖出錯的方式修改資源。當事務不再依賴鎖定資源時，它將釋放鎖。

根據需要鎖定資源的粒度和層次，鎖有許多類型，主要的有幾種：

表類型：鎖定整個表

行類型：鎖定某個行

檔案類型：鎖定某個資料庫檔案

資料庫類型：鎖定整個資料庫

頁類型：鎖定8K為單位的資料庫頁

 

鎖的粒度越小，鎖定的範圍越小，對別的訪問的阻塞就越小，但是所用的鎖可能會比較多，鎖的消耗就比較大。鎖的粒度越大，對別的訪問的阻塞可能性就越大，但是所用的鎖就會比較少，鎖的消耗就比較小。

對於編程人員來說，不用手工去設定控制鎖，sql server通過設定事務的隔離等級自動管理鎖的設定和控制。

Sql server專門管理鎖的是鎖管理器，鎖管理器通過查詢分析器分析待執行的sql語句，來判斷語句將會訪問哪些資源，進行什麼操作，然後結合設定的隔離等級自動分配管理需要用到的鎖。

2. 資料列版本設定

當啟用了基於資料列版本設定的隔離等級時，資料庫引擎 將維護修改的每一行的版本。應用程式可以指定事務使用行版本查看事務或查詢開始時存在的資料，而不是使用鎖保護所有讀取。通過使用資料列版本設定，讀取操作阻止其他事務的可能性將大大降低。

四. 隔離等級

上面提到了，sql server通過設定隔離等級來控制鎖的使用，從而實現並發法存取控制。

Microsoft SQL Server 資料庫引擎支援所有這些隔離等級：

l         未提交讀（隔離事務的最低層級，只能保證不讀取物理上損壞的資料）

l         已提交讀（資料庫引擎的預設層級）

l         可重複讀

l         可序列化（隔離事務的最進階別，事務之間完全隔離）

 

這幾種隔離等級，對應上面三種並發訪問可能產生的不利影響，分別有不同的效果，見下表：

隔離等級	髒讀	不可重複讀取	幻讀
未提交讀	是	是	是
已提交讀	否	是	是
可重複讀	否	否	是
快照	否	否	否
可序列化	否	否	否

 

五. 事務

事務是一個邏輯上的單個的工作單元，其中可以包括許多操作，但是它們在邏輯上是一個整體，要麼全部完成，要麼全部失敗，就好像什麼操作都沒進行似的。

事務是十分可靠堅固的機制，它能保證事務要麼全部完成，要麼能全部復原。

l         鎖：使用鎖的機制儘可能的保證並發事務的隔離性，避免並發的不利影響。

l         交易記錄：交易記錄記錄著整個事務的所有操作步驟，必要的時候靠日誌重新開始事務或者復原事務。不管出現什麼狀況，哪怕是網路中斷，機器斷電，甚至是資料庫引擎本身出問題了，通過交易記錄都能保證事務的完整性。

l         交易管理：保證一個事務的原子性和資料的一致性。一個事務開始後，它要麼成功的完成，要麼失敗，復原到事務沒開始前的那個狀態，事務開始做的所有修改都將複原。

1. 事務的模式

控制事務的開始結束的時間點和事務的範圍，有幾種事務模式：

1.1.明確交易（Explicit Transactions）

明確交易通過sql指令碼的BEGIN TRANSACTION或者編程介面（API）的開始事務語句啟動事務，以sql指令碼的COMMIT 或 ROLLBACK語句提交或復原事務，編程介面（API）的提交事務或復原事務語句結束事務。都是通過顯式的命令控制事務的開始和結束。

從事務開始到事務提交或者復原是一個完整的事務周期，事務一旦開始，結果要麼是提交，要麼是復原。

如果事務範圍內發生錯誤，錯誤分為幾種類型，不同類型的錯誤有不同的行為。

l         嚴重錯誤

比如，用戶端到服務端的網路中斷了，或者客戶的機器被關機了，資料引擎會被通知數據串連已中止，這樣嚴重的錯誤資料引擎會自動在服務端復原整個事務。

l         執行階段錯誤

語句之間的“GO”命令形成的地區為命令批次。資料引擎編譯和執行語句是以批次為單位的。一次編譯一個批次的命令，編譯完成後執行這個批次的命令。預存程序是整個被一次編譯的，所以一個預存程序內不分批次，整個過程就是一個批次。

大多數情況下，在一個批次中一條語句發生執行階段錯誤，這個語句將被中止，同時同一批次的所有後續語句也不再執行，但同一批次前面已經執行的命令依然有效。但是可以使用了try…catch捕獲錯誤，並進行相應處理，比如執行交易回復命令。

有一些執行階段錯誤，比如插入了一個主鍵重複的記錄，只中止當前出錯的這條語句，後續的語句照樣繼續執行。這類錯誤也能被try…catch捕獲到。

為了保證整個事務中，任何語句出現錯誤都復原整個事務，最簡單的方法是在事務開始前設定SET XACT_ABORT 為 ON，這個設定指示資料引擎，在一個事務中遇到一個錯誤後，不再執行後續的事務，並復原整個事務。

l         編譯錯誤

遇到編譯錯誤時，錯誤語句所在的批次不被執行，並不會受SET XACT_ABORT設定的影響。

1.2.自動認可事務（Autocommit Transactions）

這個模式是資料引擎的預設模式，也是各種編程介面的事務預設模式。每個單獨的語句在完成後被提交，失敗後被復原，編程人員不需要指定任何命令。

每個單獨的語句就是一個事務的單位，成功了就提交，這句語句執行錯誤就復原這條語句，對其他語句的執行不產生影響。注意這裡說的執行錯誤是執行階段錯誤，如果語句本身有編譯錯誤，比如sql語句的關鍵詞拼字錯誤了，那麼發生編譯錯誤語句所在的那個批次的語句都將不被執行。比如：

USE AdventureWorks; GO CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3)); GO INSERT INTO TestBatch VALUES (1, 'aaa'); INSERT INTO TestBatch VALUES (2, 'bbb'); INSERT INTO TestBatch VALUSE (3, 'ccc'); -- Syntax error. GO SELECT * FROM TestBatch; -- Returns no rows. GO

上面這段sql中的第三個insert語句values關鍵字拼字錯誤，將導致編譯錯誤，結果是跟這個語句在同一批次的所有三條insert語句都將不被執行。

如果上面第三個insert語句是這樣的：

INSERT INTO TestBatch VALUES (1, 'ccc'); -- Duplicate key error.

這將產生一個執行階段錯誤“重複的主鍵”，這條語句將被復原，但是不影響前面兩條insert語句。從這點可以看出，自動認可模式是每條單獨的語句要麼完成要麼復原，不影響其他語句的執行。

 

1.3.隱含交易（Implicit Transactions）

在SET IMPLICIT_TRANSACTIONS ON命令之後的第一條語句開始，就開始一個新的事務，直到遇到COMMIT 或 ROLLBACK語句結束這個事務，下一個語句又是一個新的事務，同樣直到遇到COMMIT 或 ROLLBACK語句結束這個事務。這樣形成了一個事務鏈，直到SET IMPLICIT_TRANSACTIONS OFF結束隱含交易，回到預設的自動認可事務模式。

事務中的行為跟明確交易模式是一致的。

 

事務體現在connection的水平，一個connection具有事務模式，自動認可模式是connection的預設事務模式，直到BEGIN TRANSACTION語句開始明確交易模式，或者隱含交易被SET IMPLICIT_TRANSACTIONS ON設定，串連的事務模式被置為顯式或隱含交易模式，當顯示事務被提交或者復原，隱含交易被置為關閉後，這個串連的事務模式又被置為自動認可模式。

2. 事務的編程

資料庫的編程有兩種方式，一種應用程式介面（API），包括ODBC、ADO 、ado.net等等編程介面，一種是Transact-SQL指令碼，典型的是預存程序。

2.1.Transact-SQL指令碼

BEGIN TRANSACTION

標記顯式串連事務的起始點。

COMMIT TRANSACTION 或 COMMIT WORK

如果沒有遇到錯誤，可使用該語句成功地結束事務。該事務中的所有資料修改在資料庫中都將永久有效。事務佔用的資源將被釋放。

ROLLBACK TRANSACTION 或 ROLLBACK WORK

用來復原遇到錯誤的事務。該事務修改的所有資料都返回到事務開始時的狀態。事務佔用的資源將被釋放。

2.2.ADO.NET應用程式介面

對 SqlConnection 對象使用 BeginTransaction 方法可以啟動一個明確交易。若要結束事務，可以對 SqlTransaction 對象調用 Commit() 或 Rollback() 方法。

 

下面主要以在預存程序中使用事務的編程詳加說明

 

使用事務的目的是保持一段sql語句執行的完整性，要麼全部執行成功，只要有一條語句失敗就能完全復原，回到事務開始前的狀態。

事務有起點，即通過BEGIN TRANSACTION啟動一個事務，其後執行事務中的各個語句，最後要判斷，全部語句執行都成功了，就用COMMIT TRANSACTION提交事務，把事務中執行的語句的結果固定下來；如果事務中有任何錯誤，要能捕獲到錯誤，並執行ROLLBACK TRANSACTION復原整個事務。

下面是一段範例程式碼：

USE AdventureWorks; BEGIN TRANSACTION; BEGIN TRY     -- 產生一個違反約束的錯誤.     DELETE FROM Production.Product         WHERE ProductID = 980; END TRY BEGIN CATCH     SELECT         ERROR_NUMBER() AS ErrorNumber,         ERROR_SEVERITY() AS ErrorSeverity,         ERROR_STATE() as ErrorState,         ERROR_PROCEDURE() as ErrorProcedure,         ERROR_LINE() as ErrorLine,         ERROR_MESSAGE() as ErrorMessage;     IF @@TRANCOUNT > 0         ROLLBACK TRANSACTION; END CATCH; IF @@TRANCOUNT > 0     COMMIT TRANSACTION;

把事務中要執行的語句都放在TRY語句塊中，保證所有語句產生錯誤都能被捕獲到。如果事務中的語句一旦產生錯誤，事務中的後續語句不再被執行，直接跳到CATCH語句塊執行，進行出錯後的後續處理過程。

CATCH語句塊中的最主要的工作就是執行交易回復語句，以復原整個事務。也可以進行一些其他輔助性的工作，顯示錯誤，記錄錯誤等等。

如果事務中所有語句都沒有出錯，順利執行完成，程式就跳過CATCH語句塊，執行最後的COMMIT TRANSACTION提交事務。

 

經常看到有些人使用@@error來捕獲錯誤，判斷是否需要復原事務，代碼大概如下：

BEGIN TRANSACTION; Select xxx from yyyy;      --事務中的sql語句 ……   If @@error > 0        ROLLBACK TRANSACTION;  Else        COMMIT TRANSACTION;

這裡使用@@error來判斷事務中所有的語句是否發生錯誤，並以此來決定是復原事務，還是提交事務。實際上這麼做是是十分錯誤的。

第一，@@error是針對每個sql語句執行結果，反映的是當前執行的語句出錯狀態，當執行到下一句，@@error又被重設以反應下一句語句的執行結果。所以用@@error來判斷所有語句是否出錯是不行的。

第二，sql語句的執行階段錯誤有兩類，一類是語句發生了錯誤，此語句被中止，但後續語句還能被繼續執行，一類是語句發生錯誤後，一個命令批次中的後續的所有語句也不再被執行。當事務中的語句發生這種錯誤，那麼放在最後的If @@error > 0判斷語句都不會有機會被執行了。

這樣的做法可能導致很嚴重的後果：如果事務中有語句產生第一類的錯誤，後續語句都不被執行，原來設計的ROLLBACK TRANSACTION或COMMIT TRANSACTION都沒有機會被執行，就是說被這個事務鎖了的資源都將得不到釋放，產生的後果是，如果這個事務對某些記錄設定了共用鎖定，那這些記錄再也不能被修改，更慘的是如果這個事務對某些記錄設定了獨佔鎖定，那麼讀取這些記錄的語句一直會被堵塞，執行不下去了。程式也就死在那裡了。

所以，在事務中用來捕獲語句錯誤還是需要使用try…catch語句塊。