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

キーワードと論点整理

解答の根拠と論理的説明

1. （ア）＝「出庫要求明細」
   • 図2最上段の文言：
     「“ア” テーブルへの行の イ に対して、BEFORE トリガと AFTER トリガを定義する。」
   • 在庫引当は「出庫要求明細」ごとに部品番号をもとに行うため、テーブル名は “出庫要求明細” が適切です。
   • 問題文の記述：
     「出庫要求では…“出庫要求明細” テーブルに出庫要求の内容を登録し…」
     「在庫引当では, 出庫要求に応じて, “出庫要求明細” テーブルに指定した部品番号の部品について…」
2. （イ）＝「挿入」
   • 図2最上段および中段で扱うタイミングが新規行の追加です。
   • 図2中段の文言：
     「BEFORE トリガでは、“ア” テーブルに対して イ した行の、ウ 列の値を検索条件に指定して…」
   • 「出庫要求明細」に挿入された行を契機に在庫引当を行う設計です。
3. （ウ）＝「部品番号」
   • 図2中段：
     「…した行の、ウ 列の値を検索条件に指定して、…」
   • 在庫テーブルの検索条件は「部品番号」です。
   • 問題文：
     「“出庫要求明細” テーブルに指定した部品番号の部品について出庫要求数量の出庫が可能かどうか判定し…」
4. （エ）＝「在庫」
   • 図2中段：
     「…ウ 列の値を検索条件に指定して、“エ” テーブルから オ、カ を参照する。」
   • 在庫情報を持つテーブルは “在庫” テーブルです。
5. （オ）＝「実在庫数量」
   • 図2中段：「“エ” テーブルから オ、カ を参照する。」
   • 実在庫数量と引当済数量を使って引当可否を判定します。
   • 問題文：
     「“在庫” テーブルの実在庫数量及び引当済数量を更新し…」
     「(CHKROW.実在庫数量 － CHKROW.引当済数量 <= CHKROW.基準在庫数量)」
6. （カ）＝「引当済数量」
   • 同上の判定式に現れるもう一方の列が “引当済数量” です。

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

• 「出庫要求」と「出庫要求明細」の混同
  → トリガは1行ごとの処理なので，ヘッダーテーブルの「出庫要求」ではなく明細テーブルの「出庫要求明細」に定義します。
• UPDATE トリガ vs INSERT トリガの区別
  → 中段にある「イ した行」とは新規追加された行のこと。UPDATE ではありません。
• 在庫テーブル内の列選択
  → 発注フラグや基準在庫数量ではなく、実在庫数量と引当済数量を参照して引当可否を判断します。

試験対策のまとめ

• トリガは「行単位で何をきっかけに」「どのテーブル」「何を参照して」「どのように処理するか」を正確に押さえる。
• 見慣れない図や空欄補充問題では、図中の文言を手がかりに「契機」「タイミング」「対象」「条件」「アクション」の5要素を整理する。
• テーブル名の混同に注意し、ヘッダー（出庫要求）か明細（出庫要求明細）かを区別する練習を行う。
• トリガの種類（BEFORE/AFTER, INSERT/UPDATE/DELETE）を図と問題文から読み取る訓練を積む。

1. キーワード・論点整理

• トリガの実行タイミング：
  • BEFORE トリガ → 対象行の参照（SELECT）のみ
  • AFTER トリガ → 実際の更新（UPDATE）
• トランザクションの ISOLATION レベル（READ COMMITTED）
  「データ参照時に共有ロックを掛け、参照終了時に解放する」
• ロックの種類
  • 共有ロック → 他のトランザクションは更新待ち
  • 専有ロック → 他のトランザクションは参照・更新とも待ち
• SELECT … FOR UPDATE 句
  対象行に専有ロックを掛け、トランザクション終了時まで解放しない
• レースコンディション
  複数トランザクションで同一行を同時に参照・更新するときの競合

2. 解答の論理的説明

(a) 問題の発生メカニズム

1. トランザクションT1がBEFOREトリガで在庫数量を参照（共有ロック）
2. 参照終了に伴いロック解放
3. トランザクションT2が同じ在庫行をBEFOREトリガで参照し、同じ判定結果を得る
4. T2がAFTERトリガで更新（専有ロック）→ 引当済数量を加算
5. T1に制御が戻り、AFTERトリガで更新（専有ロック）→ さらに引当済数量を加算

(b) 対策

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

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

1. 各ISOLATIONレベルの参照・更新時のロック取得・解放タイミング
2. SELECT … FOR UPDATE 句による「専有ロック」の特徴
3. トリガの実行タイミング（BEFORE／AFTER）と同一トランザクション内でのロック挙動
4. レースコンディション対策としてのロック制御方法（明示的ロック vs 暗黙ロック）

キーワード・選択肢の整理

解答のポイントと理由

1. (a) に「AFTER」を使う理由
   ・問題文では「トリガでの定量発注のために…トリガからストアドプロシージャを呼び出す」とあり，実際の更新（UPDATE）が完了した後で発注処理（CALL PARTSORDER）を行いたい。
   ・【問題文】「実行タイミング（テーブルに対する変更操作の前又は後。前者を BEFORE トリガ，後者を AFTER トリガという）」
   → 更新完了後に発注を行うため，AFTER トリガを指定する。
2. (b) に「NEW」を使う理由
   ・トリガ実行時に呼び出し元の更新後の行データを参照して「部品番号」をストアドプロシージャに渡す必要がある。
   ・【問題文】「挿入では操作後の行の内容を、更新では操作前と操作後の行の内容を…参照するには…相関名で列名を修飾する」
   → UPDATE 後の行データを扱うには NEW を使い，NEW ROW AS CHKROW とする。
3. (c) に「FOR EACH ROW」を使う理由
   ・更新対象の各行ごとに発注判定を行い，必要ならば発注処理を呼び出す設計である。
   ・【問題文】「トリガ内では，トリガを実行する契機となった変更操作…行を参照することができる」
   → 行単位で動作させるには FOR EACH ROW を指定する。
4. (d) に「WHEN」を使う理由
   ・トリガの起動条件を「実在庫数量－引当済数量 <= 基準在庫数量」の場合に限定したい。
   ・SQL標準では，行レベルトリガの条件指定に WHEN (条件式) を用いる。
   → WHEN (CHKROW.実在庫数量 － … <= CHKROW.基準在庫数量) と書く。

よくある誤り・選択肢のひっかけ

1. BEFORE と AFTER の混同
   • BEFORE トリガは変更前に動くため，UPDATE文内で別のテーブルにINSERT/UPDATE/DELETEを行うと，
     【問題文】「BEFORE トリガの処理開始から終了までの同一トランザクション内では，全てのテーブルに対して変更操作を行うことはできない」
     の制約に引っ掛かり，そもそも後続処理ができない可能性があります。発注呼び出しは実更新後に行うべきです。
2. REFERENCING の NEW/OLD の使い分け
   • OLD は更新前の値，NEW は更新後の値を参照します。
   • 発注用の部品番号は「更新後の引当済数量」を見てから呼び出すので，NEW を使う必要があります。
3. FOR EACH ROW と FOR EACH STATEMENT の使い分け
   • FOR EACH STATEMENT は文単位（UPDATE文１回呼び出し）でしか動作せず，各行ごとの情報は扱えません。
   • 本問は行ごとに在庫をチェックし，発注判定を行うため，FOR EACH ROW を使います。
4. トリガ内の条件指定：WHERE ではなく WHEN
   • WHERE は通常の SELECT/UPDATE 文の行フィルタですが，SQLトリガでは行レベルの起動条件に WHEN を使います。
   • 誤ってトリガ宣言の最後に …WHERE (条件式) と書くと文法エラーになります。

試験対策まとめ

• トリガの実行タイミング
  • BEFORE：変更前，AFTER：変更後
  • BEFORE では DML 制約やロックの違いに注意
• REFERENCING の NEW / OLD
  • INSERT：NEW のみ
  • UPDATE：OLD・NEW の両方
  • DELETE：OLD のみ
• トリガの対象範囲
  • FOR EACH ROW（行単位）
  • FOR EACH STATEMENT（文単位）
• 行レベルトリガの条件式指定
  • WHEN (条件式) を使う

キーワードと論点の整理

• 「在庫引当以外に，（あ）でも “い” テーブルの う 列が更新され，図３で定義したトリガが発動し，発注が繰り返されることになる。」
• 「発注が繰り返されないように，トリガ内で在庫を確認する際に， “い” テーブルの え 列の値も判定に加えることにした。」

解答の論理的説明

1. （あ）＝「出庫」
   問題文中の「在庫引当以外に，（あ）でも」「“い” テーブルの う 列が更新され」とあるのは，ステップ(6)の出庫処理を指しています。
   問題文の該当箇所：

   「出庫では, …“在庫” テーブルの実在庫数量及び 引当済数量 を更新し, …」
   ここで在庫引当以外に 引当済数量 を更新している操作は「出庫」です。

2. （い）＝「在庫」
   空欄（い）は「“い” テーブルの う 列」とある部分で，列名 う=「引当済数量」を持つテーブルを指します。
   テーブル一覧（図1）を見ると，「在庫」 テーブルに「引当済数量」列が存在します。
3. （う）＝「引当済数量」
   「“い” テーブルの う 列が更新され」とあるのは，まさに「在庫」テーブルの 「引当済数量」 列のことです。
   ステップ(5)・(6)でも在庫引当や出庫時にこの列を更新していることが明示されています。
4. （え）＝「発注済フラグ」
   修正策として，「トリガ内で在庫を確認する際に，“い” テーブルの え 列の値も判定に加える」とあります。
   在庫テーブル（図1）には，実在庫数量，基準在庫数量，補充ロットサイズに加えて，発注処理を管理する 「発注済フラグ」 列があります。
   これをチェック条件に加えることで，既に発注中の部品に対してトリガが発動するのを防ぎ，発注の多重実行を防止します。

注意すべきポイント

1. 操作名の取り違え
   • 「発注」や「入庫」ではなく「出庫」が正解です。出庫処理で在庫テーブルの「引当済数量」を減算して更新している点を押さえましょう。
2. テーブル名・列名の混同
   • 複数のテーブルに似た列名（部品番号など）があるため，「どのテーブルのどの列か」を図1や業務フローで正確に対応付ける必要があります。
3. 発注制御フラグの重要性
   • 定量発注トリガを設計する際，発注済みの管理フラグ（発注済フラグ）を条件に加えないと，トリガが無限ループ的に発動する設計ミスに陥ります。

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

• 在庫テーブルの構造把握：図1の「在庫」テーブルには最低限これらの列があることを確認する。

  テーブル名	列名
  在庫	実在庫数量
  	引当済数量
  	基準在庫数量
  	補充ロットサイズ
  	発注済フラグ

• 業務フローと更新内容の紐付け：
  • 在庫引当時 → 引当済数量を「加算」
  • 出庫時 → 実在庫数量を「減算」，引当済数量を「減算」
• トリガ設計の落とし穴：BEFORE/AFTERトリガで扱うテーブルや列を限定する際は，副作用となる操作を必ず洗い出し，発動条件（WHEN句など）で漏れなく抑制すること。

キーワードと論点整理

• デッドロック
  同一のリソース（行）を異なる順序でロックし、それぞれが相手のロック解放を待機してしまう状況
• 対象テーブル
  模範解答のキーワードは「在庫」

解答が「在庫」になる理由

1. 【問題文】より、出庫と入庫のそれぞれの処理内容を確認します。
   • 出庫では
     「“在庫”テーブルの実在庫数量及び引当済数量を更新し」
     （問題文 (6) 行）
   • 入庫では
     「“在庫”テーブルの実在庫数量を更新し、発注済フラグをオフにする」
     （問題文 (8) 行）
2. どちらの処理も同一のテーブル「在庫」に対して更新を行うため、
   • トランザクション A（出庫）
   • トランザクション B（入庫）
     が同時に走ると、お互いに「在庫」テーブルの同一行または複数行をロックし合い、
     「相手のロック解除待ち」が循環してしまいデッドロックが発生します。
3. 以上から、出庫要求と入庫でデッドロックが起こる対象テーブルは
   「在庫」 です。

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

• 複数テーブルが絡むと思い込む
  出庫要求明細や発注・入庫テーブルと混同しがちですが、実際に同時更新するのは「在庫」です。
• 読み取りロックだけと思う
  読み取り時の共有ロック（FOR UPDATE句なし）ではなく、更新時の専有ロックが原因になります。

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

• デッドロック発生要因
  同一リソースへの更新処理を異なるトランザクションが競合するときに起こる
• ロックの種類と解放タイミング
  • 更新時専有ロック → トランザクション終了まで解放されない
  • 読み取り時共有ロック → FOR UPDATE句指定時は専有ロック扱い
• 業務フローとテーブル更新箇所の対応付け
  問題文の業務手順ごとに「どのテーブル・どの列を更新しているか」を整理しておくと、競合箇所が特定しやすいです。

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

• デッドロック
• ロック獲得順序の統一（部品番号順）
• トランザクションの分割（部品ごとに別トランザクション）
• ロックの粒度縮小

2. なぜその解答になるのか

1. ロックの順序がバラバラだと，A⇨Bの順で行ロックを取るトランザクションと B⇨Aの順で取るトランザクションが互いに待ち合う「デッドロック」が起きます。
   • 問題文では「索引探索の場合, 索引から読み込んだ行だけをロック対象とする.」とあるため，特定の部品番号行だけをロックします。
   • そこで「出庫要求明細の登録と入庫を、それぞれ部品番号順に処理する」ことで，すべてのトランザクションが同一順序でロックを取得し，循環待ちを防げます。
2. １トランザクション内で複数行をまとめて処理すると，ロック保持時間が長くなり，競合頻度が高まります。
   • 問題文では「１トランザクションで処理する。生産ラインごとに様々な組合せの部品を要求する。」とあるため，複数部品を一度にまとめてロックする設計です。
   • これを「入庫は複数の部品をまとめず，部品ごとに別トランザクションで処理する」と分割することで，１回あたりのロック対象が１行だけになり，デッドロック発生確率が大幅に下がります。

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

• 「全表スキャンだからテーブルロックされる」と思い込むと，そもそも行単位ロックの性質を活かせません。主キー索引を使えば行単位ロックです。
• トランザクション分割の意義を「速度向上」や「並列度向上」とだけ考え，デッドロック回避策だと結びつけられないことがあります。

4. 試験対策として覚えておくべきポイント

• デッドロック回避の基本
  1. リソースを固定順序で取得する
  2. トランザクションを小さく分割し，ロック保持時間を短縮する
• RDBMSのロック特性
  • READ COMMITTED, REPEATABLE READ いずれも更新時は行単位で専有ロック
  • 索引探索なら必要行だけをロックし，表探索（全表スキャン）だと全行がロック対象に
• トリガやストアドプロシージャを使う場合も，ロック順序とトランザクション設計が重要です。