語言
快照隔離可以提升 OLTP 應用程式的並發性。
瞭解快照集隔離和資料列版本管理
啟用快照集隔離之後,必須維護每個交易的更新數據列版本。 在 SQL Server 2019 之前,這些版本儲存在 tempdb。 SQL Server 2019 引進了一項新功能加速資料庫復原 (ADR),其需要自己的一組數據列版本。 因此,從 SQL Server 2019 起,如果沒有啟用 ADR,列版本會照常保留 tempdb 。 如果已啟用 ADR,則所有與快照隔離和 ADR 相關的數據列版本都會保留在 ADR 的持續性版本存放區 (PVS)中,該存放區位於使用者指定的檔案群組中的用戶資料庫中。 唯一的交易序號會識別每個交易,而且會針對每個資料列版本記錄這些唯一的編號。 交易適用於在交易序號之前具有序號的最新資料列版本。 交易會忽略在交易開始之後所建立的較新資料列版本。
「快照集」一詞反映了交易中的所有查詢都會根據交易開始時資料庫的狀態,看到資料庫的相同版本或快照集。 快照集交易中的基礎數據列或數據頁不會取得任何鎖定,這可允許其他交易執行,而不會遭到先前未完成的交易封鎖。 修改資料的交易不會阻擋讀取資料的交易,而讀取資料的交易也不會阻擋寫入資料的交易,這在 SQL Server 預設隔離層下通常會發生READ COMMITTED。 此非封鎖性的行為也會大幅降低複雜交易發生死結的可能性。
快照隔離使用了樂觀並行控制模型。 如果快照交易嘗試提交自交易開始以來已變更的資料修改,該交易將會回滾並引發錯誤。 你可以透過使用 UPDLOCK 提示來避免這種情況,用於存取將被修改之資料的 SELECT 陳述式。 如需詳細資訊,請參閱 提示 (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隔離層級及額外的實作READ COMMITTED,為 SQL-92 的隔離層級引入了擴充功能。
SNAPSHOT交易隔離規定交易中讀取的資料永遠不會反映其他同時進行的交易所做的變更。 交易會使用在交易開始時已存在的資料列版本。 讀取資料時不會被鎖,因此SNAPSHOT交易不會阻止其他交易寫入資料。 寫入資料的交易不會封鎖快照交易讀取資料。 你需要透過設定 ALLOW_SNAPSHOT_ISOLATION 資料庫選項來啟用快照隔離,才能使用它。資料庫
READ_COMMITTED_SNAPSHOT選項控制如何READ COMMITTED讀取資料。 使用這個選項OFF時,這是 SQL Server 中的預設設定,資料庫引擎 會使用共享鎖來保護讀取。 使用此選項ON,READ COMMITTED會使用陳述式層級的資料列版本來取代共用鎖,以保護讀取。
快照隔離和行版本控制的運作原理
當SNAPSHOT隔離層啟用時,每次資料列更新時,SQL Server Database Engine 會將原始資料列的副本存於 tempdb,並在該資料列中加入交易序號。 以下是事件的發生順序:
系統會起始新交易,並指派交易序號給新交易。
資料庫引擎會在處理交易過程中讀取一列,然後從
tempdb中取得其序號最接近且低於該交易序列號的列版本。資料庫引擎會檢查交易序號在快照交易開始時,是否不在當時作用中的未提交交易的交易序號清單中。
交易會讀取該列
tempdb中截至交易開始時為當前的版本。 不會在交易開始之後看到插入的新資料列,因為這些序號值將會比交易序號值高。目前的交易能夠存取交易開始後被刪除的列,因為在
tempdb中會有一個序列號較低的列版本。
快照集隔離的淨效應是交易會看到交易開始時已存在的所有資料,而不會在底層資料表上接受或進行任何鎖定。 這能夠會在發生競爭的情況下造成效能提升。
快照交易一律採用樂觀並行控制,不保留會阻礙其他交易更新資料列的鎖定。 如果快照集交易嘗試提交對資料列的更新,而該資料列在交易開始後被更改,則該交易將被回滾,並產生錯誤。
在 ADO.NET 中使用快照隔離
要啟動帶有快照隔離的交易,為資料庫啟用 ALLOW_SNAPSHOT_ISOLATION,並將 Snapshot 傳遞給 BeginTransaction。
READ_COMMITTED_SNAPSHOT資料庫選項會控制讀取已認可隔離層級的資料列版本化讀取;但不會為交易選取快照隔離。 以下片段假設 connection 是 SqlConnection 類別的一個開啟實例。
Dim sqlTran As SqlTransaction = _
connection.BeginTransaction(IsolationLevel.Snapshot)
SqlTransaction sqlTran =
connection.BeginTransaction(IsolationLevel.Snapshot);
範例
下列範例示範不同的隔離等級如何透過嘗試存取鎖定的資料來運作,而且這不適合在實際執行的程式碼中使用。
程式碼會連接到 SQL Server 中的範例資料庫,建立名為 AdventureWorks 的資料表,並插入一列資料。 程式碼使用 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";
using (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
{
using (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";
using (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"
Using reader2 As SqlDataReader = command2.ExecuteReader()
While reader2.Read()
Console.WriteLine("Expected 1,1 Actual " _
& reader2.GetValue(0).ToString() + "," _
& reader2.GetValue(1).ToString())
End While
End Using
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
Using reader3 As SqlDataReader = command3.ExecuteReader()
While reader3.Read()
Console.WriteLine("You should never hit this.")
End While
End Using
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"
Using reader4 As SqlDataReader = command4.ExecuteReader()
While reader4.Read()
Console.WriteLine("Expected 1,22 Actual " _
& reader4.GetValue(0).ToString() _
& "," + reader4.GetValue(1).ToString())
End While
End Using
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。 已在交易中選取了三個資料列。建立第二個從
SqlConnection到AdventureWorks,並利用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: {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: {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($" {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: {ex.Message}");
}
command3.CommandText = "DROP TABLE TestSnapshotUpdate";
try
{
command3.ExecuteNonQuery();
Console.WriteLine("CLEANUP: TestSnapshotUpdate table deleted.");
}
catch (Exception ex)
{
Console.WriteLine($"CLEANUP FAILED: {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 概觀
- 交易鎖定與資料列版本設定指南