使用快照隔离snapshot isolation实例（二）

 

示例

以下示例演示修改数据时的快照隔离行为。该代码执行下列操作：

• 连接到 AdventureWorks 示例数据库并启用 SNAPSHOT 隔离。

• 创建一个名为 TestSnapshotUpdate 的表并插入三行示例数据。

• 使用 SNAPSHOT 隔离开始但是不完成 sqlTransaction1。在事务中选择三行数据。

• 创建第二个与 AdventureWorks 的 SqlConnection，并使用 READ COMMITTED 隔离级别创建第二个事务，更新在 sqlTransaction1 中选择的其中一行的值。

• 提交 sqlTransaction2。

• 返回 sqlTransaction1 并尝试更新 sqlTransaction2 已提交的相同的行。将引发 3960 错误，sqlTransaction1 将自动回滚。控制台窗口中将显示 SqlException.Number 和 SqlException.Message。

• 执行清理代码以禁用 AdventureWorks 中的快照隔离并删除 TestSnapshotUpdate 表。

 

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; namespace snapshot2 { class Program { static void Main(string[] args) { string connectionString = @"Data Source=XUWEI/SQLEXPRESS;Initial Catalog=AdventureWorks;User ID=sa;Password=sa12345"; using (SqlConnection connection1 = new SqlConnection(connectionString)) { connection1.Open(); SqlCommand command1 = connection1.CreateCommand(); // Enable Snapshot isolation in AdventureWorks，设置数据库使其支持快照隔离(Snapshot isolation)技术。 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. /*打开第二个数据库连接，并创建一个在ReadCommitted上更新数据的事务。 * 这个事务能够成功。 */ 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();//提交事务2 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. /*试图更新连接1和事务1中的一行数据。这个事务执行失败， * 这是因为在事务2中已经成功修改了数据。 * 事务1将自动回滚。 */ 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); } } } } }