CakePHP3での重複予約排他制御について

Question

###前提・実現したいこと
CakePHP3を用いた予約システムを構築しています。
重複した時間に他ユーザーが予約できないように制御を行いたいのですが、同時アクセスがあった場合、重複チェックをする抜け、同じ時間で予約ができてしまうことがわかりました。

サンプル

<?php

// ①トランザクション開始
$this->Model->connection()->begin();

try {

    // ②重複チェック
    if ($this->Model->exists([])) {
        throw new Exception('error');
    }

    // ③保存処理
    $entity = $this->Model->newEntity([]);
    if (!$this->Model->save($entity)) {
        throw new Exception('error');
    }

    // ④コミット
    $this->Model->connection()->commit();

} catch(Exception $e) {
    // ⑤ロールバック
    $this->Model->connection()->rollback();
}

②で重複チェックを行っていますが、同時アクセスの場合save前に両アクセス分のチェックが完了してしまい、両データとも保存されてしまう状況です。

②の重複チェックから保存処理までの間、もう一方のアクセスを待機させておく、もしくはDBへのアクセスをロックする方法などありますでしょうか？

知識不足で恐縮ですが、是非、お力お貸しいただけますでしょうか。
何卒よろしくお願いいたします。

###バージョンなど
PHP：7.1.3
CakePHP：3.4.11
MySQL：5.7.17
Nginx：1.10.2

追記

該当テーブルは以下となります。

CREATE TABLE `bookings` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `car_id` bigint(20) NOT NULL,
  `parking_id` bigint(20) NOT NULL,
  `status` tinyint(2) NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `checkin_date` datetime DEFAULT NULL,
  `checkout_date` datetime DEFAULT NULL,
  `cancel_date` datetime DEFAULT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Comments

[TakeoSaki]

MySQLのDBとテーブルには MyISAMやInnoDBなど複数のストレージエンジンのどれを使うか設定できます。
トランザクションの使用できるかが変わりますので、SHOW CREATE TABLE tablename ; の結果などを載せることをお勧めします。

[samuraiders]

テーブル構造を追記しました。よろしくお願いいたします。

Answer 1

通常のSELECTではその段階ではロックがかからないので、
クエリビルダーでSELECT文に追記する形で
FOR UPDATE などの句を追加することができるでしょう。
https://book.cakephp.org/3.0/ja/orm/query-builder.html#id22

もしSELECTに手を入れたく無いなら、
トランザクションの開始前に、 SET TRANSACTION 文を用いて
トランザクションの分離レベルをSERIALIZABLE に上げ、
より強い一貫性を持たせることで回避できそうるかもしれません。
https://dev.mysql.com/doc/refman/5.6/ja/set-transaction.html

生のSQLを実行させる場合
https://book.cakephp.org/3.0/ja/orm/database-basics.html#running-select-statements

しかしながら、今回のテーブル構造のままでは、
該当時間が予約されているかどうかを知るには範囲で検索する必要があり、
行単位のロックでは存在しない行があるのでおそらくうまくいきません。
たとえば10:00〜13:00の予約があるときに
11:00〜12:00の予約を防ごうとしても、そのような行はありませんので、
テーブル全体をロックせざるを得ません。

予約という性質上、未来の日付しか扱わず、
またせいぜい直近の50日など限られた将来のみで予約するのなら、
細かい単位の大量のレコードが発生しますが、
先に他の方が挙げたように5分刻みの空き状況の表を作る方が良いかもしれません。
(毎日cronなどで古い予約を破棄し、新しい空き情報レコードを追記する)
演算が面倒ですが、"2017-08-24" : 111111000011,111000000000,.... のような
24時間×60分÷5のビットマスクなどで表現する方法もあるでしょう。

もしアクセスがそう多く無いのであれば LOCK TABLE 文を用いて
明示的にテーブルごとロックしてしまう方法もあります。
https://dev.mysql.com/doc/refman/5.6/ja/lock-tables.html
この方法はパフォーマンスに悪影響があり、
またロックがすぐに解放されないとアクセス障害のリスクがあり、運用は慎重を要します。

もしこの辺りが難解で、使用しているphpサーバが1台であるならば、
極めて場当たり的な対応にはなりますが、ファイルロックで逃れられる可能性はあります。
下記のコード例は ファイルをロック用に利用して、
同一ブロックが同時に利用されることを防ぐ(手抜きな)コードです。
phpはファイルポインタ変数参照が無くなると自動でflockは解除されます。
他の場所(コード)でも予約状況を触る箇所がある場合は
ロックファイルの場所は別途定義して作成しておく必要があります。

$fp = fopen(__FILE__, "r");
$nTried = 0;
while($nTried++ < 10) { // 10回失敗したら諦める
  $locked = flock($fp, LOCK_EX|LOCK_NB); 
  if ($locked ) break;
  sleep(1);
}
if (! $locked) {
  throw new \RuntimeException("Lock failed");
}
try {
  // 予約のスキャン及び設定
  // ....
} catch (\Exception $e) {
  // ...
}
fclose($fp); // ファイルを閉じるとロックも解放される

cronの二重起動を防ぐ時などに利用できるお手軽な方法です

Comments

[samuraiders]

サンプルコードまで添付して頂きありがとうございます。
既に開発フェーズも終盤となっており、5分毎にレコードを区切ることは実現できませんが、LOCK TABLEなどの知識を深め、対応してみようと思います。

Answer 2

まだ回答を求めているようなので、参考になりそうな文献をあげておきます

MySQLのINSERT/UPDATE時におこる不整合対策

本件は、「ファントムリード問題」だと思うので、デフォルトの分離レベルでは、SELECT ... LOCK IN SHARE MODEを使えば回避できるのではないかと思います。ただし、これは悲観的ロックであり、後発のSQLはデッドロックが発生することになるのでその辺をアプリ側でカバーする必要があると思います。

※なお、自分はこのロックを使った実装をやったことがないです...すみません。

Comments

[samuraiders]

ご回答ありがとうございます。
他の方にも悲観的ロックのアドバイスを頂いたのですが、理解が追いついておらず、まだ解決には至っておりません。

[popobot]

理解をすすめるのも大事ですが、まずはコードを書いて実験されてはいかがでしょうか
CakePHP3でロック系のSQLを実行したい場合epilog()が使えます。
https://book.cakephp.org/3.0/ja/orm/query-builder.html#id22

今回の場合「重複チェック」の処理で、ロックをかけるようにすればいいと思います。
それで実際に重複するようにsleepなどで調整して、実際にどのような動作をするか検証したら、具体的なイメージが湧くと思いますよ

Answer 3

他の方宛のコメントに乗っかる感じで大変恐縮ですが、

例：

予約A：10:00〜12:00
予約B：11:00〜12:00

でしたら、（例えば1時間単位で）予約の開始時間を登録する感じにしたらどうでしょう。

日付	時間	予約したユーザー
2017/9/1	10:00	予約A
2017/9/1	11:00	予約A

のようなテーブル構成にして、

• 日付
• 時間

でUNIQUE制約をかければ、

日付	時間	予約したユーザー
2017/9/1	11:00	予約B

のINSERTは防げるのでは？

Comments

[samuraiders]

ご回答頂きありがとうございます。
ご教授頂いた方法で実現できそうですが、今回の予約システムは5分後のに予約が行えるようになっており、5分後毎にレコードを保持するとレコード数が多くなるため1レコードにstartとendを保持しております。ここではご説明を省いておりました。

やはり、他の方からもありましたとおり、悲観的ロックでの実装がよさそうですが、当方の知識不足で未だ理解ができておらず、実装の目処が立たない状況です。

引き続き調べたいと思います。

Answer 4

方法としてはMySQLで悲観的ロックをかけて他方の処理を待たせるか、DBの値にUNIQUE 制約を利用して後に書き込んだ方をUNIQUE 制約エラーにするなどで同時の重複登録を避けれるかと思います。

Comments

[samuraiders]

ご回答ありがとうございます。
例えば、以下の様な予約データの場合、UNIQUE制約は効果的ではないと考え不採用としました。

例：
予約A：10:00〜12:00
予約B：11:00〜12:00

悲観的ロックについてもネット情報を色々調べてたのですが、未だ具体的にどう実装していいいのか理解ができておりません。

引き続き調べてみます。

[aro10]

この辺りの記事を参考にしてみてください SELECT ~ FOR UPDATE
MySQL - InnoDBのロック関連まとめ[http://qiita.com/mizzwithliam/items/31fb68217899bd0559e8]