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

解答の論理構成

1. 検索要件の整理
   【問題文】には「複数の社員が、同じ会議室に対して重複する日時を指定して予約した場合は、最も早く実行された予約を予約成功とし、その他は予約失敗とする。」とあります。したがって“空き会議室検索”は重複予約が1件も存在しない会議室のみを抽出する処理です。
2. サブクエリの役割
   図2の SQL は外側で SELECT * FROM 会議室 X を行い、内側で相関をとる形です。「対象会議室が重複予約を持たない」ことを保証するため、外側 WHERE 句では“既存予約が存在しない”ことを表す必要があります。つまり (a) は NOT EXISTS です。
3. 重複判定条件
   重複（オーバーラップ）は次の条件で判定できます。
   • 既存予約の開始時刻 < 希望終了時刻
   • 既存予約の終了時刻 > 希望開始時刻
     いずれか一方が等号の場合、同一コマ境界での「背中合わせ」を許可できます。本問では“30分単位”で区切られており背中合わせは問題ないため、厳密に < と > を採用します。よって (b) は <、(c) は > となります。

誤りやすいポイント

• EXISTS と NOT EXISTS の選択を逆にし、空いていない会議室ばかり抽出してしまう。
• オーバーラップ条件を <=・>= で書き、前後がぴったり接する予約まで除外してしまう。
• BETWEEN :hv2 AND :hv3を使い、重複判定を1条件で済ませようとしても正しく表現できない。

FAQ

解答の論理構成

1. 一意制約の確認
   • 【問題文】「会議室番号、予約日、予約開始時刻で会議室予約を一意に識別する。」
     → この3列が主キー（もしくは UNIQUE 制約）であると読み取れる。
2. INSERT 処理②の動作
   • 図3②のSQLは
     sql INSERT INTO 会議室予約 (会議室番号, 予約日, 予約開始時刻, 予約終了時刻, 社員番号) VALUES(:hv4, :hv1, :hv2, :hv3, :hv5);
   • 既に同じ「会議室番号、予約日、予約開始時刻」の行が存在すると、主キー一意性が破られDBが拒否。
3. ダブルブッキング抑止との関係
   • 条件①で重複チェックをしているが、同時実行で別トランザクションが先に INSERT すると②の直前チェックをすり抜ける可能性がある。
   • その場合でも一意制約が最後の砦となり、重複キーエラーで行が挿入できず予約失敗となるためダブルブッキングは防止される。
4. 以上より、「主キー重複（重複キー）による一意制約違反」が挿入失敗の直接原因である。

誤りやすいポイント

• 「予約終了時刻」まで含めて重複チェックされると誤解しやすい。実際の主キー列に含まれない。
• ロック待ちやデッドロックが原因だと思い込むケース。あくまで制約違反が直接要因。
• SELECT①が空なら必ず INSERT 成功と決めつけ、同時実行による race condition を見落とす。

FAQ

解答の論理構成

1. ロックの前提確認
   問題文に「ISOLATIONレベルは READ COMMITTED であり、 行単位でロックをかける。データ参照時には共有ロックをかけ、参照終了時に解放する。」とあります。したがって
   • SELECT（図3①）の共有ロックは直ちに解放
   • INSERT（図3②）の専有ロックはトランザクション終了まで保持
     となります。
2. 表２ ― 実行が重ならない場合
   予約処理が直列なので、単純に時間帯の重なりで成否が決まります。
   • A：最初なので ○
   • B：11時00分〜13時00分 はAと重なるため、図3①で重複行が見付かり ×／①
   • C：11時30分〜13時00分 もAと重なるため ×／①
   • D：9時00分〜11時00分 はAの開始と接しているだけで重ならないので ○
   • E：8時00分〜18時00分 はAと重なるため ×／①
3. 表３ ― ２人の処理が重なった場合
   想定は「先行が図3①を実行後、後続が図3①を実行し、その後に先行が図3②を実行」です。ここでカギになるのは「先行の SELECT で取得した共有ロックがすぐ解放される」点です。

   ケース	後続Aの図3①結果	後続Aの図3②結果	判定
   先行B → 後続A	B の SELECT は A の行をまだ挿入していないため重複行なし。A の SELECT もまだ B の行は挿入前なので重複行なし → A は図3②へ。B が図3②で 11時00分 行を挿入後ロック、A の INSERT は一意制約違反で失敗 → ×／②
   先行C → 後続A	C の開始時刻は 11時30分 で主キーが異なる。両方の SELECT で重複行なし。先行・後続とも INSERT に成功し、時間帯は重なる → △
   先行D → 後続A	D の時間帯は重ならない。A の SELECT でも重複行なし。主キーも違うので両者 INSERT 成功 → ○
   先行E → 後続A	E は 8時00分〜18時00分。先行・後続とも SELECT で重複行を検出できず、主キーは異なるので両者 INSERT 成功 → △

   以上より
   • (j) ×, (k) ② （先行B）
   • (l) △, (m) － （先行C）
   • (n) ○, (o) － （先行D）
   • (p) △, (q) － （先行E）
4. まとめ
   ダブルブッキングは、図3①で互いの予約を検出できず、主キーも衝突しない場合に発生します。問題文の結論「同じ会議室に対して、予約処理の実行が重なった時に、
   ・予約時間帯が重なる
   ・予約開始時刻が異なる
   場合にダブルブッキングが発生する。」と完全に一致します。

誤りやすいポイント

• READ COMMITTED では SELECT 後すぐにロックが解放されることを忘れ、共有ロックが継続していると誤解する。
• 主キーが「会議室番号, 予約日, 予約開始時刻」であるため、開始時刻が違えば INSERT は競合しない点を見落とす。
• 図3②で一意制約違反が起きるのは「開始時刻が同じ」ときだけで、時間帯全体の重なりとは別である。

FAQ

解答の論理構成

1. 原文の結論部

   同じ会議室に対して、予約処理の実行が重なった時に、次の二つの条件が成立する場合にダブルブッキングが発生する。
   ・(r)が重なる。
   ・(s)が異なる。

2. 「重なる」対象は何か
   • 例では 「11時00分〜12時00分」 と 「11時30分〜13時00分」 など、開始・終了の組で示される“期間”が問題となっている。
   • 先行・後続でこの期間が重なった場合にだけ表2・表3で “△（ダブルブッキング発生）” が出現。
   • よって (r) は時間の「区間」＝「時間帯」。
3. 「異なる」対象は何か
   • 図3①の SELECT 条件は
     sql WHERE 会議室番号 = :hv4 AND 予約日 = :hv1 AND 予約開始時刻 (b) :hv3 AND 予約終了時刻 (c) :hv2
     すなわち “予約開始時刻” と “予約終了時刻” を比較して重複チェックをする。
   • もし両者の 予約開始時刻 が同一なら行ロック競合により後続がブロックされるが、異なれば「READ COMMITTED＋共有ロック即解放」のため互いを検知できない。
   • 後続が INSERT を実行した時点で幻読が発生し、ダブルブッキングが確定する。
   • よって (s) は「予約開始時刻」。
4. 以上より
   • (r)=時間帯
   • (s)=予約開始時刻

誤りやすいポイント

• 「同じ時間帯なら必ず開始時刻も同じ」と思い込む。30分単位なので“11:00〜12:00”と“11:30〜13:00”のように開始時刻がズレても重複します。
• READ COMMITTEDだから安全と勘違いし、幻読のリスクを見落とす。
• (s) に「予約終了時刻」を入れてしまう。終了時刻が同じでも開始時刻がズレていれば SELECT で漏れるケースがある点に注意。

FAQ

解答の論理構成

1. 図5の流れを確認
   • 「① 予約処理中フラグをUPDATE文で ‘Y’ に更新する」
   • 「② コミットする」
2. RDBMSのロック仕様
   • 「データ更新時には専有ロックをかけ、トランザクション終了時に解放する」
   • 「専有ロックがかかっている間、他のトランザクションからの対象行の参照、更新は専有ロックの解放待ちとなる」
3. コミット省略時の影響
   • ②を実行しないとトランザクションが終了しない
   • よって①で更新した“日別予約管理”の行は専有ロック状態のまま
4. 同時アクセスが集中する条件
   • 設問が示す「同じ日に、同じ会議室に対して予約が集中」すると、多数のトランザクションが同じ行を読もうとする
5. 結果
   • 先行トランザクションがコミットするまで後続はロック待ち
   • CPU・I/Oが遊んでしまいスループットが低下
6. よって処理番号は「①」、原因は「多数の専有ロックの解放待ちが発生する」となる。

誤りやすいポイント

• 「図5では②がコミットだから①が原因では？」と逆に考えてしまう
  → コミットを省く条件下では①がロック保持元になる点に注意。
• READ COMMITTEDだから参照はブロックされないと思い込む
  → 専有ロック中は参照も待たされる仕様が明示されている。
• “日別予約管理”を読み取り専用だと誤認
  → 実際には①で更新しているためロックが掛かる。

FAQ

解答の導き方

誤りやすいポイント

• 「①で更新できておらず」を単純に「常にロック待ち」と解釈する誤り。ロック待ち（UPDATE がブロックされている）と、UPDATE が完了して影響行数が0であった（既にフラグが 'Y'）状況は区別する必要があります。
• ③の「結果行がある」を日別予約管理のフラグと混同する誤り。③は会議室予約テーブルの重複予約を調べる処理であり、結果行は実際の予約の存在を示します。
• フローの分岐を逆に覚える誤り。図5では「①で更新できておらず、③で結果行がない場合は①に戻る」ので、更新できなかったがまだ予約が入っていない（処理中の可能性がある）場合は再試行する点を押さえておくこと。

FAQ

解答の論理構成

1. 図5①の更新
   UPDATE 日別予約管理 SET 予約処理中フラグ = 'Y' で対象行を占有。
2. 図5②でコミット
   ここで‘Y’が確定する。
3. 図5⑤〜⑦の途中エラー想定
   【問題文】「ディスク容量不足などのエラーが発生して、予約処理のトランザクションが中断」と記載。
4. フラグ復旧漏れ
   エラーにより図5⑥の UPDATE … 'N' が実行されず、フラグは‘Y’のまま。
5. 影響
   後続予約は図5①の … 予約処理中フラグ = 'N' 条件で更新不可となり、「その日付、その会議室」を誰も予約できない。

誤りやすいポイント

• トランザクションは②で一度コミット済みなので、後続ロールバックでは‘Y’を戻せない事実を見落とす。
• “会議室予約”テーブルの行ロックを考慮し、フラグ残留問題と混同する。
• “ディスク容量不足”＝ロールバックと決め付け、実際には途中で異常終了するシナリオを誤読する。

FAQ

解答の導き方

1. WHERE 節に「予約済フラグ = 'N'」がある点に注目します。図6の説明にあるように「予約済フラグで識別し、予約済みであれば 'Y'、予約済みでなければ 'N' とする」ため、この SELECT が返す行は要求区間に含まれる未予約（空き）コマの行です。つまりSELECTは「要求期間内で未予約のコマを列挙する」処理です。
2. 図7の説明にある通り「PERIODCOUNTは、開始時刻と終了時刻を与えて、含まれるコマ数を求める」ため、PERIODCOUNT(:hv2, :hv3) は要求された予約で必要なコマ数（期待する行数）を返します（例: 10時00分〜11時30分 → 3）。
3. GROUP BY 会議室番号 と HAVING (t) = PERIODCOUNT(...) の組合せは、会議室ごとに抽出された未予約のコマの個数が「要求で必要なコマ数」と一致するかを判定する意図です。したがって (t) は「グループ内の行数」を返す集約式でなければなりません。
4. グループ内の行数を正確に得る集約関数として適切なのは COUNT() です。COUNT() はグループ内の総行数（NULLの有無にかかわらず行そのものの数）を返すため、選択された未予約コマの個数と一致します。したがって (t) に入るべき字句は COUNT(*) です。

誤りやすいポイント

• COUNT(列名) を使う誤り
  社員番号などを COUNT(社員番号) で数えるとNULLを除外してしまい、期待するコマ数と合わなくなる。
• HAVING と WHERE の混同
  集約結果（COUNT 等）による条件は HAVING で指定する必要があり、WHERE では集約値を直接使えない。
• PERIODSTARTの意味の取り違え
  PERIODSTART(:hv3) は要求終端時刻から最後のコマの開始時刻を求める関数なので、範囲指定にそのまま終端時刻を使うと最後のコマを正しく含められない可能性がある。
• COUNT() の扱いの誤認
  COUNT() が行数を返すことは押さえるが、COUNT(1) が機能的に同等であることを知らないと混乱する。

FAQ

解答の論理構成

1. 図7①
   SELECT ... 予約済フラグ = 'N' ... HAVING (t) = PERIODCOUNT(:hv2, :hv3) で重複がないことを確認します。SELECT 終了時点で共有ロックは解放されます（〔RDBMS のトランザクション制御〕「データ参照時には共有ロックをかけ、参照終了時に解放する。」）。
2. 図7②
   UPDATE ... SET 予約済フラグ = 'Y' ... WHERE ... 予約済フラグ = 'N' を対象コマ数分繰り返します。もし1回でも “更新行がなかった” 場合にロールバックし予約失敗になります（図7②）。
3. 競合シナリオ
   ①が終わり共有ロックが外れた隙に、別の予約処理が同一コマを 予約済フラグ = 'Y' に変更すると、②の WHERE ... 予約済フラグ = 'N' 条件に合致せず更新０件になります。
4. 結論
   「①の終了後、②の終了までの間に、他の予約処理が範囲内のコマに予約を入れた」ときに更新０件 → 予約失敗となります。

誤りやすいポイント

• READ COMMITTED では共有ロックはSELECT終了後すぐ外れる点を忘れ、ロックでブロックされると誤解する。
• ②は “全部の更新が成功したか” を見るループであり、1コマでも更新０件なら即ロールバックする仕様を見落とす。
• PERIODCOUNTやPERIODNEXTの処理ロジックに意識が向きすぎ、ロックタイミングの問題を見逃す。

FAQ