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

AY

• 表2より

  AY | P5 | 1
  AY | P9 | 1
  

• よってレベル1で

  AY

  が持つ部品は

  P5

  と

  P9

  。

P5

• 表2より

  P5 | P3 | 1
  P5 | P6 | 1
  

• したがってレベル2で

  P5

  が持つ部品は

  P3

  と

  P6

  。
• ここで図１の（ア）・（イ）は “

  P5

  の子” に該当する。
  ⇒ （ア）＝

  P3

  、（イ）＝

  P6

P6

• 表2より

  P6 | P8 | 1
  P6 | P9 | 1
  

• したがってレベル3で

  P6

  が持つ部品は

  P8

  と

  P9

  。
• 図１の（ウ）は “（イ）＝P6 の子” のうち、まだ図に現れていない方。
  ⇒ （ウ）＝

  P8

（ア）P3, （イ）P6, （ウ）P8

親品番 = 'AZ'、子品番 = 'P7'

P7  P4  1

親品番 = 'P7'、子品番 = 'P2'

• 構成テーブルの列名は 「親品番」「子品番」「構成数」。
• 正展開処理は「親品目がどの子品目を使っているかを、階層を上から下に 1階層ずつたどる」方法。

• 第1SELECTで レベル1（親品番='AZ'）を取得。
• UNION ALL 直後のSELECTで レベル2 を取得するために、別名 L1（レベル1行）と L2（レベル2行）を自己結合。

• レベル1行の 「子品番」 が、次階層行の 「親品番」 と一致したときにのみ階層がつながる。
• よって

  L1.(a) = L2.(b)

  には
  • (a) → 子品番
  • (b) → 親品番
    を入れるのが唯一妥当。

• (a) = 子品番
• (b) = 親品番

レベル0 : AZ  
レベル1 : P3(1)、P7(2)、P9(2)  
レベル2 : P2(1×2)、P4(1×2)   ※P7 の下  
レベル3 : P3(2×2)             ※P2 の下

• AZ→P7→P2 … 2 × 1 ＝ 2（P2）
• AZ→P7→P4 … 2 × 1 ＝ 2（P4）
• AZ→P3 … 1 （P3）
• AZ→P7→P2→P3 … 2 × 1 × 2 ＝ 4（P3）

• P2：2
• P4：2
• P3：1＋4＝5

• “構成” テーブルの列は【問題文】「親品番」「子品番」「構成数」。
• 子から親へ上がるには

  L2.親品番 = L1.子品番
  

  しかない。
  ⇒ (c)＝“親品番”、(d)＝“子品番”。

• 【問題文】「製品のレベルを0」と定義。
• “品目” テーブル行を LLC＝0 でフィルタすれば“製品”だけ残る。
• これにより自己結合で 2 階層上がった先が製品かどうかを保証。

• 図1の構成より、レベル2 で “P9” が現れるのは “AX” → “P1” → “P9”。
• 他の製品 (“AY”、“AZ”) では “P9” はレベル1 または 3 で使用。
  ⇒ 出力は “AX”。

WHERE L2.子品番 = 'P9'              -- L2 に対する検索条件
ON   L2.親品番 = L1.子品番          -- L2 と L1 の結合条件
ON   L1.親品番 = 品番              -- L1 と 品目 の結合条件

• 親品番→子品番
  • L2：先頭列に等値条件が無い → 表探索
• 子品番→親品番
  • L2：

    子品番='P9'

    で先頭列一致 → 索引探索
  • L1：

    子品番

    と

    親品番

    の両等値で先頭から連続一致 → 索引探索

• SQL3 は「子品番、構成数、品目区分」を取得し、次階層の部品探索に用いる。

• 「単体部品」は【問題文】にあるとおり「単独で使われる」＝子品番を持たない。

• 手順①または③で取得した部品の品目区分が「単体部品」であれば、その部品については以後 SQL3 を発行しても結果集合が空であることが確定。
• よって単体部品を見つけた時点で探索を打ち切れば、SQL3 の呼び出し総数を「単体部品の個数」だけ減らせる。

• 以上から「単体部品は子部品がないので SQL3 の発行は不要」となる。

SQL3

AND LLC >= :HLLC

P1 → P2

P2 → P1

P1 ⇄ P2

SQL3

P1

LLC=1

P2

LLC=2

P2 → P1

LLC(=1) < :HLLC(=2)

P1

• RDBMS は行単位に排他ロックを取り、解除は COMMIT。
• 「手順②」「手順⑤」は “品番順に更新する” ので、１製品内ではロック順序は固定。

• 製品「AX」レベル1の子部品は【表2】より「P1」「P4」「P9」。品番順なので「P4」→「P9」。
• 製品「AZ」はレベル1で「P9」、レベル2で「P4」（「P7」の下）。したがって取得順は「P9」→「P4」。

• トランザクションT₁（AX処理）が「P4」をロック済みで「P9」を要求。
• 同時にトランザクションT₂（AZ処理）が「P9」をロック済みで「P4」を要求。
• 双方が相手のロック解放を待つため循環待ち＝デッドロック。

• T₁側（AXツリー）：レベル1の「P4」「P9」を丸で囲む。
• T₂側（AZツリー）：レベル1の「P9」とレベル2の「P4」を丸で囲む。
• これが【模範解答】の図と一致します。

• 「手順②…在庫の引当可能数を品番順に更新する。」
• 「手順⑤…在庫の引当可能数を品番順に更新し…」
  これらは当初からロック取得順を統一する設計意図を示しています。

• 旧処理では「製品ごとレベルごと部品ごと」に

  SQL4

  を発行し、製品ＡＸは

  P1→P4→P9

  、製品ＡＺは

  P3→P7→P9

  のように別順序でロックを取得します。
• 二つのトランザクションが交差すると相互待ちが発生し、【図３】のようなデッドロックに至ります。

• 「製品ごと部品ごとに集計した所要量」を求め、

  SQL4

  をまとめて発行する。
• そして「品番順」に統一すれば、全トランザクションが必ず同じロック順序で在庫行を更新するためデッドロックが解消します。

• 列名と実行方法を明示する必要がある → 「SQL4 を部品の品番順に実行する。」（全角１９字）