罠シリーズです〜
eclipseのpluginであるDBViewer、結構有名なプラグインです。
今回はDBViewerとMySQL5.5の罠を紹介。
DBViewerには、自動コミットモードと手動コミットモードが選択できる。
手動コミットモードには大きな罠が潜んでいる・・
罠の内容
対象テーブルにmetadata lockがかかり、drop系SQL(drop database, drop table, drop index etc...)がmetadata lockでwait状態になる。
a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends
http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html
他トランザクションが終了するまで、DataDefinitionLanguageのSQLは使用不可だよ、と言ってますね。前述のdropはDDLの一つでしかないが、開発時によくあるケースかな。
For example, if a table t1 is in use by a transaction, another session that attempts to execute DROP TABLE t1 will block until the transaction ends.
http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html
例としてもdropを挙げてるしね。
状況の再現方法
１，手動コミットモードの状態で、テーブルのタブを開く。何もせずそのままタブを閉じる。
２，１の状態で１で選択したテーブルに対してDDLを発行する。waitになる。
３，１で選択したテーブルに対して、select/update/deleteを発行する。２に対してwaitになる。
つまり、１→２→３、という順に順番待ちの行列ができあがる・・
手動コミットモードの時に１のようにテーブルを開くと、バックグラウンドで set autocommit=0; が実行される。これは正常な動作。
しかし、タブを閉じた時、commit; も rollback; もされない。つまりトランザクションが張りっぱなしの状態で放置される。
そしてこの状況を引き起こした担当者は昼食を食べに１時間以上席を外す・・・DBチームは泣く泣くMySQLのサービスを再起動する・・
このmetadata lockの状態はshow processlistで犯人を特定できる情報を得られないので、全プロセスを１個づつkillするか、サービス再起動以外に対応することができない。
そもそも「手動コミットモード時にタブを閉じた時にcommit; or rollback; が実行されない」という動作がおかしいやめた方がいいのではないだろうか。
恐らく複数のタブを開いて、それらを同一トランザクション内で更新したいのだと思うが、その間はずっとトランザクション張りっぱで、周りに迷惑かかるのは必至なのではないか。
あと、ちょっと思ったんだけど、手動コミットモードのメリットがイマイチ解らない。
通常本番環境でDBのツールを使って接続するなんて事はほぼ無い（少なくとも自分の周りでは）。
つまり、大抵は開発環境の中で使われると思われる。開発環境であれば、本番環境のようなシビアなデータ操作は求められない。
だったらこんなリスクを負わずに、素直に自動コミットモードを使えばいいのでは？と思う。
手動コミットモードのように、トランザクションを張らないといけない作業をDBのツールを使ってやろうとするな、と。SQLを書け、と。
MySQL5.0の時はmetadata lockとか無かった気がするんだけど、5.5になって動作が変わったのかな。
