了解快照隔离和行版本控制
启用快照隔离之后，每个事务的已更新行版本在 tempdb 中维护。唯一的事务序列号标识每个事务，并且为每个行版本记录这些唯一的编号。事务使用序列号在事务序列号之前的最新行版本。事务将忽略在事务开始之后创建的更新的行版本。
“快照”一词反映的情况是：事务中的所有查询根据事务开始那一刻数据库的状态，看到数据库的相同版本（即快照）。不会在快照事务中的基础数据行或数据页上获取锁，这样可以执行其他事务，而不会被以前未完成的事务所阻止。修改数据的事务不会阻止读取数据的事务，读取数据的事务不会阻止写入数据的事务，就好像通常情况下在 SQL Server 中使用默认的 READ COMMITTED 隔离级别一样。这种无阻止的行为也大大降低了复杂事务出现死锁的可能性。
快照隔离使用开放式并发模型。如果快照事务尝试提交对事务开始后更改过的数据的修改，事务将回滚并将引发错误。对访问要修改的数据的 SELECT 语句使用 UPDLOCK 提示，可以避免此问题。有关更多信息，请参见“SQL Server 联机图书”中的“锁定提示”。
在事务中使用快照隔离之前，必须先通过设置 ALLOW_SNAPSHOT_ISOLATION
other
复制
ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION
设置 READ_COMMITTED_SNAPSHOT ON 选项后，可以使用默认的 READ COMMITTED 隔离级别访问版本化的行。如果 READ_COMMITTED_SNAPSHOT 选项设置为 OFF，必须为每个会话显式设置 Snapshot 隔离级别，以便访问版本化的行。
使用隔离级别管理并发性
执行 Transact-SQL 语句所使用的隔离级别确定其锁定行为和行版本化行为。隔离级别的作用域为整个连接，使用 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 Server 2005 通过引入 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 对象。
C#
VB
复制
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 数据库的快照隔离来进行清理。
C#
VB
复制
// 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!");
示例
以下示例演示修改数据时的快照隔离行为。该代码执行下列操作：
- 连接到 AdventureWorks 示例数据库并启用 SNAPSHOT 隔离。
- 创建一个名为 TestSnapshotUpdate 的表并插入三行示例数据。
- 使用 SNAPSHOT 隔离开始但是不完成 sqlTransaction1。在事务中选择三行数据。
- 创建第二个与 AdventureWorks 的 SqlConnection，并使用 READ COMMITTED 隔离级别创建第二个事务，更新在 sqlTransaction1 中选择的其中一行的值。
- 提交 sqlTransaction2。
- 返回 sqlTransaction1 并尝试更新 sqlTransaction1 已提交的相同的行。将引发 3960 错误，sqlTransaction1 将自动回滚。控制台窗口中将显示 SqlException.Number 和 SqlException.Message。
- 执行清理代码以禁用 AdventureWorks 中的快照隔离并删除 TestSnapshotUpdate 表。
C#
VB
复制
// 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);
}
}
对快照隔离使用锁提示
在前面的示例中，第一个事务选择数据，第二个事务在第一个事务完成前更新数据，在第一个事务尝试更新相同行时造成更新冲突。通过在事务开始时提供锁提示，可以降低在需要很长时间的快照事务中发生更新冲突的机率。以下 SELECT 语句使用 UPDLOCK 提示锁定所选行：
other
复制
SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK)
WHERE PriKey BETWEEN 1 AND 3
使用 UPDLOCK 锁提示将阻止在第一个事务完成之前尝试更新行的任何行。这样可以保证所选行以后在事务中进行更新时不会发生冲突。请参见“SQL Server 联机图书”中的“锁定提示”。
如果应用程序中存在许多冲突，快照隔离也许不是最佳的选择。只有在确实需要时，才应使用提示。应用程序的设计不应使其操作始终依赖于锁提示。
标签：command1,事务,版本控制,快照,隔离,READ,和行,SNAPSHOT From： https://blog.51cto.com/u_15834343/5889904