データベーススペシャリスト 2022年 午後1 問03

解答の論理構成

1. 行の挿入順の事実
   • 問題文より「注文は、単調に増加する注文番号で識別する。」
     ⇒ 注文番号 が主キーの先頭列であり、値は昇順に連続発生。
2. RDBMSの格納アルゴリズム
   • 仕様「挿入行のキー値に近い行が格納されているページを探し…空き領域がなければ表領域の最後のページに格納する。」
     ⇒ 新しい 注文番号 は既存の最大値より大きいので、探索結果は必ず“最後尾ページ”。
3. 再編成で確保した空き領域の位置
   • 再編成は各ページに「空き領域の割合(既定値は30%)」を作るが、それは過去の注文番号が並ぶ途中ページ。
4. 結論導出
   • 挿入先が常に最後尾ページで固定されるため、中間ページの空き領域は参照されず、「再編成で予約した空き領域が使われない」。

誤りやすいポイント

• 「空き領域が使われない＝再編成は無意味」と断定する。実際は読み込み効率改善など別効果がある。
• ページ単位で異テーブル混在不可(仕様1(2))を忘れ、空き領域は他テーブル挿入に使えると誤解する。
• INSERT がランダムに行われると想定し、途中ページへ挿入されるケースを過大評価する。

FAQ

解答の論理構成

1. 前提確認
   • 最後の再編成から「この1年ほど」経過しており、その間に大量の INSERT によりページが増加。
2. RDBMSの予約空き領域の仕様
   • 再編成時に「空き領域の割合(既定値は30%)」をページ内に確保。
3. 予約空き領域が利用されない理由
   • 挿入時は「空き領域があればそのページに」だが、予約空き領域は使用対象外なので実際には使われず新ページが追加され続ける。
4. 今再編成すると…
   • 既存ページにも新規ページにも再び30% の未使用領域が生まれ、総ページ数がさらに増加。
5. 結論
   • 無駄なページ増加により「割り当てたストレージが満杯になるリスク」が発生。

誤りやすいポイント

• 予約空き領域を「将来の INSERT で自動的に使われる」と誤解する。
• 「行の削除を行わず」とあるので空きページが無いと判断しがちだが、実際はページ内に予約空き領域が存在。
• 再編成＝圧縮と短絡的に考え、ストレージ節約になると勘違いする。

FAQ

解答の論理構成

1. ケース1（在庫引当×在庫引当、対象：“在庫”）
   • 引当処理は【問題文】「“注文明細”をキー順に読み込み、その順で“在庫”を更新」とある。
   • ジョブを注文番号範囲で分割すると、各ジョブが異なる“商品コード”の在庫行を別々の順序で更新し得る。
   • 更新順序が異なると互いに行ロックを取り合い“逆順”になりデッドロックが発生。
     ⇒ (a)「異なる商品の“在庫”を逆順で更新することがあり得るから」
2. ケース2（出庫指示×出庫指示、対象：“棚別在庫”）
   • 出庫指示処理は【問題文】「“出庫指示”をキー順に読み込み、その順で“棚別在庫”を更新」と明記。
   • キー順（＝主キーの昇順）で常に同一順序でロック取得するため、同時ジョブ間でロック待ちは発生しても循環待ちは起きない。
     ⇒ (b)「“棚別在庫”を常に主キーの順で更新しているから」
3. ケース3（在庫引当×出庫指示、対象：“注文明細”）
   • 在庫引当は注文状態“0”を“1”に、出庫指示は注文状態“1”を“2”に変更。
   • ジョブ分割では注文番号範囲が互いに排他となり、状態値も異なるため「同じ行」を同時にロックする機会が無い。
     ⇒ (c)「異なるジョブが同じ注文の明細行を更新することはないから」

誤りやすいポイント

• 「キー順読み込み」がそのまま「キー順更新」になると早合点しがち。ケース1は読み込みテーブルと更新テーブルが異なるため順序が保持されない。
• デッドロック＝ロック待ちと混同し、単なる待ち時間をリスクと誤認する。
• 注文状態による行排他を見落とし、ケース3でもリスクありと判断してしまう。

FAQ

解答の導き方

1. 問題箇所の特定
   表3のケース1は、処理が「在庫引当」と「在庫引当」で、対象テーブルが「在庫」、リスクが「ある」と示しています。したがって、並列実行された複数の在庫引当処理同士でデッドロックが発生する事態を防ぐ必要があります。
2. デッドロック発生の原因を問題文から読む
   表2にある在庫引当の説明は「注文明細をキー順に読み込み、その順で在庫を更新し、…注文ごとにコミットする」とあります。つまり、各注文ごとに在庫の該当行を順に更新してロックを取得し、注文単位でコミットします。各トランザクションが行ロックを取得する順序が異なると、循環待ち（デッドロック）が起きます。
3. なぜ更新順を統一すれば回避できるか（具体例）
   • 例：注文Aは商品X→商品Y、注文Bは商品Y→商品Xの順に注文明細があるとします。
     ・トランザクションT1（注文A）はまず在庫のXをロックし、その後Yを取得しようとします。
     ・同時にT2（注文B）はまず在庫のYをロックし、その後Xを取得しようとします。
     するとT1はYのロック待ち、T2はXのロック待ちになり、互いに待ち合ってデッドロックが発生します。
   • 一方で、すべてのトランザクションが在庫更新を「商品コードの昇順」で行うと、どのトランザクションも同じ総順序でロックを取得します（例えば常にX→Y）。同一の全順序でロックを取る設計では循環待ちは発生しないため、デッドロックを回避できます。
4. なぜ「在庫引当」を変更するのが確実か
   • 在庫引当処理は実際に「在庫」行を更新してロックを取得する箇所なので、ここで取得順を明示的に制御すれば確実に全トランザクションのロック順を統一できます。
   • 対案として「注文登録」で注文明細を商品コード順に登録し注文明細番号を付与する方法も理論的に有効ですが、それは間接的な対策です。「注文明細をキー順に読み込む」という既存の振る舞いに依存するため、実装の取りこぼしや将来の変更で保証が外れやすく、より侵襲が大きくなります。よって最小の変更で確実な効果を得るには「在庫引当」を変更する方が望ましいです。
5. 実装上の注意（コミット単位・分離レベルは変更しない前提）
   • 在庫引当処理内で、当該注文の注文明細から対象の「商品コード」を抽出し、商品コードでソートした順に在庫更新（あるいは SELECT … FOR UPDATE を商品コード順で実行）するようにします。
   • こうすることで、各注文を処理するトランザクションは常に同じ商品コード順で在庫行に対してロック取得を行い、デッドロックを回避できます。
   • コミット単位（注文ごと）と分離レベルは変えないままで構いません。

誤りやすいポイント

• 注文明細の登録順を変えれば必ず解決すると思い込む
  → 注文登録で注文明細を商品コード順にする案は成り立ちますが、「注文明細番号の付与まで含めて」すべての入力経路で厳密に同じ順序を保証する必要があり、運用・実装の担当範囲が広がるため見落としが起きやすいです。
• 「並べ替え＝SQLのORDER BYだけ書けばよい」と考える誤り
  → 単にSELECTでORDER BYしても、その後の更新処理がその順序でロックを取るよう実装されていなければ意味がありません。明示的にソートした順で更新（あるいは SELECT … FOR UPDATE ORDER BY）する必要があります。
• ストレージの再編成で解決できると誤認すること
  → 物理配置（再編成）はロック取得順そのものを保証しないため、並列更新による循環待ちの根本対策にはなりません。
• コミット単位や分離レベルを変えて解決しようとする誤り
  → 設問で変更不可なので、その前提を守らないと失点になります。また、これらを変えると業務要件や整合性に影響を与えます。

FAQ

解答の論理構成

1. (あ) と (い) の選択
   • 出庫間隔時間は「ピッカーが出庫指示書の1番目の商品を出庫する場合では当該注文の出庫開始時刻からの時間」と定義されています。
   • これを SQL で実装するには PARTITION BY でピッカー単位に区切り、同一ピッカー内で ORDER BY B.出庫時刻 の時系列を作ります。
   • よって (あ)＝「A.ピッカーID」。
   • 逆にB.棚番号 で区切ると棚単位になり、ピッカー間の待ち時間が除外されて分析目的に合いません。(い) はリスク説明用の対比として「B.棚番号」。
2. (う)〜(か) の選択
   • 対策案で示された例：
     「あるピッカーに、1番目に棚3番の商品S3, 2番目に棚6番の商品S6」
     「別のピッカーには、1番目に棚（う）の商品（え）、2番目に棚（お）の商品（か）」
   • デッドロックを発生させるにはロック取得順序を逆にする必要があるため、 ・（う）＝「6番」、（え）＝「S6」
     ・（お）＝「3番」、（か）＝「S3」。
   • これによりピッカーＡは【棚3番→棚6番】、ピッカーＢは【棚6番→棚3番】で互いのロックを占有し合います。

誤りやすいポイント

• PARTITION BY B.棚番号 を選び、棚ごとに区切ってしまう
  → ピッカー待ち時間が含まれず目的不達。
• （う）と（お）を同じ棚番号にしてしまう
  → ロック順序が同一になりデッドロック例にならない。
• デッドロックは更新行単位と誤解
  → 本問は「棚別在庫」行の複合主キー（棚番号＋商品コード）で発生する。

FAQ

解答の導き方

• 出庫指示の読み込み順を出庫番号、商品コード、棚番号の順に変更する。これにより1件の出庫（出庫番号）内で商品コードごとにまとまった順序で処理されます（表2の「注文ごとにコミット」方針を維持）。
• 棚別在庫の行を商品コード、棚番号の順に更新する（すなわちその順でロックを取得する）。全ジョブが同じキー列の昇順で更新すればロック取得順が統一され、デッドロックは発生しなくなります。

誤りやすいポイント

• 「主キーの順序をそのまま使えば安全」と考える誤り：図1では出庫指示の主キーが出庫番号→棚番号→商品コードなので、そのままでは棚番号優先のロック順となり、異なるジョブ間で順序逆転が起こり得ます。主キー順に拘る必要はなく、全トランザクションで共通のロック順に揃えることが重要です。
• コミット単位やISOLATIONレベルを変更する案に飛びつく誤り：設問でこれらは変更しない条件なので、これらに頼る解答は不適です。
• 更新対象を「棚番号のみ」でソートする提案：ピッカー割当の変更で同一棚を異なる順で扱う可能性があるため、棚番号だけの順序では逆転が残る場合があります。全トランザクションで一意に定まる複合キー（例：商品コード＋棚番号）に基づく順序が必要です。

FAQ