mysql 查询开启事务

select @@pseudo_thread_id

88055009

SELECT
    NOW(),  
   (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,
    b.id,
    b.user,
    b.host,
    b.db,
    a.trx_query,
    b.COMMAND
FROM
    information_schema.innodb_trx a
        INNER JOIN
    information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id and b.COMMAND<>'Sleep';}

SELECT
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    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
 
 
 
select  NOW(),  trx_started,
   (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec ,
   a.TRX_MYSQL_THREAD_ID,
   a.trx_state from information_schema.INNODB_TRX a
   
   
   
mysql> select * from information_schema.PROCESSLIST where id='88055009';
+----------+--------+--------------------+-------+---------+------+-------+------+
| ID       | USER   | HOST               | DB    | COMMAND | TIME | STATE | INFO |
+----------+--------+--------------------+-------+---------+------+-------+------+
| 88055009 | dbsnmp | 1.1.101.231:59800 | rcedb | Sleep   |  260 |       | NULL |
+----------+--------+--------------------+-------+---------+------+-------+------+
1 row in set (0.00 sec)

mysql> select * from information_schema.PROCESSLIST where id='88055009';
+----------+--------+--------------------+-------+---------+------+-------------------+-------------------------------------------+
| ID       | USER   | HOST               | DB    | COMMAND | TIME | STATE             | INFO                                      |
+----------+--------+--------------------+-------+---------+------+-------------------+-------------------------------------------+
| 88055009 | dbsnmp | 1.1.101.231:59800 | rcedb | Query   |    6 | Sending to client | select * from t_application_broadcast_log |
+----------+--------+--------------------+-------+---------+------+-------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_trx where TRX_MYSQL_THREAD_ID='88055009';
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id          | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query                                 | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 421646043876064 | RUNNING   | 2022-02-24 22:17:42 | NULL                  | NULL             |          0 |            88055009 | select * from t_application_broadcast_log | NULL                |                 1 |                 0 |                0 |                  1136 |               0 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                1 |                          1 |
+-----------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->     NOW(),  
    ->    (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,
    ->     b.id,
    ->     b.user,
    ->     b.host,
    ->     b.db,
    ->     a.trx_query,
    ->     b.COMMAND
    -> FROM
    ->     information_schema.innodb_trx a
    ->         INNER JOIN
    ->     information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id and b.COMMAND<>'Sleep';
+---------------------+----------+----------+--------+--------------------+-------+-------------------------------------------+---------+
| NOW()               | diff_sec | id       | user   | host               | db    | trx_query                                 | COMMAND |
+---------------------+----------+----------+--------+--------------------+-------+-------------------------------------------+---------+
| 2022-02-24 22:18:52 |       70 | 88055009 | dbsnmp | 1.1.101.231:59800 | rcedb | select * from t_application_broadcast_log | Query   |
+---------------------+----------+----------+--------+--------------------+-------+-------------------------------------------+---------+
1 row in set (0.00 sec)