首页  

MySQL查看事务和锁状态     所属分类 mysql 浏览量 195
information_schema.innodb_lock_waits
information_schema.innodb_trx
information_schema.PROCESSLIST

PERFORMANCE_SCHEMA.threads
PERFORMANCE_SCHEMA.events_statements_current 

select version()

select now()

show processlist;  
显示正在运行的线程  只列出前100条

show full processlist;

show open tables;

show open tables from database;

show open tables from database;

show status like ‘%lock%'

show engine innodb status;

show variables like ‘%timeout%';
查看配置参数




查看事务等待情况 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 b.trx_state, e.state, e.time, d.state AS block_state, d.time AS block_time, a.requesting_trx_id, a.requested_lock_id, b.trx_query, b.trx_mysql_thread_id, a.blocking_trx_id, a.blocking_lock_id, c.trx_query AS block_trx_query, c.trx_mysql_thread_id AS block_trx_mysql_tread_id FROM information_schema.INNODB_LOCK_WAITS a LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id ORDER BY a.requesting_trx_id; 查看未关闭的事务 SELECT a.trx_id, a.trx_state, a.trx_started, a.trx_query, b.ID, b.USER, b.DB, b.COMMAND, b.TIME, b.STATE, b.INFO, c.PROCESSLIST_USER, c.PROCESSLIST_HOST, c.PROCESSLIST_DB, d.SQL_TEXT FROM information_schema.INNODB_TRX a LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id AND b.COMMAND = 'Sleep' LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
mysql show processlist State 含义

上一篇     下一篇
flink广播变量简介

spring事务隔离级别设置

mysql show processlist State 含义

flink dataset 测试数据说明

flink datasset sortPartition 实例

flink dataset groupBy sortGroup 实例