|
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)
?
|