令和7年度 秋期 データベーススペシャリスト試験 午後I 問題 問3 在庫管理システムのSQLと排他制御の改修

オフィスじゅう器メーカーの在庫管理システムのデータベース実装に関する次の記述を読んで，設問に答えよ。

オフィスじゅう器メーカーのY社は，RDBMSを用いた在庫管理システムを稼働させてから10年が経ち，システム更改を予定している。そこで，Kさんが改善点の検討を任された。

〔RDBMSの主な仕様〕

1. アクセス経路
   (1) アクセス経路は，RDBMSによって表探索又は索引探索に決められる。表探索では，索引を使わずに先頭ページから順に全行を読み込む。索引探索では，索引によって絞り込んでから表の行を読み込む。
   (2) 索引探索を選択するためには，WHERE句又はON句のANDだけで結ばれた一つ以上の等値比較の述語の対象列が，索引キーの全体又は先頭から連続した一つ以上の列に一致していなければならない。
   (3) 索引探索のとき，SELECT文で取得対象とする列と，探索条件で絞込みに利用する列が，索引キーに全て含まれている場合は，表の行を読み込まずに，索引へのアクセスだけで結果を返す。

2. 排他制御
   (1) 排他制御では行単位で共有ロック又は専有ロックを取得する。共有ロックを取得している間，他のトランザクションから対象行への共有ロックの取得は可能であり，専有ロックの取得は共有ロックの解放待ちとなる。専有ロックを取得している間，他のトランザクションから対象行への共有ロック及び専有ロックの取得は，専有ロックの解放待ちとなる。
   (2) アクセス経路に従って，探索した行をロックの対象とする。
   (3) SELECT文にFOR UPDATE句を指定すると，ISOLATIONレベルにかかわらず，対象行の専有ロックを取得し，トランザクション終了時に解放する。

3. 時刻印型の列
   (1) テーブル定義で，時刻印型の列（以下，TS列という）のDEFAULT句にCURRENT_TIMESTAMPを指定することで，行が挿入されたとき，TS列に現在時刻印を自動的に設定することができる。
   (2) 行が更新されたときにTS列をCURRENT_TIMESTAMPで更新するトリガーを定義することで，TS列を現在時刻印で自動的に更新することができる。

〔在庫管理システムの概要〕

1. テーブル構造
   主なテーブルのテーブル構造を図1に示す。主キーには主索引が定義されている。

2. 業務の概要
   (1) 各地の生産拠点には，組立工場と，これに隣接する倉庫がある。
   (2) 倉庫からの部品の出庫には，倉庫から隣接する組立工場に出庫する場合と，倉庫から他の生産拠点の倉庫に出庫する場合がある。
   (3) 倉庫は，倉庫コードで識別する。部品は，部品番号で識別する。
   (4) 部品の在庫は，倉庫と部品の組合せで管理する。倉庫内に存在する在庫の数量を在庫数量と呼ぶ。このうち，出庫対象として引き当てた数量を，引当済在庫数量と呼ぶ。また，在庫数量から引当済在庫数量を引いた数量を，出庫可能在庫数量と呼ぶ。
   (5) 部品の出庫は，出庫要求，在庫引当，出庫，在庫反映の流れで，それぞれのアプリケーションプログラム（以下，APという）を用いて処理し，“出庫”テーブルに処理状況を記録する。

• 出庫要求とは，部品の出庫要求を受け付けて，“出庫”テーブルに行を追加する処理である。処理状況を‘要求発生’にする。
• 在庫引当とは，出庫要求に応じて倉庫内の在庫を引き当てる処理である。指定された倉庫コード，部品番号，出庫数量の出庫が可能かどうかチェックし，出庫が可能であれば“在庫”テーブルの引当済在庫数量を更新し，処理状況を‘引当実施’にする。
• 出庫とは，倉庫から在庫の引当ができた部品を出す処理である。処理状況を‘出庫済’にする。
• 在庫反映とは，処理状況が‘出庫済’の全ての出庫を対象に，“在庫”テーブルの在庫数量及び引当済在庫数量から引き落として，処理状況を‘在庫反映済’にする一括処理である。“在庫反映”APは，毎日の業務終了時に実行する。
  (6) 入庫とは，部品メーカーから納品された，又は他の生産拠点の倉庫から出庫された部品を倉庫に入れることである。運ばれてきた部品を倉庫内の集積所に蓄積し，当日の入庫予定分がそろったら順に入庫を処理し，在庫を更新する。入庫による在庫の更新は，在庫反映と同時に実施しない。

〔在庫履歴分析手段の見直し〕

日別の在庫数量を集計するAPを用いて，在庫数量の日別の推移状況を分析している。このAPでは，“在庫”，“出庫”及び“入庫”テーブルを参照して，在庫数量を集計しており，データ量の増加に伴って処理時間が長くなっていた。この対策として，図2に示す“在庫履歴”テーブルを作成して，在庫数量の推移を蓄積していくことにした。

Kさんは，“在庫”，“出庫”及び“入庫”テーブルからこれまでの在庫履歴を作成する方法を検討した。
全ての倉庫の全ての部品について，在庫履歴を作成する初日時点の在庫数量の行をあらかじめ作成しておき，在庫履歴を蓄積する年月日分繰り返し実行するSQL1を設計した。
また，区間数7の中央移動平均のグラフを作成したいとの要望があった。区間数7の中央移動平均は，ある年月日とその前後3日間の計7日間の平均値である。計7日間のデータがそろわない期間は対象外である。Kさんは，作成した在庫履歴のデータから区間数7の中央移動平均用データを加工するSQL2を設計した。

設計したSQL文を，表1に示す。

〔トランザクションの排他制御の見直し〕

既存システムの実行ログを分析すると，複数の“在庫引当”APが，“在庫”テーブルの同じ行に対して同時実行されたときに，デッドロックによってAPのリトライが発生していることが判明した。“在庫引当”APの処理の流れとSQL文を図3に示す。
ISOLATIONレベルはREPEATABLE READで実行している。

Kさんはデッドロックの原因を調査し，上司であるL氏と対応について議論した。

L氏：デッドロックの原因は何でしょうか。
Kさん：ある“在庫引当”APが j で共有ロックを取得した後， k で専有ロックを取得し直す前に，別の“在庫引当”APが j で共有ロックを取得してしまい，互いに専有ロックを取得しようとしてロックの解放待ちになっていました。
L氏：どのような改善案がありますか。
Kさん：“在庫”テーブルの同じ行に対して，“在庫引当”APが同時実行されないように最終更新TS列で制御する案があります。“在庫引当”APを次のように変更します。①のSELECT文で l するように変更し，①の後にCOMMIT文を追加します。②のUPDATE文のWHERE句に， m ことを条件として追加します。②の後に，②のUPDATE文で該当する行がなかったら， n に戻る制御を追加します。
L氏：その案ではAPの大幅な変更が必要になります。変更を最小限にとどめる方法はありますか。

Kさん：ISOLATIONレベルをREAD COMMITTEDに変更して，jの共有ロックがすぐに解放されるようにする案もあります。

L氏　：単純にREAD COMMITTEDに変更すると，引当済在庫数量が不正になってしまうおそれがあります。同じ倉庫の同じ部品に対して，“在庫引当”APが複数同時に実行されるとき，それぞれの要求する出庫数量の合計が，oしていると，引当済在庫数量が不正になってしまいます。ISOLATIONレベルを変更しない方法はありますか。

Kさん：①のSELECT文に，pする案があります。

〔索引の見直し〕

毎日の業務終了時に実行される“在庫反映”APの処理時間の短縮要望を受け，Kさんが対策を検討した。その結果，出庫倉庫コードと部品番号の組合せの値の範囲で分割し，分割した値の範囲それぞれを各プロセスに配分して並列実行するように変更することにした。変更後の“在庫反映”APの処理の流れを図4に示す。ISOLATIONレベルはREPEATABLE READで実行する。

これを確認したL氏は，索引の見直しを指示した。

L氏　：“q”テーブルのr列とs列の組合せをこの順で索引キーとする複数列索引を定義しておく必要があります。この索引を定義していないと，自プロセスの対象行かどうかを判定するための参照が表探索となります。その場合，他APがtした行を参照しようとしてuが生じた結果，処理時間が長くなるおそれがあります。デッドロックが発生しないかの検証も必要です。

Kさん：処理要否の判定も，表の行の読込みを不要にして性能を向上させるために，“q”テーブルのv列もこの複数列索引の索引キーに加えてもよいでしょうか。
L氏：そのメリットと，そうすることで“q”テーブルを更新するAPが，①索引の更新頻度の増加によって性能が低下するデメリットとのトレードオフを検証してください。