Mysql长事务总结

select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time 
from INFORMATION_SCHEMA.INNODB_TRX t 
order by idle_time desc limit 5\G

------------
TRANSACTIONS
------------

---TRANSACTION 906574, ACTIVE 383 sec
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 71, OS thread handle 139874604435200, query id 9040045 172.17.0.1 server_234

select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT 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 c ON b.id = c.PROCESSLIST_ID
inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.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'
  AND ps.id = 进程ID
ORDER BY
  esh.EVENT_ID;

mysql> select * from sbtest5 where id<1000 and id > 500 for update;

SELECT
  tmp.*,
  c.SQL_Text blocking_sql_text,
  p.HOST blocking_host
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
  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,
  information_schema.PROCESSLIST p,
  performance_schema.events_statements_current c,
  performance_schema.threads t
WHERE
  tmp.blocking_thread = p.id
  AND t.thread_id = c.THREAD_ID
  AND t.processlist_id = p.id \G

#!/bin/bash

mysql -N -h127.0.0.1 -userver_234 -pserver_234 -P8002 -e "select now(),(unix_timestamp(now()) - unix_timestamp(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.sql_text 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 c on b.id = c.processlist_id
inner join performance_schema.events_statements_current d on d.thread_id = c.thread_id;" | while read a b c d e f g h
do
if [ "$c" -gt 30 ]
then
echo $(date +"%y-%m-%d %h:%m:%s")
echo "processid[$d] $e@$f in db[$g] hold transaction time $c sql:$h"
fi
done >> /tmp/longtransaction.txt

[root@iZbp157gcce2s43exn8ad6Z my_57_ins_8002]# cat /tmp/longtransaction.txt
20-06-22 Jun:06:1592804313
processid[71] server_234@172.17.0.1:49316 in db[server_234_db] hold transaction time 379 sql:select * from sbtest5 where id<1000 and id > 500 for update
20-06-22 Jun:06:1592804604
processid[71] server_234@172.17.0.1:49316 in db[server_234_db] hold transaction time 670 sql:select * from sbtest5 where id<1000 and id > 500 for update