MYSQL长事务排查

查看造成等待的事务执行SQL

SELECT
  tmp.waiting_thread '等待线程 ID',
  tmp.waiting_trx_id '等待事务 ID',
  tmp.wating_trx_state 等待事务状态,
  tmp.waiting_query 等待语句,
  case 
	when (tmp.waiting_time - 28800) > 0 then (tmp.waiting_time - 28800)
	else tmp.waiting_time
  end as 等待时间,
  p.HOST 阻塞连接,
  tmp.blocking_thread '阻塞线程 ID',
  tmp.blocking_trx_id '阻塞事务 ID',
  tmp.blocking_trx_state 阻塞事务状态,
  case 
	when (tmp.blocking_exe_time - 28800) > 0 then (tmp.blocking_exe_time - 28800)
	else tmp.blocking_exe_time
  end as 阻塞事务运行时间,
  IFNULL( blocking_query, c.SQL_Text ) 阻塞语句,
  c.current_schema 阻塞表
FROM
  (
  SELECT
    r.trx_state wating_trx_state,
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_Id waiting_thread,
    r.trx_query waiting_query,
    b.trx_state blocking_trx_state,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query,
	timestampdiff( SECOND, r.trx_wait_started, CURRENT_TIMESTAMP ()) as waiting_time,
	timestampdiff( SECOND, r.TRX_STARTED, CURRENT_TIMESTAMP ()) as blocking_exe_time
  FROM
    information_schema.innodb_lock_waits w
    INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
    INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id 
  ) tmp
  LEFT JOIN information_schema.PROCESSLIST p ON tmp.blocking_thread = p.id
  LEFT JOIN PERFORMANCE_SCHEMA.threads t ON t.PROCESSLIST_ID = p.id
  LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current c ON t.thread_id = c.THREAD_ID

查看长事务的连接信息

SELECT
  t.thread_id 线程ID,
  t.processlist_id 会话ID,
  t.processlist_user 用户,
  t.processlist_host 用户地址,
  t.processlist_db 数据库,
  p.command 会话状态,
  e.STATE 事务状态,
  e.timer_wait 事务持续时长,
  p.current_statement 执行语句
FROM
  PERFORMANCE_SCHEMA.events_transactions_current  e
  left JOIN PERFORMANCE_SCHEMA.threads t on t.thread_id = e.thread_id
  LEFT JOIN sys.PROCESSLIST p ON p.thd_id = t.thread_id
WHERE
  t.type = "FOREGROUND"
  and e.STATE = "ACTIVE"
ORDER BY
  e.timer_wait DESC;

　查看事务相关信息

SELECT
  b.id 会话ID,
  b.USER 用户名,
  b.HOST 连接地址,
  b.db 数据库,
  a.TRX_STATE 事务状态,
  a.trx_tables_locked 表锁数量,
  a.trx_rows_locked 行锁数量,
  d.SQL_TEXT 已执行语句,
  a.TRX_QUERY 正在执行语句,
    (
    UNIX_TIMESTAMP(
    now()) - UNIX_TIMESTAMP( a.trx_started )) 事务持续时间
FROM
  information_schema.innodb_trx a
  INNER JOIN information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id 
  AND b.command = 'Sleep'
  INNER JOIN PERFORMANCE_SCHEMA.threads t ON b.id = t.PROCESSLIST_ID
  INNER JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = t.THREAD_ID

查看历史事务语句

SELECT
  ps.id 'PROCESS ID',
  ps.USER,
  ps.HOST,
  esh.EVENT_ID,
  trx.trx_started,
  esh.event_name 'EVENT NAME',
  esh.sql_text 'SQL',
  ps.TIME 
FROM
  PERFORMANCE_SCHEMA.events_statements_history esh
  JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id
  JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id
  LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id 
WHERE
  trx.trx_id IS NOT NULL 
  AND ps.USER != 'SYSTEM_USER' 
ORDER BY
  esh.EVENT_ID