令和6年度秋期 データベーススペシャリスト 午後Ⅰ 問2

処理の時間軸と資源の関係を整理する

複数の処理が同じデータに関わる場面では、各処理がいつどの資源を扱い、どの時点で解放するかを時系列で並べて考えることが検討の出発点になります。問われている内容を一つずつその時系列に当てはめて確かめましょう。

この問題で問われていること

排他制御や再実行の設計を考える際は、処理が途中で止まった場合や他の処理と重なった場合に、データの状態がどうなっているかを場面ごとに想定できるかが検討の鍵になります。時系列の整理がその土台になります。

視点

ロックやトランザクションに関する検討では、用語の定義を覚えているかよりも、複数の処理が並行したときに何が起きるかを具体的な場面として追えるかが大切です。処理ごとに、開始、資源の取得、更新、確定や取消しといった節目を横に並べます。節目同士の前後関係を見比べると、どこで競合や待ちが生じ得るかを自分の手で確かめられます。中断後にやり直すときにどの状態から始まるかも、同じ方法で確かめられます。結論を急がず、まず場面を描き出すことを優先しましょう。

確認ポイント

• 各処理が資源を扱い始める時点と手放す時点を、処理ごとに書き出せているか
• 二つ以上の処理を同じ時間軸に並べたとき、互いに待ち合う場面が生じ得るかを自分で確かめたか
• 処理が途中で中断した後にやり直す場面も、同じ時系列の中に置いてデータの状態を追えているか

解説

労務管理システムに所在情報管理機能と監査機能を追加する事例を題材に、SQL設計、REPEATABLE READでのデッドロックの分析とアクセス順序の統一による回避、WITH HOLDカーソルとN行ごとのコミットによる再実行可能なバッチ設計、排他ロック解放待ちタイムアウトを起こさないためのN行の見積りを問う問題です。

問題の全体像

Y社は勤怠管理機能に、所在情報管理機能と監査機能を追加します。所在情報管理機能では、認証のたびに入退室ログ登録処理（図2、REPEATABLE READ）が動きます。一方、5分ごとのバッチである所在情報更新処理（図3・表1、REPEATABLE READ）が“エリア状況”と“所在情報”を更新します。監査機能（図5、READ COMMITTED）は、WITH HOLDカーソルで“勤怠”を読み、N行ごとに再開位置の更新とコミットを行う多重実行ジョブです。出題趣旨では、SQLの設計能力に加え、トランザクション制御と排他制御を理解して、適切な同時実行性を満たす設計能力を問うとしています。採点講評では、設問1(2)の時系列2〜4と設問2(3)の正答率が低かったと指摘されています。

設問1(1)

SQL2は、エリアごとに「入室した人数−退室した人数」を求める式です。CASE式で該当する行を1、それ以外を0にして合計するので、aは公式解答例のとおりSUMです。入室は入退室区分'I'、退室は'O'なので、bは「入退室区分 = 'I'」、cは「入退室区分 = 'O'」になります。SQL5でもbを使います。従業員の最新のログが入室であればそのエリアコードを、そうでなければ退出を示す'9999'を返す、という条件になっています。SQL4は、従業員ごとに今回の範囲の中で最新の入退室ログを取得する文です。ログIDは時系列に昇順で採番されるので、dは公式解答例のとおりMAX(ログID)です。

設問1(2)

両方の処理が、どのテーブルにどの順番でアクセスするかを整理します。所在情報更新は、図3の②でSQL3（“エリア状況”のUPDATE）を実行し、次に③でSQL6（“所在情報”のUPDATE）を実行します。どちらも専有ロックです。入退室ログ登録は、図2の②で“所在情報”を参照し、次に③で“エリア状況”を参照します。どちらも共有ロックです。両方ともREPEATABLE READなので、ロックはコミットまで解放されません。つまり二つの処理はテーブルに逆の順番でアクセスします。公式解答例の表2は次のとおりです。時系列1では、テーブル“エリア状況”に所在情報更新が専有ロックを掛け、ロック済みになります。時系列2では、テーブル“所在情報”に入退室ログ登録が共有ロックを掛け、ロック済みになります。時系列3では、入退室ログ登録が“エリア状況”に共有ロックを掛けようとしますが、時系列1の専有ロックと両立しないため、ロック解放待ちになります。時系列4では、所在情報更新が“所在情報”に専有ロックを掛けようとしますが、時系列2の共有ロックと両立しないため、ロック解放待ちになります。互いに相手のロックの解放を待つので、デッドロックになります。公式解答例の備考では、時系列3と4は順不同とされています。採点講評は、デッドロックになっていない解答が見られたこと、トランザクションの処理内容から「どのテーブルにどのロックが必要か」を読み取れていない受験者が多かったことを指摘しています。

設問1(3)

デッドロックの原因は、二つのトランザクションが“エリア状況”と“所在情報”に逆の順番でアクセスすることです。所在情報更新は“エリア状況”→“所在情報”の順なので、入退室ログ登録も同じ順番にします。つまり、③（“エリア状況”の参照）を先に、②（“所在情報”の参照）を後にします。こうすると、後から来た処理は最初のテーブルで待つだけになり、循環待ちが起きません。公式解答例は「②と③の処理順序を入れ替える。」です。20字以内の答案では「②と③を入れ替える」ことがはっきり分かるように書きます。

設問2(1)

カーソルの読込み範囲は、再実行を考えて開始側を再開位置にします。②-1と②-2で、HTARGETIDには初回は開始従業員番号が、再実行時は再開従業員番号が設定されます。そのため、eは:HTARGETID、fは終了従業員番号の:HENDIDです。要件(2)の「監査未チェック」は、監査結果がNULLの行を指すので、gはIS NULLです。再開位置は従業員番号で記録し、再開もその従業員番号から行います。そのため、処理順序は従業員番号順でなければならず、hは従業員番号です。同じ従業員の行を途中から再び読んでも、チェック済みの行は監査結果がNULLではないので対象外になります。

設問2(2)

要件(3)では、ジョブIDを再利用します。②では、“再開位置”にHJOBIDの行が残っていると、再実行とみなしてその再開従業員番号から処理を始めます。ジョブが正常に終わってもこの行が残っていると、翌日以降に同じジョブIDで実行したとき、処理範囲の先頭（HBEGINID）からではなく途中から始まってしまいます。そこで、正常に終了したら⑬でコミットする前に、該当する行を消しておく必要があります。公式解答例は「“再開位置”テーブルからジョブIDがHJOBIDの行を削除する。」です。40字以内の答案には、対象のテーブル（再開位置）、条件（ジョブIDがHJOBID）、操作（削除）の三つを入れます。

設問2(3)

⑨のUPDATEで“勤怠”の行に掛けた専有ロックは、⑩のコミットまで保持されます。図5の④にあるとおり、⑩は毎回ではなく、N行ごとに一度だけ実行します。⑩では再開位置を更新してからコミットするので、ここでロックが解放されます。したがって、1回のロック保持時間は「⑤〜⑨をN回分」と「⑩を1回分」の合計です。これがT秒を超えなければ、ほかのトランザクションはタイムアウトしません。式をN×(⑤〜⑨の時間)+(⑩の時間)<Tと置き、変形するとN<(T−⑩の時間)÷(⑤〜⑨の時間)になります。つまりAは、N行ごとに一度必要になる⑩（再開位置の更新とコミット）で、Tから差し引く処理です。Bは、1行ごとに繰り返す⑤（カーソルから1行読む）、⑥・⑦・⑧（チェック）、⑨（監査結果の更新）です。公式解答例もAを⑩、Bを⑤、⑥、⑦、⑧、⑨としています。④はループの制御、⑪〜⑬はループの外で1回だけ実行する処理なので、どちらにも含めません。採点講評は、この設問の正答率が低かったと述べ、どの区間でロックが続き、どの契機で解放されるかをよく考えるよう求めています。

学習のポイント

処理の手順ごとに、どのテーブルのどの行へどのロックを掛け、いつ解放するかを時系列で追う練習をしましょう。とくにデッドロックの表を作る問題と、コミット間隔からロック保持時間を見積もる問題が重要です。