Mysql 找出未提交事务的SQL及死锁

未提交事务：

通过查看information_schema.INNODB_TRX视图,您可以了解当前系统中正在运行的事务情况,从而进行问题排查和性能优化。

SELECT * FROM information_schema.innodb_trx;

通过trx_state为RUNNIG,trx_started判断是否有一直RUNNING的事务。

如果有未提交事务，查询具体执行的sql如下:

SELECT t.trx_mysql_thread_id                        AS connection_id
      ,t.trx_id                                     AS trx_id          
      ,t.trx_state                                  AS trx_state        
      ,t.trx_started                                AS trx_started     
      ,TIMESTAMPDIFF(SECOND,t.trx_started, now())   AS "trx_run_time(s)"  
      ,t.trx_requested_lock_id                      AS trx_requested_lock_id
      ,t.trx_operation_state                        AS trx_operation_state
      ,t.trx_tables_in_use                          AS trx_tables_in_use
      ,t.trx_tables_locked                          AS trx_tables_locked
      ,t.trx_rows_locked                            AS trx_rows_locked
      ,t.trx_isolation_level                        AS trx_isolation_level
      ,t.trx_is_read_only                           AS trx_is_read_only
      ,t.trx_autocommit_non_locking                 AS trx_autocommit_non_locking
      ,e.event_name                                 AS event_name
      ,e.timer_wait / 1000000000000                 AS timer_wait
      ,e.sql_text 
FROM   information_schema.innodb_trx t, 
       performance_schema.events_statements_current e, 
       performance_schema.threads c 
WHERE  t.trx_mysql_thread_id = c.processlist_id 
   AND  e.thread_id = c.thread_id;

结果如下：

死锁：

查看Lock的Table：

SELECT * FROM performance_schema.data_locks;

也可以通过show engine innodb status; 查看详细DEADLOCK信息。