令和2年度10月 データベーススペシャリスト 午後Ⅰ 問2

業務要件から設計判断を逆算する

問題の記述から、業務上守るべき条件が何かを先に言葉にしてから設計判断を検討しましょう。SQL、排他制御、運用設計のいずれでも、判断の根拠を業務要件に結び付けて考えると、検討の筋道が整理しやすくなります。

この問題で問われていること

確認すべき観点は一つ、『その設計判断はどの業務要件を満たすためのものか』です。問題の状況説明に書かれた条件や制約を拾い出し、各判断がそれとどう対応するかを自分の言葉で説明できるか確かめてください。

視点

データベースの設計や運用の判断では、一般にどの選択にも得るものと引き換えにするものがあり、どれが適切かは置かれた状況の条件によって決まります。そのため、まず状況説明に書かれた条件を整理し、判断に関わる条件とそうでない条件を分けることが検討の出発点になります。

確認ポイント

• 状況説明の中から、業務上必ず守るべき条件や制約を抜き出して書き出したか
• 検討している判断が、抜き出した条件のどれに対応するのかを説明できるか
• 判断の根拠が、状況に書かれた業務要件ではなく一般論や思い込みに頼っていないか見直したか

解説

劇場のチケット販売システムを題材にした問題です。空席情報を○×で表示するSQL（左外部結合とCASE式）、同じ座席を同時に購入しようとした場合の排他制御の見直し、仮予約の二重登録を防ぐISOLATIONレベルの選択、空席情報表示用レプリカデータの反映方式（バッチ型とイベント型）の使い分けを、RDBMS仕様と業務の記述から導きます。

問題の全体像

Y社は、空席管理システムと販売管理システムで構成されるチケット販売システムを刷新し、プレイガイドなどの外部委託先にも空席管理システムを公開します。設問1はチケット情報一覧を出力するSQL文、設問2は複数の会員が同時に購入する場合の排他制御とISOLATIONレベル、設問3は空席情報表示用のレプリカデータを作るレプリケーションの設計を扱います。公式解答例の出題趣旨では、同時に実行される要求が互いに影響して予期しない結果を生まないよう処理を設計することが重要だとしたうえで、SQLの設計能力、トランザクション制御と排他制御の理解、適切な同時実行性を満たす設計能力を問うとしています。採点講評では、全体の正答率はやや低かったとされています。

設問1(a)

図4のSELECT句は「( [a] A2.空席数 [b] THEN '×' ELSE '○' END) AS 空席情報」となっています。THEN、ELSE、ENDが並んでいるので、条件によって値を切り替えるCASE式の書き方だと分かります。最初の条件の前に置く字句なので、公式解答例は「CASE WHEN」です。採点講評では、aは正答率が低く、条件構文の理解に課題があったとされています。

設問1(b)

図3の注記によると、空席情報は○が空席あり、×が売切れです。A2は「空席数 > 0」の席種在庫だけを取り出した副問合せなので、売切れの席種はA2に対応する行がなく、外部結合によってA2.空席数がNULLになります（設問1(c)参照）。そのため、×を返す条件は「A2.空席数がNULLであること」になり、公式解答例は「IS NULL」です。NULLは「= NULL」では判定できない点に注意しましょう。

設問1(c)

図3では、売切れ（×）の席種も一覧に出ています。一方、A2は空席数が0より大きい行だけに絞り込まれています。内部結合にすると売切れの席種が一覧から消えてしまうため、A1の行をすべて残し、A2に対応する行がなければNULLを補う結合が必要です。結合列を「USING (公演番号、 公演日、 開演時刻、 席種)」で指定しているので、JOIN構文を使います。公式解答例は「LEFT [OUTER] JOIN」です。採点講評では、cも正答率が低く、結合構文に課題があったとされています。

設問1(d)

抽出条件は、公演日が2020年4月、つまり '2020-04-01' から '2020-04-30' までです。「公演日 [d] '2020-04-01' [e] '2020-04-30'」という形で範囲を指定できる述語なので、公式解答例は「BETWEEN」です。

設問1(e)

BETWEEN述語は「BETWEEN 下限 AND 上限」と書き、下限と上限をANDでつなぎます。公式解答例は「AND」です。

設問1(f)

業務概要2.(2)に「チケット情報は，公演名・公演日・開演時刻の昇順，料金の降順に出力される」とあります。図4の最後の行は並べ替えの指定なので、公式解答例は「ORDER BY」です。

設問1(g)

料金だけが降順です。公演名・公演日・開演時刻は何も指定しなければ昇順になるので、料金の後ろに降順の指定を付けます。公式解答例は「DESC」です。図3でも、同じ公演日・開演時刻の中で料金が高い順（S→A→B）に並んでいることを確認できます。

設問2(1)(あ)

表2の指摘は、ほぼ同時に購入しようとした複数の会員が、排他制御のために待たされるという問題です。ロックは行単位で掛かるので、互いに待ちが起きるのは同じ行、つまり同じチケット（座席）を対象にした場合です。公式解答例は「同じ座席」または「同じチケット」です。

設問2(1)(い)

図5では、③で購入フラグをオンにした行の専有ロックは、⑧のコミット（決済や購入確定の後）まで保持されます。専有ロックが掛かっている間は、他のトランザクションは対象行を参照することもできません。そのため、後から購入しようとした会員は、先の会員の処理が終わるまで待たされます。待ち終わった後に確認すると、その座席は先の会員が購入済みです。公式解答例は「購入できない」です。長く待たされたうえに購入できないことが分かる、という点が「会員に不便を強いる」内容です。

設問2(1)(う)

対策は、②の確認（SELECT文）をやめ、③のUPDATE文の条件に確認内容を含めることです。こうすると、空席の確認と購入中の設定を一つの更新でまとめて行えます。②で確認していたのは「購入番号がNULLであること」で、業務概要1.(4)②にも、未販売のチケットは購入番号をNULLにすると書かれています。したがって、更新条件に加えるのは購入番号についての条件で、公式解答例は「購入番号」です。

設問2(1)(え)

(う)と合わせると、更新条件は「購入番号がNULLである」ことになります。公式解答例は「NULL」です。条件に合う行、つまり未販売の行だけが更新されます。そのため、更新できたかどうかがそのまま「空席を確認し、購入中にできたか」の結果になります。

設問2(1)(お)

表2には、全ての対象座席を②～⑦でまとめて処理しているのを、ロックの期間を短くするために1座席ごとに処理して[お]するよう変更する、とあります。ロックはトランザクションが終わるまで解放されないので、1座席ごとにトランザクションを終わらせれば、ロックを持つ期間が短くなります。公式解答例は「コミット」です。

設問2(2)(ISOLATIONレベル)

公式解答例は「②」（REPEATABLE READ）です。表1によると、READ COMMITTEDは参照時の共有ロックを参照が終わった時点で解放します。REPEATABLE READは、その共有ロックをトランザクションが終わるまで保持します。図5の空席管理システムの(b)では、「空席フラグがオンか確認する」「仮予約フラグがオフか確認する」という参照の後に、「仮予約フラグをオンにする」という更新を行います。参照した内容が更新の時点まで変わらないことが前提になっている処理です。

設問2(2)(理由)

READ COMMITTEDでは、仮予約フラグがオフであることを確認した直後に共有ロックが外れます。そのため、同時に実行している別のトランザクションも同じ座席を「オフ」と確認でき、両方が仮予約フラグをオンにしてしまう（同じ座席を二重に仮予約する）おそれがあります。REPEATABLE READにすると、確認した行の共有ロックがトランザクションの終わりまで保持され、その間は他のトランザクションが対象行を変更できません。公式解答例の理由は「同時実行した他者が同じ座席を仮予約できないようにするため」で、30字以内に収まっています。答案では「同時実行する他者」「同じ座席」「仮予約できない（二重仮予約の防止）」の三つの要素を押さえます。採点講評では、ロックの解放待ち時間の短縮やデッドロックの回避を理由にした解答が見られ、(2)の理由は正答率が低かったとされています。性能の観点ではなく、処理結果が正しくなるかという観点から理由を書くことが求められています。

設問3(1)(ア)

図6には、空席確認や購入などのトランザクションを妨げないように、オリジナルをレプリケーション元、レプリカデータをレプリケーション先とする、とあります。空席情報を表示するために必要で、空席確認や購入の処理で更新されてロック待ちの原因になるのは、席種ごとの空席数を持つ「席種在庫」と、座席ごとの空席フラグ・仮予約フラグを持つ「座席状況」です。公式解答例では、ア・イは「席種在庫」「座席状況」で、順不同とされています。

設問3(1)(イ)

(ア)と組になる空欄です。公式解答例は、アが「席種在庫」、イが「座席状況」で、備考に順不同とあります。どちらを書いても、もう一方の空欄にはもう一つのテーブルを入れます。座席状況は、会員が空席の座席番号を指定する（業務概要2.(3)）ために必要な、座席ごとの空席の状況を持つテーブルです。

設問3(1)(ウ)

本文には「サービスの提供先ごとにレプリカデータを用意する」とあり、一つのオリジナルから複数のレプリカデータへ反映することになります。RDBMS仕様2.(1)の三つの機能のうち、これに当たるのは「1か所のデータを複数か所に複製する機能」です。本文中の字句を使って答えるので、公式解答例は「1か所のデータを複数か所に複製」です。採点講評では、(1)ウは正答率が低く、レプリケーション機能の仕様と状況の記述から目的と必要な処理を読み取ってほしいとされています。

設問3(2)(エ)

図6ではバッチ型を使っています。RDBMS仕様2.(2)によると、バッチ型はトランザクションとは非同期に、一定間隔で反映します。そのため、反映されるまでの間は、オリジナルとレプリカデータの内容が一致しません。1.の「購入された座席が空席として表示される」は、空席が減った変更がまだ反映されていない場合です。逆の方向として、業務概要1.(3)①には、購入された座席がキャンセルされたら空席数を戻すとあります。空席が増えた変更がまだ反映されていないと、キャンセルされた座席が空席として表示されません。公式解答例は「キャンセルされた座席が空席として表示されない。」で、1.と同じ文の形で30字以内に収まっています。

設問3(3)

公式解答例では、表4は、レプリケーション元テーブルが座席状況で対象列が空席フラグの行と、レプリケーション元テーブルが席種在庫で対象列が空席数の行の2行です。残りの欄は空欄です。

【表3の対策欄に書かれていること】表3の対策欄に書かれているのは、「イベント型のレプリケーション機能を適用する」「対象とするテーブルとその列を表4のように設定する」「ただし、レプリケーションの性能への影響を抑えるため、対象は必要最低限のものとする」の三点だけです。対象を最小限にする理由として表3が挙げているのは、性能への影響を抑えることです。

【RDBMS仕様から推論できること】ここからは表3の記載ではなく、RDBMS仕様からの推論です。仕様2.(2)によると、イベント型はレプリケーション元のトランザクションと同期してデータを反映します。仕様2.(2)②によると、反映に失敗するとレプリケーション元の変更がロールバックされます。つまり、イベント型の対象が増えるほど、空席管理システムの本来のトランザクションに負担がかかると考えられます。また、仕様2.(3)の列の選択を使えば、必要な列だけを反映できます。

【対象列の絞り込み】表3で問題になっているのは、空席かどうかの表示のずれです。業務概要1.(3)によると、購入が確定すると空席数を減らして空席フラグをオフにし、キャンセルされると空席数を戻します。空席かどうかの表示に関わるのは、座席単位では座席状況の空席フラグ、席種単位では席種在庫の空席数です。この2列だけを同期して反映すれば、表3の1.と2.の両方のずれを防げます。表の欄が全部埋まるとは限らないという注記のとおり、公式解答例では必要最低限の2行だけが示されています。

学習のポイント

外部結合で生じるNULLの扱い、ISOLATIONレベルごとのロック保持期間の違い、更新条件で確認と更新を一度に行う手法、レプリケーション方式の特徴と対象の絞り方を、本文の仕様に結び付けて説明できるようにしておきましょう。