SQL Server 中的快照集隔離
快照集隔離可增強 OLTP 應用程式的並行存取。
瞭解快照集隔離及資料列版本控制
一旦啟用快照集隔離，則必須維護每筆交易所更新的資料列版本。 在 SQL Server 2019 之前，這些版本會儲存在 tempdb 中。 SQL Server 2019 引進了一項新功能：加速資料庫復原 (ADR)，需要其自己的一組資料列版本。 因此，從 SQL Server 2019 起，如果未啟用 ADR，資料列版本一律會保留在 tempdb 中。 如果已啟用 ADR，則與快照集隔離和 ADR 相關的所有資料列版本都會保留在 ADR 的永續性版本存放區 (PVS) 中，其位於使用者指定的檔案群組的使用者資料庫中。 唯一的交易序號會識別每個交易，而且會針對每個資料列版本記錄這些唯一的編號。 交易適用於在交易序號之前具有序號的最新資料列版本。 交易會忽略在交易開始之後所建立的較新資料列版本。
「快照集」一詞反映了交易中的所有查詢都會根據交易開始時資料庫的狀態，看到資料庫的相同版本或快照集。 在快照集交易中的基礎資料列或資料頁面上沒有鎖定，這允許其他交易執行，而不會被之前未完成的交易封鎖。 修改資料的交易不會封鎖讀取資料的交易，而讀取資料的交易不會封鎖寫入資料的交易，因為它們通常會在 SQL Server 中預設的 READ COMMITTED 隔離等級之下。 此非封鎖性的行為也會大幅降低複雜交易發生死結的可能性。
快照集隔離會使用開放式並行存取模型。 如果快照集交易嘗試認可自交易開始後已變更的資料修改，交易將會回復並引發錯誤。 您可以針對存取要修改之資料的 SELECT 陳述式使用 UPDLOCK 提示，以避免發生這種情況。 如需詳細資訊，請參閱提示 (Transact-SQL)。
在交易中使用快照集隔離之前，必須先透過設定 [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]，您必須明確設定每個工作階段的快照集隔離等級，才能存取已建立版本的資料列。
使用隔離等級管理並行存取
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 隔離等級的同時，還引進了 SQL-92 隔離等級的延伸模組及 READ COMMITTED 的其他實作。 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 物件。
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
範例
下列範例示範不同的隔離等級如何透過嘗試存取鎖定的資料來運作，而且這不適合在實際執行的程式碼中使用。
該程式碼會連線至 SQL Server 中的 AdventureWorks 範例資料庫，並會建立名為 TestSnapshot 的資料表並插入一個資料列。 此程式碼會使用 ALTER DATABASE Transact-SQL 陳述式來開啟資料庫的快照集隔離，但不會設定 [READ_COMMITTED_SNAPSHOT 選項]，讓預設的 READ COMMITTED 隔離等級行為生效。 然後此程式碼會執行下列動作：
其會開始但不會完成 sqlTransaction1，這會使用 SERIALIZABLE 隔離等級來啟動更新交易。 這具有鎖定資料表的效果。
其會開啟第二個連線並使用 SNAPSHOT 隔離等級初始化第二筆交易，以讀取 TestSnapshot 資料表中的資料。 因為已啟用快照集隔離，所以此交易可以讀取 sqlTransaction1 開始之前已存在的資料。
其會開啟第三個連線，並使用 READ COMMITTED 隔離等級來起始交易，以嘗試讀取資料表中的資料。 在這種情況下，程式碼無法讀取資料，原因為第一個交易在資料表上設定的鎖定阻止其讀取資料並發生逾時。如果使用 REPEATABLE READ 及 SERIALIZABLE 隔離層級也會導致相同的結果，原因為第一個交易設定的鎖定也會阻止這些隔離層級進行讀取。
其會開啟第四個連線，並使用 READ UNCOMMITTED 隔離等級來起始交易，這會在 sqlTransaction1 中執行未認可值的中途讀取。 如果未認可第一個交易，這個值可能永遠不會實際存在於資料庫中。
其會復原第一筆交易，並藉由刪除 TestSnapshot 資料表及停用 AdventureWorks 資料庫的快照集隔離來進行清除。
注意
下列範例會使用相同的連接字串，但會關閉連線共用。 如果連線已共用，重設其隔離等級並不會重設伺服器的隔離等級。 因此，使用相同共用內部連線的後續連線，會以其設定至共用連線的隔離等級啟動。 關閉連線共用的替代方法是為每個連線明確設定隔離等級。
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(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(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)
+ "," + reader2.GetValue(1));
}
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(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(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)
+ "," + reader4.GetValue(1));
}
transaction4.Commit();
}
// Roll back the first transaction
transaction1.Rollback();
}
// CLEANUP
// Delete the TestSnapshot table and set
// ALLOW_SNAPSHOT_ISOLATION OFF
using (SqlConnection connection5 = new(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!");
' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()
Using connection1 As New SqlConnection(connectionString)
' Drop the TestSnapshot table if it exists
connection1.Open()
Dim command1 As SqlCommand = connection1.CreateCommand
command1.CommandText = "IF EXISTS " & _
"(SELECT * FROM sys.tables WHERE name=N'TestSnapshot') " _
& "DROP TABLE TestSnapshot"
Try
command1.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
' 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.
Dim transaction1 As SqlTransaction = _
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
Dim connection2 As SqlConnection = New SqlConnection(connectionString)
Using connection2
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.
Dim command2 As SqlCommand = connection2.CreateCommand()
Dim transaction2 As SqlTransaction = _
connection2.BeginTransaction(IsolationLevel.Snapshot)
command2.Transaction = transaction2
command2.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
Dim reader2 As SqlDataReader = _
command2.ExecuteReader()
While reader2.Read()
Console.WriteLine("Expected 1,1 Actual " _
& reader2.GetValue(0).ToString() + "," _
& reader2.GetValue(1).ToString())
End While
transaction2.Commit()
End Using
' Open a third connection to AdventureWorks and
' initiate a third transaction to read from TestSnapshot
' using the 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.
Dim connection3 As SqlConnection = New SqlConnection(connectionString)
Using connection3
connection3.Open()
Dim command3 As SqlCommand = connection3.CreateCommand()
Dim transaction3 As SqlTransaction = _
connection3.BeginTransaction(IsolationLevel.ReadCommitted)
command3.Transaction = transaction3
command3.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
command3.CommandTimeout = 4
Try
Dim reader3 As SqlDataReader = command3.ExecuteReader()
While reader3.Read()
Console.WriteLine("You should never hit this.")
End While
transaction3.Commit()
Catch ex As Exception
Console.WriteLine("Expected timeout expired exception: " _
& ex.Message)
transaction3.Rollback()
End Try
End Using
' 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. If the first transaction
' transaction rolls back, this value will never actually have
' existed in the database.
Dim connection4 As SqlConnection = New SqlConnection(connectionString)
Using connection4
connection4.Open()
Dim command4 As SqlCommand = connection4.CreateCommand()
Dim transaction4 As SqlTransaction = _
connection4.BeginTransaction(IsolationLevel.ReadUncommitted)
command4.Transaction = transaction4
command4.CommandText = _
"SELECT ID, valueCol FROM TestSnapshot"
Dim reader4 As SqlDataReader = _
command4.ExecuteReader()
While reader4.Read()
Console.WriteLine("Expected 1,22 Actual " _
& reader4.GetValue(0).ToString() _
& "," + reader4.GetValue(1).ToString())
End While
transaction4.Commit()
' Rollback transaction1
transaction1.Rollback()
End Using
End Using
' CLEANUP
' Drop TestSnapshot table and set
' ALLOW_SNAPSHOT_ISOLATION OFF for AdventureWorks
Dim connection5 As New SqlConnection(connectionString)
Using connection5
connection5.Open()
Dim command5 As SqlCommand = connection5.CreateCommand()
command5.CommandText = "DROP TABLE TestSnapshot"
Dim command6 As SqlCommand = connection5.CreateCommand()
command6.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
Try
command5.ExecuteNonQuery()
command6.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
範例
下列範例示範修改資料時，快照集隔離的行為。 此程式碼會執行下列動作：
連線至 AdventureWorks 範例資料庫並啟用 SNAPSHOT 隔離。
建立名為 TestSnapshotUpdate 的資料表並插入三列範例資料。
會使用 SNAPSHOT 隔離來開始 (但不會完成) sqlTransaction1。 已在交易中選取了三個資料列。
為 AdventureWorks 建立第二個 SqlConnection，並建立第二個使用 READ COMMITTED 隔離等級的交易，其會更新 sqlTransaction1 中所選取其中一個資料列內的值。
認可 sqlTransaction2。
返回 sqlTransaction1，並嘗試更新 sqlTransaction1 已認可的相同資料列。 會引發錯誤 3960，並自動復原 sqlTransaction1。 SqlException.Number 及 SqlException.Message 顯示在 [主控台] 視窗中。
執行清除程式碼以關閉 AdventureWorks 中的快照集隔離，並刪除 TestSnapshotUpdate 資料表。
// Assumes GetConnectionString returns a valid connection string
// where pooling is turned off by setting Pooling=False;.
var connectionString = GetConnectionString();
using (SqlConnection connection1 = new(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 = default!;
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(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(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);
}
}
' Assumes GetConnectionString returns a valid connection string
' where pooling is turned off by setting Pooling=False;.
Dim connectionString As String = GetConnectionString()
Using connection1 As New SqlConnection(connectionString)
' Enable Snapshot isolation in AdventureWorks
connection1.Open()
Dim command1 As SqlCommand = connection1.CreateCommand
command1.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION ON;"
Try
command1.ExecuteNonQuery()
Console.WriteLine( _
"Snapshot Isolation turned on in AdventureWorks.")
Catch ex As Exception
Console.WriteLine("ALLOW_SNAPSHOT_ISOLATION failed: {0}", ex.Message)
End Try
' Create a table
command1.CommandText = _
"IF EXISTS (SELECT * FROM sys.databases " _
& "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 ex As Exception
Console.WriteLine("CREATE TABLE failed: {0}", ex.Message)
End Try
' 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 ex As Exception
Console.WriteLine(ex.Message)
End Try
' Begin, but do not complete, a transaction
' using the Snapshot isolation level
Dim transaction1 As SqlTransaction = Nothing
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.
Dim connection2 As SqlConnection = New SqlConnection(connectionString)
Using connection2
connection2.Open()
Dim command2 As SqlCommand = connection2.CreateCommand()
command2.CommandText = "UPDATE TestSnapshotUpdate SET " _
& "CharCol=N'New value from Connection2' WHERE ID=1"
Dim transaction2 As SqlTransaction = _
connection2.BeginTransaction(IsolationLevel.ReadCommitted)
command2.Transaction = transaction2
Try
command2.ExecuteNonQuery()
transaction2.Commit()
Console.WriteLine( _
"transaction2 has modified data and committed.")
Catch ex As SqlException
Console.WriteLine(ex.Message)
transaction2.Rollback()
Finally
transaction2.Dispose()
End Try
End Using
' 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 ex As SqlException
Console.WriteLine("Expected failure for transaction1:")
Console.WriteLine(" {0}: {1}", ex.Number, ex.Message)
Finally
transaction1.Dispose()
End Try
End Using
' CLEANUP:
' Turn off Snapshot isolation and delete the table
Dim connection3 As New SqlConnection(connectionString)
Using connection3
connection3.Open()
Dim command3 As SqlCommand = connection3.CreateCommand()
command3.CommandText = _
"ALTER DATABASE AdventureWorks SET ALLOW_SNAPSHOT_ISOLATION OFF"
Try
command3.ExecuteNonQuery()
Console.WriteLine( _
"Snapshot isolation turned off in AdventureWorks.")
Catch ex As Exception
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
End Try
command3.CommandText = "DROP TABLE TestSnapshotUpdate"
Try
command3.ExecuteNonQuery()
Console.WriteLine("TestSnapshotUpdate table deleted.")
Catch ex As Exception
Console.WriteLine("CLEANUP FAILED: {0}", ex.Message)
End Try
End Using
搭配使用鎖定提示與快照集隔離
在上一個範例中，第一個交易會選取資料，第二個交易可以在第一個交易即將完成之前更新資料，進而導致當第一個交易嘗試更新相同的資料列時發生更新衝突。 您可以透過在交易開始時提供鎖定提示，以減少長時間執行之快照集交易中發生更新衝突的機會。 下列 SELECT 陳述式使用 UPDLOCK 提示來鎖定選取的資料列：
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
使用 UPDLOCK 鎖定提示會封鎖在第一個交易完成之前嘗試更新資料列的任何資料列。 這可確保選取的資料列之後在交易中更新時不會發生衝突。 如需詳細資訊，請參閱提示 (Transact-SQL)。
如果您的應用程式有許多衝突，快照集隔離可能就不是最佳選擇。 提示應該只在真正需要時才使用。 您的應用程式不應該設計成會持續依賴鎖定提示來執行其作業。
另請參閱
- SQL Server and ADO.NET (SQL Server 和 ADO.NET)
- ADO.NET 概觀 \(部分機器翻譯\)
- 交易鎖定與資料列版本設定指南