快照隔离增强了 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 选项来启用它。 这样将激活在临时数据库 (tempdb) 中存储行版本的机制。 必须在将快照隔离与 Transact-SQL ALTER DATABASE 语句一起使用的每个数据库中启用快照隔离。 在这方面，快照隔离与传统的隔离级别（READ COMMITTED、REPEATABLE READ、SERIALIZABLE 和 READ UNCOMMITTED）不同，这些级别不需要配置。 以下语句激活快照隔离并将默认的 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 下执行的事务可以读取其他事务尚未提交的修改数据值；这称为“脏”读。
SQL Server 的默认隔离级别为 READ COMMITTED。 它通过指定语句不能读取已由其他事务修改但尚未提交的数据值来防止脏读。 其他事务仍然可以在当前事务的各个执行语句之间修改、插入或删除数据，从而产生不可重复读取或“虚拟”数据。
REPEATABLE READ 是比 READ COMMITTED 更严格的隔离级别。 它包含 READ COMMITTED，另外还指定在当前事务提交之前，任何其他事务都不能修改或删除已由当前事务读取的数据。 由于读取数据上的共享锁一直保持到事务结束，而不是在每个语句结束时释放，因此并发级别低于 READ COMMITTED。
SERIALIZABLE 是限制最多的隔离级别，因为它锁定了键的整个范围，并在事务完成之前一直保持锁定状态。 它包含 REPEATABLE READ 并添加了限制，即在事务完成之前，其他事务不能将新行插入到事务已读取的范围中。
有关详细信息，请参阅事务锁定和行版本控制指南。
快照隔离级别扩展
SQL Server 通过引入 SNAPSHOT 隔离级别并另外实现了 READ COMMITTED，引入对 SQL-92 隔离级别的扩展。 新的 READ_COMMITTED_SNAPSHOT 隔离级别可以透明的方式替换所有事务的 READ COMMITTED。
快照隔离指定事务内读取的数据永远不会反映其他同步事务所做的更改。 事务使用事务开始时存在的数据行版本。 读取数据时不会对数据进行锁定，因此快照事务不会阻止其他事务写入数据。 写入数据的事务也不会阻止快照事务读取数据。 需要通过设置 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 中使用快照隔离
SqlTransaction 类支持 ADO.NET 中的快照隔离。 如果数据库已启用了快照隔离，但是未配置 READ_COMMITTED_SNAPSHOT ON，必须在调用 SqlTransaction 方法时，使用 IsolationLevel.Snapshot 枚举值启动 BeginTransaction。 此代码段假定连接是打开的 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 的表并插入三行示例数据。
使用快照隔离开始 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: {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)。
如果应用程序有很多冲突，则快照隔离可能不是最佳选择。 只应在真正需要时使用提示。 应用程序的设计不应使其操作经常依赖于锁提示。