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

模範解答

解説

解答の論理構成

1. 目的を確認
   【問題文】「“エリア状況”テーブルのエリアごとの在席者数を更新」とあり、

   SQL2

   はそのための増減数を求めるサブクエリです。
2. 算出方法を読み取る
   

   SQL2

   の完成形は

   SELECT エリアコード,
     SUM(CASE WHEN (b) THEN 1 ELSE 0 END)
   - SUM(CASE WHEN (c) THEN 1 ELSE 0 END) AS 在席者増減数
   FROM 入退室ログ …
   GROUP BY エリアコード
   

   となる想定で、入室（

   入退室区分 = 'I'

   ）は＋１、退室（

   入退室区分 = '0'

   ）は－１の計を取りたいことがわかります。
3. 選択肢を比較
   ・

   COUNT

   は行数を返すだけで、符号を付けた加減には適しません。
   ・

   MAX

   /

   MIN

   は値の最大・最小を返すだけで、合計には不適切。
   ・よって正解は合計を返す

   SUM

   一択です。
4. 結論
   以上より (a) は

   SUM

   となります。

誤りやすいポイント

• COUNT で代用できると考えてしまう
  COUNT では正負の加算ができず、差分計算が不可能です。
• 式全体を１つの SUM で括ると誤解する
  正確には「入室用の

  SUM

  」と「退室用の

  SUM

  」を引き算する二段構成です。
• GROUP BY を見落としてウインドウ関数を考えてしまう
  本問は単純集計で十分であり、ウインドウ関数は不要です。

FAQ

SUM

'0'

'0'

模範解答

解説

解答の論理構成

1. 共有ロックを取るタイミング
   【所在情報更新処理】は図３「SQL３　UPDATE エリア状況 …」の直前で対象行を読み込むため「エリア状況」に共有ロックを取得します。
   同様に【入退室ログ登録処理】は図２②で「所在情報」を参照する際に共有ロックを取得します。
2. 専有ロックを取るタイミング
   ・【所在情報更新処理】は図３「SQL６　UPDATE 所在情報 …」で「所在情報」に専有ロックを要求します。
   ・【入退室ログ登録処理】は図２③で座席数を更新（混雑情報送信用の在席者数加算）するため「エリア状況」に専有ロックを要求します。
3. 相互待ちの成立
   共有ロック取得済みの行に別トランザクションが専有ロックを要求すると，【問題文】の排他制御１項により「ロック解除待ち」となります。
   よって
   ・【所在情報更新処理】は「エリア状況」を共有ロック済み → 【入退室ログ登録処理】が同行に専有ロック要求（待ち）
   ・【入退室ログ登録処理】は「所在情報」を共有ロック済み → 【所在情報更新処理】が同行に専有ロック要求（待ち）
   となりデッドロックが完成します。
4. 表２完成
   以上を時系列に落とし込むと下記のとおりです。

誤りやすいポイント

• UPDATE 文は最初から専有ロックと思い込み，前段の共有ロック（読取り時）を見落とす。
• 図２③を「参照のみ」と誤解し，「エリア状況」に専有ロックが掛からないと判断してしまう。
• 時系列３と４の順序を固定だと思い込み，並列進行（順不同）であることを忘れる。

FAQ

模範解答

解説

解答の論理構成

1. 【問題文】で「ロックは行単位で掛ける。」とあり、各処理が取得するロックは実行順に従います。
2. 図2の
   • ② … “所在情報”系の行にアクセス
   • ③ … “エリア状況”系の行にアクセス
     と二種類の表を操作します。
3. トランザクションＡが ②→③、トランザクションＢが ③→② の順で実行すると、Ａは“所在情報”をロック中に“エリア状況”のロック待ち、Ｂはその逆となり「お互いにロック解放待ち」の状態＝デッドロックになります。
4. 双方の取得順序を「③→②」に合わせれば循環待ちは起こりません。
5. よって「②と③の処理順序を入れ替える。」が最小変更での解決策となります。

誤りやすいポイント

• ①を最後に回すなど大幅な並べ替えを検討してしまう
• ロックレベル（共有／専有）の違いだけでデッドロックが起こると誤解する
• 順序を逆にするのではなく“同時実行しない”よう排他制御を強化すると性能を大きく下げてしまう

FAQ

模範解答

解説

解答の論理構成

1. HTARGETID と HENDID の役割
   • 【問題文】②‐1 では「① の HBEGINID を HTARGETID に設定」と記載。
   • 再開時は②‐2で「その 再開従業員番号を HTARGETID に設定」。
   • よって、カーソル開始位置 (e) は

     :HTARGETID

     。
   • ①で設定した終了番号 HENDID が最終位置なので (f) は

     :HENDID

     。
2. 監査対象行の絞込み
   • 要件 (2) に「監査未チェックの勤怠を対象」とあり、未チェックは NULL と規定。
   • したがって (g) には

     IS NULL

     を入れ、

     = 'Y'

     や

     = 'N'

     を排除。
3. カーソルの並び順
   • ⑤で「カーソルから1行読む」→ ⑩で“再開従業員番号”を更新しコミット、N行ごとに①へ戻る。
   • 同一従業員の行が連続して処理されないと ⑤～⑩ の意味が崩れるため、最優先キーは 従業員番号。
   • よって (h) は

     従業員番号

     。

誤りやすいポイント

• 「監査結果は 'Y'/'N' もあるから

  <> 'Y'

  を書く」と勘違いし NULL 判定を忘れる。
• 再開処理 = フルスキャンと誤認し、(e) に

  HBEGINID

  を入れてしまう。
• ORDER 句に年月日だけを書き、従業員番号でまとまらない不安定なカーソル順序を作る。

FAQ

BETWEEN :HTARGETID AND :HENDID

HTARGETID

IS NULL

模範解答

解説

解答の論理構成

1. ②‐1,②‐2で再開位置を登録・更新
   • 【問題文】「“再開位置” テーブルに HJOBID と HTARGETID の行を挿入」「…再開従業員番号を HTARGETID に設定する」
2. ⑤〜⑩のループ中に逐次更新
   • 【問題文】「⑩ “再開位置” テーブルの当該ジョブの再開従業員番号を…更新し、コミットする」
3. ジョブが最後まで正常終了した場合は再開位置が不要
   • 再開用行が残ると②起動時に誤って②‐2へ進み、処理範囲がスキップされる危険がある。
4. よって終了直前の（ア）で削除
   • 「削除」することで②起動時に②‐1が選択され、次回は冒頭から新たに行を挿入するフローが成立。
5. したがって回答は「“再開位置” テーブルからジョブIDが HJOBID の行を削除する」となる。

誤りやすいポイント

• 更新済みの再開位置を NULL へ更新すると考えてしまう。NULL を設定しても②‐2で行が存在する限り誤判定になります。
• トランザクションレベル READ COMMITTED なので削除は不要と誤解する。再開判定は物理行の有無で行うため、コミット後も行が残ればロジック破綻を招きます。
• WITH HOLD カーソルがあるためカーソル終了時に自動クローズすれば十分と思い込み、再開位置削除を忘れる。

FAQ

模範解答

解説

解答の論理構成

1. 問題は排他ロック待ちのタイムアウトＴ秒内に処理を終えるため、１回のループで実行する行数Ｎを算定する式として
   『N < ( T - (A)に必要な時間 ) ÷ (B)に必要な時間』
   を提示している。
2. この式は
   • (A)：Ｎ行ごとに 1 回だけ走る処理
   • (B)：１行処理するたびに走る処理
     を分離して所要時間を見積もる構造になっている。
3. 図５では
   • ① が “Ｎ行おきにだけ実行する” と明記されている。よって ① が (A)。
   • ⑤～⑨ が “カーソルで読む行がある限り繰り返し” 実行される per-row 処理である。したがって ⑤,⑥,⑦,⑧,⑨ が (B)。
4. ④（ループ判定）や⑩（再開位置更新）は式の趣旨である「監査チェック時間」に直接関与しないため除外する。

誤りやすいポイント

• ⑩まで (B) と誤解する
  ⑩はコミット処理であり、チェックロジックではない。
• ④を (A) と誤解する
  ④は「行がある限りループする」という判定だけで、Ｎ行ごとの特別な処理ではない。
• ①と⑤～⑨の呼び出し頻度を取り違える
  “Ｎ行おき” と “１行ごと” の差を読み飛ばすと計算式の意味を取り違える。

FAQ