1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@K_White

DataAdapterでレコードをDeleteしたときの同時実行違反

1
Posted at

はじめに

DBのデータをDataTableで扱っており、DBへのデータ保存はDataAdapterで行っています。

行の挿入/更新/削除をする場合、挿入/更新と削除の順序が適切ではないと、削除時に同時実行違反(System.Data.DBConcurrencyException)を発生させてしまう可能性があります。
よく考えなくても当然のことですがやりがちなミスだったので、備忘録として記事に残します。

発生するエラー

Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.

同時実行違反：DeleteCommand によって、処理予定の 1 レコードのうち 0 件が処理されました。

結論

DataAdapterでDBにデータを反映する際は、以下の順序で処理を行う

  • 通常
  1. 親テーブルのレコードをInsert/Update/Delete
  2. 子テーブルのレコードをInsert/Update/Delete
  • リレーションにon delete cascadeが付いている場合
  1. 子テーブルのレコードをDelete
  2. 親テーブルのレコードをInsert/Update/Delete
  3. 子テーブルのレコードをInsert/Update

とする

同時実行違反が起きるメカニズム

当たり前のことを書いているのでここから先は読まなくても大丈夫

操作の概要

  1. DBのデータをDataTableに読み込む
    この段階ではDBのデータと手元のDataTableは一致しています
  2. DataRowをDelete
    手元のDataTableの状態が変化します
  3. DBにデータをコミット
    1. 親テーブルにinsert/update/deleteを実行
    2. 子テーブルにinsert/update/deleteを実行
  4. 3.2. で同時実行違反発生

重要なのは、「3. DBにデータをコミット」で親テーブルのレコードを先にdeleteしていることです。

親テーブルにdeleteを実行することで、親テーブルの削除対象データとそれに紐づく子テーブルのレコードがdeleteされます。
しかし、子テーブルに対応する手元のDataTableは、DataRow.RowState=DataRowState.Deletedで存在しています。

ところで、DataAdapterは楽観的同時実行制御(Optimistic Lock)を採用しています。
楽観的同時実行制御についてClaude Sonnet4.5に聞いてみました。

楽観的同時実行制御とは、複数のユーザーや処理が同じデータを同時に操作する可能性がある環境において、データの整合性を保つための手法の一つです。

この手法では、データを読み込んだ時点では排他ロックをかけず、更新時にデータが読み込み時点から変更されていないかを確認します。変更されていた場合は更新を拒否し、同時実行違反として例外をスローします。
これにより、先に更新した処理の内容が後から更新した処理によって上書きされてしまうという問題を防ぎます。

ようは、updateやdeleteのとき、Where句にヒットするレコードが無い場合は、更新対象のデータが変更されているとみなして同時実行違反の例外を投げるということです。

DataAdapterによって生成されたUpdateCommandやDeleteCommandには、更新対象のレコードを特定するためにWhere句が含まれています。
このWhere句にはprimary keyだけではなく、各列の元の値が使用されます。
手元のDataTableに存在するデータが、DB上には存在していないので、DeleteCommandのSQLのWhere句にヒットするレコードが無い、という状態になってしまいます。

そのため、3.2.で子テーブルの更新を行うと同時実行違反が発生します。

自分しか操作していないのに何で「同時実行」違反なんだろう？と頭を悩ませるエラーですが、上記のような理由があります。

コードと一緒に見てみる

前提

  • 親テーブル…parent

  • 子テーブル…child

  • childはparent_idという列を持っており、parentのid列を参照している

  • このリレーションにはon delete cascadeが付いている

  • 対応するDataTableのForeinKeyConstraint.DeleteRuleはRule.Cascadeとなっている

他のプロセスによるDB更新はなく、本処理だけが動いているものとします

DBアクセスのためにDBManagerのクラスを定義しておきます

DataBaseManager.cs
/// <summary>
/// データ操作の種類を指定する列挙型
/// </summary>
[Flags]
public enum DataOperation
{
    None = 0,
    Insert = 1,
    Update = 2,
    Delete = 4,
    All = Insert | Update | Delete
}

public class DatabaseManager : IDisposable
{
    private readonly string connectionString;
    private NpgsqlConnection connection;
    private NpgsqlTransaction transaction;

    public DatabaseManager(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public void BeginTransaction()
    {
        connection = new NpgsqlConnection(connectionString);
        connection.Open();
        transaction = connection.BeginTransaction();
    }

    public void CommitTransaction()
    {
        if (transaction != null)
        {
            transaction.Commit();
            transaction.Dispose();
            transaction = null;
        }

        if (connection != null)
        {
            connection.Close();
            connection.Dispose();
            connection = null;
        }
    }

    public void RollbackTransaction()
    {
        if (transaction != null)
        {
            transaction.Rollback();
            transaction.Dispose();
            transaction = null;
        }

        if (connection != null)
        {
            connection.Close();
            connection.Dispose();
            connection = null;
        }
    }

    public void Dispose()
    {
        if (transaction != null)
        {
            transaction.Dispose();
        }

        if (connection != null)
        {
            connection.Dispose();
        }
    }

    /// <summary>
    /// 指定されたDataTableの変更内容をデータベースに反映する
    /// </summary>
    /// <param name="dataTable">更新対象のDataTable</param>
    /// <param name="selectCommandText">データ取得用のSELECT文</param>
    public void UpdateDataTable(DataTable dataTable, string selectCommandText)
    {
        if (connection == null || transaction == null)
        {
            throw new InvalidOperationException("トランザクションが開始されていません。");
        }
    
        using (var adapter = new NpgsqlDataAdapter(selectCommandText, connection))
        {
            adapter.SelectCommand.Transaction = transaction;
    
            var commandBuilder = new NpgsqlCommandBuilder(adapter);
            adapter.InsertCommand = commandBuilder.GetInsertCommand();
            adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
            adapter.DeleteCommand = commandBuilder.GetDeleteCommand();
    
            adapter.InsertCommand.Transaction = transaction;
            adapter.UpdateCommand.Transaction = transaction;
            adapter.DeleteCommand.Transaction = transaction;
    
            adapter.Update(dataTable);
        }
    }

    
    /// <summary>
    /// 指定されたDataTableの変更内容をデータベースに反映する
    /// </summary>
    /// <param name="dataTable">更新対象のDataTable</param>
    /// <param name="selectCommandText">データ取得用のSELECT文</param>
    /// <param name="operations">実行する操作(Insert/Update/Delete)</param>
    public void UpdateDataTable(DataTable dataTable, string selectCommandText, DataOperation operations)
    {
        if (connection == null || transaction == null)
        {
            throw new InvalidOperationException("トランザクションが開始されていません。");
        }

        // 操作が指定されていない場合は何もしない
        if (operations == DataOperation.None)
        {
            return;
        }

        // 全ての操作が指定されている場合は既存のメソッドを使用
        if (operations == DataOperation.All)
        {
            UpdateDataTable(dataTable, selectCommandText);
            return;
        }

        // 指定された操作のみを含むDataTableを作成
        DataTable filteredTable = dataTable.Clone();

        foreach (DataRow row in dataTable.Rows)
        {
            bool shouldInclude = false;

            if ((operations & DataOperation.Insert) == DataOperation.Insert && row.RowState == DataRowState.Added)
            {
                shouldInclude = true;
            }
            else if ((operations & DataOperation.Update) == DataOperation.Update && row.RowState == DataRowState.Modified)
            {
                shouldInclude = true;
            }
            else if ((operations & DataOperation.Delete) == DataOperation.Delete && row.RowState == DataRowState.Deleted)
            {
                shouldInclude = true;
            }

            if (shouldInclude)
            {
                filteredTable.ImportRow(row);
            }
        }

        // フィルタリングされたDataTableを更新
        if (filteredTable.Rows.Count > 0)
        {
            UpdateDataTable(filteredTable, selectCommandText);
        }
    }
}

1. DBのデータをDataTableに読み込む

それぞれparentDataTableとchildDataTableに格納

var connectionString = "Host=localhost;Port=5432;Database=test;Username=postgres;Password=postgres";
var parentSelectCommand = "SELECT id, code, name FROM parent";
var childSelectCommand = "SELECT id, parent_id, code, name FROM child";

// parentテーブル読み込み
var parentDataTable = new DataTable();
using (var connection = new NpgsqlConnection(connectionString))
{
    await connection.Open();
    using (var adapter = new NpgsqlDataAdapter(parentSelectCommand, connection))
    {
        adapter.Fill(parentDataTable);
    }
}

// childテーブル読み込み
var childDataTable = new DataTable();
using (var connection = new NpgsqlConnection(connectionString))
{
    await connection.Open();
    using (var adapter = new NpgsqlDataAdapter(childSelectCommand, connection))
    {
        adapter.Fill(childDataTable);
    }
}

2. DataRowをDelete

子テーブルの行を削除してから、それが紐づいていた親テーブルの行を削除する
ForeinKeyConstraint.DeleteRule == Rule.Cascadeの場合は、親テーブルの行だけ削除すればOK
(デフォルトはCascade)

var childRow = childDataTable.AsEnumerable().FirstOrDefault(r => Convert.ToInt32(r["id"]) == 123);
var parentRow = childRow.GetParentRow(childDataTable.ParentRelations[0]);

// DeleteRuleがCascadeでない場合
// 子テーブルの行を削除
childRow.Delete();
// 親テーブルの行を削除
parentRow.Delete();


// DeleteRuleがCascadeの場合
// 親テーブルの行を削除
parentRow.Delete();

3. DBにデータをコミット

using (var connection = new NpgsqlConnection(connectionString))
{
    try
    {
        dbManager.BeginTransaction();
    
        // 3.1. 親テーブルにinsert/update/deleteを実行
        dbManager.UpdateDataTable(parentDataTable, ProjectDataTable.SelectCommand);
    
        // 3.2. 子テーブルにinsert/update/deleteを実行
        // 同時実行違反発生
        dbManager.UpdateDataTable(childDataTable, TaskDataTable.SelectCommand);
    
        dbManager.CommitTransaction();
    }
    catch
    {
        dbManager.RollbackTransaction();
        throw;
    }
}

ベストプラクティス

テーブルの更新順序を考えましょう。

全体の共通事項として、一つのテーブルに対する処理はまとまっていたほうが、可読性の向上につながると思っているので、一つのテーブルに対する処理はなるべくまとめて記述するようにします。

親の更新

  • 親のinsert→制限なし
  • 親のupdate→制限なし
  • 親のdelete→子の関係するレコードもdelete

親のinsert/update/deleteは順序で考慮する優先度が低いと考えられます。

子の更新

  • 子のinsert→親レコードが必要、参照行がinsert行の場合は親のinsert完了後にinsert
  • 子のupdate→親レコードが必要
  • 子のdelete→制限なし

子のinsert/updateは親の処理が完了した後に実行する必要があるとわかります。
また、子のdeleteは順序で考慮する優先度が低いように考えられますが、親レコードがdeleteされている状態で子のdeleteを行ってはいけないので、子のdeleteは親のdeleteよりも先に実行する必要があります。

これらを踏まえて、更新順序を組み立てます。

まず、子のinsertは親の処理が完了した後に実行する必要があるため、次のようになります

// 親の処理        ←
// 子のinsert  　　←

同様に子のupdateを配置します

// 親の処理
// 子のinsert
// 子のupdate  　　←

子のdeleteは親のdeleteよりも先に実行する必要があるため、親の処理より先に配置します

// 子のdelete  　　←
// 親の処理
// 子のinsert
// 子のupdate

親の処理を配置します

// 子のdelete
// 親のinsert  　　←
// 親のupdate  　　←
// 親のdelete  　　←
// 子のinsert
// 子のupdate

この処理順序であれば、同時実行違反が起きないようにテーブルが更新できます。

あとがき

DBの制約と操作には気を付けよう

また、魔界の仮面弁士さんに敬意を表します。本当に助かっています...

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?