InnoDBで行ロックを取得してるSQLを調べる

InnoDBでSELECT FOR UPDATEでセッションが溢れて、原因のpidはわかるけど、ロック取得しているSQLがなんとかとれないかなと調べてみた。

MySQL5.7以降であれば下のSQLでとれるかも。たぶんいけるはず。

SELECT 
    lo.*,
    t.thread_id AS blocking_thread_id,
    GROUP_CONCAT(CONCAT('\n',LPAD('', 25, ' '),event_id,':',sql_text) ORDER BY event_id) blocking_thread_sqlinfo
FROM
    (SELECT 
        GROUP_CONCAT(CONCAT('\n',LPAD('', 25, ' '), waiting_pid, ':', waiting_query)) wait_pidlist,
            blocking_pid,
            MAX(sql_kill_blocking_query) killsql,
            blocking_query
    FROM
        sys.innodb_lock_waits a
    LEFT JOIN (SELECT DISTINCT
        waiting_pid AS wpid
    FROM
        sys.innodb_lock_waits) b ON a.blocking_pid = b.wpid
    WHERE
        wpid IS NULL
    GROUP BY blocking_pid) lo
        LEFT JOIN
    performance_schema.threads t ON lo.blocking_pid = t.processlist_id
        LEFT JOIN
    performance_schema.events_statements_current USING (thread_id)
GROUP BY 2\G

試す

下を同時に動かして２つのレコードロックと待機を発生させる

• session 1 :begin;SELECT * FROM t0 WHERE id=1 FOR UPDATE; ← ロック取得
• session 2 : begin;SELECT * FROM t0 WHERE id=2-1 FOR UPDATE; ← session 1で待機

• session 3 : begin;SELECT * FROM t0 WHERE id=3-2 FOR UPDATE; ← session 1で待機

• session 4 : begin;SELECT * FROM t0 WHERE id=2 FOR UPDATE; ← ロック取得

• session 5 : begin;SELECT * FROM t0 WHERE id=4-2 FOR UPDATE; ← session 4で待機

でさっきのSQLを実行

*************************** 1. row ***************************
           wait_pidlist:
                         8:SELECT * FROM t0 WHERE id=2-1 FOR UPDATE,
                         17:SELECT * FROM t0 WHERE id=3-2 FOR UPDATE
           blocking_pid: 5
                killsql: KILL QUERY 5
         blocking_query: NULL
     blocking_thread_id: 30
blocking_thread_sqlinfo:
                         35:SELECT * FROM t0 WHERE id=1 FOR UPDATE
*************************** 2. row ***************************
           wait_pidlist:
                         6:SELECT * FROM t0 WHERE id=4-2 FOR UPDATE
           blocking_pid: 7
                killsql: KILL QUERY 7
         blocking_query: NULL
     blocking_thread_id: 28
blocking_thread_sqlinfo:
                         27:SELECT * FROM t0 WHERE id=2 FOR UPDATE

blocking_thread_sqlinfoがロックを取得しているSQL。
ロックを取得してるセッションをkillするにはkill sqlを実行すればOK。
現在進行系のものであればとれるけど、過去のものはわからない。