平成29年度午後Ⅰ試験問2：トランザクションの排他制御に関する問題

連載 [第2回] :

2018年2月19日(月)

はじめに

今回は、平成29年度午後Ⅰ試験問2のトランザクションの排他制御に関する問題の解き方について解説します。この問題で必要となる基礎知識はSQLです。SQLは第1回で解説したデータベース設計の関係スキーマや正規化と並び、午後Ⅰ試験の解答には欠かせない知識です。SQLの基本構文をしっかり身に付けた上で、問題に取り組みましょう。

平成29年度午後Ⅰ試験問2に挑戦！

それでは、早速平成29年度午後Ⅰ試験問2に挑戦しましょう。問題文はこちらからダウンロードしてください。また、解答用紙はこちらに用意しました。解答例はこちらをご覧ください。

設問1の解説

SQL1 (a)～(c)
まずは、オーソドックスな解き方から紹介します。〔分析機能の追加〕中に「SQL1は各部品の出庫年月日ごとの出庫数量を集計する」と記載されています。「○○ごとの××を集計する」と記載されている場合、そのSQLではGROUP BY句を用いた集計関数による集計であることが確定します。また、表2のSQL1を見ると、「SELECT B.部品番号, S.出庫年月日」と記載されています。以上のことから、(c)は「GROUP BY B.部品番号, S.出庫年月日」となります。

次に、「出庫数量を集計する」に注目します。図1の出庫テーブルの構造に列「出庫数量」が含まれていることから、(a)は「SUM(S.出庫数量)」となります。

最後に、(b)は図1のSQL1の構文より、FROM句の在庫テーブルに関する記述と出庫テーブルの記述の間にあることから、結合に関する記述が入ることがわかります。ここで、〔分析機能の追加〕中に「また，SQL1では，出庫が全くない部品も集計対象とする」と記載されています。つまり、出庫実績のない在庫データも集計対象とする必要があるため、(b)は左外部結合の「LEFT OUTER JOIN」となります。

ここまでがオーソドックスな解き方ですが、私なら(a)と(c)はとりあえず以下のように解答のあたりをつけます。そして、上記のとおり問題文を読み、解答が正しいことを確認します。

集計処理であることがわかれば、「GROUP BY句を用いたSELECT文の文法」から、(c)は「GROUP BY B.部品番号, S.出庫年月日」となる
(a)は集計関数が入ることから、「SUM(S.出庫数量)」となる

たったこれだけで(a)と(c)は終わりです。

SQL2 (d)～(e)
〔分析機能の追加〕中に「SQL2は，各部品の倉庫間の出庫について，出庫年月日，出庫元倉庫，出庫先倉庫ごとに出庫数量を集計する」と記載されています。また、〔在庫管理業務の概要〕(2)に「倉庫からの部品の出庫には，倉庫から隣接する組立工場に出庫する場合と，倉庫から他の生産拠点の倉庫に出庫する場合がある」と記載されています。このことから、SQL2は、倉庫から他の生産拠点の倉庫に対する出庫のみを対象としていることがわかります。さらに、〔在庫管理業務の概要〕(4)に「定期便は，倉庫間で部品を配送する便であり，便番号で一意に識別される」と記載されています。このことから、SQL2は定期便の出庫について集計すればよいことになります。

〔在庫管理業務の概要〕(7)に「組立工場が出庫要求する場合，出庫先倉庫コード及び出庫便番号の値はNULLとなり，出庫先工場コードが記録される」「他の生産拠点の倉庫が出庫要求する場合，出庫先工場コードはNULLとなり，出庫先倉庫コードが記録され，出庫便番号には該当する定期便の便番号が記録される」と記載されています。以上のことから、出庫テーブルを検索する際に、倉庫間の移動を抽出する条件は以下の通りとなります。

出庫先工場コードがNULLである
出庫先倉庫コードがNULLでない
出庫便番号がNULLでない

再度〔分析機能の追加〕の中の「SQL2は，各部品の倉庫間の出庫について，出庫年月日，出庫元倉庫，出庫先倉庫ごとに出庫数量を集計する」を見ると「倉庫間」との記述があります。他に「倉庫間」の記述が出てくるのは〔在庫管理業務の概要〕(4)「定期便は，倉庫間で部品を配送する便であり，便番号で一意に識別される」のみです。そして〔在庫管理業務の概要〕(7)に「出庫便番号には該当する定期便の便番号が記録される」と記載されていることから、最も適切な検索条件は3.に該当する「出庫便番号 IS NOT NULL」といえます。

1.を検索条件で記載すると「出庫先工場コード IS NULL」となります。解答として間違いではありませんが、3.の方が適切であることと、〔RDBMSの排他制御〕(2)の記述から索引を使用できない「IS NULL」条件を使うのは適切でないと考えます。

2.は、表2のSQL2の構文のGROUP BY句に出庫先倉庫コードが含まれています。この時点で、抽出対象の出庫先倉庫コードはNULLでないことが確定です。そのため、SQL2の検索条件に「出庫先工場コード IS NOT NULL」を指定することは適切とは言えません。

以上のことから、(d)は「出庫便番号」(e)は「NOT NULL」となります。

設問2の解説

(1)改修前の在庫引当APがREPEATABLE READで複数同時に実行するとデッドロックを発生させる場合
表1のREPEATABLE READにおける排他制御の内容を図2の改修前の在庫引当APに当てはめると、処理内容とロックの内容は以下の通りとなります。

① 在庫テーブルの検索条件に合致する行に対して共有ロックを掛ける
② 在庫テーブルの①と同一行に専有ロックを掛ける
③ 出庫テーブルの該当する出庫番号と合致する行に専有ロックを掛ける
④ ①～③で掛けたロックを解放する

デッドロックとは、複数のトランザクションがお互いにロックを掛け合って、処理を継続できなくなる状態のことです。そこで、デッドロックが発生する条件を考えます。

トランザクションXとYにおいて、以下の手順で処理を実行したとします。

手順1：トランザクションXにおいて①を実行する
　　　　→トランザクションXが在庫テーブルの該当行に共有ロックを掛ける
手順2：トランザクションYにおいて①を実行する
　　　　→トランザクションYが在庫テーブルの該当行に共有ロックを掛ける
手順3：トランザクションXにおいて②を実行する
　　　　→トランザクションXが在庫テーブルの該当行に専有ロックを掛けようとするが、手順2で掛けたトランザクションYの共有ロックの解除待ちとなる
手順4：トランザクションYにおいて②を実行する
　　　　→トランザクションYが在庫テーブルの該当行に専有ロックを掛けようとするが、手順1で掛けたトランザクションXの共有ロックの解除待ちとなる

これでデッドロックが発生することになります(図1)。

図1：デッドロック発生のイメージ

したがって、解答は「在庫テーブルの同じ行に対して、先行するAPの①と②の間で、後続のAPの①が実行された場合」となります。

(2)改修前の在庫引当APをREAD COMMITTEDで同じ倉庫の同じ部品に対して複数同時に実行すると在庫数量が不正になる場合
表1のREAD COMMITTEDにおける排他制御の内容を図2の改修前の在庫引当APに当てはめると、処理内容とロックの内容は以下の通りとなります。

① 在庫テーブルの出庫対象となる部品データが存在する行に対して共有ロックを掛け、検索後に共有ロックを解放する
→他のトランザクションから在庫テーブルの該当行への更新が可能となる
② ①で検索した部品データに専有ロックを掛け、出庫対象在庫数量を出庫数量だけ追加する
③ 出庫テーブルの該当する出庫番号と合致する行に専有ロックを掛け、処理状況を「引当実施」にする
④ ②～③の更新処理を確定させ、専有ロックを解放する

ただし、図2の4行目に「hv4 - hv5とhv3を比較し，出庫が可能な場合だけ以降を実行する」と記載されています。hv4(倉庫内在庫数量) - hv5(出庫対象在庫数量)とは、引当可能な在庫数量の値です。この引当可能な在庫数量とhv3(出庫数量)を比較して出庫が可能なのは「引当可能な在庫数量≧出庫数量」の場合のみです。

ところが、あるトランザクションが①を実行したあと、②を実行するまでの間に他のトランザクションが②～④を全て実行して①が検索した部品の出庫対象在庫数量が増え、あるトランザクションが②を実行するときには出庫数量が引当可能な在庫数量を超えてしまい、「出庫対象在庫数量が倉庫内在庫数量在庫数を超える状態」が生じてしまいます。これは在庫数量が不正な状態を意味しています。具体例として、倉庫コード=1、部品番号=100、倉庫内在庫数量=100、出庫対象在庫数量=0の在庫テーブルに対して、トランザクションXとYを以下の手順で処理を実行した場合を考えます。

手順1：トランザクションXにおいて①を実行する
　　　　→倉庫内在庫数量=100をhv4に代入、出庫対象在庫数量=0をhv5に代入
手順2：トランザクションYにおいて①を実行する
　　　　→倉庫内在庫数量=100をhv4に代入、出庫対象在庫数量=0をhv5に代入
手順3：トランザクションYにおいて②を実行する
　　　　→出庫数量(hv3)=80とすると、手順2よりhv4-hv5=100-0=100でhv3の値80より大きいため、手順②は実行可能。出庫対象在庫数量=0+80=80となる(在庫テーブルの該当行に専有ロック)
手順4：トランザクションYにおいて③を実行する
　　　　→出庫テーブルの該当行に占有ロックを掛ける(在庫テーブルには無関係)
手順5：トランザクションYにおいて④を実行する
　　　　→手順3より、出庫対象在庫数量=80を確定し、手順3と4で掛けた専有ロックを解放する
手順6：トランザクションXにおいて②を実行する
　　　　→出庫数量(hv3)=50とすると、手順1よりhv4-hv5=100-0=100でhv3の値50より大きいため、手順②は実行可能。また、手順5より出庫対象在庫数量=80+50=130となる(在庫テーブルの該当行に専有ロック)
手順7：トランザクションYにおいて③を実行する
　　　　→出庫テーブルの該当行に占有ロックを掛ける(在庫テーブルには無関係)
手順8：トランザクションYにおいて④を実行する
　　　　→手順6より、出庫対象在庫数量=130を確定し、手順3と4で掛けた専有ロックを解放する

これで、出庫対象在庫数量の値130>倉庫内在庫数量の値100となり、不正な状態となります(図2)。

図2：在庫数量が不正となる状態のイメージ

以上のことから、実行状況の正解は以下の通りとなります。

t2	t4	t6	t8
① ② ③ ④

同様の事象は以下の実行状況でも発生することから、以下も正解となります。

t2	t4	t6	t8
①			② ③ ④

また、在庫数量が不正となる状態の解答は、「出庫対象在庫数量が倉庫内在庫数量在庫数を超える状態」となります。

(3)(f)に入れる適切な語句
埋込みSQLの文法に関する知識が問われる問題です。カーソルが指し示している行に更新処理を実行する場合、条件句として「WHERE CURRENT OF カーソル名」と指定します。したがって、(f)は「CURRENT」となります。

設問3の解説

(1) 出庫確定APのスループット向上に関するボトルネック
〔在庫管理業務の概要〕(9)に「出庫は多頻度で行われるので，出庫ごとに在庫は更新されず，出庫確定APでまとめて更新される」と記載されています。つまり、隣接する工場や他の倉庫に対して、同一倉庫の同一部品が繰り返し出庫されます。

一方、図4の処理2-1と処理2-2において、出庫確定APのトランザクションは在庫テーブルの更新対象行に対して専有ロックを掛けます。これらのことから、出庫確定APのトランザクションを並列実行すると、出庫テーブルにおいて専有ロックの解放待ちが発生する可能性があります。在庫テーブルの主キーは(倉庫コード,部品番号)であることから、解答(ア)と(イ)は「倉庫コード」と「部品番号」、(ウ)は「在庫」(エ)は「ロックの解放待ち」となります(アとイは順不同)。

(2) (1)のボトルネックの解消
(1)のボトルネックを解消するために、在庫テーブルにおけるロックの解放待ちが発生しないようにします。そのためには、図4の処理1において列「出庫元倉庫コード」と列「部品番号」の組み合わせの値で範囲指定し、出庫確定APを並列実行することで在庫テーブルのロックの解放待ちを解消できます。したがって、解答(オ)と(カ)は「出庫元倉庫コード」と「部品番号」となります(オとカは順不同)。列「出庫元倉庫コード」の代わりに列「出庫先倉庫コード」を指定したくなるかもしれませんが、出庫先には倉庫と工場があるため、適切ではありません。

また、出庫テーブルへのアクセスへの考慮も必要です。〔出庫確定APの改修〕に「REPEATABLE READで並列実行する」と記載されています。また、表1に「REPEARTABLE READではデータ参照時に共有ロックを掛け，トランザクション終了時に解放する」と記載されています。さらに、〔RDBMSの排他制御〕(2)に「索引を使わずに，テーブルスキャンで全ての行に順次アクセスする場合，検索条件に合致するか否かにかかわらず全行がロック対象となる。索引スキャンの場合，索引から読み込んだ行だけがロック対象となる」と記載されています。そのため、出庫テーブルの列「出庫元倉庫コード」と列「部品番号」に複数列索引を定義しておかないと、図4の処理1において、出庫テーブルに対してテーブルスキャンを実行し、出庫テーブル全体に専有ロックを掛けます。その後、図4の処理2-3を実行する際に、更新対象行に専有ロックを掛けようとしますが、並列実行する他の出庫確定APが出庫テーブル全体に掛けた共有ロックの解放待ちが発生します。したがって、解答(キ)は「出庫」、解答(ク)は「テーブルスキャン」、解答(ケ)は「専有ロック」または「更新」となります。