MySQL Innodb如何找出阻塞事务之定位问题

查看正在执行的事务

-- 查看 所有mysql 进程id
show full PROCESSLIST;

#查询 正在执行的事务：
SELECT * FROM information_schema.INNODB_TRX;
kill thread_id;

#查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

#查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

事务出错日志

解说详情日志

SHOW ENGINE INNODB STATUS;

堵塞日志

数据库连接未关闭问题的过程 查看

select * from performance_schema.threads where PROCESSLIST_ID=1227425559;
select * from performance_schema.threads where processlist_id=60178157;

SELECT * FROM performance_schema.events_statememnts_current where thread_id =1227425559;

MySQL中一些查看事务和锁情况的常用语句

详情铁铲博客

#查看某段时间以来未关闭事务
SELECT
        trx_id,
        trx_started,
        trx_mysql_thread_id
FROM
        INFORMATION_SCHEMA.INNODB_TRX
WHERE
        trx_started < date_sub(now(), INTERVAL 1 MINUTE)
AND trx_operation_state IS NULL
AND trx_query IS NULL;


select * from information_schema.innodb_lock_waits;


#查看未关闭的事务：
SELECT
     a.trx_id,
     a.trx_state,
     a.trx_started,
     a.trx_query,
     b.ID,
     b. USER,
     b. HOST,
     b.DB,
     b.COMMAND,
     b.TIME,
     b.STATE,
     b.INFO
FROM
     information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
WHERE
     b.COMMAND = 'Sleep';

通过查询information_schema数据库下与事务相关的几个系统表

引用潇湘隐者的博客

SELECT b.trx_mysql_thread_id             AS 'blocked_thread_id'
      ,b.trx_query                      AS 'blocked_sql_text'
      ,c.trx_mysql_thread_id             AS 'blocker_thread_id'
      ,c.trx_query                       AS 'blocker_sql_text'
      ,( Unix_timestamp() - Unix_timestamp(c.trx_started) )
                              AS 'blocked_time'
FROM   information_schema.innodb_lock_waits  a
    INNER JOIN information_schema.innodb_trx b
         ON a.requesting_trx_id = b.trx_id
    INNER JOIN information_schema.innodb_trx c
         ON a.blocking_trx_id = c.trx_id
WHERE  ( Unix_timestamp() - Unix_timestamp(c.trx_started) ) > 4;

SELECT a.sql_text,
       c.id,
       d.trx_started
FROM   performance_schema.events_statements_current a
       join performance_schema.threads b
         ON a.thread_id = b.thread_id
       join information_schema.processlist c
         ON b.processlist_id = c.id
       join information_schema.innodb_trx d
         ON c.id = d.trx_mysql_thread_id
where c.id=17
ORDER  BY d.trx_started;

某些mysql8.0数据库查看等待事务出错

 Unknown table 'INNODB_LOCK_WAITS' in information_schema

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-table.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-lock-waits-table.html

待续 。。。。上面并没有解决我问题，一直运行的事务的原因。。。