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

模範解答のキーワードと論点整理

• 単調に増加する主キー
• 挿入時のページ選択
• 常に「表領域の最後のページ」に格納される

解答が正しい理由の論理展開

1. RDBMSの仕様（原文引用）

   「INSERT 文で行を挿入するとき, RDBMSは, 主キー値の並びの中で, 挿入行のキ一値に近い行が格納されているページを探し, 空き領域があればそのページに, なければ表領域の最後のページに格納する。」

2. 注文明細テーブルの主キーである注文番号は「単調に増加する注文番号で識別する」ため、
   新しい注文番号は常に既存のすべての行より大きく、「近い行」を持つページは表領域の最後のページになります。
3. したがって、再編成で他のページに空き領域を30%予約しても、その予約領域へは挿入されず、
   常に末尾のページに格納されるため、予約空き領域が使われません。

誤りやすいポイント

• 「空き領域を予約している＝必ず使われる」と考える誤解
  → 挿入時は主キー順の“近さ”でページを選ぶため、末尾への追加入力で予約領域は無視される。
• 後半の「空き領域がなければ新ページを追加」という文だけを見て、そちらに固執しない

試験対策のまとめ

• INSERT時のページ選択：主キー値に近いページ→空き有→挿入、無→最後のページ
• 再編成の空き領域予約（fill-factor）：挿入順序とキー分布次第で使われないことがある
• 主キーの性質（単調増加 vs ランダム割り当て）が、データ配置・性能に与える影響を押さえる
• 問題文の「仕様」を常に原文で確認し、個別の処理フローと結び付ける練習をする

キーワード・論点

• 再編成時の空き領域予約（既定値は 30%）
• 行の挿入時に「予約空き領域」が使われない
• 各ページに無駄な空きが残る → ストレージ消費が増大 → 表領域満杯リスク

解説

1. 【問題文】の仕様から、再編成時の挙動を確認します。

   「再編成するとき,テーブルに空き領域の割合(既定値は 30%)を指定した場合,各ページ中に空き領域を予約することができる。」

2. しかし、

   「当該テーブルへの行の挿入では予約された空き領域が使われないこと」
   とあるとおり、新規行の追加では再編成で予約した空き領域を利用せず、常に新規ページを割り当てる挙動になります。

3. その結果、各ページに「既定の空き領域分（30%相当）」が常に残るため、実際に格納できるデータ量に対してページ数が無駄に増加し、表領域が早期に満杯になるリスクがあります。

受験上の注意ポイント

• 空き領域予約＝必ず使われると誤解しやすいが、本問では「予約空き領域を利用しない」仕様を押さえる。
• 再編成後の空き領域設定は、実際の挿入アルゴリズムによって効果が変わる点を理解する。
• 「表領域が満杯になるリスク」は、ただ行数が増えるからではなく、１ページ当たりの有効利用率が低下する点がポイント。

試験対策まとめ

• RDBMSの 再編成 と 空き領域割合 設定の基本仕様
• ページ単位の 挿入アルゴリズム と 空き領域の使用可否
• 表領域の物理拡張リスク：ページあたり有効データ量の低下による容量増大への影響を押さえる。

キーワード・論点整理

• デッドロックの基本要因：異なるトランザクションが同じテーブルの行に対して「逆順にロック取得」すると発生
• 在庫引当バッチの更新順序
  ‐ 「注文登録」「在庫引当」「出庫指示」のうち、在庫引当は

  「注文状態が未引当の　注文明細　をキー順に読み込み、その順で　在庫　を更新」

• 出庫指示バッチの更新順序
  ‐ 出庫指示は

  「出庫指示 をキー順に読み込み、その順で　棚別在庫　を更新」

• 注文明細テーブルへの更新範囲
  ‐ 在庫引当は「注文状態が未引当の明細行」を、
  ‐ 出庫指示は「注文状態が引当済の明細行」をそれぞれ独立に更新

(a)「在庫引当／在庫引当」リスクの理由

異なる商品の“在庫”を逆順で更新することがあり得るから

1. 在庫引当は

   「注文状態が未引当の 注文明細 をキー順に読み込み、その順で 在庫 を更新し…」
   とある。

2. バッチを注文番号の範囲で分割すると、各ジョブが対象とする 注文明細 のキー順はそれぞれ異なる。
3. 結果として、ジョブA が「商品X → 商品Y」の順で 在庫 を更新し、ジョブB が「商品Y → 商品X」の順で更新する可能性がある。
4. このとき、ジョブA が先に商品X をロックしジョブB が先に商品Y をロックした後、逆の商品でロックを取りに行くと、**ロック奪い合い（デッドロック）**が発生する。

(b)「出庫指示／出庫指示」リスクの理由

“棚別在庫”を常に主キーの順で更新しているから

1. 出庫指示は

   「出庫指示 をキー順に読み込み、その順で 棚別在庫 を更新し…」

2. どのピッカー割り当てジョブでも、必ず同じ主キー順（出庫番号→棚番号→商品コードなど）で 棚別在庫 を更新する。
3. 複数ジョブが同時に並列実行しても、更新対象行のロック取得順は全ジョブで一貫して同一順序となるため、ロック競合は起きてもデッドロックにはならない。

(c)「在庫引当／出庫指示」リスクの理由

異なるジョブが同じ注文の明細行を更新することはないから

1. 在庫引当は「注文状態が未引当の 注文明細」を対象
2. 出庫指示は「注文状態が引当済の 注文明細」を対象
3. 両者は更新する明細行の状態が異なるため、同一行を同時に更新することが絶対にない
4. したがって、注文明細 テーブルを巡る排他ロックの奪い合い自体が発生せず、デッドロックは起きない

受験上の注意ポイント

• 更新順序の一貫性
  複数ジョブで同一テーブルを更新するときは「常に同じキー順で更新する」ことでデッドロックを回避できる。
• ロック対象の重複回避
  状態遷移（例：未引当→引当済→出庫指示済）のように、更新対象を段階的に分けると同一行更新の競合を防ぎやすい。
• ジョブ分割時の副作用
  キー範囲でジョブを多重化する際は、各バッチの読み込み順序・更新順序の逆転有無を必ず確認すること。
• SQLのキー順指定
  明示的に ORDER BY やウィンドウ関数でキー順を保証しないと、RDBMS内部のアクセス順によっては思わぬロック競合を招く。

模範解答の整理

キーワード・論点整理

• デッドロック
  同一「在庫」テーブルの複数行を、複数ジョブが異なる順序で更新しようとして発生
• ロック取得順序の一貫性
  並列実行するジョブ間で更新対象行の順序をそろえる必要がある
• 注文明細の主キー順
  「在庫引当」処理は

  “注文状態が未引当の "注文明細" をキー順に読み込み，その順で "在庫" を更新”
  とあるため、注文明細番号の付与順が更新順を決める

• 回避策
  商品コードでソートされた順序で更新（あるいは挿入）するように変更し、全ジョブで同一順序を担保

解答の論理的根拠

1. 表３ケース1では，

   処理名	処理名	テーブル	リスクの有無
   在庫引当	在庫引当	在庫	ある

   とあり，

   (a)
   同一テーブルの行を異なる順序で更新するとデッドロック

2. 在庫引当処理の流れ（問題文引用）：

   “注文状態が未引当の "注文明細" をキー順に読み込み，
   その順で "在庫" を更新し，…”

   注文明細番号の割り当て順がキー順になるため，
   並列ジョブ間で異なる製品コードの順序で「在庫」行をロックし，
   相互に待ち状態となりデッドロックを起こす。
3. そこで，
   • 注文明細を あらかじめ商品コード順に登録（注文登録処理の変更）
     ↓
   • 注文明細番号順＝商品コード順
   • 在庫引当は注文明細のキー順（＝商品コード順）に更新
     または
   • 在庫引当処理で “在庫”を更新する際に商品コード順に並び替え
     ↓
   • 並列ジョブ間で在庫行の更新順をそろえ，デッドロック回避

誤りやすいポイント

• 「コミット単位」「ISOLATION レベル」には手を付けられない
• 注文明細番号やPKのキー順≠商品コード順のままでは順序がバラバラ
• 並び替え忘れ（ORDER BY商品コード）や副問い合わせでインデックス順に依存すると危険
• ジョブ分割時，読み込む順序と更新する順序は必ず設計で制御する

試験対策：覚えておくべきポイント

1. デッドロック回避の基本
   • 複数リソースをロックするときは，全ジョブで同一の順序でロック取得
2. 並列バッチ処理
   • ジョブ分割するとき，アクセス順序が狂うとデッドロックの原因に
3. RDBMS の入出力順序
   • 主キー順・INDEX順は明示的に ORDER BY しないと保証されない
4. 小問では「コミット単位」「Isolation Level」は変更不可
   • 別方法（ソート順制御）でデッドロック回避を検討することが重要

キーワード・論点整理

解答解説

（あ）（い） LAG関数による「出庫間隔時間」算出のパーティションキー

B.出庫時刻 - COALESCE(
  LAG(B.出庫時刻) OVER (PARTITION BY (x) ORDER BY B.出庫時刻),
  A.出庫開始時刻
) AS 出庫間隔時間

1. PARTITION BY ピッカーID
   • 同一ピッカー全体の出庫指示を時刻順に並べるため、ある注文の最後の商品から次の注文の最初の商品まで含む。
   • したがって「商品の梱包及び出荷担当者への受渡しに掛かった時間」も含まれてしまう。
   • 問題文：「（あ）を指定すると，棚の前での待ち時間を含むが、商品の梱包及び出荷担当者への受渡しに掛かった時間が含まれてしまう。」
2. PARTITION BY 棚番号
   • 同一棚における複数の出庫指示をまとめて処理するため、同一注文内で複数回ピックする場合でも「前回の同じ棚での出庫時刻」と比較されてしまい、同一注文内で連続する商品の前後比較（＝待ち時間＋取り出し時間）が行えない。
   • 問題文：「（い）を指定すると，棚の前での待ち時間が含まれないので，分析の目的を達成できない。」

• （あ）＝「ピッカーID」
• （い）＝「棚番号」

（う）（え）（お）（か） 出庫指示バッチの多重化時のデッドロックリスク

• 出庫指示バッチは，出庫指示を「キー順に読み込み，その順で棚別在庫を更新」します。
• 「キー順」とは，出庫指示テーブルの主キー（出庫番号，棚番号，商品コード…）の並び順です。
• 典型的には「同一出庫（出庫番号）内で，棚番号の昇順」に更新を行うため， 単一スレッド時は必ず昇順でロック取得し，デッドロックは発生しません（表3 ケース2）。

1. スレッドA：
   • 1件目 → 棚3番の商品 S3 を出庫
   • 2件目 → 棚6番の商品 S6 を出庫
     （昇順で 3 → 6 の順に更新）
2. スレッドB（同時実行）：
   • 1件目 → 棚6番の商品 S6 を出庫
   • 2件目 → 棚3番の商品 S3 を出庫
     （降順で 6 → 3 の順に更新）

• （う）＝「6番」
• （え）＝「S6」
• （お）＝「3番」
• （か）＝「S3」

選択肢・誤りやすいポイント

1. 「PARTITION BY 出庫番号」を選んでしまう誤り
   • 出庫番号ごとに区切ると，１つの出庫内の連続比較は可能ですが，複数の出庫（注文）をまたぐ分析ができないため「同一ピッカー全体の動きを比較したいのか」「同一出庫内の待ち時間を比較したいのか」を混同しやすい。
2. 棚番号でパーティションを切ると「同一注文内」で棚番号が重複していない限り，２行目以降はすべて前回比較対象が他注文の同じ棚となり，まったく異なる間隔（通路移動時間など）になってしまう。
3. デッドロックの発生条件
   • 更新対象（ここでは棚別在庫テーブル）に対し，異なるスレッドが逆順でロックを取得しようとするとき発生する。
   • 「更新順序を常に昇順に統一」すれば防げるが，出庫指示の順路制御などで任意の順序にすると逆順ロックが起きる。

試験対策まとめ

• LAG関数を用いた出庫間隔時間の分析では，PARTITION BY のキー選択が「同一単位（ピッカー単位 or 出庫単位 or 棚単位）」の行をどのようにグループ化するかを意識する。
• デッドロックは「同じリソースを異なる順序でロックしようとする」ことで発生する。バッチを多重化するときは，複数ジョブ間でテーブルアクセスの順序をそろえることが重要。
• 出庫業務においては「出庫番号」「ピッカーID」「棚番号」の３つのキーの組み合わせで分析単位やロック単位が変わる点を押さえておくこと。

1. 模範解答のキーワード・論点整理

• 一貫した行ロック順序によるデッドロック回避
• “出庫指示”テーブルの読み込み順序変更
• “棚別在庫”テーブルの行更新順序変更

2. 解答が導かれる理由

（1）問題文からのポイント

• 問題文中の「出庫指示」処理では、以下のように書かれています。

  ・“出庫指示”をキー順に読み込み、
  ・“棚別在庫”を更新し、…コミットする。

• “出庫指示”の主キー順は（出庫番号, 棚番号, 商品コード）
• “棚別在庫”の主キー順は（棚番号, 商品コード）

（2）解答の対策内容

1. “出庫指示”の読み込みキーを
   →（出庫番号, 商品コード, 棚番号）
2. “棚別在庫”の行更新キーを
   →（商品コード, 棚番号）
   にそれぞれ変更し、
   すべてのジョブが「同一の (出庫番号→商品コード→棚番号) の順」でロックを取得するようにします。
   こうすることで、どのジョブ／どのピッカー指示でも商品コードごとにまとめて棚番号の昇順でロックが掛かり、順序が逆転することがなくなり、デッドロックを回避できます。

3. 受験者が誤りやすいポイント

• 「読み込み順」と「更新順」の両方をそろえる必要があること
• 主キーの元の順序（出庫番号→棚番号→商品コード）をそのまま逆にすればよいわけではなく、「商品コード」を先に出すこと
• コミット単位やISOLATIONレベルは変更禁止という条件を忘れないこと

4. 試験対策として覚えておくべき知識

• 【デッドロック回避の基本】
  複数のバッチやトランザクションが並列に同一テーブル／行を更新する場合、必ず「同一のキー順」でアクセス（ロック）すること。
• 【RDBMSの行ロック順序】
  主キーや索引のキー順で行をロックする仕組みを理解し、必要に応じて明示的にORDER BY句やソート順を指定できるようにする。
• 【バッチの多重化リスク】
  ジョブ分割によるスループット向上策では、デッドロックだけでなくコミット粒度や索引の選択が性能や整合性に与える影響にも注意すること。