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

ロスト アップデートとその解決方法 その 1 では SqlCommand クラスを利用したコードを示しましたが、
今回は ADO.NET Entity Framework を利用したコードを示します。
ただし、ステップ 3 (Read Committed & UPDLOCK) の場合のみ示します。

Entity Framework では基本的に LINQ to Entities でクエリを記述しますが、更新ロックを取得するための構文は存在しません。
ObjectContext.ExecuteStoreQuery メソッドにより、データベースに依存する任意の SELECT 文を実行できます。

なお、下記のコードを記述する前に Visual Studio で ADO.NET Entity Data Model を作成しておきます。
名前は NorthwindModel.edmx としておきます。

---

using System;
using System.Linq;
using System.Threading.Tasks;
using System.Transactions;

namespace LostUpdate
{
    class Program
    {
        private static readonly TransactionOptions transactionOptions = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };

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

        private static void AddUnitsInStock()
        {
            try
            {
                short oldValue, newValue;

                using (var scope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
                using (var context = new NorthwindEntities()) 
                {
                    // 商品を取得します。
                    Product product = context.ExecuteStoreQuery<Product>("select * from Products with (updlock) where ProductID = {0}", 1).Single();
                    context.AttachTo("Products", product);

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

                    // 商品を更新します。
                    context.SaveChanges();

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

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

---

注意点
(1) ObjectContext.ExecuteStoreQuery メソッドでパラメーター化クエリを実行する場合、
     パラメーター名には順に {0}, {1}, {2}, … または @p0, @p1, @p2, … を使用します。
(2) ObjectContext.ExecuteStoreQuery メソッドを使用した場合、
     取得したデータは自動的に ObjectContext にアタッチされないため、ObjectContext.AttachTo メソッドを呼び出します。
     標準的な

          Product product = context.Products.Single(p => p.ProductID == 1);

     などのような場合には呼び出す必要はありません。
(3) UPDATE、INSERT、DELETE などの変更操作を任意の SQL で実行する場合には、
     ObjectContext.ExecuteStoreCommand メソッドを使用します。

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

参照
ObjectContext.ExecuteStoreQuery メソッド
ObjectContext.ExecuteStoreCommand メソッド
ストア コマンドの直接実行

広告

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

前回のロスト アップデートとその解決方法 その 1 では自動トランザクション (TransactionScope) を
利用したコードを示しましたが、今回は手動トランザクションを利用したコードを示します。

ただし、特に説明する部分はないので、ステップ 3 (Read Committed & UPDLOCK) の場合のみ示します。
TransactionScope クラスの代わりに SqlTransaction クラスを利用するだけです。

---

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

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 with (updlock) where ProductID = 1"; 
        private const string UpdateCommandText = "update Products set UnitsInStock = @UnitsInStock where ProductID = 1";

        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 connection = new SqlConnection(NorthwindConnectionString)) 
                { 
                    // データベース接続を開きます。
                    connection.Open(); 

                    // トランザクションを開始します。
                    SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); 

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

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

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

                    // コミットします。
                    transaction.Commit(); 
                } 

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

---

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

ロスト アップデートとその解決方法 その 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)