今年9月に仙台で嵐のコンサートが実施されると、5月1日早朝にテレビなどが報道

嵐ファンの間でLINE等で情報が展開される

仙台近隣のホテル予約に殺到

当該ホテルがシステム障害を起こし、多重予約に至る

予約システムの概要

CREATE TABLE `rooms_available` ( `id` int(11) NOT NULL AUTO_INCREMENT, /* id */ `date` date NOT NULL, /* 宿泊日 */ `room_type` int(11) NOT NULL, /* 客室タイプ */ `available` int(11) NOT NULL, /* 空き室数 */ `reserved` int(11) NOT NULL, /* 予約済み室数 */ PRIMARY KEY (`id`), UNIQUE KEY `date_type` (`date`,`room_type`) ) ENGINE= MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1; CREATE TABLE `transaction_log` ( `id` int(11) NOT NULL AUTO_INCREMENT, /* id */ `date` date NOT NULL, /* 宿泊日 */ `room_type` int(11) NOT NULL, /* 客室タイプ */ `customer` int(11) NOT NULL, /* 顧客番号 */ `rooms` int(11) NOT NULL, /* 予約室数 */ PRIMARY KEY (`id`), UNIQUE KEY `date_room_cust` (`date`,`room_type`,`customer`) ) ENGINE= MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

<?php $date = $_GET['date']; // 宿泊日 $room_type = 1; // 客室タイプは1固定に $customer = $_GET['customer']; // 顧客番号 try { $dbh = new PDO("mysql:host=localhost;dbname=db;charset=utf8", "dbuser", "password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 当該日の空室を求める $sth = $dbh->prepare("SELECT available FROM rooms_available WHERE date=? AND room_type=?"); $sth->bindValue(1, $date, PDO::PARAM_STR); $sth->bindValue(2, $room_type, PDO::PARAM_INT); $sth->execute(); $available = 0; if ($row = $sth->fetch()) { $available = (int)$row['available']; } if ($available > 0) { // 空室ありの場合 // 取引ログに記録 $sth = $dbh->prepare("INSERT INTO transaction_log(date, room_type, customer, rooms) VALUES(?, ?, ?, 1)"); $sth->bindValue(1, $date, PDO::PARAM_STR); $sth->bindValue(2, $room_type, PDO::PARAM_INT); $sth->bindValue(3, $customer, PDO::PARAM_STR); $sth->execute(); // 空室情報を更新 $available--; // 空室数を1減じてDBに書き戻す。同時に予約数を1増やす $sth = $dbh->prepare("UPDATE rooms_available SET available=?, reserved=reserved+1 WHERE date=? AND room_type=?"); $sth->bindValue(1, $available, PDO::PARAM_INT); $sth->bindValue(2, $date, PDO::PARAM_STR); $sth->bindValue(3, $room_type, PDO::PARAM_INT); $sth->execute(); echo "ご予約を承りました"; } else { // 満室の場合 header("HTTP/1.1 400 Bad Request"); // 監視の都合でステータス400とする echo "恐れ入りますがただいま満室でございます"; } } catch (PDOException $e) { header("HTTP/1.1 500 Internal Server Error"); // 監視の都合でステータス500とする error_log($e->getMessage()); // エラー詳細はログに出力 echo 'ただいまアクセスが集中しております。しばらくたってからアクセスしてください'; }

宿泊日の空室数を取得（$available）

空室数が0より大きければ以下を行い、0の場合は満室と表示して終了

宿泊成立として取引ログに宿泊内容を書き込む



空室数を 1 減らし、予約済み室数を 1 増やす

テスト条件

mysql> SELECT * FROM rooms_available; +----+------------+-----------+-----------+----------+ | id | date | room_type | available | reserved | +----+------------+-----------+-----------+----------+ | 1 | 2015-09-18 | 1 | 203 | 0 | | 2 | 2015-09-19 | 1 | 203 | 0 | | 3 | 2015-09-20 | 1 | 203 | 0 | | 4 | 2015-09-21 | 1 | 203 | 0 | | 5 | 2015-09-22 | 1 | 203 | 0 | | 6 | 2015-09-23 | 1 | 203 | 0 | +----+------------+-----------+-----------+----------+

結果

mysql> SELECT * FROM rooms_available; +----+------------+-----------+-----------+----------+ | id | date | room_type | available | reserved | +----+------------+-----------+-----------+----------+ | 1 | 2015-09-18 | 1 | 0 | 719 | ← 各日とも700件超の予約が入っている | 2 | 2015-09-19 | 1 | 0 | 766 | | 3 | 2015-09-20 | 1 | 0 | 712 | | 4 | 2015-09-21 | 1 | 0 | 735 | | 5 | 2015-09-22 | 1 | 0 | 740 | | 6 | 2015-09-23 | 1 | 0 | 715 | +----+------------+-----------+-----------+----------+ mysql> SELECT SUM(reserved) FROM rooms_available; +---------------+ | SUM(reserved) | +---------------+ | 4387 | ← 延べ1,218室のはずが、予約総室数は 4,387に +---------------+ mysql> SELECT COUNT(*) FROM transaction_log; +----------+ | COUNT(*) | +----------+ | 4387 | ← 取引ログの総数も 4,387 件に +----------+

多重予約の原因

空室数を求めてから、空室数をアップデートするまでは排他的に処理を行う必要があります

クリティカルセクション

対策

MyISAMのままテーブルロックを使用する

ストレージエンジンをInnoDBに変更して、トランザクションと行ロックを使用する

ストレージエンジンをInnoDBに変更

トランザクションの使用と行ロック

$dbh = new PDO("mysql:host=localhost;dbname=db;charset=utf8", "dbuser", "password"); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbh->beginTransaction(); // トランザクションの開始 $sth = $dbh->prepare("SELECT available FROM rooms_available WHERE date=? AND room_type=? FOR UPDATE "); // 行ロック // 中略 $dbh->commit(); // コミット echo "ご予約を承りました"; } else { header("HTTP/1.1 400 Bad Request"); // 監視の都合でステータス400とする $dbh->rollBack(); // ロールバック echo "恐れ入りますがただいま満室でございます"; } } catch (PDOException $e) { header("HTTP/1.1 500 Internal Server Error"); $dbh->rollBack(); // ロールバック error_log($e->getMessage()); echo 'ただいまアクセスが集中しております。しばらくたってからアクセスしてください'; }

mysql> SELECT * FROM rooms_available; +----+------------+-----------+-----------+----------+ | id | date | room_type | available | reserved | +----+------------+-----------+-----------+----------+ | 1 | 2015-09-18 | 1 | 0 | 203 | | 2 | 2015-09-19 | 1 | 0 | 203 | | 3 | 2015-09-20 | 1 | 0 | 203 | | 4 | 2015-09-21 | 1 | 0 | 203 | | 5 | 2015-09-22 | 1 | 0 | 203 | | 6 | 2015-09-23 | 1 | 0 | 203 | +----+------------+-----------+-----------+----------+

PHP入門書での対応

まとめ

今年の5月1日に、仙台市内のホテルで多重予約のトラブルが発生したと報道されています。5月1日の朝に何があったのか調べてみると、この日の早朝にテレビや新聞でコンサートの情報が流れたようですね。つまり、こういうことのようです。では、このような多重予約がなぜ発生してしまったかですが、その原因は公表されていないので憶測するしかありません。「ありそうな可能性」としては、排他制御が十分でなかったのではないかという仮説が成り立ちます…ということで、表題のように、「嵐のコンサートがあるとダブルブッキングしてしまう予約システム」を作ってみました。まずはテーブル定義です。rooms_availableは宿泊日と客室タイプ毎の空き部屋数、transaction_logは予約の履歴を保持しています。続いて、客室予約のスクリプトです。宿泊日と顧客番号（整数）を指定して、予約を申し込むものです。処理の性質上POSTリクエストにすべきものですが、テストの都合でGETにしています。処理の大まかな流れは下記の通りです。当該のホテルは203室ということなので、条件を簡単にするために203室が全て同じタイプであり、この事故の前には予約は 0 だったと仮定します。rooms_availableテーブルの状態は下記のとおりです。この状態で負荷テストツール siege を用いて、同時接続 200 で連続的に予約を行い、嵐ファンの予約の様子をシミュレーションしました。siegeを用いた理由は、リクエスト毎にパラメータをファイル指定で変化させやすいからです。負荷テストの模様を映像で紹介します。画面上半分がsiegeを実行している様子。下半分は宿泊日毎の残室と予約済室数のモニタです。負荷テスト後の各テーブルの状態は下記の通りで、合計 1,218件（203×6）の予約しか受け付けられないはずのところ、約4,400件の予約が成立しました。多重予約が発生してしまう原因は、排他制御の不備にあります。以下は、リクエストAとリクエストBが同日の宿泊予約をほぼ同時に要求している場合の模式図です。上図のように、空室数を同時に（並行して）求めて、それぞれ独自に空室判定をしているところに問題があります。同じ日付の空室については、1つのスレッドのみが判定をしなければ多重予約が起きる可能性があります。つまり、。このような箇所はと呼ばれます。上記現象の対策としては排他制御をきちんとするということになりますが、MyISAMはトランザクションに対応していないので、対策方針としては以下の二案が考えられます。どちらでも対策は可能ですが、予約システムは更新処理が多いという特性から、InnoDBとトランザクションを用いるほうが一般的でしょう。トランザクション処理を利用するためには、ストレージエンジンとしてMyISAMではだめで、InnoDBを用いる必要があります。このため、CREATE TABLE文のENGINE=MyISAMとなっている2箇所をENGINE=InnoDBに変更します。次に、トランザクションの使用と行ロックを指示します。PDO+MySQLの組み合わせの場合、デフォルトではSQLの呼び出しのたびにトランザクションを開始して自動的にコミットされます（オートコミット）。これでは排他制御ができないので、beginTransaction()メソッドで明示的にトランザクションを開始して、commit()メソッドでコミットします。また、SELECT文に「FOR UPDATE」を指定することで、選択された行に対して行ロックを行います。予約ができなかった場合や例外を捕捉した場合は、ロールバックにより処理を取り消します。行ロックはコミットあるいはロールバックにより解除されます。改修後の実行結果は以下の通り、各日の予約数は203となり、ダブルブッキングは生じなくなりました。この際の処理の流れは以下のように並行ではなく、排他的な処理になり、一貫性は維持されます。PHP入門書ないし中級レベルの解説書では、通常トランザクション処理や排他制御の解説はありません。には、トランザクションの簡単な説明がありますが、具体例などは説明されていないようです。にはトランザクションの詳しい説明がありますが、これは著者の石井達夫氏がPosgreSQLの専門家なので当然かもしれません。嵐のコンサート情報が引き起こしたホテル多重予約問題を題材として、トランザクションと排他制御の重要性について説明しました。当該の事故の原因はおそらくもっと複雑なものであろうと予想しますが、ここで説明したレベルの単純な排他制御であってもPHPの入門書等では解説されていないので、初めて知ったという読者も多いのではないでしょうか。ホテル予約のダブルブッキングがセキュリティ上の問題かといえば、広義に捉えればセキュリティ上の問題と言えると思いますが、排他制御不備が個人情報漏洩等につながる場合もあり、セキュリティに直結する場合もあります。このため、拙著4.15節では、「共有資源に関する問題」として（SQLの問題ではありませんが）排他制御の重要性について説明しています。【HASHコンサルティング広告】HASHコンサルティング株式会社は、セキュリティエンジニアを募集しています。興味のある方は、 twitter facebook のメッセージ、あるいは 問い合わせページ からお問い合わせください。