语言
快照隔离增强了 OLTP 应用程序的并发性。
了解快照隔离和行版本控制
启用快照隔离后,必须维护每个事务的更新行版本。 在 SQL Server 2019 之前,这些版本存储在 tempdb 中。 SQL Server 2019 引入了一项新功能,即加速数据库恢复(ADR),它需要自己的行版本集。 因此,从 SQL Server 2019 起,如果未启用 ADR,则行版本将一如既往地保留 tempdb 。 如果启用了 ADR,则与快照隔离和 ADR 相关的所有行版本都保存在 ADR 的持久版本存储(PVS)中,该存储位于用户指定的文件组中的用户数据库中。 唯一的事务序列号标识每个事务,并为每个行版本记录这些唯一的编号。 事务处理的最新行版本在事务序列号之前有一个序列号。 事务将忽略在事务开始后创建的新行版本。
术语“快照”反映的事实是,事务中的所有查询都能看到数据库的相同版本或快照,这取决于事务开始时数据库的状态。 不会在快照事务中的基础数据行或数据页上获取锁,这样可以执行其他事务,而不会被以前未完成的事务所阻止。 修改数据的事务不会阻止读取数据的事务,读取数据的事务也不会阻止写入数据的事务,而 SQL Server 中默认的 READ COMMITTED 隔离级别通常会导致阻塞。 这种非阻塞行为也大大降低了复杂事务出现死锁的可能性。
快照隔离使用开放式并发模型。 如果快照事务尝试提交对事务开始后已更改的数据所做的修改,事务将回滚,并引发错误。 您可以通过对访问待修改数据的 SELECT 语句使用 UPDLOCK 提示来避免这种情况。 有关详细信息,请参阅 Hints (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-92 标准中定义了四个隔离级别,这四个隔离级别在早期版本的 SQL Server 中受支持:
READ UNCOMMITTED是限制最少的隔离级别,因为它忽略其他事务放置的锁。 在READ UNCOMMITTED下执行的事务可以读取其他事务尚未提交的已修改数据值;这些称为“脏读”。READ COMMITTED是SQL Server的默认隔离级别。 它通过指定语句不能读取已由其他事务修改但尚未提交的数据值来防止脏读。 其他事务仍然可以在当前事务的各个执行语句之间修改、插入或删除数据,从而产生不可重复读取或“虚拟”数据。REPEATABLE READ是一种比READ COMMITTED限制更严格的隔离级别。 它包含READ COMMITTED并另外指定,在当前事务提交之前,任何其他事务都不能修改或删除当前事务读取的数据。 并发性低于READ COMMITTED,因为对读取数据的共享锁在事务期间保留,而不是在每个语句结束时释放。SERIALIZABLE是限制性最大的隔离级别,因为它锁定了整个键范围,并保留锁,直到事务完成。 它包含REPEATABLE READ并添加了限制,即其他事务无法在事务完成之前将新行插入到已由事务读取的范围中。
有关详细信息,请参阅事务锁定和行版本控制指南。
快照隔离级别扩展
SQL Server引入了对 SQL-92 隔离级别的扩展,并引入了SNAPSHOT隔离级别以及对READ COMMITTED的额外实现。
SNAPSHOT隔离级别规定,事务中读取的数据绝不会反映其他同时进行的事务所做的更改。 事务使用事务开始时存在的数据行版本。 读取数据时不会对数据加任何锁,因此SNAPSHOT事务不会阻塞其他事务写入数据。 写入数据的事务不会阻挡快照事务读取数据。 需要通过设置 ALLOW_SNAPSHOT_ISOLATION 数据库选项来启用快照隔离,才能使用它。数据库
READ_COMMITTED_SNAPSHOT选项控制READ COMMITTED如何读取数据。 当此选项为OFF(SQL Server 中的默认值)时,数据库引擎使用共享锁来保护读取。 使用此选项ON时,READ COMMITTED使用语句级行版本而不是共享锁来保护读取。
快照隔离和行版本化的工作原理
启用SNAPSHOT隔离级别后,每次更新行时,SQL Server 数据库引擎 将原始行的副本存储在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);
示例:
下面的示例演示不同的隔离级别在尝试访问锁定数据时的工作方式,并不建议在生产代码中使用。
该代码连接到 AdventureWorks SQL Server 中的示例数据库,创建一个名为 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";
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。 在事务中选择三行数据。创建到
AdventureWorks的第二个SqlConnection,并使用READ COMMITTED隔离级别创建第二个事务,该事务更新sqlTransaction1中所选行之一的值。提交
sqlTransaction2。返回到
sqlTransaction1,并尝试更新sqlTransaction1已提交的同一行。 会引发错误 3960,并且sqlTransaction1会自动回滚。 控制台窗口中显示SqlException.Message和SqlException.Number。执行清理代码以在
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 锁提示会阻止在第一个事务完成前尝试更新这些行的任何行。 这可确保所选行稍后在事务中更新时不会发生冲突。 有关详细信息,请参阅 Hints (Transact-SQL)。
如果您的应用程序存在许多冲突,那么快照隔离可能不是最佳选择。 只应在真正需要时使用提示。 应用程序的设计不应使其操作经常依赖于锁提示。