読者です 読者をやめる 読者になる 読者になる

文系プログラマによるTIPSブログ

文系プログラマ脳の私が開発現場で学んだ事やプログラミングのTIPSをまとめています。

DBViewerのmetadata lockの罠

mysql

罠シリーズ。

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を挙げてるしね。

■状況の再現方法
1,手動コミットモードの状態で、テーブルのタブを開く。何もせずそのままタブを閉じる。
2,1の状態で1で選択したテーブルに対してDDLを発行する。waitになる。
3,1で選択したテーブルに対して、select/update/deleteを発行する。2に対してwaitになる。
つまり、1→2→3、という順に順番待ちの行列ができあがる・・

手動コミットモードの時に1のようにテーブルを開くと、バックグラウンドで set autocommit=0; が実行される。これは正常な動作。
しかし、タブを閉じた時、commit; も rollback; もされない。つまりトランザクションが張りっぱなしの状態で放置される
そしてこの状況を引き起こした担当者は昼食を食べに1時間以上席を外す・・・DBチームは泣く泣くMySQLのサービスを再起動する・・
このmetadata lockの状態はshow processlistで犯人を特定できる情報を得られないので、全プロセスを1個づつkillするか、サービス再起動以外に対応することができない。

そもそも「手動コミットモード時にタブを閉じた時にcommit; or rollback; が実行されない」という動作がおかしいやめた方がいいのではないだろうか。
恐らく複数のタブを開いて、それらを同一トランザクション内で更新したいのだと思うが、その間はずっとトランザクション張りっぱで、周りに迷惑かかるのは必至なのではないか。
あと、ちょっと思ったんだけど、手動コミットモードのメリットがイマイチ解らない。
通常本番環境でDBのツールを使って接続するなんて事はほぼ無い(少なくとも自分の周りでは)。
つまり、大抵は開発環境の中で使われると思われる。開発環境であれば、本番環境のようなシビアなデータ操作は求められない。
だったらこんなリスクを負わずに、素直に自動コミットモードを使えばいいのでは?と思う。
手動コミットモードのように、トランザクションを張らないといけない作業をDBのツールを使ってやろうとするな、と。SQLを書け、と。

MySQL5.0の時はmetadata lockとか無かった気がするんだけど、5.5になって動作が変わったのかな。