データベーススペシャリスト 2017年 午後1 問02

解答の論理構成

1. 出庫数量の集計列
   【問題文】では「SQL1は各部品の出庫年月日ごとの出庫数量を集計する。」とあります。したがって (a) には数量の合計を返す SUM(S.出庫数量) が入ります。
2. 結合種別の決定
   「SQL1 では、出庫が全くない部品も集計対象とする。」と指示されています。これは基準表（在庫）に存在するが従表（出庫）に行が存在しないケースも取り込む必要があるため、(b) には LEFT OUTER JOIN を置きます。
3. 集計単位の指定
   集計単位は「各部品の出庫年月日ごと」ですから、(c) の GROUP BY 句には B.部品番号,S.出庫年月日 を列挙します。
4. 倉庫間出庫の抽出条件
   SQL2について【問題文】には「倉庫間の出庫」を対象とするとあります。倉庫間出庫は便が割り振られるケースであり、便番号がNULLでない行が条件になります。従って (d) は 出庫便番号、(e) は NOT NULLです。

誤りやすいポイント

• 内部結合 INNER JOIN を選んでしまい、出庫が無い部品が欠落する。
• GROUP BY に B.倉庫コード を含めてしまうなど、要件外の列を入れて集計粒度を細かくしてしまう。
• (d) IS (e) を「出庫便番号 IS NULL」と誤記し、倉庫間ではなく工場向け出庫を抽出してしまう。
• SUM 句でエイリアスを付け忘れ、後続の帳票処理で列名が合致しなくなる。

FAQ

解答の論理構成

1. ①の役割
   【問題文】「データ参照時に共有ロックを掛け、トランザクション終了時に解放する。」
   → 図2の①は SELECT なので同一行に共有ロックが掛かり、トランザクション中保持され続けます。
2. ②の役割
   【問題文】「データ更新時に専有ロックを掛け、トランザクション終了時に解放する。」
   → 図2の② UPDATE では専有ロックへの昇格が必要です。
3. 複数APの同時実行
   • 先行AP：①で共有ロック取得 →②で専有ロック待ち
   • 後続AP：①で共有ロック取得（先行APの②がまだ実行前なので取得可）→②で専有ロック待ち
4. 循環待ちの成立
   先行APは後続APの共有ロック解放を、後続APは先行APの共有ロック解放を待つ状態となり、デッドロックが完成します。
5. したがって模範解答「“在庫”テーブルの同じ行に対して、先行するAPの①と②の間で、後続のAPの①が実行された場合」が成立します。

誤りやすいポイント

• SELECT はロックを掛けないと誤解し、「共有ロック」を忘れる。
• 「READ COMMITTED」なら共有ロックはすぐ解放されるが、設計は「REPEATABLE READ」である点を見落とす。
• ファイル分割でAPを並列にしているから同じ行には触れないと決めつけてしまう。
• ロックの粒度が「行単位」であることを意識せず、テーブル全体で考えてしまう。

FAQ

解答の導き方

1. 図2の処理は要約すると次のとおりです。特にチェックと更新の順序を確認します。
   「① SELECT 倉庫内在庫数量、出庫対象在庫数量 INTO :hv4, :hv5 FROM 在庫 WHERE 倉庫コード = :hv1 AND 部品番号 = :hv2」
   「hv4 − hv5とhv3を比較し、出庫が可能な場合だけ以降を実行する。」
   「② UPDATE 在庫 SET 出庫対象在庫数量 = 出庫対象在庫数量 + :hv3 WHERE 倉庫コード = :hv1 AND 部品番号 = :hv2」
   「③ UPDATE 出庫 SET 処理状況 = '引当実施' WHERE 出庫番号 = :hv0」
   「④ COMMIT」
2. RDBMS の排他制御（問題文の表）で、READ COMMITTED は「データ参照時に共有ロックを掛け、参照終了時に解放する。データ更新時に専有ロックを掛け、トランザクション終了時に解放する。」とあります。つまり SELECT（参照）は文終了で共有ロックを解放し、UPDATE（更新）はコミットまで専有ロックを保持します。
3. 図5のAP1の実行順はt1:①、t3:②、t5:③、t7:④ です。これを基に並列実行の干渉を考えます。
   • t1（AP1の ①）でAP1は現在の倉庫内在庫数量（hv4）と出庫対象在庫数量（hv5）を読み取り、文終了とともに共有ロックは解放されます（READ COMMITTEDの性質）。
   • したがって AP2 は t2 で同じ SELECT（AP2 の ①）を実行でき、同じ古い hv4, hv5 の値を取得して「hv4 − hv5 と hv3 の比較」を通過します。ここで両者は同値のチェックに合格して次に進む前提を持ちます。
   • t3（AP1 の ②）で AP1 が UPDATE を実行すると専有ロックを取得し、出庫対象在庫数量を増やします。この専有ロックは t7（COMMIT）まで解かれません。
   • t4（AP2 の ②）で AP2 が UPDATE を発行すると、AP1 の専有ロックのためにブロックされ、実際の更新は AP1 の COMMIT 後にしか実行できません。したがって AP2 の ②〜④ は実質的に t8 以降に行われます。
   • 結果としてAP1とAP2はそれぞれhv3を加算する更新を行い、両者の合算が元の倉庫内在庫数量を超えることがあり得ます。つまり出庫可否の判定は古い読み取り結果に基づいており、実際の更新は直列化されても「二重引当て（過剰引当て）」が発生します。
4. 以上より、図5の空欄にはAP2の ① をt2に、②・③・④ をt8に記入するのが正解です。

誤りやすいポイント

• 図2 の SELECT に FOR UPDATE があると誤解すること。図2 の SELECT には FOR UPDATE が無く、READ COMMITTED では文終了時に共有ロックが解放されます。図3 の注記にある「在庫カーソルに FOR UPDATE を指定した場合、FETCH された行に専有ロックが掛かる。」と区別して理解すること。
• UPDATE の式とチェックの参照対象を混同すること。チェックはローカル変数 hv4, hv5 に基づくが、更新は「出庫対象在庫数量 = 出庫対象在庫数量 + :hv3」として DB 上の現在値に加算するため、事前チェックと更新の間に他トランザクションが介在すると過剰引当てになる。
• READ COMMITTEDとREPEATABLE READの違いを逆に覚えること。READ COMMITTEDは参照終了時に共有ロックを解放し、REPEATABLE READはトランザクション終了時まで保持する点が鍵である。
• ブロックの発生位置の取り違え：SELECT はブロックされないが、UPDATE は専有ロックのためブロックされ得る。結果としてブロックされる SQL は UPDATE 側であることを押さえる。

FAQ

• アトミックな更新クエリを用いる：例えば 「UPDATE 在庫 SET 出庫対象在庫数量 = 出庫対象在庫数量 + :hv3 WHERE 倉庫コード = :hv1 AND 部品番号 = :hv2 AND 倉庫内在庫数量 - 出庫対象在庫数量 >= :hv3」 を実行し、更新件数が 0 のときは引当失敗とする。これでチェックと更新を一文で行えます。
• 楽観ロック（バージョン番号）を使う：UPDATE 時にバージョンを条件にして、失敗したら再試行する。
• トランザクション分離レベルを上げる（REPEATABLE READ等）か、図3のように明示的ロックを使う。

解答の論理構成

1. 図3の該当部分を確認
   UPDATE 在庫 SET 出庫対象在庫数量 = 出庫対象在庫数量 + :hv3 WHERE (f) OF 在庫カーソル
   ここで (f) は、直前に FETCH した行だけを更新対象とするための句です。
2. カーソル宣言の条件
   DECLARE 在庫カーソル CURSOR FOR SELECT ... FROM 在庫 ... FOR UPDATE
   問題文には「在庫カーソルに FOR UPDATE を指定した場合、FETCH された行に専有ロックが掛かる。」とあります。FOR UPDATE が付いたカーソルで直前にフェッチした行を特定する標準句は WHERE CURRENT OF です。
3. 標準SQLルール
   • WHERE CURRENT OF <cursor_name> は、そのカーソルで最後にフェッチした行のみを対象に UPDATE または DELETE を行う。
   • これにより主キーを再指定せずとも対象行を一意に特定でき、排他ロックも保持される。
4. 以上より (f) にはCURRENTを補完するのが正答となります。

誤りやすいポイント

• WHERE ROWID = :host_var のように行識別子で更新しようとすると、ロックを保持する保証がなく READ COMMITTED では競合を招く恐れがあります。
• CURRENT だけではなく WHERE CURRENT OF <カーソル名> が完全形であることを忘れ、句全体を記述してしまう。
• FOR UPDATE がないカーソルで WHERE CURRENT OF を使用できると誤解する（多くの RDBMS でエラー）。

FAQ

解答の論理構成

1. 出庫確定APの処理
   【問題文】図4 2-1, 2-2 「“在庫”テーブルの倉庫内在庫数量を更新」「“在庫”テーブルの出庫対象在庫数量を更新」
     → 同一行（キーは「倉庫コード、部品番号」）を更新する。
2. ロック仕様
   【問題文】表1 REPEATABLE READ「データ更新時に専有ロックを掛け、トランザクション終了時に解放する。」
     → 更新中の行は他トランザクションから参照も更新もできずロック待ちになる。
3. 並列化の影響
   出庫を範囲分割して並列実行しても、各プロセスが同じ「倉庫コード」「部品番号」の行を更新するケースが頻発。
   その結果 “在庫” 行の専有ロック解放を待つ ロックの解放待ち が多数発生し、スループットが頭打ちになる。
4. よって空欄には
   （ア）倉庫コード （イ）部品番号 （ウ）在庫 （エ）ロックの解放待ち
   が入る。

誤りやすいポイント

• 「範囲指定で出庫番号を分割したから競合しない」と思い込み、キー列の衝突を見落とす。
• 行ロックはREPEATABLE READ固有の問題だと誤解し、READ COMMITTEDなら解放待ちが起きないと判断する。
• 在庫テーブルに索引を追加すれば解決するという早合点（索引は探索効率でありロック待ちとは別問題）。

FAQ

解答の論理構成

1. 更新競合の原因
   出庫確定APは図4で「倉庫内在庫数量」「出庫対象在庫数量」を更新します。これらの更新条件は〈倉庫コード＝出庫元倉庫コード〉かつ〈部品番号〉です。よって同じ倉庫・部品を別プロセスが同時に扱えば、在庫行に対する専有ロック競合が起こります。
2. 適切な分割キー
   競合を無くすには「同じ在庫行を複数プロセスが更新しない」よう範囲分割します。したがってプロセス配分の主キーは
   • 出庫元倉庫コード（オ）
   • 部品番号（カ）
   の組合せとなります。
3. 索引の必要性
   問題文(1)のＲＤＢＭＳ仕様より「索引を使わずに…テーブルスキャン…全行がロック対象」と明記されています。複合索引を定義しない場合、各プロセスは自分の担当範囲を判定するために全行を読み、共有ロックであっても他プロセスが取得した専有ロックに衝突して待機します。
4. ボトルネックの連鎖
   ロック待ちは「他プロセスが更新（ケ）した行」を参照しようとした瞬間に発生します。これが新たなボトルネックになるため、複合索引の作成は必須です。
5. まとめ
   以上より、空欄は
   オ：出庫元倉庫コード
   カ：部品番号
   キ：出庫
   ク：テーブルスキャン
   ケ：更新 又は 専有ロック
   が正答となります。

誤りやすいポイント

• 出庫番号で分割しても在庫表を更新する条件列が変わらないことを見落とす。
• 「参照だけならロック競合しない」と思い込み、テーブルスキャン時の共有ロック増大を軽視する。
• 索引を作ってもロックそのものが無くなるわけではない事実を混同する。

FAQ