MySQL InnoDBでのロック競合解析

前提

MySQL5.1の場合はInnoDB pluginを有効にする必要があります。

解析方法

information_schemaデータベースにある以下の3テーブルを利用して解析を行います。

1. INNODB_TRX
   現在実行中のトランザクションを表示するテーブル
2. INNODB_LOCKS
   ロック競合を起こしているトランザクションの情報を表示するテーブル
3. INNODB_LOCK_WAITS
   どのトランザクションがどのトランザクションを待たせているのかを出力するテーブル

ロック競合を表示するSQL

select t_b.trx_mysql_thread_id blocking_id,
       t_w.trx_mysql_thread_id requesting_id,
       p_b.HOST blocking_host,
       p_w.HOST requesting_host,
       l.lock_table lock_table,
       l.lock_index lock_index,
       l.lock_mode lock_mode,
       p_w.TIME seconds,
       p_b.INFO blocking_info,
       p_w.INFO requesting_info
from information_schema.INNODB_LOCK_WAITS w,
     information_schema.INNODB_LOCKS l,
     information_schema.INNODB_TRX t_b,
     information_schema.INNODB_TRX t_w,
     information_schema.PROCESSLIST p_b,
     information_schema.PROCESSLIST p_w
where w.blocking_lock_id = l.lock_id
  and w.blocking_trx_id = t_b.trx_id
  and w.requesting_trx_id = t_w.trx_id
  and t_b.trx_mysql_thread_id = p_b.ID
  and t_w.trx_mysql_thread_id = p_w.ID
order by requesting_id,
         blocking_id
\G

実行結果

*************************** 1. row ***************************
    blocking_id: 15
  requesting_id: 24
  blocking_host: localhost:45171
requesting_host: localhost:45180
     lock_table: `user`.`users`
     lock_index: `PRIMARY`
      lock_mode: X
        seconds: 3
  blocking_info: NULL
requesting_info: SELECT * FROM `user`.`users` WHERE id = 1 for update
1 rows in set (0.01 sec)

• 15のスレッドが24のスレッドを3秒待たせている。
• 15のスレッドがuser.usersテーブルの主キーを排他ロック（X）している。
• 15のスレッドはアイドル状態である。
• 24のスレッドはid = 1をレコードを参照（for update）しようとしている。

といった情報が読み取れます。