データベースの運用で避けられないのが、ロック競合によって起こるシステムトラブルへの対応です。「2時までに終わるはずのバッチ処理が朝になっても終わっていない」とか「負荷が高いわけでもないのにシステムが無応答になっている」といったトラブルが発生したとき、DBエンジニアはそれがロック競合によるものなのかどうかを切り分けて、適切に対処しなければなりません。これまでInnoDBはロック競合に対してほとんど打つ手がなかったのですが、最近ようやく対処方法がでてきました。今日はその手順を確認していきたいと思います。今回ご紹介する手順は、MySQLの以下のバージョンを対象にしています。5.4いきなりハードルを上げてしまって申し訳ありませんが、バージョン5.0以下や素の5.1では使えませんのでご注意ください。1.0.3で行ったものです。information_schemaの新しいテーブルInnoDBの最新バージョンでは、information_schemaデータベースに新しいテーブルが7つ追加されています。sec)もうお分かりかと思いますが、これらのテーブルからロック状態を取得することができるというわけです。今回はこのうち3つを使います。INNODB_TRXから確認していきましょう。sec)INNODB_TRXは現在実行中のトランザクションを表示するテーブルです。InnoDBが内部で持っているトランザクションID(trx_id)やトランザクションの開始時刻(trx_started)、実行中のSQLがあればそのSQL文(trx_query)が出力されます。processlistで表示されるスレッドID(trx_mysql_thread_id)との対応づけができるところもポイントです。次はINNODB_LOCKSです。sec)INNODB_LOCKSはロック競合を起こしているトランザクションの情報を表示するテーブルです。待たせている方と待たされている方の両方が出力されます。ロックを取得していても他のセッションと競合していないトランザクションは出力されません。ここでは、どのテーブル(lock_table)のどのインデックス(lock_index)をつかんでいるかが分かるようになっているところがポイントです。3つ目は、INNODB_LOCK_WAITSです。sec)INNODB_LOCK_WAITSはどのトランザクションがどのトランザクションを待たせているのかを出力するテーブルです。blockingが待たせている方、requestingが待たされている方になります。ロック競合を表示するSQLこのように、InnoDBの最新バージョンではロック競合を出力するための部品が十分に揃っています。あとは解析に必要な情報をつなげて出してあげればよいというわけです。以下のようなSQLになるでしょう。blocking_id&#92;G実行結果(横)です。sec)実行結果(縦)です。7788のレコードを更新しようとしているといった情報を読み取ることができます。@IT)、ほぼ完璧といってよいレベルです。それから、今回の例のようにデータベースにTCP/IP経由で接続している場合は、TCPのポート番号からクライアントプロセスを特定することができます。6000/mysqldこの例では8番のクライアントはmysqlコマンドラインツールであり、プロセスIDは6105番であることが分かります。ここまで分かればあとは該当のプロセスをkillするといった暫定対処や、プログラムを修正するといった本格対処をとることができます。DatabaseにあってInnoDBにないものは何かと考えると、今回のようなロックの可視化とデッドロックのロギングが常に上位に来ます。ExpoでInnoDBの会議に出席したのですが、周りの参加者は案の定Oracleエンジニアがほとんどでしたし、みな口々にロックが見えるようにしろと要望を出していました。5.4で実装される運びとなり、いよいよMySQLも企業向けの利用が進むことになるのかもしれません。schemaのテーブル群は実装されていませんのでご注意ください。(PDF)をご参照ください。17:40いつも拝見しております。すいません教えてください。blocking_infoにcommitが(requesting_infoにupdate)くるというこはどういうことなんでしょうか。20:57そのままですが、COMMITをしているセッションがまだ抱えているロックを、UPDATEをしているセッションが要求しているということですね。5なので書き込みが遅い、LVMスナップショットが存在していて遅い、ストレージのレイヤで同期レプリケーションをしていて遅い、準同期レプリケーションをしていて遅い、binlog_cache_sizeが足りていない、innodb_log_buffer_sizeが足りていない、アプリケーションの作りがおかしいといった点が考えられます。参考に1トランザクション分の処理を抜き出して何をやっているのか調べてみる、アプリケーション開発者を問い詰める、といったところから始めるとよいと思います。18:09ありがとうございました。%utilで常に90-100%の状態でした。短時間でたくさんのcommitを行うとこうなっちゃうんですね。開発にcommit減らせないか確認します。DBエンジニアをやっています。DatabaseとMySQL。当サイトでは、第三者配信による広告サービスを利用しています。使用することがあります。このプロセスの詳細やこのような情報が広告配信事業者に使用されないようにする方法については、ここをクリックしてください。