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

解答の論理構成

1. 目的を確認
   【問題文】「“エリア状況”テーブルのエリアごとの在席者数を更新」とあり、SQL2はそのための増減数を求めるサブクエリです。
2. 算出方法を読み取る
   SQL2の完成形は
   sql SELECT エリアコード, SUM(CASE WHEN (b) THEN 1 ELSE 0 END)
   • SUM(CASE WHEN (c) THEN 1 ELSE 0 END) AS 在席者増減数 FROM 入退室ログ … GROUP BY エリアコード
   となる想定で、入室（入退室区分 = 'I'）は＋1、退室（入退室区分 = 'O'）は－1の計を取りたいことがわかります。
3. 選択肢を比較
   ・COUNT は行数を返すだけで、符号を付けた加減には適しません。
   ・MAX / MIN は値の最大・最小を返すだけで、合計には不適切。
   ・よって正解は合計を返す SUM 一択です。
4. 結論
   以上より (a) は SUM となります。

誤りやすいポイント

• COUNT で代用できると考えてしまう
  COUNT では正負の加算ができず、差分計算が不可能です。
• 式全体を1つの SUM で括ると誤解する
  正確には「入室用の SUM」と「退室用の SUM」を引き算する二段構成です。
• GROUP BY を見落としてウインドウ関数を考えてしまう
  本問は単純集計で十分であり、ウインドウ関数は不要です。

FAQ

解答の導き方

1. ロックと探索方式の前提

• 問題文に「ロックは行単位で掛ける。」とあるため、ロックは行ごとに付与されると考えます。共有ロックは他のトランザクションの共有ロックを許容しますが、専有ロックは共有ロック・専有ロックの取得を妨げます（排他性の説明）。
• 「索引探索の場合、索引から読み込んだ行だけをロックの対象とする。」とあること、そして図1で入退室ログのログIDが主キーで「主索引が定義されている」ので、ログIDによる検索（SQL2, SQL4等）は索引探索になり、入退室ログの読み取りは読み込んだ行だけに共有ロックが付くと判断します。

1. 各処理がいつどのテーブルにアクセスしどのロックを取るか（図3・表1・図2の記述より）

• 所在更新（図3）では、SQL2で入退室ログを読み、結果行ごとにホスト変数をセットしてSQL3を実行する。SQL3は
  UPDATE エリア状況 SET 在席者数 = 在席者数 + :HDIFF WHERE エリアコード = :HAREACODE
  であり、これは該当エリアの行に対する専有（排他）ロックを取得して更新します。その後、SQL4→SQL5→SQL6により、従業員ごとに入退室ログを参照してSQL6を実行します。SQL6は
  UPDATE 所在情報 SET 最終入室エリアコード = :HAREACODE WHERE 従業員番号 = :HEMPLOYEEID
  であり、所在情報の該当従業員行に対して専有ロックを取得します。所在更新のトランザクション分離レベルは「トランザクションのISOLATIONレベルはREPEATABLE READとする。」とあるため、共有ロック・専有ロックはコミットまで保持される想定です。
• ログ登録（図2）では、まず「① 入退室ログを挿入する。」（INSERT により挿入行に専有ロックを取得しコミットまで保持）し、入室の場合は次に「② …当該従業員の所在情報を参照」して所在情報を読み（共有ロックを取得）、さらに「③ 当該エリアの在席者数と混雑判定閾値を参照」してエリア状況を読み（共有ロックを取得）します。これらの参照は図2 の順序で行われます。

1. デッドロックが起きる典型的な時系列（相互待ちの発生）

• 所在更新がSQL2 → SQL3を実行して、あるエリアのエリア状況行に対して先に専有ロック（更新ロック）を取得する（時系列1）。この時点でそのエリアの行は所在更新がロック済み。
• その直後にログ登録が始まり、INSERT を行った後に所在情報を参照して該当従業員の所在情報行に共有ロックを取得する（時系列2）。この共有ロックはコミットまで保持される。
• 続いてログ登録がエリア状況を参照しようとすると、所在更新がすでに当該エリア状況行を専有ロックで保持しているため、ログ登録はそのエリア状況行の共有ロック取得で待ち（時系列3：ロック解除待ち）になる。
• 一方、所在更新はSQL4～SQL6の処理で該当従業員の所在情報行を更新しようとし、そこに対して専有ロックを取得しようとするが、ログ登録が既にその所在情報行に共有ロックを持っているため、所在更新は所在情報行の専有ロック取得で待ち（時系列4：ロック解除待ち）になる。
• ここでログ登録はエリア状況の解除を待ち、所在更新は所在情報の解除を待つため相互待ち（デッドロック）になります。時系列3と4の順序は問題文に「時系列3と4は順不同」とあるとおり入れ替わる可能性がありますが、重要なのはエリア状況と所在情報で互いに相手のロックを待つ循環待ちが発生する点です。

1. 以上を表現した時系列ごとの選択（表2の完成）
   次の表は設問の選択肢に合わせて、どちらに丸を付けるかを示したものです。選択は（○）で示します。

1. 時系列1
   • テーブル名：エリア状況
   • ロックを掛けるトランザクション：所在情報更新（○）　入退室ログ登録（　）
   • ロック種別：共有ロック（　）　専有ロック（○）
   • ロック状態：ロック済み（○）　ロック解除待ち（　）
2. 時系列2
   • テーブル名：所在情報
   • ロックを掛けるトランザクション：所在情報更新（　）　入退室ログ登録（○）
   • ロック種別：共有ロック（○）　専有ロック（　）
   • ロック状態：ロック済み（○）　ロック解除待ち（　）
3. 時系列3（時系列4と順不同）
   • テーブル名：エリア状況
   • ロックを掛けるトランザクション：所在情報更新（　）　入退室ログ登録（○）
   • ロック種別：共有ロック（○）　専有ロック（　）
   • ロック状態：ロック済み（　）　ロック解除待ち（○）
4. 時系列4（時系列3と順不同）
   • テーブル名：所在情報
   • ロックを掛けるトランザクション：所在情報更新（○）　入退室ログ登録（　）
   • ロック種別：共有ロック（　）　専有ロック（○）
   • ロック状態：ロック済み（　）　ロック解除待ち（○）

誤りやすいポイント

• SQL3 が UPDATE である点を見落として共有ロックにする誤り。SQL3 は「UPDATE エリア状況 …」なので該当行に専有ロックを取ります。
• 「SELECT=ロックしない」と考える誤り。問題文のロック規則では読み取りでも共有ロックを取得するので、SELECT による参照は他トランザクションの専有ロックを妨げます。
• 入退室ログの読み取りが全件ロックになると勘違いする誤り。図1のログIDが主キーであること、及び「索引探索の場合、索引から読み込んだ行だけをロックの対象とする。」という記述から、範囲検索は索引探索になり読み込んだ行だけがロックされると判断すべきです。
• 時系列3と4の順序は固定ではない点を見落とし、順序に依存した説明をしてしまう誤り。

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

解答の論理構成

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

誤りやすいポイント

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

FAQ

解答の論理構成

1. 問題は排他ロック待ちのタイムアウトＴ秒内に処理を終えるため、1回のループで実行する行数Ｎを算定する式として
   『N < ( T - (A)に必要な時間 ) ÷ (B)に必要な時間』
   を提示している。
2. この式は
   • (A)：Ｎ行ごとに1回だけ走る処理
   • (B)：1行処理するたびに走る処理
     を分離して所要時間を見積もる構造になっている。選択肢の範囲は④〜⑬である。
3. 図5の④に「⑩の処理はN行おきにだけ実行する」とある。よって ⑩ が (A)。
4. ⑤～⑨ は “カーソルで読む行がある限り繰り返し” 実行される1行ごとの処理である。したがって ⑤、⑥、⑦、⑧、⑨ が (B)。

誤りやすいポイント

• ⑩を1行ごとの処理と誤解する
  ④に「⑩の処理はN行おきにだけ実行する」と明記されている。
• ①を (A) と答えてしまう
  ①は選択肢の範囲（④〜⑬）に入っておらず、ループの前に1回だけ実行される処理である。
• ⑤～⑨と⑩の呼び出し頻度を取り違える
  “Ｎ行おき” と “1行ごと” の差を読み飛ばすと計算式の意味を取り違える。

FAQ