SQL Server でのスナップショット分離 (ADO.NET)

[アーティクル]
09/17/2008

更新 : November 2007

SQL Server 2005 では、新しいスナップショット分離レベルを導入し、OLTP アプリケーションの同時実行を強化しています。以前のバージョンの SQL Server では、同時実行は、一部のアプリケーションでブロックやデッドロックの問題を引き起こすロックだけに基づいていました。一方、スナップショット分離は、行バージョン管理の拡張機能に依存し、読み取り/書き込みブロックの問題を回避することによりパフォーマンスを向上させることを目的としています。

スナップショット分離と行バージョン管理について

スナップショット分離が有効になると、各トランザクションの更新された行のバージョン管理は、tempdb に保持されます。一意のトランザクションシーケンス番号が各トランザクションを識別し、これらの一意の番号がそれぞれの行バージョン用に記録されます。トランザクションは、シーケンス番号がトランザクションのシーケンス番号よりも前にある、最新の行バージョンを処理します。トランザクションが開始された後で作成された最新の行バーションは、トランザクションにより無視されます。

"スナップショット" という用語は、トランザクション内のすべてのクエリが、トランザクションの開始時点のデータベースの状態に基づいて、データベースの同じバージョン、つまりスナップショットを参照するという事実を表しています。ロックは、スナップショットトランザクション内の基になるデータ行やデータページでは取得されません。スナップショットトランザクションでは、先に開始されてまだ完了していないトランザクションによりブロックされることなく、他のトランザクションを実行できます。データを変更するトランザクションは、データを読み取るトランザクションをブロックしません。また、データを読み取るトランザクションは、データを書き込むトランザクションをブロックしません。この理由は、通常、これらのトランザクションは SQL Server の既定の READ COMMITTED 分離レベルにあるためです。また、ブロック不可の動作は、複雑なトランザクションのデッドロックの可能性を大幅に軽減します。

スナップショット分離では、オプティミスティック同時実行制御モデルを使用します。スナップショットトランザクションは、トランザクションの開始後に変更されたデータに対して変更をコミットしようとすると、このトランザクションがロールバックし、エラーになります。このエラーは、変更されるデータにアクセスする、SELECT ステートメントの UPDLOCK ヒントを使用することにより回避できます。詳細については、SQL Server オンラインブックの「ロックのヒント」を参照してください。

スナップショット分離は、トランザクション内で使用する前に、ALLOW_SNAPSHOT_ISOLATION ON データベースオプションを設定して有効にする必要があります。これにより、行バージョンを一時データベース (tempdb) 内に保存するためのメカニズムがアクティブになります。Transact-SQL ALTER DATABASE ステートメントで使用する、各データベース内のスナップショット分離を有効にする必要があります。この点では、スナップショット分離は、構成を必要としない READ COMMITTED、REPEATABLE READ、SERIALIZABLE、および READ UNCOMMITTED の従来の分離レベルとは異なります。次のステートメントは、スナップショット分離をアクティブにして、既定の READ COMMITTED 動作を SNAPSHOT で置き換えます。

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 ステートメントからの分離レベルが保持されます。それ以降、プールされた接続を再利用する接続では、その接続がプールされた時点で有効にされた分離レベルが使用されます。

接続内で発行される個別のクエリには、接続の分離レベルに影響を与えることなく、1 つのステートメントまたはトランザクションの分離を変更するロックヒントを含めることができます。ストアドプロシージャまたは関数内で設定される分離レベルまたはロックヒントは、これらを呼び出す接続の分離レベルを変更しません。また、分離レベルまたはロックヒントは、ストアドプロシージャまたは関数呼び出しの間だけ有効になります。

以前のバージョンの SQL Server でサポートされる、SQL-92 標準で定義された分離レベルが 4 つあります。

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 分離は、トランザクション内で読み取るデータに、他の同時トランザクションによって加えられた変更が反映されないようにします。トランザクションでは、トランザクションの開始時に存在するデータの行バージョンを使用します。データの読み取り時にロックがデータに配置されないため、データを書き込まれないようにスナップショットトランザクションによって他のトランザクションがブロックされるというようなことはありません。データを書き込むトランザクションは、スナップショットトランザクションによるデータの読み取りをブロックしません。スナップショット分離を使用するには、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 に構成されていない場合、BeginTransaction メソッドの呼び出し時に IsolationLevel.Snapshot 列挙値を使って、SqlTransaction を開始する必要があります。このコードフラグメントでは、接続は開かれている SqlConnection オブジェクトであることを前提としています。

Dim sqlTran As SqlTransaction = _
  connection.BeginTransaction(IsolationLevel.Snapshot)

SqlTransaction sqlTran = 
  connection.BeginTransaction(IsolationLevel.Snapshot);

例

ロックされたデータにアクセスしようとすることにより、分離レベルがそれぞれどのように動作するのかを、次の例に示します。このサンプルは、実行用のコードで使用されることは想定していません。

このコードは、SQL Server の AdventureWorks サンプルデータベースに接続し、TestSnapshot というテーブルを作成し、1 行のデータを挿入します。コードには ALTER DATABASE Transact-SQL ステートメントを使用し、データベースのスナップショット分離を有効にします。このとき、既定の READ COMMITTED 分離レベルの動作を有効なままにし、READ_COMMITTED_SNAPSHOT オプションは設定しません。続いてコードは、次のアクションを実行します。

更新トランザクションを開始するために、SERIALIZABLE 分離レベルを使用する sqlTransaction1 を開始し、完了しないようにします。これには、テーブルがロックするという効果があります。
2 つ目の接続を開き、SNAPSHOT 分離レベルを使って 2 つ目のトランザクションを開始し、TestSnapshot テーブル内のデータを読み取ります。スナップショット分離が有効になっているため、このトランザクションは、sqlTransaction1 が開始する前に存在していたデータを読み取ることができます。
3 つ目の接続を開いて、READ COMMITTED 分離レベルを使ってトランザクションを開始し、テーブル内のデータの読み取りを試みます。この場合、コードはデータを読み取れません。コードは最初のトランザクション内のテーブルに置かれたロックを超えて読み取りを行うことができず、タイムアウトになるためです。REPEATABLE READ 分離レベルと SERIALIZABLE 分離レベルが使用されている場合は、これらの分離レベルも、最初のトランザクション内に置かれたロックを超えて読み取りを行うことができないため、同じ結果になります。
4 つ目の接続を開き、sqlTransaction1 内でコミットされていない値のダーティリードを実行する READ UNCOMMITTED 分離レベルを使用し、トランザクションを開始します。最初のトランザクションがコミットされていない場合、この値は実際にデータベース内には存在しません。
TestSnapshot テーブルを削除し、AdventureWorks データベースのスナップショット分離をオフにすることにより、最初のトランザクションをロールバックおよびクリーンアップします。

メモ :
次の例では、接続プールを無効にした状態で同じ接続文字列を使用します。接続をプールした場合、その分離レベルをリセットしても、サーバー側の分離レベルはリセットされません。その結果、同じプールされた内部接続を使用する後続の接続は、プールされた接続と同じ分離レベルで開始されることになります。接続プールを無効にする代わりに、各接続について分離レベルを明示的に設定することもできます。

次の例では、接続プールを無効にした状態で同じ接続文字列を使用します。接続をプールした場合、その分離レベルをリセットしても、サーバー側の分離レベルはリセットされません。その結果、同じプールされた内部接続を使用する後続の接続は、プールされた接続と同じ分離レベルで開始されることになります。接続プールを無効にする代わりに、各接続について分離レベルを明示的に設定することもできます。

' 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

// 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 という名前のテーブルを作成し、3 行のサンプルデータを挿入します。
SNAPSHOT 分離を使って sqlTransaction1 を開始し、完了しないようにします。3 行のデータがトランザクション内で選択されます。
2 つ目の SqlConnection を AdventureWorks に対して作成し、sqlTransaction1 内で選択された行のうち 1 行の値を更新する READ COMMITTED 分離レベルを使用して、2 つ目のトランザクションを作成します。
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;. 
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

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

スナップショット分離でのロックヒントの使用

前の例では、最初のトランザクションがデータを選択し、このトランザクションが完了する前に 2 つ目のトランザクションがデータを更新しています。その結果、最初のトランザクションが同じ行を更新しようとすると、競合が発生します。トランザクションの先頭にロックヒントを指定することにより、長時間にわたるスナップショットトランザクションにおいて更新競合が発生する可能性を軽減できます。次の SELECT ステートメントでは、選択した行をロックするために、UPDLOCK ヒントが使用されています。

SELECT * FROM TestSnapshotUpdate WITH (UPDLOCK) 
  WHERE PriKey BETWEEN 1 AND 3

UPDLOCK ロックヒントを使用すると、最初のトランザクションが完了する前に行が更新されるのをブロックします。これにより、選択した行が後にトランザクション内で更新されるときに、競合が発生しないことが保証されます。SQL Server オンラインブックの「ロックのヒント」を参照してください。

アプリケーションで競合が多数発生する場合、スナップショット分離は適切な選択肢ではない可能性があります。ヒントの使用は、本当に必要な場合のみに制限する必要があります。アプリケーションは、ロックヒントに常に依存する操作にならないように設計されている必要があります。

参照

その他の技術情報

SQL Server と ADO.NET

次の方法で共有

SQL Server でのスナップショット分離 (ADO.NET)

スナップショット分離と行バージョン管理について

分離レベルによる同時実行の管理

スナップショット分離レベルの拡張機能

スナップショット分離と行バージョン管理の機能について

ADO.NET でのスナップショット分離の使用

例

例

スナップショット分離でのロックヒントの使用

参照

その他の技術情報

その他のリソース

次の方法で共有

SQL Server でのスナップショット分離 (ADO.NET)

スナップショット分離と行バージョン管理について

分離レベルによる同時実行の管理

スナップショット分離レベルの拡張機能

スナップショット分離と行バージョン管理の機能について

ADO.NET でのスナップショット分離の使用

例

例

スナップショット分離でのロック ヒントの使用

参照

その他の技術情報

その他のリソース

スナップショット分離でのロックヒントの使用