当前位置：首页 > news >正文

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

news 来源：原创 2024/5/20 21:38:46

示例

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

连接到 AdventureWorks 示例数据库并启用 SNAPSHOT 隔离。
创建一个名为 TestSnapshotUpdate 的表并插入三行示例数据。
使用 SNAPSHOT 隔离开始但是不完成 sqlTransaction1。在事务中选择三行数据。
创建第二个与 AdventureWorks 的 SqlConnection，并使用 READ COMMITTED 隔离级别创建第二个事务，更新在 sqlTransaction1 中选择的其中一行的值。
提交 sqlTransaction2。
返回 sqlTransaction1 并尝试更新 sqlTransaction2 已提交的相同的行。将引发 3960 错误，sqlTransaction1 将自动回滚。控制台窗口中将显示 SqlException.Number 和 SqlException.Message。
执行清理代码以禁用 AdventureWorks 中的快照隔离并删除 TestSnapshotUpdate 表。

View Code

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);
                }
            }
        }
    }
}