令和4年度秋期 データベーススペシャリスト 午後Ⅰ 問3

現象と原因の対応を記述から確かめる

性能低下や待ちの問題を検討するときは、観察された現象と、その原因として考えられる要因とを切り分け、問題中に示された条件や事実だけを根拠に両者の対応関係を一つずつ確かめる姿勢が役立ちます。

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

確認すべき観点は一つ、各主張が述べている事柄が、問題中で与えられた前提・条件・データと矛盾なく結び付くかどうかです。一般論として正しそうに見えるかではなく、この問題の状況に当てはまるかを基準に判断します。

視点

運用中のシステムで起きる遅延や停止は、どの層で何が起きているかを区別して捉えると整理しやすくなります。ある対策や説明が妥当かどうかは、それが作用する範囲と、実際に起きている事象の原因とが一致しているかで決まります。問題中の数値・時系列・処理条件などを根拠として押さえ、書かれていない事情を推測で補わないことが大切です。

確認ポイント

• 問題中で示された現象・データ・条件を、推測を交えずに書き出したか
• 検討中の主張や対策が、どの事象に作用するものかを自分の言葉で説明できるか
• その主張が問題の前提と食い違う点や、根拠が示されていない点がないか

解説

C社の販売管理システムでピーク日にバッチと出庫作業が遅延した事例です。三つの観点が問われます。一つ目は、行の削除をせずに再編成だけを行うと、なぜストレージが満杯になるリスクがあるか（RDBMSの挿入仕様と空き領域）。二つ目は、ジョブを多重化したときのデッドロックのリスク（テーブルごとの更新順序と、注文番号の範囲による分割）。三つ目は、LAG関数を使った出庫間隔時間の分析と、棚への集中を避ける対策で生じる新たなデッドロックのリスクです。どの設問も、本文の仕様と表2の処理内容を根拠に説明できます。

問題の全体像

事務用品を販売するC社では、注文登録・在庫引当・出庫指示をバッチで順に実行し、出庫実績はオンラインで処理しています。注文量が特に多いピーク日に、朝のバッチと出庫作業が遅延しました。CPU、I/O、ロック待ちに致命的なボトルネックはなく、対策会議では次の三つを検討しています。(1)「注文明細」の再編成を今すぐ行うべきか、(2)ジョブを注文番号の範囲で分割して多重実行するとデッドロックのリスクがあるか、(3)出庫の遅延原因をSQLでどう分析し、どんな対策を取るか。公式解答例の出題趣旨では、性能低下への初期対応の考え方、ウィンドウ関数を使ったSQL設計の理解、そしてリスクを予測して対策の採否を決める能力を問うとしています。

設問1(1)

着目点は二つあります。一つはRDBMSの仕様2(2)で、挿入行は「主キー値の並びでキー値が近い行があるページ」に空きがあればそこに入り、なければ表領域の最後のページに入ります。もう一つは、「注文明細」の主キーが(注文番号、 注文明細番号)で、注文番号が単調に増加することです。新しく挿入する行のキーは常に既存の最大値より大きいので、過去の注文の行があるページ（再編成で空き領域を予約したページ）の近くには挿入されません。行は最後のページに格納され続けます。公式解答例では、「主キーが単調増加するので過去の注文番号の近くに挿入しない」または「主キーの昇順に挿入すると最後のページに格納を続ける」という趣旨を示しています。40字以内に収めるには、「単調増加（昇順）の挿入」と「予約した空き領域のある過去のページではなく最後のページに入る」の二点を押さえます。

設問1(2)

本文によると、この1年ほどは再編成も行の削除もしていません。前回の再編成より後に追加された行は、設問1(1)のとおり最後のページに空きなく詰めて格納されています。この状態で空き領域の割合を既定値（30%）のまま再編成すると、この1年に追加したページの一つ一つに30%の空き領域が予約されます。同じ行数を格納するのに必要なページ数がその分増え、しかも予約した空き領域は今後の挿入でも使われません。公式解答例では「再編成後に追加した各ページで既定の空き領域分のページが増える」という趣旨です。答案では「前回の再編成以降に追加したページ」と「既定30%の空き領域の分だけページ数が増える」を結び付けて書きます。採点講評では(1)より(2)の正答率が低く、再編成で空き領域を予約する必要があるとは限らないこと、緊急時ほどリスクを慎重に予測することを求めています。

設問2(1)(a)

ケース1は、在庫引当ジョブ同士が「在庫」テーブルでぶつかる場合です。表2によると、在庫引当は「注文明細」を主キー（注文番号、 注文明細番号）の順に読み、その順に「在庫」を更新し、コミットは注文ごとです。注文明細番号は顧客が入力した順に付くので、商品コードの順序はそろっていません。そのため、あるジョブは商品S1→S2の順、別のジョブはS2→S1の順に「在庫」の行をロックすることがあり、互いの行を待ってデッドロックになり得ます。公式解答例では「異なる商品の“在庫”を逆順で更新することがあり得るから」という趣旨です。

設問2(1)(b)

ケース2は、出庫指示ジョブ同士が「棚別在庫」テーブルでぶつかる場合です。表2によると、出庫指示は「出庫指示」を主キー（出庫番号、 棚番号、 商品コード）の順に読み、その順に「棚別在庫」（主キーは棚番号、 商品コード）を更新します。ジョブが違っても、1回のコミットの中では常に棚番号→商品コードの昇順、つまり「棚別在庫」の主キー順に更新します。ロックの取得順が全ジョブで一致するため、循環待ちは起きません。公式解答例では「“棚別在庫”を常に主キーの順で更新しているから」という趣旨です。

設問2(1)(c)

ケース3は、在庫引当ジョブと出庫指示ジョブが、進み具合によって同時に動く場合です。どちらも「注文明細」の注文状態を更新します。ただしジョブは注文番号の範囲で分割されているので、異なるジョブが同じ注文の明細行を更新することはありません。ロックの競合自体が起きないため、デッドロックのリスクはありません。公式解答例では「異なるジョブが同じ注文の明細行を更新することはないから」という趣旨です。採点講評では(1)cの正答率が低く、注文番号の範囲で分割している点に着目すること、マスター・在庫系のテーブルとトランザクション系のテーブルではロック競合のリスクが違うことを理解するよう求めています。

設問2(2)

ケース1の原因は、ジョブによって「在庫」を更新する順序が違うことです。対策は、コミット単位とISOLATIONレベルを変えずに、更新順序を商品コード順にそろえることです。公式解答例では次の二通りの選び方が示されています。注文登録処理を選ぶ場合は、「“注文明細”に行を商品コードの順に登録する」または「商品コードの順に注文明細番号を付与する」。こうすると、在庫引当が主キー順に読むだけで商品コード順の更新になります。在庫引当処理を選ぶ場合は、「“在庫”の行を商品コードの順に更新する」。答案では処理名と変更内容を対応させ、「商品コード順」という具体的な順序を30字以内で明記します。

設問3(1)(あ)

空欄xはLAG関数のPARTITION BYに当たり、区画の切り方で「1行前の出庫時刻」が何を指すかが変わります。A.ピッカーIDで区画を切ると、同じピッカーの出庫時刻が一日を通して並びます。すると、ある注文の1番目の商品の1行前は、そのピッカーが直前の注文で最後に出庫した時刻になります（COALESCEで出庫開始時刻を使うのは、その日の最初の1行だけです）。棚の前での待ち時間は含まれますが、直前の注文の梱包と出荷担当者への受渡しの時間まで入ってしまいます。公式解答例では、あは「A.ピッカーID」です。

設問3(1)(い)

B.棚番号で区画を切ると、同じ棚で誰かが直前に出庫した時刻との差になります。これはその棚が使われた間隔にすぎず、ピッカーが棚の前で待った時間は表しません。そのため、長い待ちの実証という分析目的を達成できません。公式解答例では、いは「B.棚番号」です。本文の定義（1番目の商品は出庫開始時刻から、2番目以降は一つ前の商品の出庫時刻から）に合うのは、残りのB.出庫番号で区画を切る場合です。採点講評では、あ・いの正答率が低く、区画ごとにどんな並びの出庫時刻が得られるかを考えれば正答できるとしています。

設問3(1)(う)

下線部の対策は、順路の方向を変えずに、多くのピッカーが棚3番に集中しないよう出庫指示を作るものです。本文の例では、あるピッカーの順序が「棚3番S3→棚6番S6」です。デッドロックが起きるのは、別のピッカーが同じ2行を逆の順序で扱う場合です。つまり1番目に棚6番を回らせる指示で、公式解答例では、うは「6番」です。

設問3(1)(え)

1番目の棚6番で出庫する商品は、例の1人目の2番目と同じS6です。図2で棚6番にはS4とS6がありますが、1人目と同じ「棚別在庫」の行（6番、 S6）を扱う必要があります。公式解答例では、えは「S6」です。

設問3(1)(お)

2番目は、1人目の1番目と同じ棚3番です。こうすると、1人目は（3番、 S3）→（6番、 S6）、2人目は（6番、 S6）→（3番、 S3）の順に「棚別在庫」の行を更新することになり、ロックの順序が逆転します。公式解答例では、おは「3番」です。

設問3(1)(か)

2番目に棚3番で出庫する商品はS3です。これで二つの出庫指示ジョブが「棚別在庫」の同じ2行を逆の順序でロックする組み合わせになり、表3のケース2でデッドロックが起き得ます。公式解答例では、かは「S3」です。

設問3(2)

下線の対策を適用すると、ピッカーごとの出庫の順序と「棚別在庫」を更新する順序が、ジョブによってそろわなくなり得ます。設問3(1)のように更新の順序が逆転すると、ケース2の「常に同じ順序で更新しているのでリスクがない」という前提が崩れます。回避策は、コミット単位とISOLATIONレベルを変えずに、「棚別在庫」を更新する順序を全ジョブ共通の固定した順序にすることです。公式解答例では、「“出庫指示”の読込み順を出庫番号、商品コード、棚番号の順に変更する」または「“棚別在庫”の行を商品コード、棚番号の順に更新する」という趣旨が示されています。答案では、どのテーブルをどの列の順に読む（更新する）かを40字以内で具体的に書きます。

学習のポイント

主キー順の挿入と空き領域の関係、ロック順序によるデッドロックの成否の判断、ウィンドウ関数の区画の選び方を、本文の仕様から根拠を示して説明できるようにします。