快照隔離可提高 OLTP 應用程式的並行性。
了解快照集隔離與資料列版本設定
一旦啟用快照隔離，即會在 tempdb 中維護每個交易已更新的資料列版本。 唯一的交易序號會識別每個交易，而且會針對每個資料列版本記錄這些唯一的編號。 交易適用於在交易序號之前具有序號的最新資料列版本。 交易會忽略在交易開始之後所建立的較新資料列版本。
「快照集」一詞反映了交易中的所有查詢都會根據交易開始時資料庫的狀態，看到資料庫的相同版本或快照集。 在快照交易中，基礎資料列或資料頁不會被加鎖，這樣可允許其他交易執行，而不會受到先前未完成交易的封鎖。 修改資料的交易不會封鎖讀取資料的交易，而讀取資料的交易不會封鎖寫入資料的交易，因為它們通常會在 SQL Server 中預設的 READ COMMITTED 隔離等級之下。 此非封鎖性的行為也會大幅降低複雜交易發生死結的可能性。
快照隔離使用樂觀併發控制模型。 如果快照交易嘗試提交對自交易開始以來已發生變更的資料所做的修改，該交易將會回滾並引發錯誤。 您可以針對存取要修改之資料的 SELECT 陳述式使用 UPDLOCK 提示，以避免發生這種情況。 如需詳細資訊，請參閱《SQL Server 線上叢書》中的＜鎖定提示＞。
在交易中使用快照集隔離之前，必須先透過設定 [ALLOW_SNAPSHOT_ISOLATION ON] 資料庫選項將其啟用。 如此會啟動在暫存資料庫 (tempdb) 中儲存資料列版本的機制。 你必須對每個使用快照隔離的資料庫，使用 Transact-SQL ALTER DATABASE 陳述式啟用快照隔離。 在這方面，快照集隔離與 READ COMMITTED、REPEATABLE READ、SERIALIZABLE 和 READ UNCOMMITTED 的傳統隔離等級不同，上述等級不需要任何設定。 下列陳述式會啟用快照隔離，並以 SNAPSHOT 取代預設的 READ COMMITTED 行為：
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON
將 READ_COMMITTED_SNAPSHOT 選項設為 ON，可在預設的 READ COMMITTED 隔離等級下存取版本化資料列。 如果 [READ_COMMITTED_SNAPSHOT] 選項設為 [OFF]，您必須為每個工作階段明確設定 Snapshot 隔離層級，才能存取版本化的資料列。
使用隔離等級管理並行存取
Transact-SQL 陳述式執行時所用的隔離等級會決定其鎖定與資料列版本設定行為。 隔離層級的作用範圍涵蓋整個連線，一旦使用 SET TRANSACTION ISOLATION LEVEL 陳述式為某個連線設定該層級，它就會持續生效，直到連線關閉或設定了另一個隔離層級為止。 當連線關閉並返回池時，會保留上一 SET TRANSACTION ISOLATION LEVEL 語句的隔離層級。 重複使用共用連線的後續連線，會使用在共用連線時有效的隔離等級。
在連線內發出的個別查詢可以包含鎖定提示，以修改單一陳述式或交易的隔離，但不會影響連線的隔離等級。 在預存程序或函式中設定的隔離等級或鎖定提示，並不會變更呼叫它們之連線的隔離等級，而且只會在預存程序或函式呼叫的持續時間內有效。
舊版的 SQL Server 支援四種 SQL-92 標準中定義的隔離等級：
READ UNCOMMITTED 是限制最少的隔離等級，因為它會忽略其他交易所設下的鎖定。 在 READ UNCOMMITTED 隔離等級下執行的交易，可以讀取其他交易尚未提交的已修改資料值；這稱為「髒讀」。
READ COMMITTED 是 SQL Server 的預設隔離等級。 它透過規定陳述式不得讀取其他交易已修改但尚未提交的資料值，來防止髒讀。 其他交易仍然可能在目前的交易中每個陳述式執行之間修改、插入或刪除資料，導致不可重複讀取，或產生「幻影」資料。
REPEATABLE READ 是比 READ COMMITTED 嚴格的隔離等級。 它涵蓋 READ COMMITTED，並進一步規定，在目前交易提交之前，其他交易不得修改或刪除已被目前交易讀取的資料。 其並行程度低於 READ COMMITTED，因為對讀取資料所加的共用鎖定會在整個交易期間持續保留，而不是在每個陳述式結束時釋放。
SERIALIZABLE 是限制最嚴格的隔離等級，因為它會鎖定整個索引鍵範圍，並將這些鎖維持到交易完成為止。 它包含 REPEATABLE READ，並新增一項限制：在該交易完成之前，其他交易不得將新的資料列插入該交易已讀取的範圍內。
如需詳細資訊，請參閱交易鎖定與資料列版本設定指南。
快照隔離等級擴充功能
SQL Server 透過引入 SNAPSHOT 隔離等級以及 READ COMMITTED 的另一種實作方式，擴充了 SQL-92 隔離等級。 READ_COMMITTED_SNAPSHOT 隔離等級可在所有交易中透明地取代 READ COMMITTED。
SNAPSHOT 隔離層級規定，在交易內讀取的資料絕不會反映其他同時進行的交易所做的變更。 交易會使用在交易開始時已存在的資料列版本。 讀取時不會對資料進行鎖定，因此 SNAPSHOT 交易不會封鎖其他交易寫入資料。 寫入資料的交易不會封鎖快照集交易讀取資料。 您必須透過設定 [ALLOW_SNAPSHOT_ISOLATION] 資料庫選項來啟用快照集隔離，才能使用該功能。
當資料庫啟用快照隔離時，READ_COMMITTED_SNAPSHOT 資料庫選項會決定預設 READ COMMITTED 隔離層級的行為。 如果您未明確指定 [READ_COMMITTED_SNAPSHOT ON]，則會將 [READ COMMITTED] 套用至所有隱含交易。 這會產生與設定 [READ_COMMITTED_SNAPSHOT OFF] \(預設值\) 相同的行為。 當 [READ_COMMITTED_SNAPSHOT OFF] 生效時，資料庫引擎會使用共用鎖定來強制執行預設的隔離等級。 如果將 [READ_COMMITTED_SNAPSHOT] 資料庫選項設定為 [ON]，資料庫引擎就會使用資料列版本設定與快照集隔離作為預設值，而不是使用鎖定來保護資料。
快照隔離與資料列版本控制的運作方式
啟用 SNAPSHOT 隔離等級之後，每次更新資料列時，SQL Server 資料庫引擎都會在 tempdb 中儲存原始資料列的複本，並將交易序號新增至資料列。 以下是事件的發生順序：
系統會起始新交易，並指派交易序號給新交易。
資料庫引擎會讀取交易中的資料列，並從 tempdb 擷取其序號最接近但低於交易序號的該資料列版本。
資料庫引擎會檢查該交易序號是否不在快照交易開始時仍處於作用中的未認可交易之交易序號清單中。
交易從 tempdb 讀取交易開始時的資料列版本。 它將無法看見在事務開始後插入的新資料列，因為那些序號值會高於事務序號值。
目前的交易可以看到交易開始後刪除的資料列，原因為 tempdb 中會有一個序號值較低的資料列版本。
快照隔離的整體效果是，交易會看到交易開始時存在的所有資料，而不會理會或對底層資料表施加任何鎖定。 這能夠會在發生競爭的情況下造成效能提升。
快照交易一律使用樂觀並行控制，不會持有任何會阻止其他交易更新資料列的鎖定。 如果快照交易嘗試提交對在交易開始後已遭變更之資料列所做的更新，則該交易會回復，並引發錯誤。
在 ADO.NET 中使用快照隔離
ADO.NET 透過 SqlTransaction 類別支援快照隔離。 如果資料庫已啟用快照隔離，但未設定 READ_COMMITTED_SNAPSHOT ON，則您必須在呼叫 BeginTransaction 方法時，使用 IsolationLevel.Snapshot 列舉值來啟動 SqlTransaction。 此程式碼片段會假設連線是開啟的 SqlConnection 物件。
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
範例
下列範例示範不同的隔離等級如何透過嘗試存取鎖定的資料來運作，而且這不適合在實際執行的程式碼中使用。
該程式碼會連線至 SQL Server 中的 AdventureWorks 範例資料庫，並會建立名為 TestSnapshot 的資料表並插入一個資料列。 程式碼使用 ALTER DATABASE Transact-SQL 陳述式來開啟資料庫的快照隔離，但並未設定 READ_COMMITTED_SNAPSHOT 選項，因此預設的 READ COMMITTED 隔離層級行為仍然有效。 然後此程式碼會執行下列動作：
它會啟動 sqlTransaction1，但不會完成它；sqlTransaction1 會使用 SERIALIZABLE 隔離等級來啟動更新交易。 這具有鎖定資料表的效果。
其會開啟第二個連線並使用 SNAPSHOT 隔離等級初始化第二筆交易，以讀取 TestSnapshot 資料表中的資料。 因為已啟用快照集隔離，所以此交易可以讀取 sqlTransaction1 開始之前已存在的資料。
其會開啟第三個連線，並使用 READ COMMITTED 隔離等級來起始交易，以嘗試讀取資料表中的資料。 在這種情況下，程式碼無法讀取資料，因為它無法越過第一個交易在資料表上設下的鎖定來讀取資料，因此會逾時。如果使用 REPEATABLE READ 和 SERIALIZABLE 隔離層級，也會出現相同的結果，因為這些隔離層級同樣無法越過第一個交易所設下的鎖定來讀取資料。
其會開啟第四個連線，並使用 READ UNCOMMITTED 隔離等級來起始交易，這會在 sqlTransaction1 中執行未認可值的中途讀取。 如果第一筆交易未提交，這個值可能永遠都不會實際存在於資料庫中。
它會回復第一筆交易，並透過刪除 TestSnapshot 資料表以及停用 AdventureWorks 資料庫的快照隔離來完成清理。
注意
下列範例會使用相同的連接字串，但會關閉連線共用。 如果連線已共用，重設其隔離等級並不會重設伺服器的隔離等級。 因此，後續使用相同集區內部連線的連線在啟動時，其隔離等級會設為該集區連線的隔離等級。 關閉連線共用的替代方法是為每個連線明確設定隔離等級。
using Microsoft.Data.SqlClient;
class Program
{
static void Main()
{
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
string connectionString = GetConnectionString();
using (SqlConnection connection1 = new SqlConnection(connectionString))
{
// Drop the TestSnapshot table if it exists
connection1.Open();
SqlCommand command1 = connection1.CreateCommand();
command1.CommandText = "IF EXISTS "
+ "(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') "
+ "DROP TABLE TestSnapshot";
try
{
command1.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// Enable Snapshot isolation
command1.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON";
command1.ExecuteNonQuery();
// Create a table named TestSnapshot and insert one row of data
command1.CommandText =
"CREATE TABLE TestSnapshot (ID int primary key, valueCol int)";
command1.ExecuteNonQuery();
command1.CommandText =
"INSERT INTO TestSnapshot VALUES (1,1)";
command1.ExecuteNonQuery();
// Begin, but do not complete, a transaction to update the data
// with the Serializable isolation level, which locks the table
// pending the commit or rollback of the update. The original
// value in valueCol was 1, the proposed new value is 22.
SqlTransaction transaction1 =
connection1.BeginTransaction(IsolationLevel.Serializable);
command1.Transaction = transaction1;
command1.CommandText =
"UPDATE TestSnapshot SET valueCol=22 WHERE ID=1";
command1.ExecuteNonQuery();
// Open a second connection to AdventureWorks
using (SqlConnection connection2 = new SqlConnection(connectionString))
{
connection2.Open();
// Initiate a second transaction to read from TestSnapshot
// using Snapshot isolation. This will read the original
// value of 1 since transaction1 has not yet committed.
SqlCommand command2 = connection2.CreateCommand();
SqlTransaction transaction2 =
connection2.BeginTransaction(IsolationLevel.Snapshot);
command2.Transaction = transaction2;
command2.CommandText =
"SELECT ID, valueCol FROM TestSnapshot";
SqlDataReader reader2 = command2.ExecuteReader();
while (reader2.Read())
{
Console.WriteLine("Expected 1,1 Actual "
+ reader2.GetValue(0).ToString()
+ "," + reader2.GetValue(1).ToString());
}
transaction2.Commit();
}
// Open a third connection to AdventureWorks and
// initiate a third transaction to read from TestSnapshot
// using ReadCommitted isolation level. This transaction
// will not be able to view the data because of
// the locks placed on the table in transaction1
// and will time out after 4 seconds.
// You would see the same behavior with the
// RepeatableRead or Serializable isolation levels.
using (SqlConnection connection3 = new SqlConnection(connectionString))
{
connection3.Open();
SqlCommand command3 = connection3.CreateCommand();
SqlTransaction transaction3 =
connection3.BeginTransaction(IsolationLevel.ReadCommitted);
command3.Transaction = transaction3;
command3.CommandText =
"SELECT ID, valueCol FROM TestSnapshot";
command3.CommandTimeout = 4;
try
{
SqlDataReader sqldatareader3 = command3.ExecuteReader();
while (sqldatareader3.Read())
{
Console.WriteLine("You should never hit this.");
}
transaction3.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Expected timeout expired exception: "
+ ex.Message);
transaction3.Rollback();
}
}
// Open a fourth connection to AdventureWorks and
// initiate a fourth transaction to read from TestSnapshot
// using the ReadUncommitted isolation level. ReadUncommitted
// will not hit the table lock, and will allow a dirty read
// of the proposed new value 22 for valueCol. If the first
// transaction rolls back, this value will never actually have
// existed in the database.
using (SqlConnection connection4 = new SqlConnection(connectionString))
{
connection4.Open();
SqlCommand command4 = connection4.CreateCommand();
SqlTransaction transaction4 =
connection4.BeginTransaction(IsolationLevel.ReadUncommitted);
command4.Transaction = transaction4;
command4.CommandText =
"SELECT ID, valueCol FROM TestSnapshot";
SqlDataReader reader4 = command4.ExecuteReader();
while (reader4.Read())
{
Console.WriteLine("Expected 1,22 Actual "
+ reader4.GetValue(0).ToString()
+ "," + reader4.GetValue(1).ToString());
}
transaction4.Commit();
}
// Roll back the first transaction
transaction1.Rollback();
}
// CLEANUP
// Delete the TestSnapshot table and set
// ALLOW_SNAPSHOT_ISOLATION OFF
using (SqlConnection connection5 = new SqlConnection(connectionString))
{
connection5.Open();
SqlCommand command5 = connection5.CreateCommand();
command5.CommandText = "DROP TABLE TestSnapshot";
SqlCommand command6 = connection5.CreateCommand();
command6.CommandText =
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF";
try
{
command5.ExecuteNonQuery();
command6.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Console.WriteLine("Done!");
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file, using the
// System.Configuration.ConfigurationSettings.AppSettings property
return "Data Source=localhost;Initial Catalog=AdventureWorks;"
+ "Integrated Security=SSPI";
}
}
範例
下列範例示範修改資料時，快照集隔離的行為。 此程式碼會執行下列動作：
連線至 AdventureWorks 範例資料庫並啟用 SNAPSHOT 隔離。
建立名為 TestSnapshotUpdate 的資料表並插入三列範例資料。
會使用 SNAPSHOT 隔離來開始 (但不會完成) sqlTransaction1。 交易中已選取三列資料。
為 AdventureWorks 建立第二個 SqlConnection，並建立第二個使用 READ COMMITTED 隔離等級的交易，其會更新 sqlTransaction1 中所選取其中一個資料列內的值。
提交 sqlTransaction2。
回到 sqlTransaction1，並嘗試更新 sqlTransaction1 已提交的同一個資料列。 會引發錯誤 3960，且 sqlTransaction1 會自動回復。 SqlException.Number 及 SqlException.Message 顯示在 [主控台] 視窗中。
執行清除程式碼以關閉 AdventureWorks 中的快照集隔離，並刪除 TestSnapshotUpdate 資料表。
using Microsoft.Data.SqlClient; using System.Data.Common; class Program { static void Main() { // Assumes GetConnectionString returns a valid connection string // where pooling is turned off by setting Pooling=False;. string connectionString = GetConnectionString(); using (SqlConnection connection1 = new SqlConnection(connectionString)) { connection1.Open(); SqlCommand command1 = connection1.CreateCommand(); // Enable Snapshot isolation in AdventureWorks command1.CommandText = "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON"; try { command1.ExecuteNonQuery(); Console.WriteLine( "Snapshot Isolation turned on in AdventureWorks."); } catch (Exception ex) { Console.WriteLine("ALLOW_SNAPSHOT_ISOLATION ON failed: {0}", ex.Message); } // Create a table command1.CommandText = "IF EXISTS " + "(SELECT * FROM sys.tables " + "WHERE name=N'TestSnapshotUpdate')" + " DROP TABLE TestSnapshotUpdate"; command1.ExecuteNonQuery(); command1.CommandText = "CREATE TABLE TestSnapshotUpdate " + "(ID int primary key, CharCol nvarchar(100));"; try { command1.ExecuteNonQuery(); Console.WriteLine("TestSnapshotUpdate table created."); } catch (Exception ex) { Console.WriteLine("CREATE TABLE failed: {0}", ex.Message); } // Insert some data command1.CommandText = "INSERT INTO TestSnapshotUpdate VALUES (1,N'abcdefg');" + "INSERT INTO TestSnapshotUpdate VALUES (2,N'hijklmn');" + "INSERT INTO TestSnapshotUpdate VALUES (3,N'opqrstuv');"; try { command1.ExecuteNonQuery(); Console.WriteLine("Data inserted TestSnapshotUpdate table."); } catch (Exception ex) { Console.WriteLine(ex.Message); } // Begin, but do not complete, a transaction // using the Snapshot isolation level. SqlTransaction transaction1 = null; try { transaction1 = connection1.BeginTransaction(IsolationLevel.Snapshot); command1.CommandText = "SELECT * FROM TestSnapshotUpdate WHERE ID BETWEEN 1 AND 3"; command1.Transaction = transaction1; command1.ExecuteNonQuery(); Console.WriteLine("Snapshot transaction1 started."); // Open a second Connection/Transaction to update data // using ReadCommitted. This transaction should succeed. using (SqlConnection connection2 = new SqlConnection(connectionString)) { connection2.Open(); SqlCommand command2 = connection2.CreateCommand(); command2.CommandText = "UPDATE TestSnapshotUpdate SET CharCol=" + "N'New value from Connection2' WHERE ID=1"; SqlTransaction transaction2 = connection2.BeginTransaction(IsolationLevel.ReadCommitted); command2.Transaction = transaction2; try { command2.ExecuteNonQuery(); transaction2.Commit(); Console.WriteLine( "transaction2 has modified data and committed."); } catch (SqlException ex) { Console.WriteLine(ex.Message); transaction2.Rollback(); } finally { transaction2.Dispose(); } } // Now try to update a row in Connection1/Transaction1. // This transaction should fail because Transaction2 // succeeded in modifying the data. command1.CommandText = "UPDATE TestSnapshotUpdate SET CharCol=" + "N'New value from Connection1' WHERE ID=1"; command1.Transaction = transaction1; command1.ExecuteNonQuery(); transaction1.Commit(); Console.WriteLine("You should never see this."); } catch (SqlException ex) { Console.WriteLine("Expected failure for transaction1:"); Console.WriteLine(" {0}: {1}", ex.Number, ex.Message); } finally { transaction1.Dispose(); } } // CLEANUP: // Turn off Snapshot isolation and delete the table using (SqlConnection connection3 = new SqlConnection(connectionString)) { connection3.Open(); SqlCommand command3 = connection3.CreateCommand(); command3.CommandText = "ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"; try { command3.ExecuteNonQuery(); Console.WriteLine( "CLEANUP: Snapshot isolation turned off in AdventureWorks."); } catch (Exception ex) { Console.WriteLine("CLEANUP FAILED: {0}", ex.Message); } command3.CommandText = "DROP TABLE TestSnapshotUpdate"; try { command3.ExecuteNonQuery(); Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted."); } catch (Exception ex) { Console.WriteLine("CLEANUP FAILED: {0}", ex.Message); } } Console.WriteLine("Done"); Console.ReadLine(); } static private string GetConnectionString() { // To avoid storing the connection string in your code, // you can retrieve it from a configuration file, using the // System.Configuration.ConfigurationSettings.AppSettings property return "Data Source=(local);Initial Catalog=AdventureWorks;" + "Integrated Security=SSPI;Pooling=false"; } }
在快照隔離中使用鎖定提示
在上一個範例中，第一個交易會選取資料，第二個交易可以在第一個交易即將完成之前更新資料，進而導致當第一個交易嘗試更新相同的資料列時發生更新衝突。 您可以在交易一開始提供鎖定提示，以降低長時間執行的快照交易中發生更新衝突的機率。 下列 SELECT 陳述式使用 UPDLOCK 提示來鎖定選取的資料列：
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
使用 UPDLOCK 鎖定提示會封鎖任何在第一個交易完成之前嘗試更新這些資料列的作業。 這可確保選取的資料列之後在交易中更新時不會發生衝突。 請參閱《SQL Server 線上叢書》中的＜鎖定提示＞。
如果您的應用程式有許多衝突，快照隔離可能就不是最佳選擇。 提示應該只在真正需要時才使用。 您的應用程式不應該設計成會持續依賴鎖定提示來執行其作業。
後續步驟
- SQL Server and ADO.NET (SQL Server 和 ADO.NET)
- 交易鎖定與資料列版本設定指南