データベーススペシャリスト試験 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

LEFT OUTER JOIN

RIGHT OUTER JOIN

LEFT OUTER JOIN

RIGHT

出庫便番号

NULL

NOT NULL

SUM(S.出庫数量)

LEFT OUTER JOIN

NULL

SUM

NULL

COALESCE

模範解答

解説

解答の論理構成

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

誤りやすいポイント

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

FAQ

模範解答

実行状況：

解説

解答の論理構成

1. ロック動作の整理
   • READ COMMITTED … 「データ参照時に共有ロックを掛け，参照終了時に解放する。」

   • FOR UPDATE

     … 「FETCH された行に専有ロックが掛かる。」
2. AP1 の進行（t1→t7）
   ①

   SELECT … FOR UPDATE

   で在庫行に専有ロック
   ② 出庫対象在庫数量を加算
   ③ “出庫” を更新
   ④

   COMMIT

   でロック解放
3. AP2 の競合シナリオ
   • t2 で「①」を発行 → 専有ロック待ちで 待機
   • t7 で AP1 が

     COMMIT

     → ロック解放
   • t8 で AP2 の FETCH 完了（加算済みの在庫を取得）→ ②③④ を連続実行
4. 結果
   AP2 は AP1 の加算を認識できず 再度加算。倉庫内在庫数量は変わらないまま、
   出庫対象在庫数量 ＝ 以前の値＋hv3（AP1）＋hv3（AP2）
   したがって「出庫対象在庫数量が倉庫内在庫数量を超える。」

誤りやすいポイント

• 「専有ロックを掛ければ同時実行は起きない」と思い込み、

  COMMIT

  後の非再現読を見落とす。
• REPEATABLE READ と READ COMMITTED のロック解放タイミングを混同する。

• CLOSE

  した時点でロックが解放されると誤解する（実際はトランザクション終了時）。

FAQ

UPDATE … WHERE … AND 倉庫内在庫数量−出庫対象在庫数量 >= :hv3

模範解答

解説

解答の論理構成

1. 図３の該当部分を確認

   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

WHERE CURRENT OF

FOR UPDATE

COMMIT

CURRENT OF

CURRENT

UPDATE ... WHERE CURRENT OF <カーソル名>

OF 在庫カーソル

(f)

CURRENT

模範解答

解説

解答の論理構成

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

誤りやすいポイント

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

FAQ

模範解答

解説

解答の論理構成

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

誤りやすいポイント

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

FAQ