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

キーワードと論点整理

解答の論理的説明

SQL1 のポイント

1. (a) 出庫数量の集計
   「各部品の出庫年月日ごとの出庫数量を集計する」 → SUM(S.出庫数量)
2. (b) 結合の種類
   「出庫が全くない部品も集計対象とする。」 → LEFT OUTER JOIN
3. (c) 集計後のグルーピング
   GROUP BY は「B.部品番号,S.出庫年月日」でグループ化する必要があります。

SELECT
  B.部品番号,
  S.出庫年月日,
  SUM(S.出庫数量)                 -- (a)
FROM
  在庫 AS B
  LEFT OUTER JOIN                -- (b)
  出庫 AS S
    ON B.倉庫コード = S.出庫元倉庫コード
   AND B.部品番号   = S.部品番号
   AND S.出庫年月日 >= '2016-04-01'
   AND S.出庫年月日 <  '2017-04-01'
GROUP BY                          -- (c)
  B.部品番号,
  S.出庫年月日;

SQL2 のポイント

「倉庫から他の生産拠点の倉庫に出庫する場合は…出庫便番号には該当する定期便の便番号が記録される。」
「組立工場が出庫要求する場合…出庫便番号の値は NULL となり…」

4. (d) 対象カラム → 出庫便番号
5. (e) NULL 判定 → NOT NULL

SELECT
  部品番号,
  出庫年月日,
  出庫元倉庫コード,
  出庫先倉庫コード,
  SUM(出庫数量)
FROM
  出庫
WHERE
  出庫便番号 IS NOT NULL      -- (d),(e)
  AND 出庫年月日 >= '2016-04-01'
  AND 出庫年月日 <  '2017-04-01'
GROUP BY
  部品番号,
  出庫年月日,
  出庫元倉庫コード,
  出庫先倉庫コード;

受験者が誤りやすいポイント

• INNER JOIN と LEFT OUTER JOIN の混同
  出庫がない部品を含めたい場合、INNER JOIN では行が落ちてしまうので必ず LEFT OUTER JOIN を用いる点に注意。
• WHERE によるフィルタリング位置
  LEFT OUTER JOIN 後に WHERE 句で結合先テーブルのカラムに対して条件を付けると、NULL の行を排除してしまい、LEFT JOIN の意味が損なわれる場合がある。
  → 本問 SQL1 では、日付条件を ON 句内に置くことで、NULL 行を残したままフィルタリングしている。
• NULL 判定の書き方
  IS NULL／IS NOT NULL は比較演算子 = や <> ではなく専用の構文を用いる。

試験対策として覚えておくべきポイント

1. SQL の結合で「左側のテーブルを必ず残す」には LEFT OUTER JOIN
2. 集計関数を使う場合、SUM(...) と GROUP BY の組み合わせ
3. NULL 判定は IS NULL／IS NOT NULL
4. JOIN の ON 節と WHERE 節の使い分け：フィルタリングのタイミングが異なる
5. 検索条件に日付範囲を指定するときは、>= '開始日' AND < '終了日' のパターンが多い

キーワード・論点整理

• デッドロック
• トランザクションの ISOLATION レベル：REPEATABLE READ
• 図2 中の丸数字①（SELECT 文での共有ロック取得）と②（UPDATE 文での専有ロック取得）
• 同一行に対するロック取得の順序競合

解答の論理的説明

1. AP1 が丸数字①の SELECT … FROM 在庫 を実行
   → 対象行に共有ロックを取得
2. AP2 が丸数字①の SELECT … FROM 在庫 を実行
   → 対象行に共有ロックを取得
3. AP1 が丸数字②の UPDATE 在庫 … を実行しようとし、
   → 共有ロックから専有ロックへのアップグレードを要求し待機
4. AP2 も丸数字②の UPDATE 在庫 … を実行しようとし、
   → 同じく待機

注意すべきポイント

• REPEATABLE READ では SELECT 時の共有ロックが トランザクション終了時 まで保持される点を押さえる。
  • READ COMMITTED では共有ロックは 参照終了時 に解放されるため、本事象は発生しない。
• ロックの「共有(＝読取り)」と「専有(＝更新)」の性質を混同しない。
• 同一行に対して複数トランザクションが共有ロックを取得し、かつその後でそれぞれ専有ロックを要求するとデッドロックになる。

覚えておきたい知識

• トランザクション分離レベルによるロックの取得・解放タイミング

  分離レベル	SELECT（参照）時のロック	UPDATE（更新）時のロック
  READ COMMITTED	共有ロック／参照終了時解放	専有ロック／トランザクション終了時解放
  REPEATABLE READ	共有ロック／トランザクション終了時解放	専有ロック／トランザクション終了時解放

• デッドロック回避策の例
  • 同一トランザクション内でロック順序を統一する
  • 悲観的ロックから楽観的ロックへの切替検討
  • ロックタイムアウトやデッドロック検出機能の活用

1. キーワード・論点の整理

• ISOLATION レベル
  • READ COMMITTED：
    「データ参照時に共有ロックを掛け，参照終了時に解放する。データ更新時に専有ロックを掛け，トランザクション終了時に解放する。」
  • REPEATABLE READ：
    「データ参照時に共有ロックを掛け，トランザクション終了時に解放する。データ更新時に専有ロックを掛け，トランザクション終了時に解放する。」
• SELECT と共有ロックの保持期間
  • READ COMMITTED では SELECT ステートメントが終わると共有ロックを解放 → 他トランザクションの UPDATE を間に挟める
  • REPEATABLE READ ではトランザクション終了まで共有ロックを保持 → 後続の SELECT は更新済みデータを見る
• Lost Update（ロスト・アップデート）
  ‥複数トランザクションが同じ行を同じ値を基に更新し，後からコミットしたほうが前の更新を上書きしてしまう現象
• 図2（改修前在庫引当 AP）の処理
  ① SELECT 倉庫内在庫数量, 出庫対象在庫数量 INTO :hv4, :hv5
  ② UPDATE 在庫 SET 出庫対象在庫数量 = 出庫対象在庫数量 + :hv3
  ③ UPDATE 出庫 SET 処理状況 = ’引当実施’
  ④ COMMIT

2. なぜその実行順序になるのか

1. READ COMMITTED の場合、①の SELECT が終了すると共有ロックが解放されます。
2. 他のプロセス（AP1）が①→②→③→④を完了してコミットしても、その間に別プロセス（AP2）の①が先に実行できてしまいます。
3. AP2 は古い :hv4（倉庫内在庫数量） と :hv5（出庫対象在庫数量） を使って「在庫引当可能」と判断し、後で②③④を実行すると Lost Update が発生します。
4. 結果として「出庫対象在庫数量」が不正に加算され（または下回り）、最終的に「出庫対象在庫数量が倉庫内在庫数量を超える」 という整合性違反が発生します。

実行状況（AP2 の SQL が問題となるタイミング）

• t2 で ①（SELECT） を実行（共有ロックはステートメント終了時に解放）
• AP1 が t4, t6 に②③④を完了・コミット
• t8 で AP2 の ②③④（UPDATE→UPDATE→COMMIT）を実行 → Lost Update

3. 受験者が誤りやすいポイント

• トランザクション分離レベルの共有ロック期間
  「SELECT を実行すると必ず次の UPDATE をブロックする」と思い込むと誤解します。
• FOR UPDATE の有無
  図3 改修後では SELECT … FOR UPDATE によって行ロックを保持するため、READ COMMITTED でも競合を防げます。
• 索引スキャン vs テーブルスキャン
  索引スキャンなら対象行のみロック、テーブルスキャンでは全行ロックになる点を区別していないことがある。

4. 試験対策として覚えておくべきポイント

解答のキーワードと論点整理

• キーワード：CURRENT OF
• 論点：
  1. 図3で「DECLARE 在庫カーソル CURSOR … FOR UPDATE」とし、カーソル取得行を専有ロックする点
  2. 更新対象の行を特定するためにSQL標準で用いられる句が「WHERE CURRENT OF カーソル名」である点

なぜ「CURRENT」が正解なのか

…  
UPDATE 在庫 SET 出庫対象在庫数量 = 出庫対象在庫数量 + :hv3  
    WHERE □f OF 在庫カーソル  
…  

1. 図3では、最初に

   DECLARE 在庫カーソル CURSOR
   FOR SELECT … FROM 在庫 … FOR UPDATE
   OPEN 在庫カーソル
   FETCH 在庫カーソル INTO :hv4, :hv5

   とあり、FOR UPDATE 指定でカーソルが取得する行に専有ロックが掛かります。
2. 取得済みの行に対して更新を行うには、SQL標準で
   「WHERE CURRENT OF カーソル名」
   という文法を用い、カーソルが現在参照している行を指定します。
3. したがって、□f に入るべき字句は CURRENT です。
   完成するSQLは次のとおりです。

   UPDATE 在庫
     SET 出庫対象在庫数量 = 出庫対象在庫数量 + :hv3
     WHERE CURRENT OF 在庫カーソル
   

受験者が誤りやすいポイント

• 「CURSOR」や「FETCH」と混同してしまうと、WHERE CURSOR OF のような誤答につながります。
• 「WHERE CURRENT」だけでは WHERE CURRENT OF … という決まり書式を満たしません。

試験対策として覚えておくべきポイント

1. カーソルを使った更新
   • DECLARE カーソル名 CURSOR FOR SELECT … FOR UPDATE
   • OPEN → FETCH → UPDATE … WHERE CURRENT OF カーソル名 → CLOSE
   • 最後に COMMIT でトランザクション終了
2. FOR UPDATE 指定
   • カーソル取得時に対象行に専有ロックを掛ける
   • 取得行に対して後から WHERE CURRENT OF で安全に更新
3. SQL標準の句の正確な使い方
   • WHERE CURRENT OF はセットで使う
   • 他のキーワード（POSITION、CURSORなど）は更新句には用いない

模範解答のキーワードと論点整理

解答の論理的説明

1. 問題文が指すボトルネックの説明

   「複数の出庫を並列に処理することになるが, 同じ（ア）と（イ）に対する出庫が複数存在するので, “（ウ）”テーブルの更新で（エ）が発生する。」

2. なぜ「倉庫コード」「部品番号」か
   • 在庫テーブルは「倉庫コード, 部品番号」を主キーとして在庫行を一意に管理します（問題文「在庫テーブルの主キー」）。
   • 並列プロセスは出庫単位で同じ倉庫・同じ部品を更新する可能性があるため、同一行に対して複数の専有ロック要求が発生します。
3. なぜ「在庫」テーブルか
   • 図４の処理手順 2-1, 2-2 にあるように、各出庫プロセスは

     「“在庫”テーブルの倉庫内在庫数量を更新（出庫数量分を減算）」
     「“在庫”テーブルの出庫対象在庫数量を更新（出庫数量分を減算）」
     を行います。

   • この更新時に行単位の専有ロックが掛かります（問題文「ロックは行単位で掛ける。専有ロックが掛かっている間…更新は専有ロックの解放待ちとなる。」）。
4. なぜ「ロックの解放待ち」か
   • 同一行に対して複数のトランザクションが同時に更新を試みると、先に取得した専有ロックが解放されるまで後続は待機します。
   • 結果として、更新処理間でロックのコンテンションが発生し、スループットが頭打ちになります。

受験者が誤りやすいポイント

1. 出庫テーブルを選んでしまう
   • 出庫確定APは「出庫」テーブルを更新しますが，出庫番号の範囲を分割して並列処理しているため，出庫テーブル上ではプロセス間で競合しません。
   • 真の競合は，複数プロセスが同じ在庫行を更新する「在庫」テーブルで起きます。
2. テーブルロックと行ロックの違い
   • 問題文では「ロックは行単位で掛ける」と明示されています。テーブルロックではなく行ロックであることを押さえておきましょう。
3. ISOLATIONレベルの影響
   • ISOLATIONレベルが REPEATABLE READ でも READ COMMITTED でも，更新時の専有ロック待機は発生します。ロックの持続期間に違いはありますが，競合自体は同様に起きます。

試験対策として押さえておくべきポイント

• RDBMSの排他制御
  • 行単位ロックとテーブル単位ロック，共有ロック／専有ロックの動作を正確に理解する。
  • ISOLATIONレベル（READ COMMITTED／REPEATABLE READ）ごとのロック取得・解放タイミングを整理する。
• スケーラビリティ設計
  • 並列処理時の「ホットスポット」（同一行への更新集中）はボトルネックになりやすい。
  • パーティション分割や負荷分散をどこに適用するか，テーブル設計と業務単位の粒度を検討する。
• 試験問題の読み方
  • 「同じ○○に対する更新がある」という記述は，そのリソースに競合が生じることを示唆しています。
  • 図や手順だけでなく，選択しているISOLATIONレベルやロック方式の記述も併せて確認しましょう。

キーワード・論点整理

• 並列プロセス実行のボトルネック
  出庫確定 AP を複数プロセスで並列実行するときに、各プロセスが同じ「出庫番号」の範囲を基準にするとロック競合が起こりやすい。
• 複数列索引（複合インデックス）
  複数の列を組み合わせた索引を定義すると、索引スキャンで対象行のロックを限定でき、排他ロックの待ちを減らす。
• テーブルスキャン vs 索引スキャン
  • テーブルスキャン：全行にロックを掛ける → ロック競合増
  • 索引スキャン：該当行のみロック → 並列実行に有利

解答選択の論理的根拠

「出庫番号ではなく，（オ）と（カ）の組合せの値の範囲指定で各プロセスに配分するように変更する。また，図4の1で，出庫番号の昇順ではなく，（オ）と（カ）の昇順に処理を行うように変更する。 “（キ）”テーブルの“（オ）”列と“（カ）”列に，複数列索引を定義しておく。なお，この索引を定義していない場合，自プロセスの対象行かを判定するための参照が（ク）となるので，他プロセスが（ケ）した行を参照しようとしてロックの解放待ちとなり，別のボトルネックが生じる。」

1. 各プロセスに割り当てる範囲キー
   • 出庫番号ではなく、出庫元倉庫コードと部品番号の組合せで範囲分割
     → 「（オ）＝出庫元倉庫コード」「（カ）＝部品番号」
2. 処理順序の変更
   • 出庫番号昇順ではなく、同じく出庫元倉庫コード → 部品番号の昇順で処理
     → テーブル全体を均等に分散して負荷集中を防ぐ
3. 複数列索引の定義場所
   • “（キ）”テーブル＝出庫テーブルに対して、
   • 複合索引：(出庫元倉庫コード, 部品番号)
     を定義しておく
4. 索引未定義時の問題
   • 自プロセスの対象行かを判定する参照が テーブルスキャン になる
   • テーブルスキャンでは全行をロックし、他プロセスが持つ更新／専有ロックを解放待ち
     → 新たなボトルネックが発生

誤りやすいポイント

1. 「（オ）」「（カ）」に別の組合せを考えてしまう
   • 出庫先倉庫コードや出庫便番号では範囲分割とソートに適さない
   • 処理対象を均等に分割できるキーであることが重要
2. 「（ク）」を「索引スキャン」と書いてしまう
   • 索引が未定義のため、判定処理はテーブル全行走査＝テーブルスキャンになる
3. 「（ケ）」を「共有ロック」と書いてしまう
   • 他プロセスが実行中の UPDATE で掛かるのは専有ロック（更新ロック）

試験対策ポイント

• 並列実行時の分散キー設計
  並列プロセスに同一範囲で集中しないよう、複数の列を組み合わせたキーで範囲分割・処理順序を決める。
• 複合インデックスの効果
  複数列索引を定義すると、索引スキャンで対象行のみロックでき、テーブルスキャンに伴う不要なロックを防止できる。
• 排他制御とロック挙動
  • テーブルスキャン：全行にロック → 他トランザクションの待ち増
  • 索引スキャン：該当行のみロック
  • 共有ロック／専有ロックの違いを正確に理解すること
• SQL実行計画の基礎知識
  特定のWHERE句で索引が使われないときはテーブルスキャンになること、並列実行の排他制御への影響を抑える工夫を覚えておきましょう。