ノンリピータブル リードとその解決方法 その 1

(目次: トランザクションのサンプル)

ノンリピータブル リード (Non-repeatable Read) とは、
同一のトランザクション内で値を複数回読み取ったときに、その結果が異なってしまう現象のことです。
今回は、ノンリピータブル リードの発生と SQL Server における解決方法について、実際のコードを示して説明します。

前提として、SQL Server におけるロック メカニズムやトランザクション分離レベルについての基礎知識が必要になります。
これらは SQL Server のロック管理 (@IT) で確認できます。

以下で示すサンプルでは同時実行制御のために自動トランザクション (TransactionScope クラス) を使用していますが、
手動トランザクションなどを使用しても同様です。
さらに、データ アクセスのために SqlCommand クラスを使用していますが、
型指定された TableAdapter などを使用しても同様です。

■ ステップ 1: Read Committed (→ノンリピータブル リード発生)

先にコードを示します。
コンソール アプリケーション プロジェクトを作成し、Program.cs に次のように記述します。
System.Transactions.dll への参照も必要です。

---

using System;
using System.Data.SqlClient;
using System.Threading.Tasks;
using System.Transactions;

namespace NonrepeatableRead
{
    class Program
    {
        private const string NorthwindConnectionString = @"Data Source=.\SQLExpress;Initial Catalog=Northwind;Integrated Security=True";

        private const string SelectCommandText = "select UnitsInStock from Products where ProductID = 1";
        private const string UpdateCommandText = "update Products set UnitsInStock = UnitsInStock + 1 where ProductID = 1";

        private static readonly TransactionOptions transactionOptions = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };

        private static int isNonrepeatableCount;

        static void Main(string[] args)
        {
            // 更新と読み取りを並列に実行します。
            Parallel.Invoke(
                () =>
                {
                    for (int i = 0; i < 200; i++)
                    {
                        UpdateUnitsInStock();
                    }
                },
                () =>
                {
                    for (int i = 0; i < 100; i++)
                    {
                        SelectUnitsInStock();
                    }
                }
            );
        }

        /// <summary>
        /// Northwind データベースの Products テーブルの UnitsInStock の値を更新します。
        /// </summary>
        private static void UpdateUnitsInStock()
        {
            using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
            using (var connection = new SqlConnection(NorthwindConnectionString))
            {
                // データベース接続を開きます。
                connection.Open();

                // 在庫個数を更新します。
                using (var command = new SqlCommand(UpdateCommandText, connection))
                {
                    command.ExecuteNonQuery();
                }

                // コミットします。
                scope.Complete();
            }
        }

        /// <summary>
        /// Northwind データベースの Products テーブルの UnitsInStock の値を読み取ります。
        /// </summary>
        private static void SelectUnitsInStock()
        {
            using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
            using (var connection = new SqlConnection(NorthwindConnectionString))
            {
                // データベース接続を開きます。
                connection.Open();

                // 在庫個数を読み取ります。
                using (var command = new SqlCommand(SelectCommandText, connection))
                {
                    short unitsInStock1 = (short)command.ExecuteScalar();
                    short unitsInStock2 = (short)command.ExecuteScalar();

                    if (unitsInStock1 != unitsInStock2)
                    {
                        Console.WriteLine("{0} = {1} ({2} 回目)", unitsInStock1, unitsInStock2, ++isNonrepeatableCount);
                    }
                    else
                    {
                        Console.WriteLine("{0} = {1}", unitsInStock1, unitsInStock2);
                    }
                }
            }
        }
    }
}

---

今回も Northwind データベースの Products テーブルを使用します。
読み取りおよび更新の対象となるのはこの図の値です。
なお、UnitsInStock 列のデータ型は smallint (C# では short) です。

UpdateUnitsInStock メソッドは、その在庫個数の値を 1 だけ増加させます。

SelectUnitsInStock メソッドは、その在庫個数の値を 2 回読み取って比較します。
トランザクション分離レベルを Read Committed に設定して、一連の処理を TransactionScope で囲みます。

そしてこの 2 つのメソッドを並列に実行します。
なお、並列処理には .NET Framework 4 で追加されたタスク並列ライブラリ (TPL) を使用しています。

実行結果は上の図のようになります。
SelectUnitsInStock メソッドを 100 回実行して 40 回ほどは同一トランザクション内で値が変化してしまっています。
これがノンリピータブル リードという現象です。
同一トランザクション内では、何度読み取っても同じ値にならなければ一貫性を満たしているとは言えません。

Read Committed の場合には、トランザクション内で共有ロックを最後まで取得し続けないために、
別のトランザクションから更新ができてしまいます。

■ ステップ 2: Repeatable Read (→解決)

トランザクション分離レベルを Repeatable Read に変更します。

---

private static readonly TransactionOptions transactionOptions = new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead };

---

コードを上記のように 1 行だけ書き換えて実行します。

すると、今度は期待通りに動作します。
Repeatable Read であればトランザクションが終了するまで共有ロックを取得するため、
別のトランザクションは値を更新できなくなります。

ただし、このようにして一貫性についての解決はできるのですが、
同一トランザクション内で同じ値を複数回読み取るのは無駄に通信を発生させているともいえます。
同じ値を読み取るのが 1 回だけになるように設計するのが望ましいでしょう。
そうすれば Repeatable Read を使う必要もなく、Read Committed で十分となります。

同一トランザクション内で更新が必要な業務であれば、
ロスト アップデートとその解決方法 その 1 で説明した通り、
Read Committed で更新ロックを取得する方法を利用するとよいでしょう。

バージョン情報
.NET Framework 4
SQL Server 2008, 2008 R2

参照
.NET エンタープライズ Web アプリケーション開発技術大全 〈Vol.5〉 トランザクション設計編
エンタープライズ技術大全(トランザクション設計編) – WikiWiki (上記書籍の要約)
SQL Server のロック管理 (@IT)