最近物忘れのひどい私が書いた、二重登録防止のためにMySQLでロックを使ったPHPのサンプルコード #PHP

結論

InnoDBのテーブル
トランザクションを開始する
SELECT ... FOR UPDATEをする
新規追加もしくは更新のクエリを実行する
トランザクションを終了する

概要

１件だけの登録、１件のだけの更新時の処理用
同時登録、同時更新によりデータの整合性が取れなくなる事を防ぐ事が目的
行ロックなのかテーブルロックになってしまうかなどは、考えていない

サンプル用テーブル

idとnoだけのテーブル。InnoDBである事。

CREATE TABLE test (
  id int NOT NULL AUTO_INCREMENT,
  no int NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

-- １件入れておく
INSERT INTO test (no) VALUES (1);

新規作成

仕様はnoは重複してはならない（ユニークインデックスを貼れば済むが）とします。なので挿入するnoが存在していない事をチェックしてから挿入します。「伝票番号などのnoが重複してはならないという仕様ならユニークインデックス張っておいたら？」と思いますか？思いますよね？私も思うんです。でも世の中にはユニークインデックス使用禁止というシステムが存在するので、存在する事を覚えておいてください。

// 伝票番号など 例)100番
$no = 100;

$pdo = new \PDO(略);

try {

    $pdo->beginTransaction();

    // ロックを取得する
    // FOR UPDATEで他のセッションがロックを取得している場合、解除されるまで待つ
    // FOR UPDATEの後ろにNOWAITを付けている場合、解除されるまで待たずに例外がスローされる
    $stmt = $pdo->query("SELECT COUNT(*) as cnt FROM test WHERE no = {$no} FOR UPDATE");
    // $stmt = $pdo->query("SELECT COUNT(*) as cnt FROM test WHERE no = {$no} FOR UPDATE NOWAIT");

    // ３秒以内に別ブラウザで叩いて確認する。FOR UPDATE を付けてないと二重登録されてしまう
    sleep(3);

    $r = $stmt->fetch(\PDO::FETCH_OBJ);
    if (0 < $r->cnt) {
        echo "<h1>no({$no})のデータが既に存在しています。</h1>";
        exit;
    }

    // 新規追加
    $pdo->exec("INSERT INTO test (no) VALUES ({$no})");
    $pdo->commit();

    echo '<h1>正常に登録されました</h1>';
}
catch (\Exception $e) {
    $pdo->rollBack();
    echo '<h1>エラーが発生しました</h1>';
    echo $e->getMessage();
    // NOWAITでロックが取得できなかった時の例外メッセージ
    // SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
}

更新処理

なんらかの処理が行われたらnoがインクリメントされる処理があるとします。
もし完全に同時に２回行われた時、１回分のインクリメントしかされない事がないようにロックを取得します。

$pdo = new \PDO(略);

try {
    $pdo->beginTransaction();

    // idが1のデータのnoをインクリメントするため現在の値を取得
    $stmt = $pdo->query('SELECT no FROM test WHERE id = 1 FOR UPDATE');
    // $stmt = $pdo->query('SELECT no FROM test WHERE id = 1 FOR UPDATE NOWAIT');

    // ３秒以内に別ブラウザで叩いて確認する。FOR UPDATEが入っていないと２回のインクリメントのはずが１回分しか増えない
    sleep(3);

    // 値をインクリメントする更新処理
    $r = $stmt->fetch(\PDO::FETCH_OBJ);
    $new_no = $r->no + 1;
    $pdo->exec("UPDATE test SET no = {$new_no} WHERE id = 1");
    $pdo->commit();

    echo '<h1>正常に更新されました</h1>';
    echo "{$r->no}から{$new_no}へ更新";

}
catch (\Exception $e) {
    $pdo->rollBack();
    echo '<h1>エラーが発生しました</h1>';
    echo $e->getMessage();
}