SQL Server でのスナップショット分離Snapshot isolation in SQL Server

ADO.NET のダウンロード

スナップショット分離では、OLTP アプリケーションの同時実行性が向上します。Snapshot isolation enhances concurrency for OLTP applications.

スナップショット分離と行のバージョン管理についてUnderstanding snapshot isolation and row versioning

スナップショット分離が有効になると、各トランザクションの更新された行のバージョン管理は、tempdb に保持されます。Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. 一意のトランザクション シーケンス番号によって、各トランザクションが識別されます。これらの一意の番号は、行バージョンごとに記録されます。A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. トランザクションは、トランザクションのシーケンス番号の前にシーケンス番号が付いた最新の行バージョンで動作します。The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. トランザクションの開始後に作成された新しい行バージョンは、トランザクションによって無視されます。Newer row versions created after the transaction has begun are ignored by the transaction.

"スナップショット" という用語は、トランザクションの開始時点のデータベースの状態に基づいて、トランザクション内のすべてのクエリが、データベースの同じバージョン (つまりスナップショット) を参照するということを表しています。The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. スナップショット トランザクション内の基になるデータ行またはデータ ページに対してロックは取得されません。これにより、以前の未完了のトランザクションによってブロックされることなく、他のトランザクションを実行できます。No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior incompleted transaction. トランザクションは通常、SQL Server で既定の READ COMMITTED 分離レベルにあるため、データを変更するトランザクションは、データを読み取るトランザクションをブロックしません。また、データを読み取るトランザクションは、データを書き込むトランザクションをブロックしません。Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. この非ブロッキング動作によって、複雑なトランザクションがデッドロックする可能性も大幅に減少します。This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.

スナップショット分離では、オプティミスティック同時実行制御モデルが使用されます。Snapshot isolation uses an optimistic concurrency model. スナップショット トランザクションで、トランザクションの開始後に変更されたデータに対して変更をコミットしようとすると、トランザクションがロールバックされ、エラーが発生します。If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised. これを回避するには、変更するデータにアクセスする SELECT ステートメントに UPDLOCK ヒントを使用します。You can avoid this by using UPDLOCK hints for SELECT statements that access data to be modified. 詳細については、SQL Server オンラインブックの「ロック ヒント」を参照してください。See "Locking Hints" in SQL Server Books Online for more information.

スナップショット分離を有効にするには、トランザクションで使用する前に、ALLOW_SNAPSHOT_ISOLATION ON データベース オプションを設定する必要があります。Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. これにより、行バージョンを一時データベース (tempdb) 内に保存するためのメカニズムがアクティブになります。This activates the mechanism for storing row versions in the temporary database (tempdb). Transact-SQL の ALTER DATABASE ステートメントで使用するスナップショット分離を、各データベースで有効にする必要があります。You must enable snapshot isolation in each database that uses it with the Transact-SQL ALTER DATABASE statement. この点で、スナップショット分離は、構成を必要としない、READ COMMITTED、REPEATABLE READ、SERIALIZABLE、および READ UNCOMMITTED という従来の分離レベルとは異なります。In this respect, snapshot isolation differs from the traditional isolation levels of READ COMMITTED, REPEATABLE READ, SERIALIZABLE, and READ UNCOMMITTED, which require no configuration. 次のステートメントでは、スナップショット分離をアクティブ化し、既定の READ COMMITTED 動作を SNAPSHOT に置き換えます。The following statements activate snapshot isolation and replace the default READ COMMITTED behavior with SNAPSHOT:

ALTER DATABASE MyDatabase  
SET ALLOW_SNAPSHOT_ISOLATION ON  
  
ALTER DATABASE MyDatabase  
SET READ_COMMITTED_SNAPSHOT ON  

READ_COMMITTED_SNAPSHOT ON オプションを設定すると、既定の READ COMMITTED 分離レベルでバージョン管理された行にアクセスできます。Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level. READ_COMMITTED_SNAPSHOT オプションが OFF に設定されている場合、バージョン管理された行にアクセスするには、セッションごとにスナップショット分離レベルを明示的に設定する必要があります。If the READ_COMMITTED_SNAPSHOT option is set to OFF, you must explicitly set the Snapshot isolation level for each session in order to access versioned rows.

分離レベルによるコンカレンシーの管理Managing concurrency with isolation levels

Transact-SQL ステートメントが実行される分離レベルによって、ロックおよび行のバージョン管理の動作が決定します。The isolation level under which a Transact-SQL statement executes determines its locking and row versioning behavior. 分離レベルには接続全体のスコープがあり、SET TRANSACTION ISOLATION LEVEL ステートメントを使用して接続に設定すると、接続が閉じられるか別の分離レベルが設定されるまで、有効なままになります。An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. 接続が閉じられ、プールに返されると、最後の SET TRANSACTION ISOLATION LEVEL ステートメントの分離レベルが保持されます。When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. プールされた接続を再利用する後続の接続では、接続がプールされたときに有効であった分離レベルが使用されます。Subsequent connections reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled.

接続内で発行される個々のクエリには、1 つのステートメントまたはトランザクションの分離を変更しても、接続の分離レベルには影響しないロック ヒントを含めることができます。Individual queries issued within a connection can contain lock hints that modify the isolation for a single statement or transaction but do not affect the isolation level of the connection. ストアド プロシージャまたは関数で設定された分離レベルまたはロック ヒントでは、それらを呼び出す接続の分離レベルは変更されず、ストアド プロシージャまたは関数呼び出しの実行中にのみ有効にされます。Isolation levels or lock hints set in stored procedures or functions do not change the isolation level of the connection that calls them and are in effect only for the duration of the stored procedure or function call.

以前のバージョンの SQL Server では、SQL-92 標準に定義されている 4 つの分離レベルがサポートされていました。Four isolation levels defined in the SQL-92 standard were supported in early versions of SQL Server:

  • READ UNCOMMITTED は、他のトランザクションによって設定されたロックを無視するため、最も制限の緩い分離レベルです。READ UNCOMMITTED is the least restrictive isolation level because it ignores locks placed by other transactions. READ UNCOMMITTED で実行されているトランザクションは、他のトランザクションによってまだコミットされていない変更されたデータ値を読み取ることができます。これらは "ダーティ" リードと呼ばれます。Transactions executing under READ UNCOMMITTED can read modified data values that have not yet been committed by other transactions; these are called "dirty" reads.

  • READ COMMITTED は、SQL Server の既定の分離レベルです。READ COMMITTED is the default isolation level for SQL Server. これにより、ステートメントで他のトランザクションによって変更されていてもまだコミットされていないデータ値を読み取ることができないように指定することで、ダーティ リードを防止できます。It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. その他のトランザクションでは、現在のトランザクション内の個々のステートメントの実行で、引き続きデータの変更、挿入、削除が可能であるため、反復不可能な読み取りや "ファントム" データが発生します。Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or "phantom" data.

  • REPEATABLE READ は、READ COMMITTED よりも制限の厳しい分離レベルです。REPEATABLE READ is a more restrictive isolation level than READ COMMITTED. これは READ COMMITTED を含み、加えて現在のトランザクションがコミットされるまで、現在のトランザクションによって読み取られたデータを他のトランザクションが変更または削除できないことを指定します。It encompasses READ COMMITTED and additionally specifies that no other transactions can modify or delete data that has been read by the current transaction until the current transaction commits. 読み取りデータに対する共有ロックは、各ステートメントの最後に解放されるのではなく、トランザクションの実行中に保持されるため、コンカレンシーは READ COMMITTED よりも低くなります。Concurrency is lower than for READ COMMITTED because shared locks on read data are held for the duration of the transaction instead of being released at the end of each statement.

  • キー範囲全体がロックされ、トランザクションが完了するまでその状態が保持されるので、SERIALIZABLE は最も制限の厳しい分離レベルといえます。SERIALIZABLE is the most restrictive isolation level, because it locks entire ranges of keys and holds the locks until the transaction is complete. これは REPEATABLE READ を含み、トランザクションが完了するまで、トランザクションによって読み取られた範囲に新しい行を挿入できないという制限を追加します。It encompasses REPEATABLE READ and adds the restriction that other transactions cannot insert new rows into ranges that have been read by the transaction until the transaction is complete.

詳細については、「トランザクションのロックおよび行のバージョン管理ガイド」を参照してください。For more information, refer to the Transaction Locking and Row Versioning Guide.

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

SQL Server では、SNAPSHOT 分離レベルの導入および READ COMMITTED の追加実装と共に、SQL-92 分離レベルの機能を強化しています。SQL Server introduced extensions to the SQL-92 isolation levels with the introduction of the SNAPSHOT isolation level and an additional implementation of READ COMMITTED. READ_COMMITTED_SNAPSHOT 分離レベルは、すべてのトランザクションの READ COMMITTED を自動的に置き換えることができます。The READ_COMMITTED_SNAPSHOT isolation level can transparently replace READ COMMITTED for all transactions.

  • SNAPSHOT 分離は、トランザクション内で読み取られるデータには、他の同時実行トランザクションによる変更が反映されないことを指定します。SNAPSHOT isolation specifies that data read within a transaction will never reflect changes made by other simultaneous transactions. このトランザクションでは、トランザクションの開始時に存在していたデータ行のバージョンが使用されます。The transaction uses the data row versions that exist when the transaction begins. データが読み取られるときにロックが設定されないため、SNAPSHOT トランザクションは、他のトランザクションによるデータの書き込みをブロックしません。No locks are placed on the data when it is read, so SNAPSHOT transactions do not block other transactions from writing data. トランザクションでデータが書き込まれている間も、SNAPSHOT トランザクションではデータを読み取ることができます。Transactions that write data do not block snapshot transactions from reading data. スナップショット分離を使用するには ALLOW_SNAPSHOT_ISOLATION データベース オプションを設定して、それを有効にする必要があります。You need to enable snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option in order to use it.

  • データベースでスナップショット分離が有効になっている場合、READ_COMMITTED_SNAPSHOT データベース オプションによって、既定の READ COMMITTED 分離レベルの動作が決定されます。The READ_COMMITTED_SNAPSHOT database option determines the behavior of the default READ COMMITTED isolation level when snapshot isolation is enabled in a database. READ_COMMITTED_SNAPSHOT ON を明示的に指定しない場合、すべての暗黙のトランザクションに READ COMMITTED が適用されます。If you do not explicitly specify READ_COMMITTED_SNAPSHOT ON, READ COMMITTED is applied to all implicit transactions. これにより、READ_COMMITTED_SNAPSHOT OFF (既定) を設定する場合と同じ動作が生成されます。This produces the same behavior as setting READ_COMMITTED_SNAPSHOT OFF (the default). READ_COMMITTED_SNAPSHOT OFF が有効な場合、データベース エンジンでは、共有ロックを使用して既定の分離レベルが適用されます。When READ_COMMITTED_SNAPSHOT OFF is in effect, the Database Engine uses shared locks to enforce the default isolation level. READ_COMMITTED_SNAPSHOT データベース オプションを ON に設定した場合、データベース エンジンでは、データを保護するためにロックを使用するのではなく、行のバージョン管理とスナップショット分離が既定として使用されます。If you set the READ_COMMITTED_SNAPSHOT database option to ON, the database engine uses row versioning and snapshot isolation as the default, instead of using locks to protect the data.

スナップショット分離と行のバージョン管理のしくみHow snapshot isolation and row versioning work

SNAPSHOT 分離レベルが有効になっている場合、行が更新されるたびに、SQL Server データベース エンジンは tempdb 内の元の行のコピーを保存し、行にトランザクション シーケンス番号を追加します。When the SNAPSHOT isolation level is enabled, each time a row is updated, the SQL Server Database Engine stores a copy of the original row in tempdb, and adds a transaction sequence number to the row. 次に一連のイベントの発生を示します。The following is the sequence of events that occurs:

  1. 新しいトランザクションが開始され、トランザクション シーケンス番号が割り当てられます。A new transaction is initiated, and it is assigned a transaction sequence number.

  2. データベース エンジンは、トランザクション内の行を読み取り、トランザクション シーケンス番号より小さくて、トランザクション シーケンス番号に最も近いシーケンス番号の行バージョンを、tempdb から取得します。The Database Engine reads a row within the transaction and retrieves the row version from tempdb whose sequence number is closest to, and lower than, the transaction sequence number.

  3. データベース エンジンでは、スナップショット トランザクションが開始されたときにアクティブになっており、コミットされていないトランザクションのトランザクション シーケンス番号の一覧に、トランザクション シーケンス番号が含まれているかどうかがチェックされます。The Database Engine checks to see if the transaction sequence number is not in the list of transaction sequence numbers of the uncommitted transactions active when the snapshot transaction started.

  4. トランザクションは、トランザクションの開始時点で最新だった tempdb から、行のバージョンを読み取ります。The transaction reads the version of the row from tempdb that was current as of the start of the transaction. トランザクションの開始後に挿入された新しい行は、それらのシーケンス番号値がトランザクション シーケンス番号値よりも大きくなるため、確認されません。It will not see new rows inserted after the transaction was started because those sequence number values will be higher than the value of the transaction sequence number.

  5. 現在のトランザクションは、トランザクションの開始後に削除された行を確認します。この理由は、トランザクション シーケンス番号より小さいシーケンス番号の値を持つ行バージョンが tempdb 内に存在する可能性があるためです。The current transaction will see rows that were deleted after the transaction began, because there will be a row version in tempdb with a lower sequence number value.

スナップショット分離の実質的な効果として、トランザクションでは、基になるテーブルへのロックの適用や配置を行うことなく、すべてのデータがトランザクションの開始時に存在していたものと見なされます。The net effect of snapshot isolation is that the transaction sees all of the data as it existed at the start of the transaction, without honoring or placing any locks on the underlying tables. これにより、競合が発生している状況でパフォーマンスが向上することができます。This can result in performance improvements in situations where there is contention.

スナップショット トランザクションでは常にオプティミスティック同時実行制御が使用され、他のトランザクションによる行の更新を妨げるようなロックが行われません。A snapshot transaction always uses optimistic concurrency control, withholding any locks that would prevent other transactions from updating rows. スナップショット トランザクションでは、トランザクションの開始後に変更された行に対する更新をコミットしようとすると、トランザクションがロールバックされ、エラーが発生します。If a snapshot transaction attempts to commit an update to a row that was changed after the transaction began, the transaction is rolled back, and an error is raised.

ADO.NET でスナップショット分離を使用するWorking with snapshot isolation in ADO.NET

スナップショット分離は、ADO.NET で SqlTransaction クラスによってサポートされています。Snapshot isolation is supported in ADO.NET by the SqlTransaction class. データベースでスナップショット分離が有効になっているが、READ_COMMITTED_SNAPSHOT ON に構成されていない場合、BeginTransaction メソッドの呼び出し時に IsolationLevel.Snapshot 列挙値を使って、SqlTransaction を開始する必要があります。If a database has been enabled for snapshot isolation but is not configured for READ_COMMITTED_SNAPSHOT ON, you must initiate a SqlTransaction using the IsolationLevel.Snapshot enumeration value when calling the BeginTransaction method. このコード フラグメントでは、接続が開いている SqlConnection オブジェクトであることを前提としています。This code fragment assumes that connection is an open SqlConnection object.

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

Example

次の例では、ロックされたデータにアクセスを試みて、さまざまな分離レベルがどのように動作するかを示すものですが、運用環境コードでの使用を意図するものではありません。The following example demonstrates how the different isolation levels behave by attempting to access locked data, and it is not intended to be used in production code.

このコードは、SQL Server の AdventureWorks サンプル データベースに接続し、TestSnapshot というテーブルを作成し、1 行のデータを挿入します。The code connects to the AdventureWorks sample database in SQL Server and creates a table named TestSnapshot and inserts one row of data. このコードでは、ALTER DATABASE Transact-SQL ステートメントを使用して、データベースのスナップショット分離を有効にしていますが、READ_COMMITTED_SNAPSHOT オプションは設定せず、既定の READ COMMITTED 分離レベルの動作を有効なままにしています。The code uses the ALTER DATABASE Transact-SQL statement to turn on snapshot isolation for the database, but it does not set the READ_COMMITTED_SNAPSHOT option, leaving the default READ COMMITTED isolation-level behavior in effect. 次に、このコードでは次のアクションを実行します。The code then performs the following actions:

  1. これは sqlTransaction1 を開始しますが、完了するのではなく、SERIALIZABLE 分離レベルを使用して更新トランザクションを開始します。It begins, but does not complete, sqlTransaction1, which uses the SERIALIZABLE isolation level to start an update transaction. これには、テーブルをロックする効果があります。This has the effect of locking the table.

  2. 2 つ目の接続を開き、SNAPSHOT 分離レベルを使って 2 つ目のトランザクションを開始し、TestSnapshot テーブル内のデータを読み取ります。It opens a second connection and initiates a second transaction using the SNAPSHOT isolation level to read the data in the TestSnapshot table. スナップショット分離が有効になっているため、このトランザクションでは、sqlTransaction1 が開始される前に存在していたデータを読み取ることができます。Because snapshot isolation is enabled, this transaction can read the data that existed before sqlTransaction1 started.

  3. これは 3 番目の接続を開き、READ COMMITTED 分離レベルを使用してトランザクションを開始し、テーブル内のデータの読み取りを試みます。It opens a third connection and initiates a transaction using the READ COMMITTED isolation level to attempt to read the data in the table. この場合、コードはデータを読み取れません。コードは最初のトランザクション内のテーブルに置かれたロックを超えて読み取りを行うことができず、タイムアウトになるためです。REPEATABLE READ 分離レベルと SERIALIZABLE 分離レベルが使用されている場合は、これらの分離レベルも、最初のトランザクション内に置かれたロックを超えて読み取りを行うことができないため、同じ結果になります。In this case, the code cannot read the data because it cannot read past the locks placed on the table in the first transaction and times out. The same result would occur if the REPEATABLE READ and SERIALIZABLE isolation levels were used because these isolation levels also cannot read past the locks placed in the first transaction.

  4. これは 4 番目の接続を開き、READ UNCOMMITTED 分離レベルを使用してトランザクションを開始し、sqlTransaction1 でコミットされていない値のダーティ リードを実行します。It opens a fourth connection and initiates a transaction using the READ UNCOMMITTED isolation level, which performs a dirty read of the uncommitted value in sqlTransaction1. 最初のトランザクションがコミットされていない場合、この値は実際にはデータベースに存在しない可能性があります。This value may never actually exist in the database if the first transaction is not committed.

  5. TestSnapshot テーブルを削除し、AdventureWorks データベースのスナップショット分離をオフにすることにより、最初のトランザクションをロールバックおよびクリーンアップします。It rolls back the first transaction and cleans up by deleting the TestSnapshot table and turning off snapshot isolation for the AdventureWorks database.

注意

次の例では、接続プールを無効にして、同じ接続文字列を使用します。The following examples use the same connection string with connection pooling turned off. 接続がプールされている場合、その分離レベルをリセットしても、サーバーの分離レベルはリセットされません。If a connection is pooled, resetting its isolation level does not reset the isolation level at the server. その結果、プールされている同じ内部接続を使用する後続の接続は、その分離レベルをプールされている接続の分離レベルに設定して開始されます。As a result, subsequent connections that use the same pooled inner connection start with their isolation levels set to that of the pooled connection. 接続プールを無効にする代わりに、各接続の分離レベルを明示的に設定することもできます。An alternative to turning off connection pooling is to set the isolation level explicitly for each connection.

using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        // 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!");
    }

    static private string GetConnectionString()
    {
        // To avoid storing the connection string in your code, 
        // you can retrieve it from a configuration file, using the 
        // System.Configuration.ConfigurationSettings.AppSettings property
        return "Data Source=localhost;Initial Catalog=AdventureWorks;"
            + "Integrated Security=SSPI";
    }
}

Example

次の例では、データの変更中のスナップショット分離の動作を示しています。The following example demonstrates the behavior of snapshot isolation when data is being modified. このコードで次のアクションが実行されます。The code performs the following actions:

  1. AdventureWorks サンプル データベースに接続し、SNAPSHOT 分離を有効にします。Connects to the AdventureWorks sample database and enables SNAPSHOT isolation.

  2. TestSnapshotUpdate という名前のテーブルを作成し、3 行のサンプル データを挿入します。Creates a table named TestSnapshotUpdate and inserts three rows of sample data.

  3. SNAPSHOT 分離を使用して sqlTransaction1 を開始しますが、完了はしません。Begins, but does not complete, sqlTransaction1 using SNAPSHOT isolation. トランザクションでは、3 行のデータが選択されます。Three rows of data are selected in the transaction.

  4. 2 つ目の SqlConnectionAdventureWorks に対して作成し、sqlTransaction1 内で選択された行のうち 1 行の値を更新する READ COMMITTED 分離レベルを使用して、2 つ目のトランザクションを作成します。Creates a second SqlConnection to AdventureWorks and creates a second transaction using the READ COMMITTED isolation level that updates a value in one of the rows selected in sqlTransaction1.

  5. sqlTransaction2 をコミットします。Commits sqlTransaction2.

  6. sqlTransaction1 に戻り、sqlTransaction1 がすでにコミットした同じ行を更新しようとします。Returns to sqlTransaction1 and attempts to update the same row that sqlTransaction1 already committed. エラー 3960 が発生し、sqlTransaction1 が自動的にロールバックされます。Error 3960 is raised, and sqlTransaction1 is rolled back automatically. SqlException.NumberSqlException.Message がコンソール ウィンドウに表示されます。The SqlException.Number and SqlException.Message are displayed in the Console window.

  7. クリーンアップ コードを実行して AdventureWorks 内のスナップショット分離をオフにし、TestSnapshotUpdate テーブルを削除します。Executes clean-up code to turn off snapshot isolation in AdventureWorks and delete the TestSnapshotUpdate table.

    using Microsoft.Data.SqlClient;
    using System.Data.Common;
    
    class Program
    {
        static void Main()
        {
            // 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);
                }
            }
            Console.WriteLine("Done");
            Console.ReadLine();
        }
    
        static private string GetConnectionString()
        {
            // To avoid storing the connection string in your code, 
            // you can retrieve it from a configuration file, using the 
            // System.Configuration.ConfigurationSettings.AppSettings property 
            return "Data Source=(local);Initial Catalog=AdventureWorks;"
                + "Integrated Security=SSPI;Pooling=false";
        }
    
    }
    

スナップショット分離でのロック ヒントの使用Using lock hints with snapshot isolation

前の例では、最初のトランザクションでデータが選択され、最初のトランザクションが完了する前に 2 番目のトランザクションによってデータが更新されるため、最初のトランザクションで同じ行を更新しようとしたときに更新の競合が発生します。In the previous example, the first transaction selects data, and a second transaction updates the data before the first transaction is able to complete, causing an update conflict when the first transaction tries to update the same row. トランザクションの開始時にロック ヒントを指定することで、実行時間の長いスナップショット トランザクションで更新の競合が発生する機会を削減できます。You can reduce the chance of update conflicts in long-running snapshot transactions by supplying lock hints at the beginning of the transaction. 次の SELECT ステートメントでは、UPDLOCK ヒントを使用して、選択した行をロックします。The following SELECT statement uses the UPDLOCK hint to lock the selected rows:

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

UPDLOCK ロック ヒントを使用すると、最初のトランザクションが完了する前に行を更新しようとするすべての行がブロックされます。Using the UPDLOCK lock hint blocks any rows attempting to update the rows before the first transaction completes. これにより、トランザクションの後半で、選択した行が更新されるときに、確実に競合が発生しなくなります。This guarantees that the selected rows have no conflicts when they are updated later in the transaction. SQL Server オンラインブックの「ロック ヒント」を参照してください。See "Locking Hints" in SQL Server Books Online.

アプリケーションで多くの競合が発生する場合は、スナップショット分離が最適な選択肢にならないことがあります。If your application has many conflicts, snapshot isolation may not be the best choice. ヒントは、本当に必要な場合にのみ使用してください。Hints should only be used when really needed. アプリケーションでは、その操作が常にロック ヒントに依存することのないように設計してください。Your application should not be designed so that it constantly relies on lock hints for its operation.

次のステップNext steps