DBデータベーススペシャリスト試験午後Ⅰ

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

事例を読んで設問の答えを考えてから、解説のポイントとIPA公式の解答例・採点講評で確認しましょう。

問題

問2 トランザクションの排他制御に関する次の記述を読んで，設問1～3に答えよ。 Y社は，オフィスじゅう器メーカである。現在，在庫管理システムのアプリケーションプログラム（以下，APという）の改修を実施している。〔在庫管理システムのテーブル〕在庫管理システムの主なテーブル構造を，図1に示す。各テーブルには主索引が定義されている。図1 主なテーブル構造〔在庫管理業務の概要〕 (1) 各地の生産拠点には，組立工場と，これに隣接する倉庫がそれぞれ一つ配置されている。 (2) 倉庫からの部品の出庫には，倉庫から隣接する組立工場に出庫する場合と，倉庫から他の生産拠点の倉庫に出庫する場合がある。 (3) 倉庫は，倉庫コードで一意に識別され，組立工場は，工場コードで一意に識別される。生産拠点を識別するコードは存在しない。 (4) 定期便は，倉庫間で部品を配送する便であり，便番号で一意に識別される。 (5) 部品は，部品番号で一意に識別される。 (6) 部品の在庫は，倉庫と部品の組合せで，その数量をもつ。倉庫内に存在する在庫を，倉庫内在庫と呼ぶ。このうち，隣接する組立工場又は他の生産拠点の倉庫に向けて出庫対象となったものを，出庫対象在庫と呼ぶ。 (7) 出庫要求とは，倉庫に対して部品の出庫を要求することである。“出庫”テーブルに出庫要求の内容が登録され，処理状況に‘要求発生’が記録される。出庫番号は，出庫要求の発生順の一意な連番である。組立工場が出庫要求する場合，出庫先倉庫コード及び出庫便番号の値は NULL となり，出庫先工場コードが記録される。他の生産拠点の倉庫が出庫要求する場合，出庫先工場コードは NULL となり，出庫先倉庫コードが記録され，出庫便番号には該当する定期便の便番号が記録される。 (8) 在庫引当とは，出庫要求に応じて倉庫内の在庫を引き当てることである。在庫引当 AP は，毎日の業務中に定期的に実行され，その時点で登録されている出庫要求を処理する。指定された倉庫コード，部品番号，出庫数量の出庫が可能かどうかチェックし，出庫可能であれば出庫対象在庫数量を更新する。在庫引当が完了したら，処理状況は‘引当実施’に更新される。 (9) 出庫とは，出庫要求に従って，倉庫から部品を出すことである。出庫は多頻度で行われるので，出庫ごとに在庫は更新されず，出庫確定 AP でまとめて更新される。 (10) 出庫確定 AP は，毎日の業務終了時に実行される。“出庫”テーブルの処理状況が‘引当実施’のものを対象に，倉庫内在庫数量及び出庫対象在庫数量が更新され，処理が完了したら，処理状況は‘出庫実施’に更新される。 (11) 入庫とは，他の生産拠点の倉庫で出庫された部品を倉庫に入れることである。入庫による在庫の更新は，在庫引当 AP 及び出庫確定 AP と同時に実行されることはない。〔RDBMS の排他制御〕 (1) 在庫管理システムの RDBMS で選択できるトランザクションの ISOLATION レベルとその排他制御は，表1のとおりである。ロックは行単位で掛ける。共有ロックが掛かっている間，他のトランザクションからの対象行の参照は可能であり，更新は共有ロックの解放待ちとなる。専有ロックが掛かっている間，他のトランザクションからの対象行の参照，更新は専有ロックの解放待ちとなる。表1 トランザクションの ISOLATION レベルとその排他制御 (2) 索引を使わずに，テーブルスキャンで全ての行に順次アクセスする場合，検索条件に合致するか否かにかかわらず全行がロック対象となる。索引スキャンの場合，索引から読み込んだ行だけがロック対象となる。〔分析機能の追加〕適切な生産計画を立てるために，部品ごとに在庫数量，出庫数量の日別の推移状況を見たいという要望があり，そのための集計 AP を追加した。集計 AP で実行する SQL の一部を，表2に示す。SQL1 は各部品の出庫年月日ごとの出庫数量を集計する。また，SQL1 では，出庫が全くない部品も集計対象とする。SQL2 は，各部品の倉庫間の出庫について，出庫年月日，出庫元倉庫，出庫先倉庫ごとに出庫数量を集計する。表2 集計 AP で実行する SQL の構文（未完成）〔在庫引当 AP の改修〕在庫管理システムでは，トランザクションの ISOLATION レベルを REPEATABLE READ として設計，運用していた。システムの改修に当たり，在庫引当 AP のトランザクションの ISOLATION レベルを READ COMMITTED に変更することにした。ISOLATION レベルの変更で問題が発生しないように在庫引当 AP を改修した。改修前の在庫引当 AP は図2，改修後の在庫引当 AP は図3のとおりである。これらの AP の実行に先立って，“出庫”テーブルの処理状況が‘要求発生’の行を抽出し，出庫先倉庫ごとに分割したファイルを作成する。それぞれのファイルのレコードは，出庫番号順に記録されている。作成したファイルを入力として，在庫引当 AP を並列実行している。在庫引当 AP は，入力ファイルのレコードごとに繰り返し実行される。なお，図2，3中のホスト変数 hv0 は出庫番号，hv1 は出庫元倉庫コード，hv2 は部品番号，hv3 は出庫数量を表す。hv4 と hv5 は，検索結果を返す出力ホスト変数を表す。図2 REPEATABLE READ で実行していた改修前の在庫引当 AP 図3 READ COMMITTED で実行する改修後の在庫引当 AP（未完成）〔出庫確定 AP の改修〕出庫確定 AP の処理に掛かる時間を短縮するために，出庫確定 AP を並列に多重プロセスで実行するように変更することにした。“出庫”テーブルの出庫番号の値の範囲指定で各プロセスに均等に配分して，REPEATABLE READ で並列実行する。出庫確定 AP の概要は，図4のとおりである。図4 出庫確定 AP の概要設問1 〔分析機能の追加〕について，表2中の［a］～［e］に入れる適切な字句を答えよ。設問2 〔在庫引当 AP の改修〕について，(1)～(3)に答えよ。 (1) 図2の改修前の在庫引当 AP が，REPEATABLE READ で複数同時に実行されるとデッドロックが発生するおそれがある。どのような場合にデッドロックが発生するか，AP 間の SQL の実行状況を，図2中の丸数字を用いて60字以内で述べよ。 (2) 図2の改修前の在庫引当 AP が，READ COMMITTED で同じ倉庫の同じ部品に対して複数同時に実行されると，在庫数量が不正になるおそれがある。在庫数量が不正になる AP の実行状況を図5に示す。不正になるのは，AP2 の①～④の各 SQL が，t2，t4，t6，t8 のどの時間帯で実行された場合か，該当する時間帯に①～④を記入せよ。ここで，一つの時間帯に複数の SQL を実行できる。また，この状況が発生した場合の，在庫数量が不正とは具体的にどのような状態か，30字以内で述べよ。図5 在庫数量が不正になる AP の実行状況 (3) 図3中の［f］に入れる適切な字句を答えよ。設問3 〔出庫確定 AP の改修〕について，(1)，(2)に答えよ。 (1) 並列に実行するように変更したが，スループットはさほど向上しなかった。ボトルネックはどこにあるかを説明する次の記述について，［ア］～［エ］に入れる適切な字句を答えよ。複数の出庫を並列に処理することになるが，同じ［ア］と［イ］に対する出庫が複数存在するので，“［ウ］”テーブルの更新で［エ］が発生する。 (2) (1)のボトルネックを解消するためには出庫確定 AP をどのように変更する必要があるかを説明する次の記述について，［オ］～［ケ］に入れる適切な字句を答えよ。出庫番号ではなく，［オ］と［カ］の組合せの値の範囲指定で各プロセスに配分するように変更する。また，図4の1で，出庫番号の昇順ではなく，［オ］と［カ］の昇順に処理を行うように変更する。“［キ］”テーブルの“［オ］”列と“［カ］”列に，複数列索引を定義しておく。なお，この索引を定義していない場合，自プロセスの対象行かを判定するための参照が［ク］となるので，他プロセスが［ケ］した行を参照しようとしてロックの解放待ちとなり，別のボトルネックが生じる。

図1 主なテーブル構造。倉庫，組立工場，定期便，部品，在庫，出庫の6テーブルの関係スキーマ。主キー（実線下線）と外部キー（破線下線）が示され，在庫は倉庫コードと部品番号の複合主キーを持つ。

表1 トランザクションの ISOLATION レベルとその排他制御。READ COMMITTEDは参照時に共有ロックを掛けて参照終了時に解放し，REPEATABLE READはトランザクション終了時に解放する。どちらも更新時は専有ロックを掛け，トランザクション終了時に解放する。

表2 集計 AP で実行する SQL の構文（未完成）。SQL1は在庫Bと出庫Sを空欄bで結合し，部品番号・出庫年月日ごとに空欄aを集計する（空欄cあり）。SQL2は出庫テーブルを空欄d IS 空欄eと期間で絞り込み，部品番号・出庫年月日・出庫元・出庫先倉庫コード別にSUM(出庫数量)を求める。

図2 REPEATABLE READ で実行していた改修前の在庫引当 AP。入力ファイルからhv0～hv3を設定する。①で在庫をSELECTし，hv4-hv5とhv3を比較して出庫可能な場合だけ，②で出庫対象在庫数量をUPDATE，③で出庫の処理状況を‘引当実施’にUPDATEし，④でCOMMITする。

図3 READ COMMITTED で実行する改修後の在庫引当 AP（未完成）。FOR UPDATE指定の在庫カーソルをDECLARE・OPEN・FETCHし，出庫可否を判定する。在庫をWHERE［f］OF 在庫カーソルでUPDATEし，CLOSE後に出庫の処理状況を更新してCOMMITする。専有ロックに関する注記付き。

図4 出庫確定 AP の概要。指定範囲の出庫番号の昇順に出庫行を参照し，‘引当実施’の場合だけ在庫の倉庫内在庫数量と出庫対象在庫数量を減算して処理状況を‘出庫実施’に更新する。範囲内の全行を処理したらCOMMITする。

図5 在庫数量が不正になる AP の実行状況。時間帯t1～t8の表。AP1（先行AP）はt1に①，t3に②，t5に③，t7に④を実行し，AP2の行は空欄になっている。AP1・AP2，t1～t8，①～④の意味を説明する注記付き。

解き方のヒントを見る

処理の時系列から並行性の影響を考える

複数の処理が同時に動く場面では、それぞれがどのデータをどの時点で読み書きするかを順序立てて並べることが検討の出発点になります。変更前後で何が変わり得るかを、処理の重なり方に注目して自分の言葉で整理してみましょう。

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

問われているのは、設定や実行形態を変えたときに、同時に動く処理どうしの間でデータの見え方や更新結果にどのような影響が生じ得るかを、根拠を持って説明できるかどうかという点だと考えられます。

視点

並行実行の課題を検討するときは、個々の処理を単独で見るのではなく、複数の処理の読み取りと書き込みを一本の時間軸の上に並べてみると整理しやすくなります。互いの操作がどの順で重なり得るかを書き出したうえで、その重なりが最終的なデータの値や集計の結果にどう反映されるかを、一つずつ自分で追ってみてください。

確認ポイント

各処理がデータを読む時点と書く時点を、時間の順に書き出せているか
二つ以上の処理の操作が重なる区間で、相手の変更が見えるかどうかを自分で確かめたか
その重なりが最終的な値や集計結果にどう表れるかを、具体的な例で追えたか

正解と解説を見る

解説

在庫管理システムを題材に、外結合とGROUP BYを使った集計SQL、REPEATABLE READで同じ行を対象に起きるデッドロック、READ COMMITTEDで起きる更新の不整合とFOR UPDATEカーソルによる対策、並列化した出庫確定APのロック待ちの原因と解消策を問う問題です。表1のロック保持期間と、〔RDBMSの排他制御〕(2)の「テーブルスキャンでは全行がロック対象になる」という規則を当てはめれば、各設問の答えを導けます。

問題の全体像

Y社の在庫管理システムのAP改修が題材です。在庫引当APは出庫要求ごとに、“在庫”行を参照して出庫できるかを判定し、出庫対象在庫数量に加算します。出庫確定APは業務の終了時に、倉庫内在庫数量と出庫対象在庫数量をまとめて減算します。出題趣旨では、同時に実行されるAP同士が影響し合って予期しない結果を生まないように、SQLの設計能力とトランザクション制御・排他制御の理解を問うとしています。採点講評によると、全体の正答率は低めでした。設問1（SQL）の正答率は高かった一方、設問2（同じ行を対象にしたデッドロックと不整合）の正答率は低く、設問3では(1)エと(2)クの正答率が低かったとされています。

設問1(a)

SQL1は「各部品の出庫年月日ごとの出庫数量を集計する」ものです。SELECT句には部品番号と出庫年月日に続けて集計値を書く必要があります。“出庫”表の別名はSなので、公式解答例ではSUM(S.出庫数量)としています。

設問1(b)

SQL1には「出庫が全くない部品も集計対象とする」という条件があります。“在庫”（B）を基準にして、対応する“出庫”の行がない場合もBの行を残す必要があるので、公式解答例はLEFT OUTER JOINです。出庫年月日の範囲条件がWHERE句ではなくON句に書かれているのも、外結合で残した行を消さないためです。

設問1(c)

集計の単位は部品と出庫年月日なので、GROUP BY句が必要です。外結合で出庫がない部品のS側の列はNULLになるため、部品番号は必ず値を持つB側の列を使います。公式解答例はGROUP BY B.部品番号、 S.出庫年月日です。“在庫”は倉庫と部品の組合せで1行なので、部品番号でまとめると倉庫をまたいで集計されます。

設問1(d)

SQL2は倉庫間の出庫だけを対象にします。業務の概要(7)によると、他の生産拠点の倉庫への出庫では出庫便番号に定期便の便番号が記録され、組立工場への出庫では出庫便番号がNULLになります。この値の有無で倉庫間の出庫を区別できるので、公式解答例では出庫便番号としています。採点講評では、dの列名を誤った解答が散見されたと指摘されています。

設問1(e)

倉庫間の出庫では出庫便番号に値が入るので、条件は「出庫便番号 IS NOT NULL」となり、公式解答例はNOT NULLです。採点講評では、dの列名とeのNULL/NOT NULLの組合せを誤った解答が散見されたとあります。どの列が、どちらの出庫で値を持つのかを状況記述で確認することが大切です。

設問2(1)

REPEATABLE READでは、①のSELECTで掛けた共有ロックをCOMMITまで保持します。入力ファイルは出庫先倉庫ごとに分かれているので、別々のAPが同じ出庫元倉庫・同じ部品の“在庫”行を扱うことがあります。先行するAPが①を実行してから②を実行するまでの間に、後続のAPが同じ行に①を実行すると、両方が共有ロックを持った状態になります。その後、それぞれが②のUPDATEで専有ロックを取ろうとすると、互いに相手の共有ロックの解放を待つことになり、デッドロックが発生します。公式解答例は「“在庫”テーブルの同じ行に対して、先行するAPの①と②の間で、後続のAPの①が実行された場合」です。60字以内の答案では、同じ行であること、先行APの①と②の間、後続APの①の実行、の3つの要素を押さえます。採点講評では、複数のテーブルや行を対象にしたデッドロックの一般論を書いただけの誤答が目立ったとされています。

設問2(2)実行状況

READ COMMITTEDでは、①の共有ロックは参照が終わると解放されます。AP1の②（t3）で掛かる専有ロックは、④（t7）のCOMMITまで保持されます。不整合になるのは、AP2の①がAP1の②より前の値を読んだ場合です。つまりAP2の①はt2で実行されます。AP2の②をt4やt6に置くと、AP1の専有ロックの解放を待つことになり、その時間帯には実行できません。そのため②③④はt8になります。また、t2の中で①②③④をすべて実行してCOMMITまで終える場合も、AP1はt1で読んだ古い値を基に②を実行するので不整合になります。公式解答例では「t2に①、t8に②③④」または「t2に①②③④」の2通りを示しています。

設問2(2)状態

AP1とAP2は、どちらも更新前の同じ値（倉庫内在庫数量−出庫対象在庫数量）を見て出庫可能と判断し、それぞれの出庫数量を出庫対象在庫数量に加算します。その結果、実際の在庫を超えて引き当てられることがあります。公式解答例は「出庫対象在庫数量が倉庫内在庫数量を超える。」です。30字以内の答案では、出庫対象在庫数量と倉庫内在庫数量の大小関係を明示します。

設問2(3)

図3ではFOR UPDATEを指定したカーソルでFETCHし、読み込んだ行に専有ロックを掛けたまま判定と更新をします。これで、ほかのAPが途中で同じ行を読み書きできなくなります。カーソルが現在位置している行を更新する構文はWHERE CURRENT OF カーソル名なので、公式解答例はCURRENTです。

設問3(1)(ア)

出庫番号の範囲で処理を割り振ると、同じ倉庫・同じ部品に対する出庫が複数のプロセスに分かれて入ります。“在庫”行は倉庫コードと部品番号で特定されるので、公式解答例ではアを倉庫コードとしています（イとは順不同）。

設問3(1)(イ)

アと組み合わせて“在庫”の行を特定するもう一方の主キー列が部品番号です。公式解答例はイを部品番号とし、アとは順不同としています。

設問3(1)(ウ)

図4の2-1と2-2では“在庫”テーブルを更新します。複数のプロセスが同じ“在庫”行を更新しようとして競合するので、公式解答例は在庫です。

設問3(1)(エ)

REPEATABLE READでは更新時の専有ロックをトランザクションの終了まで保持します。図4の3により、COMMITは指定範囲のすべての行を処理し終えてから実行されます。そのため、ある“在庫”行を更新したプロセスが範囲全体を処理し終えるまで、ほかのプロセスはその行の更新を待たされます。公式解答例は「ロックの解放待ち」です。採点講評では、エの正答率が低かったとされています。

設問3(2)(オ)

“在庫”行の競合をなくすには、同じ在庫行に対応する出庫が必ず同じプロセスに入るように割り振ればよいです。“出庫”テーブルで“在庫”の倉庫コードにあたる列は出庫元倉庫コードなので、公式解答例ではオを出庫元倉庫コードとしています（カとは順不同）。処理順もこの列の昇順にそろえます。

設問3(2)(カ)

“在庫”のもう一方の主キー列に対応する“出庫”の列が部品番号です。公式解答例はカを部品番号とし、オとは順不同です。出庫元倉庫コードと部品番号の組合せで範囲を指定すれば、各プロセスが更新する“在庫”行は重なりません。

設問3(2)(キ)

範囲指定と並べ替えの対象は“出庫”テーブルの出庫元倉庫コード列と部品番号列です。複数列索引はこのテーブルに定義するので、公式解答例は出庫です。

設問3(2)(ク)

〔RDBMSの排他制御〕(2)に、索引を使わずにテーブルスキャンすると、条件に合うかどうかに関係なく全行がロック対象になるとあります。索引がなければ、自プロセスの対象行かどうかを判定するための参照がテーブルスキャンになります。そのため、公式解答例はテーブルスキャンです。採点講評では、クの正答率が低かったとされています。

設問3(2)(ケ)

テーブルスキャンでは他プロセスの担当行も参照します。他プロセスが図4の2-3で処理状況を更新した“出庫”行には、COMMITまで専有ロックが掛かっています。その行を参照しようとすると、専有ロックの解放を待つことになります。公式解答例は「更新」または「専有ロック」です。採点講評にあるとおり、問題文で与えられた排他制御の仕組みを落ち着いて当てはめれば導けます。

学習のポイント

共有ロックと専有ロックの両立性、ISOLATIONレベルによるロック保持期間の違い、同じ行で起きるデッドロックの仕組み、WHERE CURRENT OFの使い方、並列処理ではロック対象が重ならないように処理を割り振ること、索引がない場合のテーブルスキャンで全行がロックされることを押さえます。

出典：独立行政法人情報処理推進機構（IPA）平成29年度春期データベーススペシャリスト試験午後Ⅰ 問2（公式資料：問題冊子（PDF）／解答例／採点講評）

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

問題