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

問1 オートリース会社の車両保守業務におけるデータベースの実装・運用に関する次の記述を読んで，設問に答えよ。 A社は，法人向けに車両のリース及び保守業務を行っており，業務に車両保守システムを使用している。A社では，夜間に実施しているバッチ処理の昼間帯への移行を検討している。 〔車両保守システムの概要〕 1. 業務の概要 (1) 車両のリース提供先の法人を顧客という。顧客との間でリース契約を結び，契約に基づいて車両を定額制で貸し出す。 (2) リース契約に基づいて新しい車両を購入することもあれば，リース契約終了後の車両を再リースすることもある。 (3) 契約のオプションとして車両保守を受託する。車両保守には，車検，法定点検（以下，点検という），整備などの保守区分がある。複数の保守区分を受託することもある。 (4) 外部の自動車整備工場などの委託先に保守区分の実施を依頼する。 (5) 受託ごとに一定期間内の保守区分を洗い出し，保守区分ごとに着手予定日を決めて予定表を作成する。予定表に従って手配を開始し，進捗を確認する。手配は，顧客への事前案内，入庫日の調整，委託先への実施依頼などから成る。手配は，定められた順に実施し，各手配の完了時に進捗を記録する。車検と整備を同時期に実施するなど，同じ車両の異なる保守区分を同時に実施することもある。この場合，保守区分ごとに同じ予定を立てる。 (6) 各車両の一つの保守区分は一つの委託先で実施する。委託先に複数の保守区分の実施を同時に依頼することもある。委託先から保守区分の実施結果を受け取り，進捗を記録する。実施完了の報告を受けると車両の出庫手続に移る。 (7) 車両ごとに，全ての車検，点検，整備の結果及び内容を記録簿に記録する。 2. テーブル構造 車両保守システムのテーブル構造を図1に，主な列の意味・制約を表1に示す。 図1 車両保守システムのテーブル構造（一部省略） 表1 主な列の意味・制約 3. 主な処理 オンライン時間帯は平日の9:00～19:00で，オンライン時間帯終了後の夜間にバッチ処理を行っている。オンライン処理及びバッチ処理は，それぞれ複数のアプリケーションプログラム（以下，APという）から成る。オンライン処理，バッチ処理の概要及び1日の処理行数を表2，3に，バッチ処理の実行スケジュールを図2に示す。表2，3中の二重引用符で囲んだ名前は図1中のテーブル名である。 表2 オンライン処理の概要及び1日の処理行数 表3 バッチ処理の概要及び1日の処理行数 図2 バッチ処理の実行スケジュール 4. 主なAPとテーブルの関係 車両保守システムの主なAPのCRUDを表4に示す。 表4 主なAPのCRUD 5. 障害の考え方 車両保守システムでは，障害を，AP障害，サーバ障害，メディア障害の3種類に分けて，それぞれの対策を立てている。 (1) AP障害は，APのバグ，メモリリークなどによってトランザクションの途中でAPが異常終了する障害である。 (2) サーバ障害は，RDBMSが稼働するサーバが，ハードウェア異常，電源遮断などによって機能停止する障害である。 (3) メディア障害は，RDBMSが使用する制御ファイル，表領域を物理的に格納するストレージの故障，破損によって入出力ができなくなる障害である。 〔RDBMSの主な仕様〕 1. 表領域 (1) テーブル及び索引のストレージ上の物理的な格納場所を表領域という。 (2) RDBMSとストレージとの間の入出力単位をページという。同じページに異なるテーブルの行が格納されることはない。 2. 参照時の専有ロック データ参照時にFOR UPDATE句を指定すると，ISOLATIONレベルにかかわらず，対象行に専有ロックを掛け，トランザクション終了時に解放する。 3. ログ ログは，データより先にログバッファからストレージに出力される。これをログ出力処理と呼ぶ。このとき，トランザクションのコミットはログ出力処理の完了まで待たされる。ログ出力処理は，次のいずれかの事象を契機に行われる。 (1) ログバッファが一杯になった。 (2) トランザクションがコミット又はロールバックされた。 (3) あるテーブルのデータバッファが変更ページによって一杯になった。 4. チェックポイント (1) チェックポイントは，指定されたログの出力量になった時点，又は指定された経過時間に従ってRDBMSが自動で取得する。 (2) チェックポイントは，コマンドを使用して任意に手動で取得することもできる。 5. 異常終了後のRDBMS再開始 RDBMSがサーバ障害によって異常終了した後のRDBMS再開始において，RDBMSは直前のチェックポイントから異常終了時までに完了していたトランザクションの更新データ及び異常終了時に未完了のトランザクションの更新データを自動で回復する。 具体的には，RDBMSはログを用いて，直前のチェックポイントから異常終了時までロールフォワードを行い，異常終了時点と同じデータベースの状態に回復する。その後，異常終了時に未完了だったトランザクションをロールバックする。その際，同じ行の更新順序を担保するために，ロールフォワードは，全てのトランザクションのログに対して，ログが出力されている順に逐次に実行される。トランザクション間の排他制御によって同じ行の同時更新がないことは担保されているので，ロールバックはトランザクションごとに並行して実行される。 6. ログなしモード ログなしモードとは，ログなしモードを指定されたテーブルの更新に対してログを出力しないモードである。テーブルごとにログなしモードを指定することができる。ログなしモードのテーブルを更新するAPで実行されるトランザクションは，ログを取得しないので，その分だけトランザクションの実行時間を短縮できる。ログなしモードのテーブルを更新するトランザクションには次の制約がある。 (1) ログなしモードのテーブルについて，トランザクション内で最初の更新時にテーブル全体に専有ロックを掛け，トランザクションがコミットされたときに専有ロックを解放する。 (2) ログなしモードのテーブルに専有ロックが掛けられている間は，チェックポイントを取得しない。手動でも取得できない。 (3) トランザクションがロールバックされたとき，ログなしモードのテーブルは使用不可になる。使用不可になったテーブルは，バックアップから復元するか，テーブルを削除して再度定義（以下，再定義という）する必要がある。 7. オンラインバックアップ・回復 データベースの稼働中に更新中のデータを含めて全表領域，全ログをRDBMSの稼働環境とは異なるストレージに複写してバックアップを取得する機能を備えている。 (1) 専用のコマンドを実行して，全表領域，全ログのバックアップを一定の多重度で別系のストレージに格納する。 (2) オンラインバックアップ取得開始後に発生したログは，ストレージに書き込まれると同時にそのログのバックアップが別系のストレージに格納される。 (3) メディア障害発生時には，ストレージ交換後，全表領域，全ログ，オンラインバックアップ取得後に発生したログのバックアップをストレージに復元し，異常終了後のRDBMS再開始と同様にロールフォワード及びロールバックによってデータを回復する。 8. ロックメモリ RDBMSがロックを掛けるときに排他制御のために使用するメモリ領域をロックメモリという。1行又は1テーブルにロックを掛けるごとにロックメモリにロック情報を登録し，そのロックを解放したらロック情報を削除する。ロックメモリは，同時にロックを掛ける行数，テーブル数から見積もって指定されたサイズで確保する。必要以上に大きなサイズで確保しようとするとメモリ領域が不足してRDBMSを開始できない。逆に，必要なサイズを確保していなければ，SQLの実行時にロックメモリの未使用領域がなくなり，ロックを掛けることができずにSQLがエラーとなる。 〔現行バッチ処理のバックアップ・リカバリ〕 1. バッチ処理のバックアップの取得スケジュール 車両保守システムでは，オンライン時間帯の終了後に次の要件でバックアップを取得している。バックアップの取得スケジュールを表5にまとめた。表5では，図2中の各契機でテーブル単位のバックアップを取得するテーブルに“○”印をつけている。 (1) 当日のオンライン処理のAPの実行結果を全て反映した状態に回復できること (2) 全てのバッチ処理のAP（以下，バッチAPという）の実行結果を反映した状態に回復できること (3) それぞれのバッチAPの実行中にメディア障害が発生した場合，速やかに対象テーブルを回復した後，バッチAPを再実行できること (4) 必要最小限のバックアップを取得すること (5) “点検抽出”，“車検抽出1”，“車検抽出2”の各APで更新する，“WK1”，“WK2”の各テーブルには，ログなしモードを指定すること (6) “WK1”，“WK2”の各テーブルは，ワークテーブルであり，バックアップの対象外とすること。これらのワークテーブルは，オンライン時間帯の終了後，バッチ処理を開始する前に再定義して使用する。 表5 バックアップの取得スケジュール（未完成） 2. サーバ障害時のRDBMS再開始 RDBMS再開始に備えて，バッチ処理中のチェックポイントに関する検証を行った。なお，T1～T6は図2中の各契機を示す。 (1) RDBMS再開始後の運用 “実施登録”APと“車検抽出2”APの実行中に，サーバ障害が発生した場合に，RDBMS再開始後にどのような対処が必要になるか，チェックポイントの取得状況に応じた対処を整理した。 契機T2でチェックポイントを取得している場合，“［a］”テーブルを［b］した後，“［c］”AP，“［d］”APを再実行する。また，“［e］”APも再実行する。その後，契機T3以降のバッチ処理を継続する。 契機T2でチェックポイントを取得していない場合，チェックポイントを取得している場合の対処を行う前に，“［f］”テーブルを［b］した後，“［g］”APを再実行しておく必要がある。 (2) RDBMS再開始に要する時間 契機T3でチェックポイントを取得後，契機T5からT6の間でサーバ障害が発生して再開始する状況を想定する。契機T3の後，サーバ障害が発生するまでの間にチェックポイントは取得していないものとする。追加・更新1行当たりロールフォワードに2ミリ秒，ロールバックに1ミリ秒を要するものと仮定して，RDBMS再開始のときにデータベースの回復に要する時間を見積もった。 最も時間が掛からないケースは，バッチAPでの更新が発生する直前にサーバ障害が発生した場合で，トランザクションのロールフォワードが必要なAPは［h］，ロールバックが必要なAPは［i］，回復に要する時間は［j］秒となる。最も時間が掛かる，理論上の最悪のケースは，更新処理のある全てのバッチAPがコミットする直前にサーバ障害が発生した場合で，トランザクションのロールフォワードが必要なAPは［k］，ロールバックが必要なAPは［l］，回復に要する時間は［m］秒となる。 〔バッチ処理のオンライン時間帯への移行〕 図2中のバッチ処理の実行順序（先行・後続の関係）を変更せずに，表3の全APをオンライン時間帯に移行するに当たって，APに必要な変更を検討した。 1. 移行後に発生する問題 表2，3中のAPを変更せずに，表3中の全処理をオンライン時間帯に移行すると仮定した場合，発生し得る問題を表6にまとめ，その対処を検討した。表6では，どのAPもISOLATIONレベルをREAD COMMITTEDとしてトランザクションを実行することを前提としている。 表6 発生し得る問題 2. 問題への対処 表6の問題に対処して，表2，3中のいずれかのAPを変更する。 (1) 問題1 この問題は，“報告作成”APが“【ア】”テーブルの更新対象行に対する【イ】を処理終了まで保持し，“契約登録”APがその解放を待つことで発生する。問題への対処として“報告作成”APを変更する。 (2) 問題2 “予約登録”AP，“進捗更新”APの処理内容を図3，4に示す。図3，4では特に断りのない限り，行の読込みには共有ロックが掛かるものとする。 この問題は，“予約登録”APが点検の出庫予約の受付中に，“進捗更新”APで同じ受託の“予定表”テーブルの現手配#を5（実施完了予定確認）から【ウ】に変更し，その後で“予約登録”APが同じ現手配#を【エ】に変更することで発生する。手配#の順序の制約及びISOLATIONレベルを変えずに問題に対処するために，一つのAPを変更することにした。 受け付けた1回の入庫又は出庫の予約について，契約#，受託#を指定して，次を行う。 ① “受託”テーブルの行を，専有ロックを掛けて読み込む。 ② “予定表”テーブルから対象行を読み込む。 ③ “進捗”テーブルに行を追加する。 ④ “予定表”テーブルの行の現手配#が“進捗”テーブルの手配#よりも小さい番号であれば，⑤に進み，それ以外の場合は⑥に進む。 ⑤ “予定表”テーブルの行の現手配#に手配#と同じ値を設定して更新する。 ⑥ コミットを発行する。 図3 “予約登録”APの処理内容 “実施結果”テーブルから報告YMDが当日に等しい行を読み込み，行ごとに次を行う。 ① “進捗”テーブルに，完了確認の手配#に対応する行を追加する。 ② “予定表”テーブルの該当行の現手配#に完了確認の手配#を設定して更新する。 ③ 保守区分が車検で，“進捗”テーブルの手配#が完了確認の場合は，“車両”テーブルの車検満了YMDを次回の車検満了日に更新する。 ④ “記録簿”テーブルに行を追加する。 ⑤ コミットを発行する。 図4 “進捗更新”APの処理内容 (3) 問題3 処理時間の短縮を図るため，“点検率更新”APを同じ報告YMDの中で重複していないソート連番の範囲を指定することで，ジョブを多重処理できるように変更した。“点検率更新”APの処理内容を図5に示す。 ① 報告YMD，開始連番，終了連番を引数で受け取る。 ② “実施結果”テーブルから指定された引数に報告YMDが一致し，開始連番から終了連番までの間の行をソート連番順に読み込む。 ③ 読み込んだ行ごとに，契約#が一致する“点検率”テーブルの行を読み込み，実績点数に点数を加えて更新する。 ④ ②，③を繰り返し，結果行を全て処理したら，最後に一度だけコミットを発行する。 図5 “点検率更新”APの処理内容 図5の処理内容変更案をレビューしたところ，APの多重処理によって，デッドロックが発生するおそれがあるとの指摘を受けた。デッドロックの発生を防ぐために，次の二つの案を検討した。 案1 図5中の処理内容を修正する。 案2 表3中の“点検率更新”AP以外のAPを一つ変更する。 〔移行後の運用設計〕 1. メディア障害への対策 メディア障害への対策として，オンライン時間帯に何回かのオンラインバックアップを取得し，オンライン時間帯終了後にRDBMSを停止してデータベース全体のオフラインバックアップを取得することにした。オンライン時間帯にメディア障害が発生した場合のリカバリでは，ログを用いて回復に要する時間がリカバリ時間の多くを占めるので，回復に要する時間を見積もり，オンラインバックアップの取得頻度を求めた。 (1) 見積りの前提 ① 表2，3の1日の処理行数の合計がログ量に等しいと仮定する。1日のログ量は，処理行数を合計して，追加に190、000行，更新に100、000行になる。 ② 全テーブルの1ページ当たりの平均行数を20行，1ページ当たりのストレージへのI/O時間を10ミリ秒とし，ストレージへのI/O以外のCPU処理，索引探索，ネットワーク通信などに掛かる時間を考慮しないものとする。 ③ ログによる回復では，追加については，ページ中の行数が平均行数を超えるまではバッファ内で処理し，更新については，バッファヒット率を0%として試算する。 (2) 見積りの結果 1日の全てのログによる回復時間は，追加では，【オ】ページのI/Oが発生するので【カ】秒，更新では，【キ】ページのI/Oが発生するので【ク】秒を要する。ログ量が均等になるタイミングでオンラインバックアップを取得し，ログによる回復時間を5分以内にするためには，1日に【ケ】回のオンラインバックアップを取得すればよい。 2. バッチAPの運用変更 ログなしモードのテーブルを対象に実行していた“点検抽出”，“車検抽出1”，“車検抽出2”の各APをオンライン時間帯に移行する影響について検証した。ログなしモードのテーブルを更新するAPをオンラインで実行すると，開始から完了までの間，チェックポイントが取得されない。この間にサーバ障害が発生すると，RDBMS再開始のとき，【コ】が大量に行われるおそれがあり，回復に要する時間が長期化するという問題がある。 そのため，ログなしモードの指定を取りやめることにした。ただし，APの処理内容は変更しない。また，“WK1”，“WK2”の各テーブルは，バックアップ対象外のままとする。この場合，運用の変更に当たって，次の2点のリソースの見直しが必要となる。 (1) “WK1”テーブルと“WK2”テーブルのログが新たに出力されるので，ログの格納領域が不足しないよう見直す。 (2) “WK1”テーブルと“WK2”テーブルの【サ】ので【シ】が不足しないよう見直す。 設問1 〔現行バッチ処理のバックアップ・リカバリ〕について答えよ。 (1) 表5中の太枠部分内に，“○”を記入して表を完成させよ。ただし，太枠部分以外のバックアップを取得する契機には全て“○”を記入済みである。なお，バックアップを取得しないテーブルとその契機は，空欄のままとすること。 (2) “2. サーバ障害時のRDBMS再開始”について答えよ。 (a) 本文中の［a］～［g］に入れる適切な字句を答えよ。 (b) 本文中の［h］，［i］に入れる適切なAP名を全て答えよ。ここで，対象となるAPがない場合，“なし”とすること。また，［j］に入れる適切な秒数を答えよ。 (c) 本文中の［k］，［l］に入れる適切なAP名を全て答えよ。ここで，対象となるAPがない場合，“なし”とすること。また，［m］に入れる適切な秒数を答えよ。 設問2 〔バッチ処理のオンライン時間帯への移行〕について答えよ。 (1) 問題1への対処について答えよ。 (a) 本文中の【ア】，【イ】に入れる適切な字句を答えよ。 (b) “報告作成”APの変更内容を40字以内で具体的に答えよ。 (c) (b)の変更を行った場合，AP障害の発生後に行う再処理において，“報告”テーブルに，追加済みの行と報告内容が同一の行の追加は発生するか。発生の有無を判断し，答案用紙の“有”又は“無”のどちらかを○で囲み，判断の根拠を40字以内で具体的に答えよ。 (2) 問題2への対処について答えよ。 (a) 本文中の【ウ】，【エ】に入れる適切な手配#を答えよ。 (b) 変更するAP名を答えよ。 (c) (b)で解答したAPの変更内容を35字以内で具体的に答えよ。 (3) 問題3への対処について答えよ。 (a) レビューで指摘を受けたデッドロックは，どのような状況で発生するか。30字以内で具体的に答えよ。 (b) 案1について，図5の処理内容の修正内容を20字以内で具体的に答えよ。ただし，コミットの発行頻度は変更しないものとする。 (c) 案2について，変更するAP名を答えよ。また，変更内容を30字以内で具体的に答えよ。 設問3 〔移行後の運用設計〕について答えよ。 (1) 本文中の【オ】～【ケ】に入れる適切な数値を答えよ。 (2) 本文中の【コ】，【シ】に入れる適切な字句を答えよ。また，【サ】に入れる適切な文を20字以内で答えよ。