where句なしでupdateしちゃったとき

RDBMSでwhere句なしのupdate/deleteをする機会は極めて限られ、本番環境でついうっかりやってしまうと夜逃げしたくなるレベルである。しかしやっちまったものは仕方ないので、どうすれば少なくとも無かったことにする状態に出来るのかを考える。

環境

Oracle Database Express Edition 11g Release 2をCentOS 6.4 x86_64上で動作
Oracle SQL Developer 3.2
SQL*Plus: Release 11.2.0.2.0 Production

準備

下記をテスト用の生贄テーブルとして用意する。

drop table hoge purge;
create table hoge 
(
  hoge_id integer not null,
  hoge_value varchar2(16),
  constraint hoge_pk primary key (hoge_id)
);

テストデータを100万件入れる。

insert into hoge
select 
  rownum,
  dbms_random.string('X', 16)
from
  (select rownum from all_catalog where rownum <= 1000),
  (select rownum from all_catalog where rownum <= 1000);
commit;

基礎データの計測

まず、100万件insertにかかる時間、全件updateにかかる時間、全件updateをrollbackする時間を計測する。

全件updateは下記の通り。あらかじめバッファキャッシュをクリアした上で行う。

alter system flush buffer_cache;
update hoge set hoge_value = '0000000000000000';

rollbackはそのまま。

rollback;

種類	時間（秒）
insert	61.313
update	38.047
rollback	49.437

まず、理論的には全件updateをしてしまってもrollbackすれば元に戻るのがまともなRDBMSの良い所である。では、rollbackにはどのくらいの時間がかかるのだろうか。それを計測したのが上記の表である。もちろん、マシンスペックなどによってこの数値は変わるので、数値の絶対値には余り意味が無い。ここで重要なのは、rollbackにかかる時間が全件updateそのものの時間を上回っている点にある。いくらrollbackが出来るといっても、場合によっては復旧に甚大な時間やリソースを食う可能性がある。よって、やっちまったらなるべく速く行動に移す必要がある。

やっちまったときに取る行動、その時発生する状況

超高速でクライアントを強制終了させる。

SQL*Plusの場合

SQL*Plusを強制終了させる。

より正確には、Oracleのセッションを強制的に切断する。この場合、トランザクションは即座にロールバックされる。

セッションが停止すると、そのセッションのアクティブ・トランザクションがロールバックされ、そのセッションが保持していたリソース(ロックやメモリー領域など)がただちに解放されて、他のセッションで使用可能になります。
Oracle Database管理者ガイド 11g リリース2 (11.2) セッションの停止

そのため、やっちまったら即SQL*Plusを強制終了すべき、ということになる。基本的にはSQLクライアントの終了は、Oracleのセッションの停止と同義になると考えられる。ただし、俺が使うのはSQL*PlusとSQL Developer（こちらは後述）くらいなので他のすべてのツールについても同じことが言えるかどうかは不明である。

SQL Developerの場合

「タスクの取消」ボタンを押す。下図キャプチャ参照。

このボタンを押すと、下記のようなエラーが返ってくる。ORA-01013でぐぐってみると長時間かかるクエリが途中で打ち切られる時に良く目にするようである。なので、「タスクの取消」ボタンを押すとSQL Developerがタイムアウトによるキャンセルと同等のことをやってくれている、と推測できる。

エラー・レポート:
SQLエラー: ORA-01013: ユーザーによって現行の操作の取消しがリクエストされました。
01013. 00000 -  "user requested cancel of current operation"
*Cause:    
*Action:

まぁタブンjava.sql.Statement#cancel呼んでるだけだとは思うけど。より確実にしたい場合は、セッションを切断した方が良いと思う。ただまぁ、非常事態になると細かい手順なんて思い出してるヒマないんで「速攻でSQLクライアントを強制終了する」ってだけ覚えておくのが良さそうな感じである。

ALTER SYSTEM KILL SESSION

可能であれば管理者権限のコマンドでセッション停止も視野に入る。というか、よくわっかんなくなったらDBの管理者にセッションを殺害して頂く事になるんではなかろうか。

Oracle Database管理者ガイド 11g リリース2 (11.2) セッションの停止に詳しい手順が書いてある。
SQL Developerの簡易的なデータベース監視機能 - kagamihogeのblog SQL Developerからやる場合はコチラの「セッションのモニター」を参照。怪しいupdateを実行してるセッションを探して停止させる操作になると思う。

セッション停止時のバッファキャッシュの状況を確認

データについては、最悪rollbackまたは即座にSQLクライアントを終了させることで、無かったことに出来ることがわかった。では、バッファキャッシュへの影響はどうか。トランザクションを途中で打ち切ることになるので、それ以上メモリをムダ使いすることは無さそうである。ここでは、SQL*Plus強制終了時のバッファキャッシュの動きを追うことで確認してみる。

下記のようなSQLでバッファキャッシュを確認する。

select b.obj, o.object_name, count(*) blocks, b.state, b.lru_flag, b.tch
  from x$bh b
  left outer join dba_objects o
    on b.obj = o.object_id
  where b.state <> 0
  group by b.obj, o.object_name, b.state, b.lru_flag, b.tch
  order by blocks desc;

参考：続 X$BH に関する検証　その１ | Insight Technology, Inc.

まず、全件update後のキャッシュを確認する。hogeテーブルで約3300ブロック使用しているようで、全ブロックがキャッシュにのっちゃってるであろうことが伺える。

次に、キャッシュをクリアする。

alter system flush buffer_cache;

hogeテーブルはどこかへと消えうせる。

SQL*Plusで全件updateを実行してすぐに強制終了させたあと、キャッシュを確認する。さすがに無傷とはいかないまでも、全ブロックと思しき約3300ブロックよりかは少ないブロック数に留まっている。

よって、即効でSQL*Plusなどのクライアントを落とせばダメージは最小で済む、と考えられる。

自動コミットモードはオフにすべき

割とどーにもならないのが自動コミットモードをオンにしていた場合。最も、Oracleの場合フラッシュバッククエリが使えればなんとかなることもあるけれど。

いちいちcommitするのがめんどくさくなってオンにしたりとか、Excelライクに使いたくてオンにしたりとか、稀にオンにしちゃってることがある。もちろん理解して使う分にはcommitの手間が省けて便利なのだが、いざというとき地獄が見えてしまうので原則的に自動コミットモードはオフにしておくべきでしょう。

kagamihogeの日記

kagamihogeの日記です。