データベーススペシャリスト 2024年 午後1 問02
総合商社の労務管理システムのデータベース実装に関する次の記述を読んで、設問に答えよ。
総合商社の Y社は、労務管理に RDBMS を用いている。既存の勤怠管理機能に、新たに所在情報管理機能と監査機能を追加することになった。
〔RDBMS の排他制御〕
1．ロックは行単位で掛ける。 共有ロックを掛けている間、他のトランザクションから対象行への共有ロックは可能であり、専有ロックは共有ロックの解放待ちとなる。 専有ロックを掛けている間、他のトランザクションから対象行への共有口ック及び専有ロックは、専有ロックの解放待ちとなる。
2．索引を使わずに表探索で全ての行に順次アクセスする場合、検索条件に合致するか否かにかかわらず全行をロックの対象とする。 索引探索の場合、索引から読み込んだ行だけをロックの対象とする。
〔所在情報管理機能の追加〕
1．機能の概要
オフィス内のフリーアドレス化を推進するため、従業員の就業エリア(以下、エリアという)への入退室のログを記録し、従業員の所在情報をエリア情報表示端末で社内に公開する。 エリア情報表示端末では、エリアの混雑状況なども表示する。
エリアには、建屋、フロア、執務室があり、従業員は各エリアへの入退室時にICカード社員証で認証を行う。 従業員は入退室時に必ず認証を行うように定められており、他の従業員に続いて認証せずに入退室することは禁止されている。 従業員は、建屋内にいる限りいずれかのエリアに所在しており、建屋から出ることで退出として扱われる。
所在情報管理機能では、認証時に入退室ログを記録する。 また、記録した入退室ログを基に、従業員の現在位置を示す所在情報及びエリアの混雑状況を定期的に更新する。
2．テーブル構造
主なテーブルのテーブル構造は、図1のとおりである。 主キーには主索引が定義されている。
3．入退室ログ登録処理
従業員が入退室で認証する都度、“入退室ログ”テーブルに行を追加する。ログID は時系列に昇順で採番し、入退室区分には入室('T')又は退室 ('0') を設定する。必要に応じて、連携する勤怠管理機能及びエリア情報表示端末に情報を送信する。
処理の概要を図2に示す。 トランザクションの ISOLATION レベルは REPEATABLEREAD とする。
4．所在情報更新処理
5分ごとに実行するバッチ処理で、定期的にエリア状況及び所在情報を更新する。
“入退室ログ”テーブルに登録されている入退室ログを読み込み、“エリア状況”テーブルのエリアごとの在席者数を更新、“所在情報” テーブルの従業員ごとの最終入室エリアコードを更新する (退出している場合は、最終入室エリアコードには‘9999' を設定する)。 全ての更新が終わったら、読み込み終わった入退室ログの位置を記録しておき、次回実行時はその続きから読み込む。
所在情報更新処理の概要を図3, 所在情報更新処理に用いる SQL 文を表1に示す。トランザクションの ISOLATION レベルは REPEATABLE READ とする。
〔監査機能の追加〕
従業員の残業時間、休暇、エリアを、入退室ログと突き合わせてチェックする監査機能を、要件に基づいて設計した。
1．監査機能の要件
(1) 監査対象月の1日から月末日までの勤怠をチェックする。 監査対象月の月末の営業日から翌月の5営業日までの間、毎日実行する。
(2) 上長承認済み、かつ、監査未チェックの勤怠を対象にチェックする。 なお、一度監査が終了した行でも、勤怠情報を従業員が更新した場合は、上長承認と監査結果をリセットして、再度、上長承認及び監査を行う。
(3) 監査機能のジョブは多重実行する。 全てのジョブに一意なジョブIDを割り当て、それぞれのジョブに重複していない従業員番号の範囲を指定して実行する。また、処理の途中で失敗した場合、その原因を取り除いて、同じジョブIDで再実行することで、処理を再開する。 なお、ジョブIDは再利用する。
2．監査機能のテーブル設計
主なテーブルのテーブル構造は図4のとおりである。 主キーには主索引が定義されている。
“勤怠” テーブルには、従業員ごと年月日ごとの勤怠情報を記録する。 上長承認には、承認済み ('Y') 又は未承認 ('N') を設定する。 監査結果には、監査未チェックの行は NULL を設定し、チェックしたら適正 ('Y') 又は不適正 ('N') を設定する。
監査機能のジョブが途中で失敗した場合に、ジョブを再実行するために “再開位置” テーブルを使用する。 再開従業員番号には処理中の従業員番号を記録し、再実行時には記録していた従業員番号から処理を再開する。
3．監査機能の処理設計
処理の流れと SQL文を図5に示す。 トランザクションの ISOLATION レベルは READCOMMITTED とする。
監査機能の実行時には他トランザクションも実行されており、排他ロック解放待ちタイムアウトの考慮も必要である。 排他ロック解放待ちタイムアウト時間を T秒とすると、他トランザクションで排他ロック解放待ちタイムアウトさせないための図5中のN行を見積もる計算式は、次の式となる。
N < ( T - (A)に必要な時間 ) ÷ (B)に必要な時間
設問1：所在情報管理機能の追加 について答えよ。
（1）表中の(a)に入れる適切な字句を答えよ。
模範解答
a：SUM
b：入退室区分 = 'I'
c：入退室区分 = '0'
d：MAX(ログID)
解説
解答の論理構成
-
目的を確認
【問題文】「“エリア状況”テーブルのエリアごとの在席者数を更新」とあり、SQL2 はそのための増減数を求めるサブクエリです。 -
算出方法を読み取る
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、退室（入退室区分 = '0'）は－1の計を取りたいことがわかります。 -
選択肢を比較
・COUNT は行数を返すだけで、符号を付けた加減には適しません。
・MAX / MIN は値の最大・最小を返すだけで、合計には不適切。
・よって正解は合計を返す SUM 一択です。 -
結論
以上より (a) は SUM となります。
誤りやすいポイント
- COUNT で代用できると考えてしまう
COUNT では正負の加算ができず、差分計算が不可能です。 - 式全体を1つの SUM で括ると誤解する
正確には「入室用の SUM」と「退室用の SUM」を引き算する二段構成です。 - GROUP BY を見落としてウインドウ関数を考えてしまう
本問は単純集計で十分であり、ウインドウ関数は不要です。
FAQ
Q: COUNT と CASE WHEN の組み合わせでも実現できますか？
A: CASE WHEN の中を 1／NULL として COUNT する方法もありますが、試験では典型解と一致させる必要があるため、SUM を使った表現が最適です。
A: CASE WHEN の中を 1／NULL として COUNT する方法もありますが、試験では典型解と一致させる必要があるため、SUM を使った表現が最適です。
Q: 退室区分が '0' である理由は？
A: 【問題文】「入退室区分には入室('T')又は退室 ('0') を設定する」と定義されており、文字 '0' が退室を示すコードです。
A: 【問題文】「入退室区分には入室('T')又は退室 ('0') を設定する」と定義されており、文字 '0' が退室を示すコードです。
Q: REPEATABLE READ が指定されているのはなぜですか？
A: 在席者数や所在情報を更新するバッチ処理中に新規ログが追加されても、一貫したスナップショットで集計できるようにするためです。
A: 在席者数や所在情報を更新するバッチ処理中に新規ログが追加されても、一貫したスナップショットで集計できるようにするためです。
関連キーワード: SQL集約関数、CASE式、差分集計、排他制御、トランザクション分離レベル
設問1：所在情報管理機能の追加 について答えよ。
（2）所在情報更新処理を実行中に、入退室ログ登録処理が発生するとデッドロックとなる可能性がある。 デッドロックを引き起こすロックの状況について、時系列に、対象となるテーブルのテーブル名、ロックを掛けるトランザクション、ロック種別、ロック状態を答えて、次に示す表 2 を完成させよ。
なお、ロックを掛けるトランザクション、ロック種別 ロック状態については、表中の該当する方を○で囲んで示せ。 （時系列3と4は順不同）
模範解答
解説
解答の論理構成
- 共有ロックを取るタイミング
【所在情報更新処理】は図3「SQL3 UPDATE エリア状況 …」の直前で対象行を読み込むため「エリア状況」に共有ロックを取得します。
同様に【入退室ログ登録処理】は図2②で「所在情報」を参照する際に共有ロックを取得します。 - 専有ロックを取るタイミング
・【所在情報更新処理】は図3「SQL6 UPDATE 所在情報 …」で「所在情報」に専有ロックを要求します。
・【入退室ログ登録処理】は図2③で座席数を更新（混雑情報送信用の在席者数加算）するため「エリア状況」に専有ロックを要求します。 - 相互待ちの成立
共有ロック取得済みの行に別トランザクションが専有ロックを要求すると、【問題文】の排他制御1項により「ロック解除待ち」となります。
よって
・【所在情報更新処理】は「エリア状況」を共有ロック済み → 【入退室ログ登録処理】が同行に専有ロック要求（待ち）
・【入退室ログ登録処理】は「所在情報」を共有ロック済み → 【所在情報更新処理】が同行に専有ロック要求（待ち）
となりデッドロックが完成します。 - 表2完成
以上を時系列に落とし込むと下記のとおりです。
誤りやすいポイント
- UPDATE 文は最初から専有ロックと思い込み、前段の共有ロック（読取り時）を見落とす。
- 図2③を「参照のみ」と誤解し、「エリア状況」に専有ロックが掛からないと判断してしまう。
- 時系列3と4の順序を固定だと思い込み、並列進行（順不同）であることを忘れる。
FAQ
Q: 「共有ロック → 専有ロック」へは常に昇格するのですか？
A: 多くの RDBMS で UPDATE は対象行を検索する際に共有ロックを取り、実際に更新時に専有ロックへ昇格します。【問題文】の排他制御もその挙動を前提にしています。
A: 多くの RDBMS で UPDATE は対象行を検索する際に共有ロックを取り、実際に更新時に専有ロックへ昇格します。【問題文】の排他制御もその挙動を前提にしています。
Q: 専有ロックを直接取得すればデッドロックは防げますか？
A: 直接取得できれば今回の相互待ちは避けられますが、行検索前に範囲が確定しない場合やインデックスを使う都合で共有ロック→専有ロックの流れになるケースが一般的です。
A: 直接取得できれば今回の相互待ちは避けられますが、行検索前に範囲が確定しない場合やインデックスを使う都合で共有ロック→専有ロックの流れになるケースが一般的です。
Q: デッドロック発生時はどちらのトランザクションがロールバックされますか？
A: RDBMS のアルゴリズム（被害者選択）により、一方のトランザクションが自動的にロールバックされます。どちらが選ばれるかは DB 製品や設定に依存します。
A: RDBMS のアルゴリズム（被害者選択）により、一方のトランザクションが自動的にロールバックされます。どちらが選ばれるかは DB 製品や設定に依存します。
関連キーワード: 共有ロック、専有ロック、デッドロック、ロック昇格、排他制御
設問1：所在情報管理機能の追加 について答えよ。
（3）（2）のデッドロックを回避するために、図2の入退室ログ登録処理の処理順序を変更する。 変更内容を、図2中の ①〜③を用いて20字以内で答えよ。
模範解答
②と③の処理順序を入れ替える。
解説
解答の論理構成
- 【問題文】で「ロックは行単位で掛ける。」とあり、各処理が取得するロックは実行順に従います。
- 図2の
- ② … “所在情報”系の行にアクセス
- ③ … “エリア状況”系の行にアクセス
と二種類の表を操作します。
- トランザクションＡが ②→③、トランザクションＢが ③→② の順で実行すると、Ａは“所在情報”をロック中に“エリア状況”のロック待ち、Ｂはその逆となり「お互いにロック解放待ち」の状態＝デッドロックになります。
- 双方の取得順序を「③→②」に合わせれば循環待ちは起こりません。
- よって「②と③の処理順序を入れ替える。」が最小変更での解決策となります。
誤りやすいポイント
- ①を最後に回すなど大幅な並べ替えを検討してしまう
- ロックレベル（共有／専有）の違いだけでデッドロックが起こると誤解する
- 順序を逆にするのではなく“同時実行しない”よう排他制御を強化すると性能を大きく下げてしまう
FAQ
Q: 順序をそろえてもロック待ち時間はゼロになりますか？
A: ゼロにはなりません。待ちは発生しますが、循環待ちがなくなるためタイムアウトやロールバックが不要になります。
A: ゼロにはなりません。待ちは発生しますが、循環待ちがなくなるためタイムアウトやロールバックが不要になります。
Q: トランザクションの ISOLATION レベルを変更しても解決できますか？
A: 本設問ではロック順序の不統一が原因なので、ISOLATION レベルを変更しても根本的なデッドロックは解消されません。
A: 本設問ではロック順序の不統一が原因なので、ISOLATION レベルを変更しても根本的なデッドロックは解消されません。
関連キーワード: デッドロック、行ロック、排他制御、トランザクション、ロック順序
設問2：監査機能の追加 について答えよ。
（1）図5中の(e)〜(h)に入れる適切な字句を答えよ。
模範解答
e：:HTARGETID
f：:HENDID
g：IS NULL
h：従業員番号
解説
解答の論理構成
-
HTARGETID と HENDID の役割
- 【問題文】②‐1 では「① の HBEGINID を HTARGETID に設定」と記載。
- 再開時は②‐2で「その 再開従業員番号を HTARGETID に設定」。
- よって、カーソル開始位置 (e) は :HTARGETID。
- ①で設定した終了番号 HENDID が最終位置なので (f) は :HENDID。
-
監査対象行の絞込み
- 要件 (2) に「監査未チェックの勤怠を対象」とあり、未チェックは NULL と規定。
- したがって (g) には IS NULL を入れ、= 'Y' や = 'N' を排除。
-
カーソルの並び順
- ⑤で「カーソルから1行読む」→ ⑩で“再開従業員番号”を更新しコミット、N行ごとに①へ戻る。
- 同一従業員の行が連続して処理されないと ⑤～⑩ の意味が崩れるため、最優先キーは 従業員番号。
- よって (h) は 従業員番号。
誤りやすいポイント
- 「監査結果は 'Y'/'N' もあるから <> 'Y' を書く」と勘違いし NULL 判定を忘れる。
- 再開処理 = フルスキャンと誤認し、(e) に HBEGINID を入れてしまう。
- ORDER 句に年月日だけを書き、従業員番号でまとまらない不安定なカーソル順序を作る。
FAQ
Q: BETWEEN :HTARGETID AND :HENDID では範囲が変わるたびにパラメータを変える必要がありますか？
A: はい。再開時は②‐2で取得した HTARGETID をセットし直してからカーソルを開きます。
A: はい。再開時は②‐2で取得した HTARGETID をセットし直してからカーソルを開きます。
Q: IS NULL だとインデックスが使われにくいのでは？
A: 多くの RDBMS で NULL 値も索引に含められる設定が可能です。本問題では索引有無より 論理正当性 の説明が主眼です。
A: 多くの RDBMS で NULL 値も索引に含められる設定が可能です。本問題では索引有無より 論理正当性 の説明が主眼です。
Q: ORDER 句で複合キー全体を指定しないとロック順序が不安定になりませんか？
A: 主要キーを 従業員番号、年月日 の順に指定しているため、監査処理が必要とする従業員単位の安定順序は確保できます。
A: 主要キーを 従業員番号、年月日 の順に指定しているため、監査処理が必要とする従業員単位の安定順序は確保できます。
関連キーワード: BETWEEN句、NULL判定、カーソル処理、排他ロック、READ COMMITTED
設問2：監査機能の追加 について答えよ。
（2）図5中の（ア）で行うべき処理を40字以内で答えよ。
模範解答
ア：“再開位置” テーブルからジョブIDが HJOBID の行を削除する。
解説
解答の論理構成
- ②‐1,②‐2で再開位置を登録・更新
- 【問題文】「“再開位置” テーブルに HJOBID と HTARGETID の行を挿入」「…再開従業員番号を HTARGETID に設定する」
- ⑤〜⑩のループ中に逐次更新
- 【問題文】「⑩ “再開位置” テーブルの当該ジョブの再開従業員番号を…更新し、コミットする」
- ジョブが最後まで正常終了した場合は再開位置が不要
- 再開用行が残ると②起動時に誤って②‐2へ進み、処理範囲がスキップされる危険がある。
- よって終了直前の（ア）で削除
- 「削除」することで②起動時に②‐1が選択され、次回は冒頭から新たに行を挿入するフローが成立。
- したがって回答は「“再開位置” テーブルからジョブIDが HJOBID の行を削除する」となる。
誤りやすいポイント
- 更新済みの再開位置を NULL へ更新すると考えてしまう。NULL を設定しても②‐2で行が存在する限り誤判定になります。
- トランザクションレベル READ COMMITTED なので削除は不要と誤解する。再開判定は物理行の有無で行うため、コミット後も行が残ればロジック破綻を招きます。
- WITH HOLD カーソルがあるためカーソル終了時に自動クローズすれば十分と思い込み、再開位置削除を忘れる。
FAQ
Q: ジョブが異常終了した場合、削除してしまうと再開できませんか？
A: 異常終了時は⑫まで到達せず削除が行われないため、行は残ります。再実行時は②‐2で再開できます。
A: 異常終了時は⑫まで到達せず削除が行われないため、行は残ります。再実行時は②‐2で再開できます。
Q: ジョブ ID を再利用するとありますが、削除で問題は起きませんか？
A: 再利用時には②‐1で新たに挿入するため競合しません。完了時に行をクリーンに消しておくことが再利用を安全にします。
A: 再利用時には②‐1で新たに挿入するため競合しません。完了時に行をクリーンに消しておくことが再利用を安全にします。
関連キーワード: 再開処理、排他制御、WITH HOLD カーソル、READ COMMITTED
設問2：監査機能の追加 について答えよ。
（3）A と B の対象となる処理に該当するものを、図5中の④〜⑬から選べ。 なお、該当する処理が複数ある場合は全て選ぶこと。
模範解答
Aの対象となる処理：①
Bの対象となる処理：⑤、⑥、⑦、⑧、⑨
解説
解答の論理構成
- 問題は排他ロック待ちのタイムアウトＴ秒内に処理を終えるため、1回のループで実行する行数Ｎを算定する式として
『N < ( T - (A)に必要な時間 ) ÷ (B)に必要な時間』
を提示している。 - この式は
- (A)：Ｎ行ごとに 1 回だけ走る処理
- (B)：1行処理するたびに走る処理
を分離して所要時間を見積もる構造になっている。
- 図5では
- ① が “Ｎ行おきにだけ実行する” と明記されている。よって ① が (A)。
- ⑤～⑨ が “カーソルで読む行がある限り繰り返し” 実行される per-row 処理である。したがって ⑤、⑥、⑦、⑧、⑨ が (B)。
- ④（ループ判定）や⑩（再開位置更新）は式の趣旨である「監査チェック時間」に直接関与しないため除外する。
誤りやすいポイント
- ⑩まで (B) と誤解する
⑩はコミット処理であり、チェックロジックではない。 - ④を (A) と誤解する
④は「行がある限りループする」という判定だけで、Ｎ行ごとの特別な処理ではない。 - ①と⑤～⑨の呼び出し頻度を取り違える
“Ｎ行おき” と “1行ごと” の差を読み飛ばすと計算式の意味を取り違える。
FAQ
Q: ⑩を (B) に含めない理由は？
A: ⑩は監査結果ではなく “再開位置” の更新とコミットで、チェックロジックに要する時間とは分離して考えるためです。
A: ⑩は監査結果ではなく “再開位置” の更新とコミットで、チェックロジックに要する時間とは分離して考えるためです。
Q: ④はループ判定だから (A) では？
A: ④自体は各行処理を開始する判定であり「Ｎ行おきにだけ実行する」わけではないため (A) には該当しません。
A: ④自体は各行処理を開始する判定であり「Ｎ行おきにだけ実行する」わけではないため (A) には該当しません。
Q: Ｎ行の間隔を大きくすれば速くなる？
A: Ｎを大きくすると ① の回数は減りますが、排他ロック保持時間が長くなり他トランザクションに影響するため、式の範囲内で適切に設定する必要があります。
A: Ｎを大きくすると ① の回数は減りますが、排他ロック保持時間が長くなり他トランザクションに影響するため、式の範囲内で適切に設定する必要があります。
関連キーワード: トランザクション分離レベル、排他ロック、バッチ処理、パフォーマンスチューニング、サイジング
