SQLの競合チェックを確実に行う方法（Node.js + PostgreSQL）

データベースを扱うシステムでは、複数の利用者が同時にデータを操作するため、競合（データの不整合）が発生することがあります。
この記事では、特定の条件で競合が発生していないかを確認するSQLの書き方について解説します。

競合チェックが必要な理由

データベースでは、同じデータを複数の処理が同時に更新しようとすると、意図しない上書きが発生する可能性があります。
例えば、以下のような場面が考えられます。

このような事態を防ぐために、データの競合が発生していないかを確認する必要があります。

SQLを使った競合チェックの実装例

以下のSQLを実行することで、指定したデータがデータベース内に存在し、かつ更新日時が一致しているかを確認できます。

SELECT COUNT(*) AS count
FROM データテーブル名
WHERE id = $1
AND DATE_TRUNC('second', 更新日時カラム) = DATE_TRUNC('second', $2::timestamptz)

各条件の意味

1. id = $1
   • 特定のデータ（行）を識別するための条件です。更新対象のIDと一致するデータがあるかを確認する
2. DATE_TRUNC('second', 更新日時カラム) = DATE_TRUNC('second', $2::timestamptz)
   • 更新日時カラム の値を秒単位で切り捨て、指定した更新日時（$2）と比較する
   • これにより、ミリ秒以下の細かいズレによる誤判定を防ぐ

このSQLの実行結果

• count が1以上の場合 → 指定したidのデータが存在し、指定した更新日時と一致している
• count が0の場合 → 指定したidのデータが存在しない、または更新日時が異なっている

競合が発生するケース

このSQLを使ったチェックの結果 count が0 になるのは、次のような場合です。

1. データが削除された
   • 対象のIDを持つデータがすでに削除されている
2. 別の処理でデータが更新されている
   • すでに別の処理によって更新日時が変わっており、指定した日時と一致しない

この場合、競合が発生していると判断できます。

実装例（Node.jsを使用）

実際のシステムでこのSQLをどのように実装するかを示します。

/**
 * データの競合をチェックする関数
 * @param {object} client - データベース接続オブジェクト
 * @param {string} tableName - 確認対象のテーブル名
 * @param {number} id - チェック対象のID
 * @param {string} updateDt - チェック対象の更新日時
 * @returns {boolean} 競合がある場合はtrue、ない場合はfalse
 */
const isConflict = async (client, tableName, id, updateDt) => {
  const query = {
    text: `
      SELECT COUNT(*) AS count
      FROM ${tableName}
      WHERE id = $1
      AND DATE_TRUNC('second', update_dt) = DATE_TRUNC('second', $2::timestamptz)
    `,
    values: [id, updateDt],
  };

  const results = await client.query(query);
  return results.rows[0].count === "0";
};

関数の説明

1. tableName（テーブル名）、id（対象のID）、updateDt（更新日時）を引数に取る
2. SQLを実行し、条件に一致するデータの件数を取得する
3. 取得した件数が0なら、データが存在しないか更新日時が変わっているため、競合があると判断する
4. 競合がある場合は true を返し、競合がない場合は false を返す

競合を防ぐための対策

楽観的ロックを使用する

楽観的ロックとは、データを更新する前に、更新前の情報と一致するかをチェックする方法です。
上記のSQLを実行し、データが想定どおりの状態かを確認してから更新処理を行います。

トランザクションを活用する

トランザクションを使用することで、データの更新が適切に行われるように制御できます。
競合が発生した場合はロールバックすることで、不整合を防ぐことが可能です。

更新日時の精度を統一する

システムによっては、更新日時の精度がミリ秒単位か秒単位かで異なることがあります。
SQL側で DATE_TRUNC('second', ...) を使うことで、比較時のズレを防ぎます。

まとめ

データベースの競合を防ぐには、適切なチェックとロックの仕組みを導入することが重要です。
本記事で紹介したSQLの競合チェックを活用することで、データの整合性を保つことができます。
実装の際には、トランザクションや楽観的ロックと組み合わせて、より安全なシステムを構築してください。

• Original:https://minory.org/sql-check-conflict.html
• Source:minory
• Author:管理者

Amazonベストセラー