平成31年度春期 データベーススペシャリスト 午後Ⅰ 問2

処理の流れを時系列で追って整理する

設問が問う場面で、どの処理がいつ、どのデータに対して何を行うのかを時系列に並べて確認する姿勢が大切です。記述や条件を一つずつ根拠と照らし合わせ、自分の推測ではなく与えられた情報から判断を組み立てましょう。

この問題で問われていること

確認すべき観点は一つ、複数の処理が同じ時間帯に動くとき、各処理が扱う対象と操作の順番を時間の流れに沿って整理できているかです。整理した流れをもとに、問われている内容がどの時点のどの状態に関係するかを見極めてください。

視点

データベースでは、ある操作をきっかけに別の処理が自動で動いたり、複数の利用者の処理が重なって進んだりします。こうした場面では、すぐに結論を選ばないでください。まず登場する処理を洗い出します。次に、それぞれの処理が始まってから終わるまでに触れるデータと操作を順に書き出します。こうすると状況を客観的につかみやすくなります。

確認ポイント

• 登場する処理を洗い出し、それぞれの開始から終了までを書き出したか
• 各時点で、どの処理がどのデータに何をしているかを対応付けたか
• 問われている内容が、整理した流れのどの時点に当たるかを確かめたか

解説

在庫管理システムを題材に、在庫引当と定量発注をトリガで実装する問題です。問われているのは、トリガの対象テーブル・実行タイミング・相関名・実行条件の定義、READ COMMITTEDでの同時実行による引当の不整合とFOR UPDATE句による対策、出庫でもトリガが発動して発注が繰り返される問題と発注済フラグによる対策、そして在庫テーブルの複数行ロックで起きるデッドロックとその回避策です。

問題の全体像

Y社は部品の入出庫と発注を管理する在庫管理システムを構築しています。出庫要求に連動して在庫引当を行うトリガ（図2）と、引当後の在庫が基準在庫数量以下になったら定量発注のストアドプロシージャを呼び出すトリガ（図3）を設計しています。RDBMSの仕様として、表1のISOLATIONレベル、FOR UPDATE句、BEFOREトリガ内では変更操作ができないこと、列値による実行条件を定義できることが示されています。出題趣旨では、トリガの影響で予期しない結果が起きないよう、変更操作の内容とトリガの定義条件を注意深く設計する能力を問うとしています。採点講評では、設問1は正答率が高く、設問2(1)は低め、設問3は同じテーブル内の複数行間で起きるデッドロックの発生要因に気付けない受験者が多かったと述べられています。

設問1(1)(ア)

在庫引当は出庫要求に連動して実行したい処理です。在庫管理業務の概要(5)によると、在庫引当は「出庫要求明細」テーブルに指定した部品番号と出庫要求数量をもとに判定します。部品ごとの要求内容は明細行として登録されるので、トリガを定義する対象は「出庫要求明細」テーブルです。公式解答例も「出庫要求明細」です。

設問1(1)(イ)

出庫要求では、「出庫要求明細」テーブルに内容を登録します（業務概要(4)）。そのため、トリガのきっかけとなる変更操作は行の「挿入」です。RDBMSの仕様(3)によると、挿入では操作後の行を参照できます。BEFOREトリガで挿入された行の値を使えることとも整合します。公式解答例は「挿入」です。

設問1(1)(ウ)

挿入された明細行から在庫を探すには、在庫テーブルの主キーと対応する列を検索条件にする必要があります。図1の「在庫」テーブルの主キーは部品番号で、明細行にも部品番号があります。したがって、公式解答例のとおり「部品番号」列の値を検索条件に指定します。

設問1(1)(エ)

引当が可能かどうかを判定するには、部品ごとの在庫状況が必要です。在庫状況は「在庫」テーブルが持っています。また、AFTERトリガで引当済数量を更新する先も「在庫」テーブルです（業務概要(5)）。公式解答例は「在庫」です。

設問1(1)(オ)

引当できる数量は、倉庫にある実在庫から、すでに出庫対象になった引当済在庫を差し引いた分です（業務概要(2)）。そのため、判定には「実在庫数量」と引当済数量の両方を参照する必要があります。オは公式解答例のとおり「実在庫数量」です。

設問1(1)(カ)

カは、判定のためにBEFOREトリガで参照する列であり、AFTERトリガで更新する列でもあります。業務概要(5)に「出庫可能であれば『在庫』テーブルの引当済数量を更新する」とあるので、公式解答例は「引当済数量」です。BEFOREトリガ内では変更操作ができない（仕様(4)）ため、判定はBEFORE、更新はAFTERと役割を分けている点も押さえておきましょう。

設問1(2)(a)

表1によると、READ COMMITTEDでは参照時の共有ロックが参照終了時に解放されます。そのため、あるトランザクションのBEFOREトリガが在庫を参照して引当可能と判定した後、AFTERトリガで更新するまでの間は、その行にロックがかかっていません。この間に別の出庫要求のトランザクションが割り込み、同じ部品の判定と引当済数量の更新を先に済ませることがあります。すると、元のトランザクションは古い在庫状況に基づく判定のまま引当済数量を更新し、実在庫を超える引当になり得ます。公式解答例は「BEFOREトリガ実行後に別のトランザクションが割り込んで先に実行されると、AFTERトリガで不当な引当判定に基づいて更新する」という趣旨です。80字以内では、①BEFORE実行後に別トランザクションが割り込む、②AFTERトリガが不当な判定に基づいて更新する、の二点を因果関係が分かるように書きます。

設問1(2)(b)

判定から更新までの間に他のトランザクションが割り込めないよう、参照した行のロックをトランザクションの終了まで保持する必要があります。仕様1によると、データ参照時にFOR UPDATE句を指定すると対象行に専有ロックがかかり、トランザクション終了時に解放されます。このため、公式解答例は「FOR UPDATE句を指定する。」です。20字以内の設問なので、この要素だけを簡潔に書けば足ります。

設問2(1)(a)

図3のトリガが呼び出すストアドプロシージャPARTSORDERは、「発注」テーブルと「在庫」テーブルに変更操作を行います。仕様(4)により、BEFOREトリガ内では変更操作ができません。また、判定には引当済数量を更新した後の状態が必要です。したがって、公式解答例のとおりア（AFTER）を選びます。

設問2(1)(b)

発注するかどうかは、引当済数量を更新した後の値で判定する必要があります。仕様(3)によると、更新では操作前と操作後の両方の行を参照できます。更新後の行に相関名CHKROWを付けるため、REFERENCINGにはNEW ROW AS CHKROWと指定します。公式解答例はオ（NEW）です。

設問2(1)(c)

定量発注は部品ごとに行うものです（業務概要(3)）。CHKROW.部品番号のように、更新された1行ずつの値を使って判定し、プロシージャを呼び出します。そのため、トリガは行単位で実行する必要があり、公式解答例はウ（FOR EACH ROW）です。文単位のFOR EACH STATEMENTでは、行ごとの相関名による判定と合いません。

設問2(1)(d)

dの後ろの条件式は「CHKROW.実在庫数量 − CHKROW.引当済数量 <= CHKROW.基準在庫数量」です。これは、引当済数量を更新した後、実在庫数量から引当済数量を差し引いた値が基準在庫数量以下になったかを調べ、該当するときにだけPARTSORDERを呼び出して定量発注を実行するための判定です。業務概要(3)の定量発注の仕様（差し引いた値が基準在庫数量を下回った都度、補充ロットサイズを発注）を、図3では「以下」の比較で表しています。仕様(2)に「列値による実行条件を定義することができる」とあり、トリガの実行条件を書くキーワードはWHENです。WHEREはSQL文で検索対象の行を絞り込むための句で、トリガを実行するかどうかの条件には使いません。公式解答例はキ（WHEN）です。採点講評でも、設問2(1)はトリガの仕様と状況記述からSQLの目的と必要な定義内容を読み取ってほしいと指摘されています。

設問2(2)(あ)

図3のトリガは「在庫」テーブルの引当済数量が更新されるたびに発動します。業務概要(6)によると、在庫引当のほかに「出庫」でも、部品の出庫完了時に実在庫数量と引当済数量が更新されます。出庫では両方の数量が同じだけ減るので差し引いた値は変わらず、条件を満たしたままトリガが発動して、再び発注してしまいます。公式解答例は「出庫」です。

設問2(2)(い)

指摘内容のいは、出庫で更新されるテーブルであり、図3のトリガが定義されているテーブルでもあります（ON 在庫）。対策欄の「在庫を確認する際に」参照するテーブルも同じです。公式解答例は「在庫」です。

設問2(2)(う)

図3のトリガはUPDATE OF 引当済数量で定義されているので、発動のきっかけは引当済数量列の更新です。出庫でもこの列が更新されるため、トリガが発動します。公式解答例は「引当済数量」です。

設問2(2)(え)

業務概要(7)によると、発注すると「在庫」テーブルの発注済フラグがオンになります。(8)によると、入庫時にオフに戻ります。トリガの判定に発注済フラグも加え、フラグがオンのとき（発注済みで入庫を待っている間）は発注しないようにすれば、発注の繰返しを防げます。公式解答例は「発注済フラグ」です。

設問3(1)

出庫要求では、1トランザクションで複数部品の明細を挿入します。そのたびにトリガが「在庫」テーブルの該当行を参照（FOR UPDATEで専有ロック）し、引当済数量を更新します。部品の組合せや順序は生産ラインごとにさまざまです。一方、入庫でも、納品された複数部品の「在庫」行を1トランザクションでまとめて更新します。両者が同じ部品の行を異なる順序でロックすると、互いに相手のロック解放を待つ状態になります。公式解答例は「在庫」です。採点講評によると、この設問は同じテーブル内の複数行間で起きるデッドロックを題材としていました。

設問3(2)(1)

デッドロックは、複数のトランザクションが複数行のロックを異なる順序で取ることで循環待ちになると起きます。ロックを取る順序をそろえれば循環待ちは起きません。公式解答例の一つ目は「出庫要求明細の登録と入庫を、それぞれ部品番号順に処理する」です。50字以内では、対象の処理（出庫要求明細の登録と入庫）と順序の基準（部品番号順）の両方を具体的に書きます。

設問3(2)(2)

もう一つの考え方は、一方のトランザクションが複数行のロックを同時に保持しないようにすることです。入庫を部品ごとに別トランザクションにすれば、入庫側が持つ在庫行のロックは常に1行だけです。そのため、出庫要求側との循環待ちが起きません。公式解答例の二つ目は「入庫は複数の部品をまとめず、部品ごとに別トランザクションで処理する」です。50字以内で、まとめ処理をやめて部品単位のトランザクションにすることを明確に書きます。

学習のポイント

トリガの構文要素（AFTER/BEFORE、NEW/OLD、FOR EACH ROW、WHEN）の意味を押さえます。あわせて、ISOLATIONレベルごとのロックの保持期間、トリガが想定外の更新でも発動する点、ロックを取る順序の違いから起きるデッドロックの仕組みと回避策を理解することが重点です。