データベーススペシャリスト 2020年 午後1 問02

解答の論理構成

1. 空席判定部 (a)(b)
   図4は
   「( (a) A2.空席数 (b) THEN 'X' ELSE 'O' END ) AS 空席情報」
   と示されています。空席数がNULLなら売切れ (×) にしたいので、SQL標準の条件式 CASE WHEN … IS NULL THEN … を用いるのが自然です。したがって
   (a) CASE WHEN
   (b) IS NULL
   となります。
2. テーブル結合部 (c)
   A2 は「空席数 > 0」で抽出済みの在庫ビューです。これに一致しない行でも公演情報は一覧に載せる必要があるため、外部結合が必須です。方向は「会場、A1」に対し A2 をくっつけるので LEFT OUTER JOIN が正解です。
3. 日付条件部 (d)(e)
   条件句は
   「公演日 (d) '2020-04-01' (e) '2020-04-30'」
   とあり、期間検索を1文で表す BETWEEN … AND 構文が典型です。従って
   (d) BETWEEN
   (e) AND
   となります。
4. 並び替え部 (f)(g)
   要件「公演名・公演日・開演時刻の昇順、料金の降順」に対応するため、 ORDER BY 公演名, 公演日, 開演時刻, 料金 DESC
   のように記述します。図4の (f)…(g) に該当するのは
   (f) ORDER BY
   (g) DESC
   です。

誤りやすいポイント

• IS NULLではなくA2.空席数 = 0と書くと、空席数列が存在しない行を売切れ扱いできません。
• INNER JOIN を選ぶと在庫が無い席種が一覧に出ず要件違反となります。
• BETWEEN を使わず >= と <= を別々に書くと (e) を何にするか迷って誤答しがちです。
• ORDER BY で料金だけ DESC にする場合、列ごとに昇降を個別指定できることを忘れ、全体を DESC 指定してしまうケースがあります。

FAQ

解答の導き方

• WHERE の条件は SQL では NULL 比較に等号 (=) を用いず、必ず IS NULL を使います。例えば、個々の座席について「購入番号が NULL のときだけ購入フラグをオンにする」処理は、次のような条件付き UPDATE にしてください（字下げした素のテキストで示します）。
  UPDATE チケット SET 購入フラグ = 'オン' WHERE 公演番号 = … AND 公演日 = … AND 開演時刻 = … AND 座席番号 = … AND 購入番号 IS NULL;
• この方式は「TOCTOU（時間差による競合）」を避けます。SELECT で確認してから UPDATE する方式だと、SELECT→UPDATE の間に他トランザクションが状態を変えると競合が発生しますが、条件付き UPDATE は「確認と更新を原子操作としてデータベースに行わせる」ため、安全性が高くなります。UPDATE 実行後は更新件数（rows affected）を必ず確認し、期待した件数が更新されていなければ競合検出として処理を取りやめるかリトライします。

• 長所：各座席の処理ごとにロック保持時間が短くなり、他の会員の待ち時間やロック競合が減る（同時実行性が向上する）。表題にある「やり直しが極力発生しないようにする方針」に合致する（再試行が減る方向に寄与する）。
• 短所：ある会員が複数枚を一括で購入する場合に全体の原子性（Atomicity）が失われる可能性がある。具体例：3席購入のうち最初の2席はコミットされ、3席目の決済が失敗すると部分的に購入が成立してしまう。図5は例外処理を省略しているため、この設計変更を行うなら失敗時の補償（既にコミットされた座席の取消）や仮予約のタイムアウト等を明確に設計する必要があります。

• 複数枚の購入に対して「全体の原子性を保証したい」場合：販売側と空席管理側で一貫した原子性を保つには分散トランザクション（2フェーズコミット等）を採用するか、販売側で仮予約を行い最終確定時にまとめて確定する方式（仮予約フラグ＋最終更新）を用いる。どちらも運用コストや性能への影響があるため慎重に検討します。
• 部分コミットを許容する場合：1座席ごとにコミットしても問題ない業務ルール（部分購入が許容される、あるいは自動キャンセルで補償できる）なら有効です。補償ロジックとユーザーへの扱い（部分成立の可否、返金等）を明確にします。

誤りやすいポイント

• NULL比較に等号（=）を使う：SQLでは必ず IS NULLを使用する点を見落としやすい。
• SELECT→UPDATE の組合せで TOCTOU を起こす：先に SELECT してから UPDATE すると、その間に状態が変わる可能性がある。条件付き UPDATE を使うか SELECT … FOR UPDATE を検討する。
• 更新件数のチェックを忘れる：条件付き UPDATE の後に rows affected を確認しないと、競合で更新されなかったことに気づけない。
• 1座席ごとのコミットで原子性を失うことを見落とす：複数枚購入の原子性要件を満たすかを必ず検討する。
• ロック／隔離レベルの挙動の誤解：READ COMMITTEDは参照時の共有ロックを参照終了時に解放する、REPEATABLE READはトランザクション終了時まで共有ロックを保持する、という点を押さえる。

FAQ

解答の論理構成

1. 目的の確認
   空席確認～仮予約の間に「同時実行されたトランザクションのやり直しが極力発生しないようにする方針」（【問題文】）が示されています。
2. READ COMMITTEDの挙動
   表1より、"READ COMMITTED" は「参照終了時に共有ロックを解放」。座席行を SELECT した直後にロックが外れるため、他の会員が直後に UPDATE で仮予約できてしまいます。
3. REPEATABLE READの挙動
   同表より、"REPEATABLE READ" は「トランザクション終了時に解放」。SELECT した座席行は COMMIT まで共有ロックが維持され、他者による UPDATE（専有ロック要求）は待ち状態になります。
4. 要件との対応
   仮予約確定まで一貫性を保ちたい → "REPEATABLE READ" が適合。
5. 30字以内の理由
   「同時実行した他者が同じ座席を仮予約できないようにするため」。

誤りやすいポイント

• 「変更は専有ロック時のみ影響」と思い込み、SELECT だけなら READ COMMITTED で良いと判断してしまう。
• ロック粒度が「行単位」である点を見落とし、トランザクション全体に影響が大きいと誤解する。
• 座席行を UPDATE する③の段階だけを見て、SELECT 時の共有ロックが重要なことを忘れる。

FAQ

解答の論理構成

1. レプリケーション対象テーブルの決定
   • 空席表示に必要な情報は「席種ごとの空席数」と「座席ごとの空席／仮予約状態」。
   • 問題文の定義
     • 「席種在庫には、席種ごとの空席数をもつ。」
     • 「座席状況には、公演開催明細ごとの全ての座席の状況をもつ。」
   • よって（ア）（イ）にはこれら2テーブルを入れる。
2. レプリケーション方式の選定
   • 委託先は“サービスの提供先ごとにレプリカデータを用意する”ため、1つのオリジナルから複数のレプリカに配信する形になる。
   • RDBMS仕様2.(1) より「1か所のデータを複数か所に複製する機能」が該当。
   • よって（ウ）は「1か所のデータを複数か所に複製」。
3. 結合
   • 以上より（ア）「席種在庫」、（イ）「座席状況」、（ウ）「1か所のデータを複数か所に複製」と決定します。

誤りやすいポイント

• 「公演開催明細」を対象に選んでしまう
  → 空席数自体を保持していないため目的外。
• （ウ）を「双方向に反映」と誤記
  → 委託先からオリジナルへは更新しないので双方向は不要。
• RDBMS仕様2.(2) の“イベント型”と混同
  → 問われているのはデータ流方向であり同期方式ではない。

FAQ

解答の論理構成

1. 【問題文】表3は“不正表示”の例を2つ列挙し、
   1. 「購入された座席が空席として表示される。」
   2. 「（エ）」
      と対比構造で示しています。
2. 指摘1は「販売済み ➔ 空席表示」という“売れたはずなのに○”矛盾。
3. レプリケーション遅延で起き得るもう一方の矛盾は「キャンセルで空席に戻った ➔ ×（売切れ）表示」の逆パターン。
4. したがって（エ）は「キャンセルされた座席が空席として表示されない。」となります。
5. これにより表3の2項は、双方の遅延エラー（売れた／戻った）の両面を網羅し、レビューコメントと整合します。

誤りやすいポイント

• 「空席として表示される／されない」の主語を“購入”と“キャンセル”で取り違える。
• “取消”でなく“返金”など別語に置き換えてしまい原文引用要件を満たさない。
• 遅延原因を「排他制御」だと誤解し、解答をロック待ちの説明に寄せてしまう。

FAQ

解答の論理構成

1. 表3のレビュー指摘
   「レプリカデータにアクセスするタイミングによって…1. 購入された座席が空席として表示される。」
   ここで問題となるのは公演・座席単位の可視性であり、空席管理システムの「空席フラグ」が実体です。
2. レプリケーションの最小化方針
   「レプリケーションの性能への影響を抑えるため、対象は必要最低限のものとする。」
   よって列全体ではなく、購入表示に直接関わる列のみを選択します。
3. 座席レベルの不整合解消
   公演番号＋公演日＋開演時刻＋座席番号で管理される「座席状況」テーブルには列「空席フラグ」があり、ＯＮ／ＯＦＦで可否を示します。イベント型で即時同期させれば購入直後に反映されます。
4. 席種レベルの不整合解消
   指摘2（表記は空欄（エ）だが内容は“売切れなのに空席として表示される／その逆”）は、席種単位の残量集計ずれが原因です。残量は「席種在庫」テーブルの「空席数」で一元管理されているため、同列を同期対象とします。
5. 他の列が不要な理由
   「仮予約フラグ」は購入確定前段階なので、レプリカ側の閲覧用途（確定情報のみ表示）には不要。その他列も同様に直接表示値に影響しません。

誤りやすいポイント

• 全行レプリケーションを選び、性能低下を招く。列単位選択が出来る仕様を見落とさないこと。
• 「仮予約フラグ」まで同期対象に入れてしまう。仮予約状態は閲覧サービスで公開すべき情報ではない。
• 「購入フラグ（チケット）」を選択してしまう。レプリカは空席管理システム側のテーブル構造と“同等”であり、販売管理システムの列は対象外です。

FAQ