運用に強いDB設計.1(復旧)

はじめに

データベースの設計の話と言えば、正規化やリレーション、データフローダイアグラムなど色々とあります。
でもそういった内容はネット上に良質な情報がごろごろ転がっているのでそちらをご参照を。
もちろんこれらの設計が行われていることが運用に強いＤＢの最低条件です。

これから伝えたい内容はもっと泥臭い内容です。
システムの世界では論理的で理想的な設計で完結に表現されますが、それを動かす現実世界の不純物をどう扱うかという設計です。
色々とあるので一度には伝えられませんので、何回かに分けて伝えていきたいと思います。
今回はデータの復旧と犯人捜しについてです。
※説明しきれない物は部分的にキーワードだけ置いておき機会があったら説明します。

これから技術者になる方へ

これから話す話は、実際にシステムを作り上げ運用を経験してみないと分からない領域の話じゃないかなと思います。
なるべくシステム開発をやったことが無い人にも、システム開発に入るとこんな事も考えているよと言う事が伝われば良いなと思います。
知って欲しいのはシステムは作って終わりではなく、動いてからが勝負と言う事です。

サービスのはじまり

サービスがリリースされるとシステムは動き出します。
例えばそれが1000人が利用する様なサービスの場合を想定してみましょう。

そのシステムを検証するために技術者10人で毎日8時間一ヶ月(20日)テストを行ったとして、延べ1600時間分の稼働テストはしているわけです。

このテストに掛ける人数と期間は大きいわけではありません、一般的にもっとテストは行われますがイメージをつかみやすくするための例です。

これが1000人が操作し始めると、たった1.6時間で技術者が一ヶ月かけて行ったテストと同じ時間システムは稼働されます。
それも、技術者が思いもしなかった操作方法によって!

この辺の裏事情を知っていくと、リリースと同時に不具合を起こして詫び石をばら撒いているゲーム等もとても寛大な気持ちで見守れるようになります。

データはどこに消えた？

サービスが開始されると様々な問合せが来ます。
そしてある日、こんな問合せが来ます。
「登録したはずのデータが出てこないんだけど！」

このとき多分運用をしている人であれば、さっと次の様な事を考えると思います。

1. 本当に登録した？　←真っ先に思ってしまう(笑)
2. データは登録されているのに検索方法間違えてない？
3. 登録の失敗などのメッセージが出てたのに無視してない？
4. 間違って誰か削除や更新してない？

1.～3.について

考え得る対処方法は操作ログと、ジャーナルファイルかな
検索に関する問題も主にアプリケーション側の問題が多いので割愛します。
若干更新されたことによってリレーションが切れたなどの場合もありますが、その辺は以降の「間違って誰か削除や更新してない？」で犯人を捜せます。
エラーメッセージなんかも、データベースエラーのハンドリングとユーザへの見せ方になるのでこれも次の機会に

間違って誰か削除や更新してない？

ここでデータベース設計時の話が出てきます！
今回の話題はこれです！
こういった問合せに対応するために、私はデータベースの設計段階で事前にいくつか仕掛けを打つようにしています。

まずは、SQLのDMLと言えば

• SELECT
• INSERT
• UPDATE
• DELETE

この中で特に注意すべきは「DELETE文」です。

これを、実行するとデータベースから跡形もなくデータが消えます。
と言う事は「消した！」と言う事実すらも消えてなくなります。
これは何かあったときの調査の際に非常に困った問題になります。
また、こういった問い合わせでは、調査だけでなくデータの復旧という作業がセットでついてくることがほとんどです。
そこで調査復旧するための対策が必要になってきます。

履歴情報を持つ

これに対する解決方法の一つは、データの履歴を取ると言う方法です。
具体的にはINSERT/UPDATE/DELETEが行われたタイミングで一つ前の操作の状態を記録しておくという方法です。
これをしておけば、間違って消してしまったとしても最新の履歴が消す前の状態と分かるので調査や復旧が可能です。

履歴の取り方もいくつかありますが、私が主に行っているのは次の様な対処です。

1. データベースの中に履歴テーブルを持つ
   1. アプリで登録時に履歴テーブルにも同じ内容を記録する
      • 注意点は履歴は業務トランザクションとは関係なので、別トランザクションで非同期で処理する等
        本登録が終わった後に非同期の方の登録がこけた場合のリカバリをどうするかを検討しておかなければならない。
   2. データベーストリガー等で、データベースの機能を使って履歴テーブルに記録する
      • これもトリガーの失敗するケースもあるので注意を
2. 一つ前の情報をログファイルなどに出力しておく
   • ファイルに出力しておくことは簡単だが、複数人のアクセスを1ファイルに書き込もうとすると色々問題がある。
     何の単位で、何ファイルに分けて管理するなどの検討は必要

いずれにしても、データが何らかの方法で復旧できる仕組みというのは大事です。
履歴を持っていれば、履歴の情報からデータを復旧することが出来ます。
持ってないと最悪、前回取ったデータベースのバックアップから再開なんて事にもなりかねません。

復旧できないと、エンドユーザに「もう一回登録してください」って事になります。
運用してみると分かりますが、このお願いはエンドユーザに非常にしづらいです。
聞こえはしませんが、電話の向こうから心の舌打ちが直接頭の中に響いてきます。
なるべく、エンドユーザではなく、エンドユーザと技術者の間にいる情シスなどにリカバリを報告できる方が気が楽です。(闇)
データ復旧が出来るなら復旧しましたの報告だけで済む場合が多いからです。

ちなみに、私は1.1.の方法をとることが多いです。
履歴テーブルに付加情報（どの画面のどの操作で履歴が起きた？等々）を持たせたりと何かと小回りがきくからです。

DELETE文は使わない

最近は主にこれです。
DELETE文を使わなければ、情報が無くなることもないので調査が非常に楽です。
ユーザから削除と言うアクションが行われた場合に、レコードに「削除フラグ」と言う項目を持たせておきます。
削除処理がされたときにその項目にフラグを立てます。
フラグの立ったレコードは以降使われないと言う仕組みです。
この場合UPDATE文しか使わないのでデータが不具合などでも消されることはありません。
最悪のフルデリート時限爆弾みたいなアプリ不具合トラップは回避できます。

何らかの不具合でWhere句の利かないDELETEが走ったりすることがあります。
データがまるっと無くなることになるので被害は甚大です。
ならば、リスクは少しずつでも減らしておくと精神衛生上も良いです。

ただ、デメリットもあります。
それは各処理で扱うデータの取得条件に必ず

WHERE 削除フラグ = False (削除されてないレコードだけね)

と言う条件を付記する必要があると言うことです。
この辺を開発で開発者に意識させず行うか、と言うことは開発フレームワークの話に発展しそうなのでここまでにします。
こんなデメリットがあっても、システムが動いてからの事を考えると、トータルではメリットの方が大きいと言う現実もあると言う事です。

最大のメリットは
「間違って消しちゃった！」とユーザから連絡が入っても受話器を首と肩に挟みつつ、簡単なUPDATE文一発（削除フラグの解除）で「はい、戻しましたよ。」と即座に対応できること。
聞こえはしませんが、電話の向こうから「やだイケメン」と言う賞賛の声が直接頭の中に響いてきます。

注意：戻したことによる大惨事のケースもあるのでちゃんとシステムのデータのERDは頭に入れて運用しよう。

もう一つのメリット
企業の基幹システムになってくると、必ずシステム監査という物が入るようになります。
その場合

• 何故削除されたのか
• 何故連番に抜け番があるのか？

等を説明する材料が必要になる事があります。（J-SOX法などをちょっと調べてみると良いかも知れません）
要件定義時にこの観点からもこの方式を進めたりしています。
これ言うと、ちょっと「お！」って思ってもらえることもある。

真実はいつも一つ！

さて、上で話した様な対処を行っておけば一先ず「登録したはずのデータが出てこないんだけど！」の登録が確認できれば、後は削除されたかどうかは調査可能となったわけです。
ここで登録されてないとなれば、上でちょっと触れた操作ログやジャーナルの出番になるわけですが今回の趣旨ではないので割愛します。
登録されていた事が確認された後の話をします。

登録されていたとすれば後は何故表示されないかの調査です。
履歴や、削除フラグで対処を取っておけば削除されていないかの判断は簡単です。
「あ、削除されてますね」と返しましょう。

でも、こう言い換えされることでしょう。
「削除した覚えがないんだけど！」

そこで、もう一つ手を打っておきます。
それは、レコードに「何時」「誰が」「何の機能で」「何をした」の情報を持たせることです。
この情報を持っていると、以下の様に答えることが出来ます。
「○月○日○時○分に、△△さんが□□画面で、削除したみたいですね。
　何故その操作をしたのか聞いてみて頂けませんか？」

でも、状況証拠はそろっても犯人はしらを切る場合もありますけどね(笑)。
そのときは情シスの人とだけわかり合いましょう。

もう一つ答えに困るケースは、システムの処理上整合性を保つために自動的にユーザの手を介さずにデータが更新、削除されるケース。
こればっかりはユーザが知らないところで行われているので、何故そうしなければならないのかを含め説明が必要になります。

後は、システム操作ではどうにも行かなくなったときに、データベースを直接修正することもあるでしょう。
その場合に備えてレコードに「修正コメント」みたいな項目を置いておくのも良いかもしれません。

復旧をしよう！

データが履歴管理されていると普及は容易です。
更新したいテーブルと履歴テーブルをジョインして、キーでセレクトして更新すれば一発で前の状態に戻ります。
この辺はSQLのテクニックの話なので、ヒントだけおいておきます。

UPDATE U1
       SET U1.Col1 = H1.Col1
          ,U1.Col1 = H1.Col2
           :
      FROM UpdateTable  U1
INNER JOIN HistoryTable H1 ON U1.Key = H1.Key
     WHERE U1.Key = …

まとめ

裏取りをするための情報を残すこと

• 履歴を取る

犯人を特定する情報を残すこと

• 登録者、更新者情報を取る

この、二つをするだけでも大分運用が楽になっているという話でした。
今回の対処が最適解というわけではありません。
システムの特性にもよります。

最後に

もともとは、若手技術者からなんでこんな事してんの？と言う質問に答えた内容をまとめた物です。
知って欲しかったのは、開発案件に参加したときに「何か面倒くさいことしてんな」と思ったときにその裏に隠れた意味などを考えて欲しいなという事です。
システムを設計する上で、動かした後の活動も事前に考えておかないと後が大変だよと言う意図も込めて…システム開発って、開発が終わったその後も含めて泥臭いんだよ、イメージのキラキラばかりじゃないよと。