ホーム > データベーススペシャリスト試験 > 2020年
データベーススペシャリスト試験 2020年 午後1 問02
データベースの実装に関する次の記述を読んで, 設問1〜3に答えよ。
劇場運営会社の Y 社は, オンラインチケット販売システム (以下,チケット販売システムという)を構築してインターネットでのチケットの申込みを受け付けている。Y社ではチケット販売システムを刷新し、新たにプレイガイドなどでのチケット販売委託を進めることになった。
〔RDBMS の主な仕様〕
チケット販売システムに用いている RDBMS の主な仕様は,次のとおりである。
1．ISOLATION レベル
選択できるトランザクションの ISOLATION レベルとその排他制御の内容は,表1のとおりである。
ロックは行単位で掛ける。 共有ロックを掛けている間は,他のトランザクションからの対象行の参照は可能であり,変更は共有ロックの解放待ちとなる。 専有ロックを掛けている間は,他のトランザクションからの対象行の参照, 変更は専有ロップクの解放待ちとなる。
2．レプリケーション機能
(1) 1か所のデータを複数か所に複製する機能, 複数か所のデータを1か所に集約する機能,及び両者を組み合わせて双方向に反映する機能がある。これらの機能を使用すると,一方のテーブルへの挿入・更新・削除を他方に自動的に反映させることができる。
(2) トランザクションログを用いてトランザクションと非同期に一定間隔でデータを反映するバッチ型と,レプリケーション元のトランザクションと同期してデータを反映するイベント型がある。
① バッチ型では, テーブルごとに, レプリケーションの有効化, 無効化をコマンドによって指示することができる。 無効化したレプリケーションを有効化するときには,蓄積されたトランザクションログを用いてデータを反映する。
② イベント型では,レプリケーション先への反映が失敗すると, レプリケーション元の変更はロールバックされる。
(3) 列の選択, 行の選択及びその組合せによって, レプリケーション先のテーブルに必要とされるデータだけを反映することができる。
〔チケット販売システムの概要〕
チケット販売システムは, 空席管理システムと販売管理システムで構成される。 オンラインチケット販売時には, 空席管理システムで空席を確認した後に座席を確保し、販売管理システムでチケット情報を保持する。
〔チケット販売システムのテーブル〕
空席管理システムと販売管理システムのそれぞれの主なテーブルのテーブル構造は,図 1,2のとおりである。 索引は, 主キー及び外部キーに定義している。
〔チケット販売業務の概要〕
1．テーブル及び列の設定
(1) 会場と座席
① 会場には,一意な会場番号を付与して, 都道府県,住所, 座席図などを設定する。
② 座席には,会場ごとに一意な座席番号を付与して, 席種 ( 'S', 'A', 'B'など) を設定する。
(2) 公演と席種料金
① 公演には,一意な公演番号を付与する。 公演開催明細には,公演日時 (公演日,開演時刻)ごとに,開場時刻, 公演会場番号, 販売開始日などを設定する。
② 席種料金には,公演の席種ごとに料金を設定する。
(3) 席種在庫と座席状況
① 席種在庫には,席種ごとの空席数をもつ。 座席の購入が確定したら空席数を減らし、 購入された座席がキャンセルされたら空席数を戻す。
② 座席状況には,公演開催明細ごとの全ての座席の状況をもつ。 販売開始時には,空席フラグはオン, 仮予約フラグはオフとする。 座席の購入処理中は仮予約フラグをオンにする。 座席の購入が確定したら, 空席フラグをオフにして仮予約フラグをオフにする。
(4) チケット
① チケットには,公演開催明細ごとに, 全ての座席番号と席種を設定する。
② 未販売のチケットは, 購入フラグをオフに, 購入番号を NULL に設定する。
③ チケットの購入を申し込まれたら, 購入フラグをオンにする。 購入が確定したら, 購入番号を設定する。
2．チケットの購入
(1) チケットを購入するためには、会員登録をする必要がある。
(2) 会員は,チケットの検索を行って, チケット情報一覧を表示する。 チケット情報は,公演名・公演日・開演時刻の昇順,料金の降順に出力される。
(3) 会員は, チケット情報一覧から, 空席のある公演の席種を選択する。 その後,枚数を指定し, 空席の座席番号を希望枚数分指定する。
(4) 会員は,決済を行い, 決済が成立すれば購入が確定する。
〔チケット情報一覧を出力する SQL 文の設計〕
空席管理システムから, 公演日が2020年4月かつ都道府県が東京のチケット情報一覧の出力の例を図 3, 出力する SQL文の構文を図4に示す。
〔オンラインチケット販売処理の設計〕
チケット販売委託のため, 空席管理システムは, プレイガイドなどの外部委託先にも公開する。このために, 空席管理システムの空席確認, 仮予約の処理の見直しを行った。 見直しに当たって, 同時実行されたトランザクションのやり直しが極力発生しないようにする方針とした。
ある会員が複数のチケットを購入することを想定して, チケットの販売処理について検討した。 その概要を図5に示す。
図5では,会員の意思で購入を途中でキャンセルした場合, 空席でない座席があり購入が失敗した場合, 又はその他のエラーが発生した場合の、途中までの処理を取り消すための例外処理を省略している。
図5の内容のレビューを行った。レビューでの指摘内容と対策を表2に示す。
〔レプリケーションの設計 〕
チケット販売委託先に、空席管理システムを介して, 空席情報を表示するサービスも提供する。そのため, データベースへの大量のアクセスによるロックの解放待ちの多発が見込まれるので, 空席情報表示用のレプリケーション先のテーブル(以下,レプリカデータという)を作成することにした。 レプリカデータのテーブル構造は,図1 の空席管理システムの主なテーブルと同等なものとし, サービスの提供先ごとにレプリカデータを用意する。 レプリカデータの運用について図6に示す。 さらに, 図6の内容のレビューを行った。 レビューでの指摘内容と対策を表3に示す。
設問1
〔チケット情報一覧を出力するSQL文の設計〕について、図4中の(a)〜(g)に入れる適切な字句を答えよ。
模範解答
a：CASE WHEN
b：IS NULL
c：LEFT [OUTER] JOIN
d：BETWEEN
e：AND
f：ORDER BY
g：DESC
解説
1. 核心キーワードと対応
以下のブランクに対応するキーワードは，それぞれ SQL の基本機能と要求仕様から決まります。
2. 解答に至る論理的説明
a：CASE WHEN
図４行２の構文
( a A2.空席数 b THEN '×' ELSE '○' END ) AS 空席情報
は，空席数が存在しない（NULL）場合は“×”，それ以外は“○”と表示する CASE 式です。CASE 式では
CASE WHEN <条件> THEN <値1> ELSE <値2> END
の形をとるため，a に「CASE WHEN」が入ります。
b：IS NULL
同じ CASE 式中で，A2.空席数 が NULL かを判定するには
A2.空席数 IS NULL
と書きます。
= NULL では動作せず，必ず IS NULL を使う点が重要です。c：LEFT [OUTER] JOIN
図４行５～６の
… AS A1
c (SELECT * FROM 席種在庫 WHERE 空席数 > 0 ) AS A2
USING (…)
では，公演ごとの全席種情報（A1）を母体にし，空席数がある分だけ A2 と結合し，空席数がない席種には A2 側が NULL になるようにします。
この要件を満たすのが
この要件を満たすのが
LEFT JOIN（または明示的に LEFT OUTER JOIN）です。d, e：BETWEEN … AND
図４行８の
AND 公演日 d '2020-04-01' e '2020-04-30'
は日付の範囲条件です。SQL では
<列> BETWEEN <開始値> AND <終了値>
と書くので，d に
BETWEEN，e に AND を入れます。f, g：ORDER BY … DESC
問題文で
「チケット情報は,公演名・公演日・開演時刻の昇順,料金の降順に出力される。」
とあるので，最後の行は
ORDER BY 公演名, 公演日, 開演時刻, 料金 DESC
となります。f が
ORDER BY，g が DESC です。3. 誤りやすいポイント
- CASE 式を使う際に，
CASE a …やCASE <列> WHEN …と混同しがちですが，ここでは条件式を直書きするCASE WHENの形式です。 - NULL 判定は
= NULLではなく 必ずIS NULL。 - JOIN の種類を間違え，
INNER JOINにすると，在庫がない席種が出力されずに空席情報一覧が不完全になります。 - 日付範囲は
>= '2020-04-01' AND <= '2020-04-30'と書いてもよいですが，問題ではBETWEEN … AND …が空欄に対応します。 - ORDER BY の後に降順指定を忘れると料金順が昇順になってしまいます。
4. 試験対策に覚えておくべきポイント
- CASE 式の基本形
CASE WHEN <条件> THEN <値1> ELSE <値2> END - NULL 判定は
IS NULL/IS NOT NULL - 結合方法の使い分け
- INNER JOIN：両方に存在する行だけ
- LEFT [OUTER] JOIN：左側を全て、右側は一致しないと NULL
- RIGHT [OUTER] JOIN：右側を全て、左側は一致しないと NULL
- 日付範囲条件
BETWEEN <開始> AND <終了>>= <開始> AND <= <終了>
- 並べ替え句
ORDER BY <列1> [ASC|DESC], <列2> [ASC|DESC], …- 指定がない場合は ASC（昇順）
これらは頻出の基本事項ですので，確実に使い方を押さえておきましょう。
設問2（1）：〔オンラインチケット販売処理の設計〕 について(1),(2)に答えよ。
表2中の（あ）〜（お）に入れる適切な字句を答えよ。
模範解答
あ：同じ座席 又は 同じチケット
い：購入できない
う：購入番号
え：NULL
お：コミット
解説
1. キーワード・論点の整理
本問は「販売管理システム」と「空席管理システム」の同時実行制御に関する指摘と対策を読み、「（あ）～（お）」に適切な字句を埋める問題です。
以下の語句がポイントになります。
以下の語句がポイントになります。
2. 解答根拠と論理的説明
（あ）「同じ座席 又は 同じチケット」
問題文より――
「複数の会員が、ほぼ同時に、（あ）を購入しようとした場合、排他制御によって、…おそれがある。」
ここで「排他制御によって待たされる」のは，まさに同じリソース（座席／チケット）を二人が同時に取得しようとする状況です。
従って，(あ) には「同じ座席」あるいは「同じチケット」が入ります。
従って，(あ) には「同じ座席」あるいは「同じチケット」が入ります。
（い）「購入できない」
続く指摘文――
「後から購入しようとした会員は、先に購入しようとした会員の購入処理が完了するまで待たされてから、（い）ことが判明する。」
待たされた結果，その座席は既に売り切れであるため，「購入できない」ことが明らかになります。
（う）「購入番号」、（え）「NULL」
対策文より――
「販売管理システムでの空席確認が、そのままチケット購入中となるように、②の処理をやめて、③の処理の購入フラグをオンにする条件に（う）が（え）であることを追加する。」
②の処理は「全ての購入希望チケットの購入番号が NULL か確認する（SELECT 文）」であり，
購入フラグをオンにする前提条件として「購入番号が NULL（未購入）」であることをチェックします。
したがって，（う）には「購入番号」，（え）には「NULL」が適合します。
購入フラグをオンにする前提条件として「購入番号が NULL（未購入）」であることをチェックします。
したがって，（う）には「購入番号」，（え）には「NULL」が適合します。
（お）「コミット」
さらに――
「1座席ごとに処理して（お）するよう変更する」
処理をまとめずに「1座席ごとに処理し」かつ小さな単位でトランザクションを終了させるには，「コミット」を行う必要があります。
3. 受験者が誤りやすいポイント
- （あ）「同じ座席／同じチケット」を「別の」など逆の意味で回答しないよう注意。
- （い）「購入できない」以外の「売れない」「登録できない」では，問題文の文脈に合いません。
- （う）（え）は「購入番号」「NULL」とセットで覚えること。
- （お）で「コミット」を「コミット処理」や「終了」とせず，正確に「コミット」と記述すること。
4. 試験対策としてのまとめ
- 同時実行制御（排他制御）の基本概念
- 同一の行（座席やチケット）を同時更新すると待機やロールバックが発生。
- トランザクションの粒度設計
- 大きなトランザクションより，細粒度にコミットすることでロック競合を減らす。
- 業務要件と SQL 処理順序の対応
- 「購入番号 = NULL」のチェックを先行させ購入フラグ更新条件とする。
- 用語の正確な理解と記述
- 排他制御の指摘文や対策文からキーワードを正しく拾う訓練を行う。
- 過去問演習
- 同種の同時実行制御問題を繰り返し解き，語句の置き換えパターンに慣れる。
以上を押さえることで，本症例のような同時実行制御に関する設問を確実に回答できるようになります。
設問2（2）：〔オンラインチケット販売処理の設計〕 について(1),(2)に答えよ。
空席管理システムで実行するトランザクションの ISOLATION レベルはREAD COMMITTED(①)と REPEATABLE READ(②)のどちらを設定すべきか,①か②で答えよ。 また,その理由を30字以内で述べよ。
模範解答
ISOLATIONレベル：②
理由：同時実行した他者が同じ座席を仮予約できないようにするため
解説
1. キーワード・論点の整理
- ISOLATION レベル
- READ COMMITTED：参照時に共有ロックを掛け、参照終了時に解放
- REPEATABLE READ：参照時に共有ロックを掛け、トランザクション終了時に解放
- 仮予約処理
- 「座席の購入処理中は仮予約フラグをオンにする」（問題文）
- 同時実行制御
- 他者が同じ座席を仮予約できないようにロックを保持する必要
2. 解答が②(REPEATABLE READ)となる理由
- 問題文では、空席管理システムでの仮予約において「同時実行されたトランザクションのやり直しが極力発生しないようにする方針」としている。
- READ COMMITTEDでは、SELECT実行後に共有ロックが解放されるため、他トランザクションが同一行のUPDATE（仮予約フラグのオン）を先に行ってしまい、競合が起きるおそれがある。
- 一方、REPEATABLE READでは、SELECTで掛けた共有ロックをトランザクション終了時まで保持するため、
- 問題文「データ参照時に共有ロックを掛け、トランザクション終了時に解放する」
の仕様により - 仮予約確認→仮予約フラグON→コミットまで、他者が同一座席の更新（仮予約フラグON）をできず、二重予約を防止できる。
- 問題文「データ参照時に共有ロックを掛け、トランザクション終了時に解放する」
3. 受験者が誤りやすいポイント
- 「READ COMMITTEDでも排他制御される」と思ってしまう
→ 参照時に掛かる共有ロックは即時解放されるため、仮予約確認直後に他者が同じ座席をUPDATEできてしまう。 - 「SELECT … FOR UPDATE」を用いない場合の動作を取り違える
→ 問題文の仕様では通常のSELECTに対して共有ロックを掛けるのみであり、READ COMMITTEDでは解放タイミングが早い。
4. 試験対策として覚えるべきポイント
- 各ISOLATIONレベルの「共有ロック」「専有ロック」の取得・解放タイミング
- 仮予約や空席確保など、同一レコードの連続参照→更新を行う処理では、参照ロックをトランザクション終了まで保持するREPEATABLE READを選ぶ。
- 問題文の仕様や条件（「同時実行のやり直しを極力発生させない」など）に合わせてロックの粒度・解放タイミングを考慮すること。
設問3（1）：〔レプリケーションの設計〕 について,(1)〜(3)に答えよ。
図 6 中の（ア）〜（ウ）に入れる適切な字句を,本文中の字句を用いて答えよ。（ア、イは順不同）
模範解答
ア：席種在庫
イ：座席状況
ウ：1か所のデータを複数か所に複製
解説
1. キーワード・論点の整理
本問は，図６中の空欄（ア）～（ウ）に，本文で定義されている用語を当てはめる問題です。
特に押さえるべき論点は以下のとおりです。
特に押さえるべき論点は以下のとおりです。
- 空席管理システムの主なテーブル名
→ 図１に「席種在庫」「座席状況」などが並んでいる - レプリケーション機能の種類
→ 【ＲＤＢＭＳの主な仕様】２.(1)に「１か所のデータを複数か所に複製する機能，…」とある
2. 解答に至る論理的説明
図６の該当箇所は，以下の文中の三つの空欄です。
「オリジナルをレプリケーション元，レプリカデータをレプリケーション先として，“ア”テーブルと“イ”テーブルを対象に，ウする機能を使用する。」
「オリジナルをレプリケーション元，レプリカデータをレプリケーション先として，“ア”テーブルと“イ”テーブルを対象に，ウする機能を使用する。」
-
（ア）・（イ）に入るのは，レプリケーションの対象となる「オリジナル」のテーブル名
- 図１「空席管理システムの主なテーブルのテーブル構造」の一覧のうち，空席表示に必要なデータを持つのは
- 席種ごとの空席数を管理している「席種在庫」
- 各座席の空席フラグ・仮予約フラグを管理している「座席状況」
- 図１「空席管理システムの主なテーブルのテーブル構造」の一覧のうち，空席表示に必要なデータを持つのは
-
（ウ）に入るのは，どのレプリケーション機能を使うか
- 【ＲＤＢＭＳの主な仕様】２.(1)に
「１か所のデータを複数か所に複製する機能,…を使用すると,一方のテーブルへの挿入・更新・削除を他方に自動的に反映させることができる。」
とあり，空欄には「１か所のデータを複数か所に複製」が入ります。
- 【ＲＤＢＭＳの主な仕様】２.(1)に
以上より，空欄には以下を当てはめます。
3. 誤りやすいポイント
-
「ア」「イ」について
図１には複数のテーブル名が並んでいるため，
・「席種料金」「座席」などと誤答しがち
→ 空席情報表示に必要な「空席数（席種在庫）」と「空席フラグ／仮予約フラグ（座席状況）」を選ぶことが重要です。 -
「ウ」について
レプリケーションには「複数か所→１か所」「双方向」もあるが，
図６の文脈は「オリジナル → 複数のレプリカ」にデータを反映させる設定の説明なので，
「１か所のデータを複数か所に複製」が正解です。
4. 試験対策としてのまとめ
- 空席管理システムのテーブル構造（図１）を確実に把握しておく
- レプリケーション機能の３つの基本パターンを区別できるようにする
- １か所のデータを複数か所に複製
- 複数か所のデータを１か所に集約
- 双方向に反映
- 問題文中の「オリジナル」「レプリカ先」などキーワードと対応する機能・テーブル名を正確に結び付ける訓練をすることが，午後Ⅰ試験では重要です。
設問3（2）：〔レプリケーションの設計〕 について,(1)〜(3)に答えよ。
表3中の（エ）に入れる文章を, 1. に倣って30字以内で述べよ。
模範解答
エ：キャンセルされた座席が空席として表示されない。
解説
模範解答のキーワード・論点整理
- 不整合事象：
- 「購入された座席が空席として表示される」
- 「エ：キャンセルされた座席が空席として表示されない」
- 原因：バッチ型レプリケーションの遅延による反映漏れ
- 対策：イベント型レプリケーションに切り替え同期的に反映
なぜ「キャンセルされた座席が空席として表示されない」か
-
問題文の指摘内容（表３より引用）「レプリカデータにアクセスするタイミングによって，次のように，表示する空席情報が不正になる場合がある。
- 購入された座席が空席として表示される。
- エ」
-
レプリケーション方式の説明（問題文より引用）
- バッチ型レプリケーションは「トランザクションログを用いて非同期に一定間隔でデータを反映する」
- 無効化→有効化時に一括でログを適用し遅延を解消するが、通常稼働時はタイムラグがある
- この遅延により、
- 購入データの反映前に参照すると「空席」と誤表示
- キャンセル処理の反映前に参照すると「売切れ」のまま（＝空席に戻らない）
-
その結果、「キャンセルされた座席が空席として表示されない」という不整合が起きるため、
イベント型レプリケーションを採用し「反映失敗時はロールバックされる（同期反映）」にする。
受験者が誤りやすいポイント
試験対策として覚えておくポイント
- レプリケーションの方式
- バッチ型：非同期反映、タイムラグ・遅延による不整合リスク
- イベント型：同期反映、反映失敗時はロールバック
- 不整合パターン
- ① 更新前参照：古いデータ（未購入）を表示
- ② 削除・キャンセル前参照：古いデータ（売切れ）を残す
- 解答文作成のコツ
- 「～されない」「～される」といった主語・述語を明確に
- 30字以内の制限を意識して簡潔にまとめること
設問3（3）：〔レプリケーションの設計〕 について,(1)〜(3)に答えよ。
イベント型レプリケーション機能の対象とするテーブルとその列を答えて 表4を完成させよ。
なお, 表4の欄は全て埋まるとは限らない。
模範解答
解説
1. 模範解答のキーワード・論点整理
- イベント型レプリケーション機能
- 「必要最低限のもの」として選定するテーブル・列
- 空席情報の変化をリアルタイムに反映
- 性能への影響を抑える
2. 解答の根拠と論理的説明
問題文中に，レプリケーション設計のレビュー指摘として次の記述があります。
イベント型のレプリケーション機能を適用する。対象とするテーブルとその列を表４のように設定する。ただし，レプリケーションの性能への影響を抑えるため，対象は必要最低限のものとする。
レプリケーションの対象は「空席情報」を正しく表示するために必要な列に限られます。
空席管理システムのテーブル構造から，空席情報に関わる主な列を抽出すると次のとおりです。
空席管理システムのテーブル構造から，空席情報に関わる主な列を抽出すると次のとおりです。
この中で，表示用に必要かつリアルタイム性重視で最低限なのは，
- 座席単位の空席可否を表す「空席フラグ」
- 席種単位の残席数を表す「空席数」
よって，表４には次のように設定します。
3. 受験者が誤りやすいポイント
- 仮予約フラグまでレプリケーション対象に含めてしまう
→「仮予約」状態は一時的な中間状態であり，外部表示には不要。 - 他の参照用カラム（公演番号や座席番号など）を追加してしまう
→ これらはレプリケーション先にインデックスとして保持されるが，「性能への影響を抑える」という要件に反する。 - バッチ型とイベント型の特徴を混同
→ イベント型は「元トランザクションと同期して即時反映し，失敗時はロールバックされる」ことが問題文に明示されている。
4. 試験対策ポイント
- レプリケーション機能の種類
- バッチ型：非同期，ログ蓄積からまとめて反映
- イベント型：同期，失敗時はロールバック
- レプリケーション対象の選定
- 表示に必要な列だけを抽出し，性能・帯域を抑制
- データ同期のリアルタイム性要件を見抜く
- 「表示の不整合を避ける」「即時反映」が求められる場合はイベント型
- テーブル設計の目的を整理
- 空席単位の可否情報は「座席状況」
- 席種単位の残数情報は「席種在庫」