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

SELECT * FROM 会議室 X

NOT EXISTS

• 既存予約の開始時刻 < 希望終了時刻
• 既存予約の終了時刻 > 希望開始時刻
  いずれか一方が等号の場合、同一コマ境界での「背中合わせ」を許可できます。本問では“30分単位”で区切られており背中合わせは問題ないため、厳密に

  <

  と

  >

  を採用します。よって (b) は

  <

  、(c) は

  >

  となります。

• 【問題文】「会議室番号、予約日、予約開始時刻で会議室予約を一意に識別する。」
  → この３列が主キー（もしくは UNIQUE 制約）であると読み取れる。

• 図3②の SQL は

  INSERT INTO 会議室予約
    (会議室番号、予約日、予約開始時刻、予約終了時刻、社員番号)
  VALUES(:hv4, :hv1, :hv2, :hv3, :hv5);
  

• 既に同じ「会議室番号、予約日、予約開始時刻」の行が存在すると、主キー一意性が破られ DB が拒否。

• 条件①で重複チェックをしているが、同時実行で別トランザクションが先に INSERT すると②の直前チェックをすり抜ける可能性がある。
• その場合でも一意制約が最後の砦となり、重複キーエラーで行が挿入できず予約失敗となるためダブルブッキングは防止される。

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

• 例では 「11時00分〜12時00分」 と 「11時30分〜13時00分」 など、開始・終了の組で示される“期間”が問題となっている。
• 先行・後続でこの期間が重なった場合にだけ表２・表３で “△（ダブルブッキング発生）” が出現。
• よって (r) は時間の「区間」＝「時間帯」。

• 図３①の SELECT 条件は

  WHERE 会議室番号 = :hv4
    AND 予約日     = :hv1
    AND 予約開始時刻 (b) :hv3
    AND 予約終了時刻 (c) :hv2
  

  すなわち “予約開始時刻” と “予約終了時刻” を比較して重複チェックをする。
• もし両者の 予約開始時刻 が同一なら行ロック競合により後続がブロックされるが、異なれば「READ COMMITTED＋共有ロック即解放」のため互いを検知できない。
• 後続が INSERT を実行した時点で幻読が発生し、ダブルブッキングが確定する。
• よって (s) は「予約開始時刻」。

• (r)=時間帯
• (s)=予約開始時刻

• 「① 予約処理中フラグをUPDATE文で ‘Y’ に更新する」
• 「② コミットする」

• 「データ更新時には専有ロックをかけ、トランザクション終了時に解放する」
• 「専有ロックがかかっている間、他のトランザクションからの対象行の参照、更新は専有ロックの解放待ちとなる」

• ②を実行しないとトランザクションが終了しない
• よって①で更新した“日別予約管理”の行は専有ロック状態のまま

• 設問が示す「同じ日に、同じ会議室に対して予約が集中」すると、多数のトランザクションが同じ行を読もうとする

• 先行トランザクションがコミットするまで後続はロック待ち
• CPU・I/O が遊んでしまいスループットが低下

予約処理中フラグ = 'N'

'Y'

UPDATE 日別予約管理 SET 予約処理中フラグ = 'Y'

UPDATE … 'N'

… 予約処理中フラグ = 'N'

GROUP BY 会議室番号 HAVING (t) = PERIODCOUNT(:hv2, :hv3)

PERIODCOUNT 関数は、開始時刻と終了時刻を与えて、含まれるコマ数を求める

WHERE … 予約済フラグ = 'N'

COUNT(*)

(t)

COUNT(*)

SELECT ... 予約済フラグ = 'N' ... HAVING (t) = PERIODCOUNT(:hv2, :hv3)

UPDATE ... SET 予約済フラグ = 'Y' ... WHERE ... 予約済フラグ = 'N'

予約済フラグ = 'Y'

WHERE ... 予約済フラグ = 'N'