ロスト アップデートとその解決方法 その 1

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

ロスト アップデート (Lost Update, 失われた更新) とは、更新したはずのデータが失われてしまう現象のことです。
「後勝ち」と表現されることもあります。
わざと後勝ちルールを採用する業務もあると思いますが、頻繁なアクセスが見込まれる場合には適切な排他制御が必要になります。
今回は、ロスト アップデートの発生と 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 LostUpdate
{
    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 where ProductID = 1";

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

        static void Main(string[] args)
        {
            Parallel.For(0, 100, i => AddUnitsInStock());
        }

        /// <summary>
        /// Northwind データベースの Products テーブルの UnitsInStock の値を 1 だけ増加させます。
        /// </summary>
        private static void AddUnitsInStock()
        {
            try
            {
                short oldValue, newValue;

                using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
                using (var connection = new SqlConnection(NorthwindConnectionString))
                {
                    // データベース接続を開きます。
                    connection.Open();

                    // 在庫個数を取得します。
                    using (var command = new SqlCommand(SelectCommandText, connection))
                    {
                        oldValue = (short)command.ExecuteScalar();
                    }

                    // 在庫個数を 1 だけ増加させます。
                    newValue = (short)(oldValue + 1);

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

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

                Console.WriteLine("{0} → {1}", oldValue, newValue);
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

---

AddUnitsInStock メソッドは、Northwind データベース内の特定の商品の在庫個数を 1 だけ増加させます。
これは、「同一トランザクション内で、あるデータを取得して、新たな値で更新する」という特徴を持っています。
トランザクション分離レベルを Read Committed に設定して、一連の処理を TransactionScope で囲みます。
更新処理が完了した場合は「100 → 101」のように値の変化を表示し、例外がスローされた場合はそのメッセージを表示します。

取得および更新の対象となるのはこの値です。
なお、UnitsInStock 列のデータ型は smallint (C# では short) です。

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

実行結果は上の図のようになります。
例外がスローされているわけでもなく、正常に終了したようにも見えますが、
実際には値が 60 ほどしか増加していません (結果は毎回変化します)。
これがロスト アップデートという現象です。

Read Committed の場合には、後発のスレッドが古い値を読み取ることができる上に、
トランザクション内で共有ロックを最後まで取得し続けないためにすべてのスレッドで更新ができてしまいます。

■ ステップ 2: Serializable (→デッドロック発生)

Read Committed がだめなら、ということで今度は Serializable にしてみます。

---

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

---

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

すると、Read Committed のときには発生していなかったはずの例外が通知されます。
約 20 回は成功し、約 80 回はデッドロックによりロールバックされています。
ただし、ロスト アップデートは発生していません。

デッドロック自体は、あるスレッドの更新時に別のスレッドが共有ロックを取得してしまっていることが原因で発生していますが、
別々のスレッドが更新前の同じ値を読み取ってしまうという根幹の問題は Serializable にしただけでは解決できません。

今回のデッドロックは、先に取得した共有ロックを排他ロックに変換しようとするときに発生することから、
変換デッドロックと呼ばれます。

ところで、Read Committed の場合はすべてのスレッドが正常に終了したと通知されていながら一部のデータが失われているのに対し、
Serializable の場合は正常に終了したと通知された分だけ実際のデータに反映されており、整合性は保たれています。
Serializable にしておけば ACID 属性は保証されるので、Read Committed よりは安全といえるでしょう。

■ ステップ 3: Read Committed & UPDLOCK (→解決)

変換デッドロックを防ぐには、初めの SQL で強めのロックを取得させます。
しかし排他ロックにする必要はなく、更新ロックで十分です。
更新ロックを取得するには、テーブル ヒント「with (updlock)」を使用します。
また、初めの SQL で更新ロックを取得するので、分離レベルは Read Committed で十分となります。

---

private const string SelectCommandText = "select UnitsInStock from Products with (updlock) where ProductID = 1";

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

---

実行します。

すると、今度は見事に期待通りに動作します。

UPDLOCK により初めの読取りのときに更新ロックを取得することで、
後発のスレッドがデータを読み取ろうとしても自身のトランザクションが終了するまで待機させることができます。

注意点
(1) デバッグする場合には、データベース接続がタイムアウトしないよう、
     TransactionOptions および SqlCommand のタイムアウトの値を長めに設定するとよいです。
(2) 更新用の SQL を

         update Products set UnitsInStock = @UnitsInStock where ProductID = 1 and UnitsInStock = @Original_UnitsInStock

     とする方法もありますが、これは主に取得と更新が別トランザクション (対話型など) となる場合に使用されます。
     楽観的同時実行制御と呼ばれ、頻繁なアクセスが見込まれない場合の実装方法です。
     Visual Studio で型指定された DataTable を自動生成した場合は、既定でこの SQL が利用されます。

ロスト アップデートとその解決方法 その 2 に手動トランザクションを利用したコードを、
ロスト アップデートとその解決方法 その 3 に ADO.NET Entity Framework を利用したコードを載せました。

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

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