データベーススペシャリスト試験 2019年 午後1 問02
データベースでのトリガの実装に関する次の記述を読んで、設問1〜3に答えよ。
オフィスじゅう器メーカの Y 社は、部品の入出庫、発注を行う在庫管理システムを構築している。
〔RDBMS の主な仕様〕
在庫管理システムに用いている RDBMS の主な仕様は、次のとおりである。
1．ISOLATION レベル
選択できるトランザクションの ISOLATION レベルとその排他制御の内容は、表1のとおりである。 ただし、データ参照時に FOR UPDATE 句を指定すると、対象行に専有ロックを掛け、トランザクション終了時に解放する。
ロックは行単位で掛ける。 共有ロックを掛けている間は、他のトランザクションからの対象行の参照は可能であり、更新は共有ロックの解放待ちとなる。 専有ロックを掛けている間は、他のトランザクションからの対象行の参照、更新は専有ロックの解放待ちとなる。
索引を使わずに、表探索で全ての行に順次アクセスする場合、検索条件に合致するか否かにかかわらず全行をロック対象とする。 索引探索の場合、索引から読み込んだ行だけをロック対象とする。
2．トリガ
テーブルに対する変更操作 (挿入・更新・削除) を契機に、あらかじめ定義した処理を実行する。
(1) 実行タイミング (テーブルに対する変更操作の前又は後。 前者を BEFORE トリガ、後者を AFTER トリガという)を定義することができる。
(2) 列値による実行条件を定義することができる。
(3) トリガ内では、トリガを実行する契機となった変更操作を行う前と後の行を参照することができる。 挿入では操作後の行の内容を、更新では操作前と操作後の行の内容を削除では操作前の行の内容を参照することができる。 参照するには、操作前と操作後の行に対する相関名をそれぞれ定義し、相関名で列名を修飾する。
(4) BEFORE トリガの処理開始から終了までの同一トランザクション内では、全てのテーブルに対して変更操作を行うことはできない。
(5) トリガ内で例外を発生させることによって、契機となった変更操作をエラーとして終了することができる。
〔在庫管理システムのテーブル〕
在庫管理システムの主なテーブルのテーブル構造は、図1のとおりである。 索引は、主キーだけに定義している。
〔在庫管理業務の概要〕
(1) 組立工場では、複数の生産ラインが稼働し、それぞれ異なる製品を組み立てている。 各製品の組立てに必要な部品は倉庫に保管し、必要に応じて生産ラインに出庫する。
(2) 部品は、部品番号で識別する。 倉庫内に存在する在庫を、実在庫と呼ぶ。 このうち、出庫対象となったものを、引当済在庫と呼ぶ。
(3) 部品の発注の方式は定量発注である。 Y 社の定量発注では、部品ごとの実在庫数量から引当済数量を差し引いた値が、基準在庫数量を下回った都度、部品ごとに決められた部材メーカに対して、決められた数量 (補充ロットサイズ) を発注する。
(4) 出庫要求では、倉庫に対して部品の出庫を要求する。 “出庫要求” テーブル及び“出庫要求明細” テーブルに出庫要求の内容を登録し、“出庫要求” テーブルの処理状況に‘要求発生' を記録する。 出庫要求番号は、出庫要求の発生順に一意な連番である。組立てに必要な複数の部品を一つの出庫要求とし、1 トランザクションで処理する。 生産ラインごとに様々な組合せの部品を要求する。 また、部品の要求は生産ラインでの組立ての状況に応じて任意の契機で発生する。
(5) 在庫引当では、出庫要求に応じて、“出庫要求明細” テーブルに指定した部品番号の部品について出庫要求数量の出庫が可能かどうか判定し、出庫可能であれば"在庫” テーブルの引当済数量を更新する。 全ての部品の在庫引当が完了したら、“出庫要求” テーブルの処理状況を‘引当実施' に更新する。在庫引当できない部品が存在した場合は、在庫引当を破棄して処理状況を‘引当失敗”に更新し、部品が入庫されるのを待って改めて出庫要求する。
(6) 出庫では、在庫引当が完了した部品を倉庫から搬出する。 毎朝、“出庫要求”テーブルの処理状況が ‘引当実施' のものを対象に実施する。 それぞれの部品の出庫が完了したら、“在庫” テーブルの実在庫数量及び引当済数量を更新し、“出庫要求”テーブルの処理状況を‘出庫実施' に更新する。 出庫は出庫要求単位に1トランザクションで処理し、全ての部品をまとめて出庫する。
(7) 発注では、“発注” テーブルの処理状況に '要求発生” を記録し、“在庫” テーブルの発注済フラグをオンにする。
(8) 入庫では、部材メーカから納品される都度、“在庫” テーブルの実在庫数量を更新し、発注済フラグをオフにする。 また、“発注” テーブルの処理状況を‘入庫実施'に更新する。 納品された複数の部品をまとめて、1 トランザクションで処理する。
〔トリガでの在庫引当処理の設計〕
出庫要求に連動した在庫引当を実行させたいので、トリガを利用するように処理を見直すことにした。 トリガでの在庫引当処理を図2に示す。
なお、引当失敗の場合は、出庫要求側でロールバックを行った後、“出庫要求” テーブルの処理状況を‘引当失敗’に更新する。
〔トリガでの定量発注の設計〕
在庫引当時の定量発注のために、発注の具体的な処理はストアドプロシージャで用意し、トリガから呼び出すことにした。 ストアドプロシージャでは、“発注” テーブルと“在庫” テーブルに変更操作を行う。 トリガを定義する SQL を図 3 に示す。 さらに、図3の内容のレビューを行った。 レビューでの指摘内容と対策を表2に示す。
設問1：トリガでの在庫引当処理の設計について、(1)、(2)に答えよ。
（1）図2中の（ア）〜（カ）に入れる適切な字句を答えよ。
模範解答
ア：出庫要求明細
イ：挿入
ウ：部品番号
エ：在庫
オ：実在庫数量
カ：引当済数量
解説
解答の論理構成
- トリガ発火テーブルと操作の特定
- 【問題文】「出庫要求に応じて、“出庫要求明細” テーブルに指定した部品番号…」
- 出庫要求明細への新規行追加時に在庫引当判定を行う、と明記されています。したがって
・（ア）＝ “出庫要求明細”
・操作は行追加なので（イ）＝ “挿入”
- 判定キー列の特定
- 同じ引用文に「部品番号の部品について…判定」とあります。よって（ウ）＝ “部品番号”。
- 参照先テーブルと列の特定
- 【問題文】「出庫可能であれば"在庫” テーブルの引当済数量を更新する。」
- 在庫テーブルから必要数を照合するため、（エ）＝ “在庫”。
- 判定対象の列は実在庫数量と引当済数量です。【問題文】「在庫…実在庫数量及び引当済数量を更新し…」
- BEFORE 判定で在庫数量を取得するときに使うのは（オ）＝ “実在庫数量”、AFTER で更新するのは（カ）＝ “引当済数量”。
誤りやすいポイント
- “出庫要求”テーブルと混同してトリガ対象を誤る。引当は明細単位で行われる点に注意。
- UPDATE や DELETE と誤解しやすいが、行が追加される時点で在庫を拘束するため操作は挿入。
- 在庫テーブルのキーを「部品番号＋倉庫」などと深読みしすぎて別列を答える。実際のキー指定は部品番号のみ。
- 判定列を「基準在庫数量」と答えるミス。基準は比較対象であり、トリガ内で直接更新される列ではない。
FAQ
Q: なぜ BEFORE ではなく AFTER トリガにしないのですか？
A: 在庫不足時に「契機となった変更操作を…エラーとして終了」させる必要があるため、判定は変更前（BEFORE）に行います。AFTER では既に行が確定してしまいロールバックコストが高くなります。
A: 在庫不足時に「契機となった変更操作を…エラーとして終了」させる必要があるため、判定は変更前（BEFORE）に行います。AFTER では既に行が確定してしまいロールバックコストが高くなります。
Q: 実在庫数量だけでは判定できませんか？
A: 引当済数量を加味しないと、既に他要求で確保済みの数量を二重使用してしまいます。そのため「実在庫数量 − 引当済数量」で可用在庫を計算します。
A: 引当済数量を加味しないと、既に他要求で確保済みの数量を二重使用してしまいます。そのため「実在庫数量 − 引当済数量」で可用在庫を計算します。
Q: 行ロックによる競合は発生しませんか？
A: 本文仕様では「データ更新時に専有ロックを掛け、トランザクション終了時に解放する」とあるため、同一部品番号に対する同時引当はロック待ちとなり整合性が保たれます。
A: 本文仕様では「データ更新時に専有ロックを掛け、トランザクション終了時に解放する」とあるため、同一部品番号に対する同時引当はロック待ちとなり整合性が保たれます。
関連キーワード: トリガ、引当処理、行ロック、可用在庫、BEFOREトリガ
設問1：トリガでの在庫引当処理の設計について、(1)、(2)に答えよ。
（2）図2のトリガを実行するトランザクションの ISOLATION レベルについて、(a)、(b)に答えよ。
(a)READ COMMITTED で、複数の出庫要求が同時に発生した場合、トリガに起因して引当済数量が不正になることがある。 どのようにして問題が発生するか、80字以内で述べよ。
(b)READ COMMITTED で、(a)の問題を発生させないためには、BEFORE トリガで行を参照する際に、どのような対策を施す必要があるか、20 字以内で述べよ。
模範解答
(a)：BEFOREトリガを実行した後に、別のトランザクションが割り込んで先に実行されてしまうと，AFTERトリガで不当な引当判定に基づいて更新することになる。
(b)：FOR UPDATE 句を指定する。
解説
解答の論理構成
- 前提
表１の「READ COMMITTED」は「データ参照時に共有ロックを掛け、参照終了時に解放する。」 - 事件の流れ
a. 出庫要求Ｔ１が発生し、BEFORE
トリガが「在庫」行を参照（共有ロック取得）。
b. 参照終了 → 共有ロック解放。
c. 直後に出庫要求Ｔ２が同じ行をUPDATE
し「引当済数量」を増加させる（専有ロック取得）。
d. Ｔ２がコミットしロック解放。
e. Ｔ１のAFTER
トリガが、BEFORE
時点の在庫情報を使い「引当済数量」を再度加算。 - 結果
同じ在庫を重複して引き当て、在庫マイナスや過剰発注などの異常が発生。 - 防止策
「ただし、データ参照時に FOR UPDATE 句を指定すると、対象行に専有ロックを掛け、トランザクション終了時に解放する。」を利用し、BEFORE
トリガ内の在庫取得 SQL にFOR UPDATE
を付与し専有ロックを維持する。
誤りやすいポイント
- 「共有ロックなら更新はブロックされるから安全」と勘違いし、解放タイミングを見落とす。
AFTER
トリガ側をFOR UPDATE
にすれば良いと思い込み、BEFORE
～AFTER
間のギャップを解消できていない。- ISOLATION レベルを
REPEATABLE READ
に変更すれば済むと考えるが、要件によっては他処理の同時実行性能が落ちる。トリガ側での対策の方が局所的。
FAQ
Q:
A: 参照時の共有ロックは「トランザクション終了時に解放する」ため保持されますが、全システムをそのレベルにするとスループット低下が大きいので、行単位の
REPEATABLE READを選べばロックは保持されますか？
A: 参照時の共有ロックは「トランザクション終了時に解放する」ため保持されますが、全システムをそのレベルにするとスループット低下が大きいので、行単位の
FOR UPDATEが推奨です。
Q:
A: 「専有ロック」を取得するので他トランザクションの参照・更新とも待機します。しかし対象は在庫の該当行のみで影響範囲は限定的です。
FOR UPDATEを使うと読取専用処理も待たされませんか？
A: 「専有ロック」を取得するので他トランザクションの参照・更新とも待機します。しかし対象は在庫の該当行のみで影響範囲は限定的です。
Q:
A:
AFTERトリガだけに
FOR UPDATEを付けてもいいですか？
A:
BEFORE〜
AFTERの間で別トランザクションが介入できるため不十分です。必ず
BEFOREトリガ内で行をロックしてください。
関連キーワード: 排他制御、READ COMMITTED, FOR UPDATE, 共有ロック、専有ロック
設問2：トリガでの定量発注の設計について、(1)、(2)に答えよ。
（1）図 3 中の(a)〜(d)に入れる適切な字句を解答群の中から選び、記号で答えよ。
解答群
ア：AFTER
イ：BEFORE
ウ：FOR EACH ROW
エ：FOR EACH STATEMENT
オ：NEW
キ：WHEN
カ：OLD
ク：WHERE
模範解答
a：ア
b：オ
c：ウ
d：キ
解説
解答の論理構成
- タイミングの判定
【問題文】「“発注” テーブルと“在庫” テーブルに変更操作を行う」ため、まず「在庫」テーブルの更新が終わってから発注を掛ける必要があります。この流れは“変更操作の後に起動”＝「AFTER」に一致するため (a) は「ア：AFTER」。 - 参照する擬似表の選択
図3の 2 行目は「REFERENCING (b) ROW AS CHKROW」とあり、更新後の値を使って残量計算を行います。更新後を表す擬似表は【問題文】「(3) 挿入では操作後の行... 更新では操作前と操作後の行...」より「NEW」なので (b) は「オ：NEW」。 - トリガ対象の粒度
1 行ごとに在庫残量が変わり得るため、行単位で判定しなければ過不足が発生します。図3 の 1 行目「UPDATE OF 引当済数量 ON 在庫」と 6 行目「CALL PARTSORDER(CHKROW.部品番号);」からも行ごと処理であることが分かるので (c) は「ウ：FOR EACH ROW」。 - 条件節の構文
4 行目は「(d)(CHKROW.実在庫数量 - CHKROW.引当済数量 <= CHKROW.基準在庫数量)」と書式が続きます。トリガ定義で条件を付けるキーワードは SQL99 以降「WHEN」であり、WHERE ではありません。したがって (d) は「キ：WHEN」。
誤りやすいポイント
- BEFORE を選ぶと、在庫更新前に発注判定をしてしまい二重発注を招く。
- 「FOR EACH STATEMENT」を選ぶと、同一トランザクション内で複数行更新時に残量計算が誤る。
- OLD を選んでしまうと“更新前の値”で判定し、残量チェックが不正確になる。
- WHERE を使うと SQL 文全体の検索条件と混同し、トリガ条件節の構文エラーとなる。
FAQ
Q: UPDATE 文で複数行を一括更新したら発注はどう動きますか？
A: 「FOR EACH ROW」なので更新された各行に対してトリガが個別に実行されます。1 回の UPDATE 文でも行数分だけ PARTSORDER が呼び出されます。
A: 「FOR EACH ROW」なので更新された各行に対してトリガが個別に実行されます。1 回の UPDATE 文でも行数分だけ PARTSORDER が呼び出されます。
Q: AFTER トリガ内でさらに「在庫」テーブルを更新できますか？
A: AFTER であっても同一トランザクション内に再帰的 UPDATE を行うと複雑なロック待ちが発生します。本問題ではストアドプロシージャ側で「在庫」テーブルを更新しておらず、処理対象は “発注” テーブルと “在庫” テーブルの発注済フラグのみという想定です。
A: AFTER であっても同一トランザクション内に再帰的 UPDATE を行うと複雑なロック待ちが発生します。本問題ではストアドプロシージャ側で「在庫」テーブルを更新しておらず、処理対象は “発注” テーブルと “在庫” テーブルの発注済フラグのみという想定です。
Q: WHEN 句と REFERENCING 句の順序は固定ですか？
A: ANSI SQL の構文では「REFERENCING」の後に「FOR EACH …」「WHEN」などが続く決まりです。順序を入れ替えると構文エラーになります。
A: ANSI SQL の構文では「REFERENCING」の後に「FOR EACH …」「WHEN」などが続く決まりです。順序を入れ替えると構文エラーになります。
関連キーワード: トリガ、行レベルトリガ、擬似表、WHEN句、排他制御
設問2：トリガでの定量発注の設計について、(1)、(2)に答えよ。
（2）表2中の（あ）〜（え）に入れる適切な字句を答えよ。
模範解答
あ：出庫
い：在庫
う：引当済数量
え：発注済フラグ
解説
解答の論理構成
- トリガが誤って再発動するシナリオの把握
- 表２の指摘内容には「在庫引当以外に、（あ）でも “（い）” テーブルの（う）列が更新され、…発注が繰り返される」とあります。
- 「（あ）」を特定
- 【問題文】「(6) 出庫では…“在庫” テーブルの 実在庫数量 及び 引当済数量 を更新し…」
- 引当済数量を更新するもう一つの業務は出庫なので（あ）＝出庫。
- 「（い）」「（う）」を特定
- 在庫に関する列であるためテーブルは “在庫”、列は「引当済数量」。
- 発注済フラグを条件に追加する理由
- 【問題文】「(7) 発注では… “在庫” テーブルの 発注済フラグ をオンにする」
- 既に発注中の部品については重複発注を避けたいので、トリガ内判定に発注済フラグを含める（え）＝発注済フラグ。
誤りやすいポイント
- 「実在庫数量」が更新される場面と混同し、（う）に実在庫数量を入れてしまう。
- 発注済フラグの役割を見落とし、（え）に基準在庫数量など別列を書いてしまう。
- 出庫処理を「入庫」と誤読して（あ）を入庫にしてしまう。
FAQ
Q: 発注済フラグは ON/OFF どちらが「発注中」を示しますか？
A: 【問題文】「発注では… 発注済フラグをオンにする」ので、ON が発注中（未入庫）を示します。
A: 【問題文】「発注では… 発注済フラグをオンにする」ので、ON が発注中（未入庫）を示します。
Q: 引当済数量と実在庫数量を同時に更新する場面は？
A: 【問題文】「出庫では… 実在庫数量 及び 引当済数量 を更新」するため、出庫完了時に両方が動きます。
A: 【問題文】「出庫では… 実在庫数量 及び 引当済数量 を更新」するため、出庫完了時に両方が動きます。
関連キーワード: トリガ、排他制御、BEFORE/AFTER, ストアドプロシージャ、フラグ管理
設問3：デッドロックについて(1)、(2)に答えよ。
（1）出庫要求と入庫でデッドロックが発生することがある。対象のテーブル名を答えよ。
模範解答
在庫
解説
解答の論理構成
-
更新対象の確認
- 出庫業務の説明より
“それぞれの部品の出庫が完了したら、“在庫” テーブルの実在庫数量及び引当済数量を更新し…” - 入庫業務の説明より
“納品される都度、“在庫” テーブルの実在庫数量を更新し…”
したがって両業務とも “在庫” に対する UPDATE を行う。
- 出庫業務の説明より
-
ロックの性質
- 表１「データ更新時に専有ロックを掛け、トランザクション終了時に解放する」。
- 行単位ロックであり、索引を使って対象行だけをロックする。
-
デッドロック発生パターン
- 出庫トランザクション: 部品Ａ→部品Ｂの順で “在庫” を更新。
- 入庫トランザクション: 部品Ｂ→部品Ａの順で “在庫” を更新。
- 出庫は部品Ａで専有ロック取得後、部品Ｂで入庫のロック解放待ち。
- 入庫は部品Ｂで専有ロック取得後、部品Ａで出庫のロック解放待ち。
- 待ち合わせが循環しデッドロックが確定。
-
結果
デッドロックの原因テーブルは “在庫”。
誤りやすいポイント
- 出庫要求・入庫という業務名から “出庫要求明細” や “入庫” テーブルを答えてしまう。実際に競合するのは共通で更新される “在庫”。
- 「共有ロックなら参照は可能だから安全」と早合点し、専有ロック保持時間を見落とす。
- 部品番号が主キーなので「行が分かれているから衝突しない」と考えるが、同じ部品番号を取り扱うケースを想定しない。
FAQ
Q: 共有ロックだけならデッドロックになりますか？
A: 共有ロック同士は相互にブロックしないので、今回のような更新（専有ロック）が絡まなければデッドロックは起きにくいです。
A: 共有ロック同士は相互にブロックしないので、今回のような更新（専有ロック）が絡まなければデッドロックは起きにくいです。
Q: REPEATABLE READ にすれば解決しますか？
A: 表１によれば REPEATABLE READ でも更新時は専有ロックを「トランザクション終了時に解放」するため、ロック保持期間は変わらず根本的な解決にはなりません。
A: 表１によれば REPEATABLE READ でも更新時は専有ロックを「トランザクション終了時に解放」するため、ロック保持期間は変わらず根本的な解決にはなりません。
Q: ロック順序を統一すれば回避できますか？
A: はい。出庫・入庫ともに “在庫” の部品を同一順（例：部品番号昇順）で更新すれば循環待ちが発生せず、デッドロックを大幅に低減できます。
A: はい。出庫・入庫ともに “在庫” の部品を同一順（例：部品番号昇順）で更新すれば循環待ちが発生せず、デッドロックを大幅に低減できます。
関連キーワード: 排他制御、専有ロック、行ロック、トランザクション、デッドロック
設問3：デッドロックについて(1)、(2)に答えよ。
（2）(1)のデッドロックの回避策を二つ挙げ、それぞれ 50字以内で具体的に述べよ。
模範解答
①：出庫要求明細の登録と入庫を、それぞれ部品番号順に処理する。
②：入庫は複数の部品をまとめず、部品ごとに別トランザクションで処理する。
解説
解答の論理構成
-
デッドロック発生メカニズム
- 「ロックは行単位で掛ける。」
- 「出庫要求…全ての部品をまとめて出庫する。」
- 「入庫では…複数の部品をまとめて、１トランザクションで処理する。」
出庫系は部品Ａ→Ｂ→Ｃの順でロック、入庫系はＣ→Ｂ→Ａの順でロックすると、相互に待ち合う典型的デッドロックが生じます。
-
回避策①（模範解答①）
「出庫要求明細の登録と入庫を、それぞれ部品番号順に処理する。」
同一順序でロックを取得すれば待ち合わせは生じず、デッドロックが解消されます。 -
回避策②（模範解答②）
「入庫は複数の部品をまとめず、部品ごとに別トランザクションで処理する。」
更新対象行を１件に限定しトランザクションを短寿命化すれば、競合確率を大幅に低減できます。
誤りやすいポイント
- ロック方式を「ページ単位」と誤解し順序統一策の効果を過小評価する。
- 自動ロック解放による“デッドロックタイムアウト”があるから対策不要と判断する。
- 「READ COMMITTED だから共有ロックのみ」と誤読し、更新時の専有ロック保持を見落とす。
FAQ
Q: 順序統一はすべての並列処理で必要ですか？
A: 同一テーブルの複数行を同一トランザクションで更新し得る処理同士では必須です。単一行更新だけの処理には効果が薄く、過剰に適用するとスループットが下がります。
A: 同一テーブルの複数行を同一トランザクションで更新し得る処理同士では必須です。単一行更新だけの処理には効果が薄く、過剰に適用するとスループットが下がります。
Q: トランザクションを分割すると整合性が心配です。
A: 部品ごとの在庫更新は他部品と独立しており、業務的整合性は部品単位で保たれます。もし一括性が必要な場合はアプリ側で再確認を入れるなど補完策を取れば安全です。
A: 部品ごとの在庫更新は他部品と独立しており、業務的整合性は部品単位で保たれます。もし一括性が必要な場合はアプリ側で再確認を入れるなど補完策を取れば安全です。
Q: データベースのデッドロック検出機能に任せてもよいのでは？
A: 検出機能は最終手段です。業務トランザクションがロールバックされると在庫数が戻り、処理再試行のロジックが必要になるため、設計段階で発生確率を減らす方が運用負荷を抑えられます。
A: 検出機能は最終手段です。業務トランザクションがロールバックされると在庫数が戻り、処理再試行のロジックが必要になるため、設計段階で発生確率を減らす方が運用負荷を抑えられます。
関連キーワード: デッドロック、行ロック、トランザクション分割、ロック順序、排他制御