MySQL 阻塞查询

蓝皮书Comments are off for this post.

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=884
ORDER BY d.trx_started\G;

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

ERROR:
No query specified