获取mysql锁超时的会话

CREATE PROCEDURE `kill_select_timeout_transaction`()
    COMMENT '获得导致行锁等待和行锁等待超时的会话'
BEGIN
    select l.* from ( select 'Blocker' role,    p.id,    p.user,    left(p.host, locate(':', p.host) - 1) host,
    tx.trx_id,    tx.trx_state,    tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index,
    tx.trx_query,    lw.requested_lock_id Blockee_id,    lw.requesting_trx_id Blockee_trx from    information_schema.innodb_trx tx,    
    information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo,    information_schema.processlist p where    
    lw.blocking_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.blocking_lock_id union 
    select    'Blockee' role,    p.id,    p.user,    left(p.host, locate(':', p.host) - 1) host,    tx.trx_id,    tx.trx_state,    
    tx.trx_started, timestampdiff(second, tx.trx_started, now()) duration, lo.lock_mode, lo.lock_type, lo.lock_table, lo.lock_index,    
    tx.trx_query,    null,    null from    information_schema.innodb_trx tx,    information_schema.innodb_lock_waits lw, information_schema.innodb_locks lo,    
    information_schema.processlist p where    lw.requesting_trx_id = tx.trx_id and p.id = tx.trx_mysql_thread_id and lo.lock_id = lw.requested_lock_id) l order by role desc, trx_state desc;
END;