Ref: http://cxgl.blog.bokee.net/bloggermodule/blog_viewblog.do?id=1339196併發控制的型別

當許多人試圖同時修改資料庫中的資料時，必須實現一個控制系統，使一個人所做的修改不會對他人所做的修改產生負面影響。這稱為併發控制。

併發控制理論根據建立併發控制的方法而分為兩類：

悲觀併發控制

一個鎖定系統，可以阻止使用者以影響其他使用者的方式修改資料。如果使用者執行的操作導致應用了某個鎖，只有這個鎖的所有者釋放該鎖，其他使用者才能執行與該鎖衝突的操作。這種方法之所以稱為悲觀併發控制，是因為它主要用於資料爭用激烈的環境中，以及發生併發衝突時用鎖保護資料的成本低於回滾事務的成本的環境中。

樂觀併發控制

在樂觀併發控制中，使用者讀取資料時不鎖定資料。當一個使用者更新資料時，系統將進行檢查，檢視該使用者讀取資料後其他使用者是否又更改了該資料。如果其他使用者更新了資料，將產生一個錯誤。一般情況下，收到錯誤資訊的使用者將回滾事務並重新開始。這種方法之所以稱為樂觀併發控制，是因為它主要用於資料爭用較少的環境中，以及回滾事務的成本偶爾高於讀取資料時鎖定資料的成本的環境中。

Microsoft SQL Server 2005 支援某個範圍的併發控制。使用者透過為遊標上的連線或併發選項選擇事務隔離級別來指定併發控制的型別。這些特性可以使用 Transact-SQL 語句或透過資料庫應用程式程式設計介面（API，如 ADO、ADO.NET、OLE DB 和 ODBC）的屬性和特性來定義。

SQL-99 標準定義了下列隔離級別，Microsoft SQL Server 資料庫引擎 支援所有這些隔離級別：

• 未提交讀（隔離事務的最低階別，只能保證不讀取物理上損壞的資料）
• 已提交讀（資料庫引擎的預設級別）
• 可重複讀
• 可序列化（隔離事務的最高階別，事務之間完全隔離）

SQL Server 2005 還支援使用行版本控制的兩個事務隔離級別。一個是已提交讀隔離的新實現，另一個是新事務隔離級別（快照）。

• 將 READ_COMMITED_SNAPSHOT 資料庫選項設定為 ON 時，已提交讀隔離使用行版本控制提供語句級別的讀取一致性。讀取操作只需要 SCH-S 表級別的鎖，不需要頁鎖或行鎖。將 READ_COMMITED_SNAPSHOT 資料庫選項設定為 OFF（預設設定）時，已提交讀隔離的行為與在 SQL Server 的早期版本中相同。兩個實現都滿足已提交讀隔離的 ANSI 定義。
• 快照隔離級別使用行版本控制來提供事務級別的讀取一致性。讀取操作不獲取頁鎖或行鎖，只獲取 SCH-S 表鎖。讀取其他事務修改的行時，讀取操作將檢索啟動事務時存在的行的版本。將 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設定為 ON 時，將啟用快照隔離。預設情況下，使用者資料庫的此選項設定為 OFF。

下表顯示了不同隔離級別允許的併發副作用。

控制到 SQL Server 的連線發出的 Transact-SQL 語句的鎖定行為和行版本控制行為。

語法

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }[ ; ]

引數

READ UNCOMMITTED指定語句可以讀取已由其他事務修改但尚未提交的行。

在 READ UNCOMMITTED 級別執行的事務，不會發出共享鎖來防止其他事務修改當前事務讀取的資料。

READ UNCOMMITTED 事務也不會被排他鎖阻塞，排他鎖會禁止當前事務讀取其他事務已修改但尚未提交的行。設定此選項之後，可以讀取未提交的修改，這種讀取稱為髒讀。在事務結束之前，可以更改資料中的值，行也可以出現在資料集中或從資料集中消失。該選項的作用與在事務內所有 SELECT 語句中的所有表上設定 NOLOCK 相同。這是隔離級別中限制最少的級別。

在 SQL Server 2005 中，您還可以使用下列任意一種方法，在保護事務不髒讀未提交的資料修改的同時儘量減少鎖定爭用：

· READ COMMITTED

隔離級別，並將 READ_COMMITTED_SNAPSHOT 資料庫選項設定為 ON。 SNAPSHOT 隔離級別。

READ COMMITTED 指定語句不能讀取已由其他事務修改但尚未提交的資料。這樣可以避免髒讀。其他事務可以在當前事務的各個語句之間更改資料，從而產生不可重複讀取和幻像資料。該選項是 SQL Server 的預設設定。

READ COMMITTED 的行為取決於 READ_COMMITTED_SNAPSHOT 資料庫選項的設定：

· 如果將 READ_COMMITTED_SNAPSHOT 設定為 OFF（預設設定），則資料庫引擎會使用共享鎖防止其他事務在當前事務執行讀取操作期間修改行。共享鎖還會阻止語句在其他事務完成之前讀取由這些事務修改的行。語句完成後便會釋放共享鎖。

· 如果將 READ_COMMITTED_SNAPSHOT 設定為 ON，則資料庫引擎會使用行版本控制為每個語句提供一個在事務上一致的資料快照，因為該資料在語句開始時就存在。不使用鎖來防止其他事務更新資料。

當 READ_COMMITTED_SNAPSHOT 資料庫選項設定為 ON 時，您可以使用 READCOMMITTEDLOCK 表提示為 READ_COMMITTED 隔離級別上執行的事務中的各語句請求共享鎖，而不是行版本控制。

注意：

設定 READ_COMMITTED_SNAPSHOT 選項時，資料庫中僅允許存在執行 ALTER DATABASE 命令的連線。在 ALTER DATABASE 完成之前，資料庫中不允許有其他開啟的連線。資料庫不必處於單使用者模式。

REPEATABLE READ

指定語句不能讀取已由其他事務修改但尚未提交的行，並且指定，其他任何事務都不能在當前事務完成之前修改由當前事務讀取的資料。

對事務中的每個語句所讀取的全部資料都設定了共享鎖，並且該共享鎖一直保持到事務完成為止。這樣可以防止其他事務修改當前事務讀取的任何行。其他事務可以插入與當前事務所發出語句的搜尋條件相匹配的新行。如果當前事務隨後重試執行該語句，它會檢索新行，從而產生幻讀。由於共享鎖一直保持到事務結束，而不是在每個語句結束時釋放，所以併發級別低於預設的 READ COMMITTED 隔離級別。此選項只在必要時使用。

SNAPSHOT

指定事務中任何語句讀取的資料都將是在事務開始時便存在的資料的事務上一致的版本。事務只能識別在其開始之前提交的資料修改。在當前事務中執行的語句將看不到在當前事務開始以後由其他事務所做的資料修改。其效果就好像事務中的語句獲得了已提交資料的快照，因為該資料在事務開始時就存在。

除非正在恢復資料庫，否則 SNAPSHOT 事務不會在讀取資料時請求鎖。讀取資料的 SNAPSHOT 事務不會阻止其他事務寫入資料。寫入資料的事務也不會阻止 SNAPSHOT 事務讀取資料。

在資料庫恢復的回滾階段，如果嘗試讀取由其他正在回滾的事務鎖定的資料，則 SNAPSHOT 事務將請求一個鎖。在事務完成回滾之前，SNAPSHOT 事務會一直被阻塞。當事務取得授權之後，便會立即釋放鎖。

必須將 ALLOW_SNAPSHOT_ISOLATION 資料庫選項設定為 ON，才能開始一個使用 SNAPSHOT 隔離級別的事務。如果使用 SNAPSHOT 隔離級別的事務訪問多個資料庫中的資料，則必須在每個資料庫中將 ALLOW_SNAPSHOT_ISOLATION 都設定為 ON。

不能將透過其他隔離級別開始的事務設定為 SNAPSHOT 隔離級別，否則將導致事務中止。如果一個事務在 SNAPSHOT 隔離級別開始，則可以將它更改為另一個隔離級別，然後再返回 SNAPSHOT。一個事務從執行 BEGIN TRANSACTION 語句開始。

在 SNAPSHOT 隔離級別下執行的事務可以檢視由該事務所做的更改。例如，如果事務對錶執行 UPDATE，然後對同一個表發出 SELECT 語句，則修改後的資料將包含在結果集中。

SERIALIZABLE

請指定下列內容：

· 語句不能讀取已由其他事務修改但尚未提交的資料。

· 任何其他事務都不能在當前事務完成之前修改由當前事務讀取的資料。

· 在當前事務完成之前，其他事務不能使用當前事務中任何語句讀取的鍵值插入新行。

範圍鎖處於與事務中執行的每個語句的搜尋條件相匹配的鍵值範圍之內。這樣可以阻止其他事務更新或插入任何行，從而限定當前事務所執行的任何語句。這意味著如果再次執行事務中的任何語句，則這些語句便會讀取同一組行。在事務完成之前將一直保持範圍鎖。這是限制最多的隔離級別，因為它鎖定了鍵的整個範圍，並在事務完成之前一直保持範圍鎖。因為併發級別較低，所以應只在必要時才使用該選項。該選項的作用與在事務內所有 SELECT 語句中的所有表上設定 HOLDLOCK 相同。

備註一次只能設定一個隔離級別選項，而且設定的選項將一直對那個連線始終有效，直到顯式更改該選項為止。事務中執行的所有讀取操作都會在指定的隔離級別的規則下執行，除非語句的 FROM 子句中的表提示為表指定了其他鎖定行為或版本控制行為。事務隔離級別定義了可為讀取操作獲取的鎖型別。針對 READ COMMITTED 或 REPEATABLE READ 獲取的共享鎖通常為行鎖，儘管當讀取引用了頁或表中大量的行時，行鎖可以升級為頁鎖或表鎖。如果某行在被讀取之後由事務進行了修改，則該事務會獲取一個用於保護該行的排他鎖，並且該排他鎖在事務完成之前將一直保持。例如，如果 REPEATABLE READ 事務具有用於某行的共享鎖，並且該事務隨後修改了該行，則共享行鎖便會轉換為排他行鎖。

在事務進行期間，可以隨時將事務從一個隔離級別切換到另一個隔離級別，但有一種情況例外。即在從任一隔離級別更改到 SNAPSHOT 隔離時，不能進行上述操作。否則會導致事務失敗並回滾。但是，可以將在 SNAPSHOT 隔離中啟動的事務更改為任何其他隔離級別。

將事務從一個隔離級別更改為另一個隔離級別之後，便會根據新級別的規則對更改後讀取的資源執行保護。在更改前讀取的資源將繼續按照以前級別的規則受到保護。例如，如果某個事務從 READ COMMITTED 更改為 SERIALIZABLE，則在該事務結束前，更改後所獲取的共享鎖將一直處於保留狀態。

如果在儲存過程或觸發器中發出 SET TRANSACTION ISOLATION LEVEL，則當物件返回控制時，隔離級別會重設為在呼叫物件時有效的級別。例如，如果在批處理中設定 REPEATABLE READ，並且該批處理呼叫一個將隔離級別設定為 SERIALIZABLE 的儲存過程，則當該儲存過程將控制返回給該批處理時，隔離級別就會恢復為 REPEATABLE READ。

注意：

使用者定義函式以及公共語言執行時 (CLR) 使用者定義型別不能執行 SET TRANSATION ISOLATION LEVEL。但是，可透過使用表提示來重寫隔離級別。有關詳細資訊，請參閱表提示 (Transact-SQL)。

當您使用 sp_bindsession 繫結兩個會話時，每個會話都會保留它自身的隔離級別設定。使用 SET TRANSACTION ISOLATION LEVEL 更改某個會話的隔離級別設定時，不會影響與該會話繫結的其他任何會話的設定。

SET TRANSACTION ISOLATION LEVEL 會在執行或執行時生效，而不是在分析時生效。

對於儲存為堆的表執行的最佳化大容量載入操作將阻塞查詢，但在以下隔離級別下執行最佳化大容量載入操作時，通常查詢不會受到阻塞。

· SNAPSHOT

· READ UNCOMMITTED

· 使用行版本控制的 READ COMMITTED

相反，在這些隔離級別下執行的查詢會阻塞針對堆執行的最佳化大容量載入操作。有關大容量載入操作的詳細資訊，請參閱大容量匯入和大容量匯出概述和最佳化大容量匯入效能。

END