1
2
3
4
5
6
7
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;
1
2
3
4
5
6
7
8
9
10
11
12
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=884
ORDER BY  d.trx_started\G;
1
2
3
4
5
6
7
8
9
10
11
12

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;
+-------------------+-------------------------------------------+-------------------+------------------+--------------+ blocked_thread_id blocked_sql_text blocker_thread_id blocker_sql_text blocked_time +-------------------+-------------------------------------------+-------------------+------------------+--------------+ 882 select * from lockr where i=10 for update 884 NULL 45 +-------------------+-------------------------------------------+-------------------+------------------+--------------+ 1 row in set, 1 warning (0.00 sec)

root@db 13:46: [(none)]> 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=884 -> ORDER BY d.trx_startedG; *************************** 1. row *************************** sql_text: select @@version id: 884 trx_started: 2019-04-23 13:45:27 1 row in set (0.01 sec)

原文作者: liups.com

原文链接: http://liups.cn/posts/13f961ef/

许可协议: 知识共享署名-非商业性使用 4.0 国际许可协议