データベーススペシャリスト 2015年 午後1 問03

解答の導き方

誤りやすいポイント

• 表2の「売上明細連番200,000,000」をそのまま「1か月分」と誤解する。表2はジョブ3直後の「直近10か月分」の合計であり、1か月分は $200,000,000÷10=20,000,000$ である点を忘れないこと。
• 「延べページ数」と「実際に存在するページ数（ユニークページ数）」を混同する。問題文中の「延べ最大(オ)ページ」は延べ（合計の読み込み回数）を指すため、同一ページの複数回読み込みもカウントされる点に注意する。
• インデックスのバッファヒット率が100%であってもテーブルページのバッファを考慮しない前提があるため、インデックス探索自体が無コストでもテーブルページ読み込みは生じる点を見落としやすい。
• デッドロックの発生箇所は更新対象テーブル（利用残高）である点を見落とすと誤答になる。

FAQ

解答の論理構成

1. 変更前ジョブ2の処理
   • 売上明細を読取り
   • 「カード番号」をキーに“利用残高”を UPDATE【問題文引用】
2. 行ロックの特性
   • 「排他制御は行単位で行う」ため、UPDATE 時にその行をロックします。
3. 多重実行時の並行動作
   • 各ジョブは売上明細連番で分担しても、同じ「カード番号」を含むことがある。
   • ジョブAは①番目の売上明細で「カード番号=123」をロック後、「カード番号=456」をロック。
   • ジョブBは逆順に「カード番号=456」→「カード番号=123」をロック。
4. 相互待ち発生 → デッドロック
   • 互いに相手が保持する行ロック解放を待って停止、デッドロック検出・ロールバックとなります。
5. したがって「同じカード番号の行を異なる順番で更新するから」という短答になるわけです。

誤りやすいポイント

• 「売上明細」側の行ロックが原因と勘違いする。実際に UPDATE を行うのは“利用残高”テーブルです。
• 行ロックなので安全と思い込む。ロック単位が小さくても取得順序が不一致ならデッドロックは起こります。
• READ COMMITTEDだから大丈夫と誤解する。隔離レベルはロック取得順序の衝突を防ぎません。

FAQ

解答の論理構成

1. 表3の変更
   • 変更前：「加盟店コード、利用年月日」
   • 変更後：「カード番号、利用年月日」
     ― ジョブ1がこの順序でINSERTするため“売上明細”はカード番号順に連番が振られる。
2. ジョブ2の読み込み方法
   • 図2のカーソルは「売上明細連番」でORDERなし、連番＝物理挿入順。
   • したがってジョブ2はカード番号順に“売上明細”を処理する。
3. “利用残高”の構造
   • 表2より「ページ当たりの行数：10」「列値個数：5,000,000」。
   • 主索引「1A」はクラスタ化でカード番号順配置。
4. 読み込みページ数の決定
   • 連続カード番号ごとに同一ページを再利用でき、全カードを一巡するだけ。
   • 総ページ数＝5,000,000 ÷ 10 ＝ 500,000。
5. 読み込み形態
   • 変更前は加盟店単位のばらばらなカード番号で更新要求が散在し、ページを「ランダム」に読む必要。
   • 変更後はカード番号が昇順に並ぶので「順次」読みで済み、I/O回数が大幅減。

誤りやすいポイント

• 「500,000」を件数と誤認し、ページ数換算を忘れる。
• ジョブ2のカーソルが「ORDER BY カード番号」だと思い込み、連番＝物理順の影響を見落とす。
• ランダムアクセスが減る理由を「索引追加」と勘違いし、ソート順変更との因果を説明できない。

FAQ

解答の論理構成

1. “CKPT” テーブルは「ジョブ単位でチェックポイントを保持する」目的で新設される。【問題文】「“チェックポイント” テーブル (以下、“CKPT” テーブルという) を定義する。」
2. 初回実行時は INSERT INTO CKPT (ジョブID) とある通り、ジョブID だけで行が生成される。したがって CKPT連番 は後で更新される列であり、一意性を保証する役目ではない。
3. 以降の更新・削除はいずれも WHERE ジョブID = :ジョブID 句のみで対象行を特定している。これは「ジョブID」が行の一意性を担保している証拠である。
4. 以上より、主キーは「ジョブID」、保持すべき列は「ジョブID」と「CKPT連番」と結論付けられる。

誤りやすいポイント

• CKPT連番 を主キーに含めてしまう
  連番という名前から主キーと誤解しやすいですが、実際には更新対象であり一意とは限りません。
• 列名の誤記
  「ジョブID」「CKPT連番」は【問題文】の大文字小文字・全角英字を含む表記をそのまま使う必要があります。
• 下線の付け忘れ
  主キーを示すために列名に下線を付ける指示があります。

FAQ

解答の論理構成

1. 図3の⑥では
   「④、⑤をN回処理するごとに、“CKPT”テーブルを更新する。」
   と記載されています。ここで「更新」は行の書換えに過ぎず、まだ同期（COMMIT）は行われていません。
2. 問題文では
   「ジョブが失敗した後に同じジョブを再実行したとき、(N×K+1)行目から処理を再開するためには、図3中に同期を取る処理が必要」
   と指示されています。再開点を正確にするには、チェックポイントとそれまでの業務データ（利用残高・利用明細）を同一コミット単位で確定させる必要があります。
3. ④・⑤（業務データ更新）が終わり、⑥でチェックポイントを書いた直後が“確定すべき最小単位”であり、そこより前でコミットするとチェックポイントが含まれず、再開点が後退します。逆に⑦以降では「次のN行の処理」に入ってしまうため、失敗時には再び重複処理が発生します。
4. よって、④〜⑧の中で条件を満たすのは「⑥」の直後のみとなり、解答は⑥です。

誤りやすいポイント

• ⑧（カーソルクローズ後）でコミットと誤解する
  → 失敗が⑦のループ中に起きると未コミットのN行がロールバックされ、再開点がずれてしまいます。
• ⑤直後でコミットと考える
  → “CKPT”テーブルが未更新のまま確定するため、再実行時に同じN行を二重計上する危険があります。
• 「WITH HOLDだからコミット不要」と思い込む
  → WITH HOLDはカーソル保持の設定であり、再開ロジックの精度とは無関係です。

FAQ

解答の論理構成

1. 図3③のSELECT文（引用）
   sql SELECT 売上明細連番, カード番号, 利用金額 FROM 売上明細 WHERE 登録年月 = :登録年月 AND 売上明細連番 BETWEEN :開始連番 AND :終了連番 AND 売上明細連番 > :処理連番
2. 問題文より、「売上明細連番」は「テーブルに行が追加されたとき、自動的に一意かつ昇順で付与される連番」、《表2》では列値個数「200,000,000」と示されており、主索引「1A」が設定されています。
3. 同じく問題文には「ページ及び行は追加された順に並び」とあり、登録年月は行追加時点で決定するタイムスタンプ属性です。
4. 「売上明細連番 BETWEEN :開始連番 AND :終了連番」で検索範囲が限定されると、対象行は登録年月が同一であるか否かに関係なく一意に取得できます。
5. 登録年月は売上明細連番に完全関数従属する（主キーが決まれば一意に決まる）ため、追加の述語「登録年月 = :登録年月」は論理的にも物理的にも冗長です。
6. よって不要述語は「登録年月 = :登録年月」となります。

誤りやすいポイント

• 「索引があるから使った方が速い」と考え、無条件に複合条件を残してしまう。
• 多重処理時に登録年月で分割していると誤解し、必須と勘違いする。
• 間違って「売上明細連番 > :処理連番」が不要と答えてしまう。

FAQ

解答の論理構成

1. 問題文の前提を確認
   • 「排他ロック待ちタイムアウト時間がT秒のとき、図3中の④ ⑤を合わせた処理に平均A秒、⑥の処理に平均B秒、同期を取る処理に平均C秒の時間を要すると仮定する。」
2. 1バッチ（N行）処理中にロックを保持する総時間
   • ④⑤：1行あたりA秒 → N × A秒
   • ⑥：バッチに1回 → B秒
   • 同期(COMMIT)：バッチに1回 → C 秒
   • よって合計はN×A + B + C秒
3. タイムアウトを回避する条件
   • ロック保持時間 < タイムアウト時間 T
   • 不等式：N×A + B + C < T
4. 変形してNを求める
   • N×A < T - B - C
   • $N<\frac{T-B-C}{A}$
5. よって
   • (a) に入る式：T-B-C
   • (b) に入る式：A

誤りやすいポイント

• ⑥と同期(COMMIT)を「行ごと」に掛かると誤解し N×B や N×C としてしまう。
• 不等式を <= として上限値を誤判定する。
• T-B-Cが負になるケースを考慮せず現実的に実行不能なNを算出する。

FAQ